pymysql连接选择查询和fetchall返回元组，其字节字面量如b'25.00'，而不是字符串如'25 .00' [英] pymysql connection select query and fetchall return tuple that has byte literals like b'25.00' rather than strings like '25.00'

问题描述

当使用pymysql连接仅连接到具有Windows 8.1上的MySQL 5.6数据库的本地测试机时，我的python script运行良好. Select query / fetchal()返回类似('1'，'2015-01-02 23:11:19'，'25 .00')的元组.

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').

但是，当我使用经过稍微修改的相同脚本以包括到在Linux服务器上运行的远程MySQL 5.0.96生产数据库的第二个连接时，它将返回tuples，例如(b'1'，b'2015- 01-02 23:11:19'，b'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.

任何原因，以及如何使它返回带有没有"b"前缀的列值的tuples?

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

推荐答案

我通过以下解决方法解决了此问题.它涉及处理从远程数据库列返回的字节文字，如下面创建的示例中所示，以解释答案.

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") 

感谢m170897017指出这些是字节字面量，并感谢Neha Shukla帮助澄清.我仍然很想知道为什么远程数据库返回字节文字，而不是本地数据库返回的字符串.我需要使用某种编码来连接到远程数据库，如何编码?导致它的是远程数据库中使用的MySQL的较旧版本吗? Linux远程与Windows本地有什么区别?还是引入字节文字的fetchall()函数?如果有人知道，请提出以帮助我进一步理解.

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.

这篇关于pymysql连接选择查询和fetchall返回元组，其字节字面量如b'25.00'，而不是字符串如'25 .00'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！