inserting into MySQL binary(8) field with Python

Question

I am trying to rewrite the following PHP code in Python but am not sure how to do it. My experience with bytes, hexadecimal and binary is almost none and I am finding this to be very confusing.

        $code_string = "0000000000000248";     
        $code_array = array();
        $tokens = str_split($code_array, 2);

        foreach ($tokens as $token) {
            $byte = hexdec($token);
            array_push($code_array, $byte);
        }

        $code_blob = pack("C*", 
                $code_array[0], 
                $code_array[1], 
                $code_array[2], 
                $code_array[3],
                $code_array[4], 
                $code_array[5], 
                $code_array[6], 
                $code_array[7]
            );

        $sql = "INSERT INTO some_table (code) VALUES ($code_blob)"

The "code" field is a MySQL binary(8) type. When the above code is ran in PHP the code is insert with no issues. In phpMyAdmin when I look at the record it is represented as "0000000000000248".

I'm not sure if this is helpful information but when i print the tokens one by one they look like this

        foreach ($tokens as $token) {
            $byte = hexdec($token);
            array_push($code_array, $byte);
            echo "$byte ";
        }

        // looks like 0 0 0 0 0 0 2 72

When I print the SQL it looks like this

    INSERT INTO some_table (code) VALUES ('H')

I have no idea how to do this in Python (3.4)

Thanks to t.m.adam for the correct answer. Just as a note, to insert it into the database you must do it like this

    sql = "INSERT INTO some_table (code) VALUES (%s)"
    cursor.execute(sql, (code_blob,))
    db.commit()

If you put the blob directly into the string in Python the string will look like b'...' and break the SQL.

t.m.adam · Accepted Answer · 2017-05-10 10:19:23Z

1

In your PHP code you make an array of decimals from $code_string with the hexdec function, then use this array to make a string of chars with the pack function .
Python equivalent :

from struct import pack

code_string = '0000000000000248'
code_array = [ int(code_string[i:i+2], 16) for i in range(0, len(code_string), 2) ]
code_blob = pack('B'*len(code_array), *code_array)
#code_blob = ''.join( chr(i) for i in code_array )  ## pack alternative ##

Result :

print(code_array)
print(code_blob)

[0, 0, 0, 0, 0, 0, 2, 72]
'      ☻H'

edited May 10, 2017 at 10:19

answered May 8, 2017 at 5:20

t.m.adam

15.4k3 gold badges34 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

inserting into MySQL binary(8) field with Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related