CODEWITHSUNDEEP
phppythonmysqlbinaryhex
ThriceGood
ThriceGood

Reputation: 1703

inserting into MySQL binary(8) field with Python

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.

Upvotes: 0

Views: 184

Answers (1)

t.m.adam
t.m.adam

Reputation: 15376

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'

Upvotes: 1

Related Questions