pymysql connection select query and fetchall retur

Question

I have a python script that runs fine when only connecting to my local test machine having MySQL 5.6database on Windows 8.1, using pymysql connection. Select query / fetchal() returns tuples like ('1', '2015-01-02 23:11:19', '25.00').

However, when I use the same script slightly modified to include a second connection to a remote MySQL 5.0.96 production database running on a Linux server, it returns tuples like (b'1', b'2015-01-02 23:11:19', b'25.00') and the script does not run correctly as match conditions and queries using the returned tuples fail.

Any idea why, and how can I make it return the tuples with column values that have no "b" prefix?

Answer 1

I resolved this issue with the following work around. It involved processing the returned byte literals from the remote database columns as shown in the example below that I created to explain the answer.

conn = pymysql.connect(host=myHost, port=myPort, user=myUser, passwd=myPassword, database=myDatabase, charset="utf8")
cur = conn.cursor()

theDateTime = re.sub( r' ', '-', str(datetime.now()))
theDateTime = theDateTime[0:10] + " " + theDateTime[11:19]

productName = 'abc'
myMaxPrice = 100.0

try:
    # The below query to the remote database returned tuples like (b'1', b'2015-01-02 23:11:19', b'25.00') for MySQL DB tableA columns: ID, date_changed, price
    query = "SELECT IFNULL(ID,''), IFNULL(date_changed,''), IFNULL(price, '') FROM tableA WHERE product = '" + productName + "';"   
    cur.execute(query)
    for r in cur.fetchall():
        # Given the returned result tuple r[] from the remote DB included byte literals instead of strings, I had to encode the '' strings in the condition below to make them byte literals
        # However, I did not have to encode floats like mMaxyPrice and float(r[2]) as they were not a string; float calculations were working fine, even though the returned float values were also byte literals within the tuple
        if not r[1] and float(r[2]) >= myMaxPrice: 
            #Had to encode and then decode r[0] below due to the ID column value r[0] coming back from the remote DB query / fetchall() as a byte literal with a "b" prefix
            query = "UPDATE tableA SET date_changed = '" + theDateTime + "', price = " + str(myMaxPrice) + " WHERE ID = " + r[0].decode(encoding='utf-8') + ";"  
            cur.execute(query)
            conn.commit()
except pymysql.Error as e:
    try:
        print("\nMySQL Error {0}: {1}\n".format(e.args[0], e.args[1]))
    except IndexError:
        print("\nMySQL Index Error: {0}\n".format(str(e)))
    print("\nThere was a problem reading info from the remote database!!!\n") 

Thanks to m170897017 for pointing out these are byte literals and to Neha Shukla for helping clarify. I would still be interested though in figuring out why the remote database returned byte literals, rather than strings that the local database returned. Is there a certain encoding I need to use for the connection to the remote DB and how? Is it the older version of MySQL used in the remote database that caused it? Is it the difference of Linux remote vs Windows local? Or was it the fetchall() function that introduced the byte literals? If anyone knows, please pitch it to help me understand this further.

Answer 2

b prefix means byte literal in Python3. Try to convert it to string.

...
res = (b'1', b'2015-01-02 23:11:19', b'25.00')
new_res = []
for i in res:
    new_res.append(i.decode(encoding='utf-8'))

new_res = tuple(new_res)
...