Python mysql 连接器准备好的 INSERT 语句截断文本

Question

我一直在尝试用 Python 的 mysql.connector 把一个很长的字符串插入到 MySQL 数据库里。遇到的问题是，当我使用预处理语句时，长字符串在某个地方被截断了。我现在用的是 MySQL 官网提供的 MySQL Connector/Python。我用下面的代码来重现我遇到的问题。

db = mysql.connector.connect(**creditials)
cursor = db.cursor()

value = []

for x in range(0, 2000):
    value.append(str(x+1))

value = " ".join(value)

cursor.execute("""
                CREATE TABLE IF NOT EXISTS test ( 
                    pid VARCHAR(50),
                    name VARCHAR(120),
                    data LONGTEXT,
                    PRIMARY KEY(pid)
                )  
                """)
db.commit()


#this works as expected
print("Test 1")
cursor.execute("REPLACE INTO test (pid, name, data) VALUES ('try 1', 'Description', '{0}')".format(value))
db.commit()
cursor.close()


#this does not work
print("Test 2")
cursor = db.cursor(prepared=True)
cursor.execute("""REPLACE INTO test (pid, name, data) VALUE (?, ?, ?)""", ('try 2', 'Description2', value))
db.commit()
cursor.close()

测试1按预期工作，能存储所有数字到2000，但测试2在数字65后就被截断了。我更想使用预处理语句，而不是自己去处理输入的字符串。希望能得到一些帮助。

额外信息：

电脑：Windows 7 64位

Python：在 Python 3.4 和 3.3 上都试过

MYSQL：5.6.17（随 WAMP 一起提供）

库：MySQL Connector/Python

Answer 1

当 MySQL Connector 驱动处理预编译语句时，它使用一种较低级别的二进制协议来逐个传递值给服务器。也就是说，它会告诉服务器这些值是整数（INT）、变长字符串（VARCHAR）还是文本（TEXT）等。这个过程并不是特别智能，因此会出现一些“行为”。在这种情况下，它识别到值是一个 Python 字符串，并告诉 MySQL 这是一个 VARCHAR 值。VARCHAR 值有一个字符串长度限制，这会影响发送到服务器的数据量。更糟糕的是，长值和有限数据类型长度之间的互动可能会导致一些奇怪的行为。

最终，你有几个选择：

1. 使用文件链接对象来处理你的字符串

   MySQL Connector 将文件和类似文件的对象视为 BLOB 和 TEXT（具体取决于文件是以二进制模式还是非二进制模式打开）。你可以利用这一点来实现你想要的行为。

   import StringIO
   
   ...
   
   cursor = db.cursor(prepared=True)
   cursor.execute("""REPLACE INTO test (pid, name, data) VALUES (?, ?, ?)""",
                  ('try 2', 'Description', StringIO.String(value)))
   cursor.close()
   db.commit()
   

2. 不要使用 MySQL Connector 的预编译语句

   如果你在创建游标时不使用 prepared=True 这个参数，它会为每次执行生成完整有效的 SQL 语句。在这种情况下，避免使用 MySQL 的预编译语句并不会让你损失太多。你需要以稍微不同的形式传递 SQL 语句，以获得合适的占位符清理行为。

   cursor = db.cursor()
   cursor.execute("""REPLACE INTO test (pid, name, data) VALUES (%s, %s, %s)""",
                  ('try 2', 'Description', value))
   cursor.close()
   db.commit()
   

3. 使用其他 MySQL 驱动

   有几种不同的 Python MySQL 驱动可供选择：

   • MySQLdb
   • oursql