0

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?

Michael D.
  • 5
  • 1
  • 3
  • Do you have same python versions on both systems or a python 2 on one and a python3 on other ? – Serge Ballesta Jan 09 '15 at 06:39
  • I run both scripts on the same win8.1 machine using python 3.4. What changes is the connection to the remote MySQL db at GoDaddy that has the older MySQL version. – Michael D. Jan 09 '15 at 06:43

3 Answers3

0

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)
...
Michael D.
  • 5
  • 1
  • 3
Stephen Lin
  • 4,852
  • 1
  • 13
  • 26
  • tried it and this line: new_res.append(i.decode('utf-8')) gives error: AttributeError: 'str' object has no attribute 'decode' – Michael D. Jan 09 '15 at 07:01
  • @MichaelD. It seems OK from myside. Try another way. Check my update. – Stephen Lin Jan 09 '15 at 07:19
  • I use eclipse with Pydev and it complains: Undefined variable: unicode. Do I need to import something? – Michael D. Jan 09 '15 at 08:10
  • @MichaelD. Check my latest update. Tell my if it works. Since I don't have your code, I can only guess the solution... – Stephen Lin Jan 09 '15 at 08:27
  • I tried the latest and I still get AttributeError: 'str' object has no attribute 'decode'This is weird as it tells me that it is a 'str' object, yet when I try a string replacement function on i.decode(encoding='utf-8') to see if I can get rid of the "b" prefix, then it complains that it is a byte object! I suspect this has to do with the encoding of the connection to the remote machine, because the same code on my local machine returns the strings without the "b" prefix. If we can figure out the right encoding for the connection, then no need to fix it within the code? – Michael D. Jan 09 '15 at 08:43
  • In fact the script includes and uses both connections to the local and the remote machine. The same command on the local machine returns the tuples without the "b" prefix. – Michael D. Jan 09 '15 at 08:52
  • @MichaelD. Yes, you have to check what's the difference between environemnt of local and remote machine. Maybe you need to add # -*- coding: utf-8 -*- at the top of every file. – Stephen Lin Jan 09 '15 at 08:54
  • the part that did not work was appending it to the list. Somehow python 3.4 was giving error messages. However, using this i.decode(encoding='utf-8') within the query string worked and helped. Please go ahead and edit to get rid of the appending part / reflect the below code and I'll subsequently go ahead and accept it. Many thanks for your time trying to help out. – Michael D. Jan 10 '15 at 07:34
0

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.

Michael D.
  • 5
  • 1
  • 3
0

I have this same issue with the pymsql library.

When executing a simple query that should return a string:

SELECT SERVERPROPERTY('productversion') as version

it returned a byte array (byte literal?). Rather than convert it on the python side, I altered the sql query and the library gave me a string to work with:

SELECT cast(SERVERPROPERTY('productversion') as varchar) as version
Tzane
  • 2,752
  • 1
  • 10
  • 21
Johnboy
  • 1
  • 1