How to store and query hex values in mysqldb

Question

I want to use a thermal printer with raspberry pi. I want to receive the printer vendor id and product id from mysql database. My columns are of type varchar.

My code is

import MySQLdb
from escpos.printer import Usb
db= MySQLdb.connect(host=HOST, port=PORT,user=USER, passwd=PASSWORD, db=database)
cursor = db.cursor()
sql = ("select * from printerdetails")
cursor.execute(sql)
result = cursor.fetchall()
db.close()
for row in result:
    printer_vendor_id = row[2]
    printer_product_id = row[3]
    input_end_point = row[4]
    output_end_point = row[5]
print printer_vendor_id,printer_product_id,input_end_point,output_end_point
Printer = Usb(printer_vendor_id,printer_product_id,0,input_end_point,output_end_point)
Printer.text("Hello World")
Printer.cut()

but it doesnot work. the id's are string. print command shows 0x154f 0x0517 0x82 0x02.in my case

Printer = Usb(0x154f,0x0517,0,0x82,0x02)

works fine.How could I store the same id's to the database and use them to configure the printer

Answer 1

Your problem is that your call to Usb is expecting integers, which works if you call it like this

Printer = Usb(0x154f,0x0517,0,0x82,0x02)

but your database call is returning tuples of hexadecimal values stored as strings. So you need to convert those strings to integers, like this:

for row in result:
    printer_vendor_id = int(row[2],16)
    printer_product_id = int(row[3],16)
    input_end_point = int(row[4],16)
    output_end_point = int(row[5],16)

Now if you do

print printer_vendor_id,printer_product_id,input_end_point,output_end_point

you will get

(5455, 1303, 130, 2)

which might look wrong, but isn't, which you can check by asking for the integers to be shown in hex format:

print ','.join('0x{0:04x}'.format(i) for i in (printer_vendor_id,printer_product_id,input_end_point,output_end_point))

0x154f,0x0517,0x0082,0x0002

I should point out that this only works because your database table contains only one row. for row in result loops through all of the rows in your table, but there happens to be only one, which is okay. If there were more, your code would always get the last row of the table, because it doesn't check the identifier of the row and so will repeatedly assign values to the same variables until it runs out of data.

The way to fix that is to put a where clause in your SQL select statement. Something like

"select * from printerdetails where id = '{0}'".format(printer_id)

Now, because I don't know what your database table looks like, the column name id is almost certainly wrong. And very likely the datatype also: it might very well not be a string.