python - DatabaseError : Execution failed on sql, 无法将元组连接到字节

Question

我正在运行以下代码:

db = pymysql.connect(host=host, database=db_name, user=user, password=password)
batchsize = 100
for offset in range(0,1000,batchsize):
     df = pd.read_sql(('SELECT * FROM anime LIMIT %s OFFSET %s', (batchsize,offset)), con=db)
     print("rows and columns: ",df.shape)

但它在第 4 行抛出以下错误:

can't concat tuple to bytes

欢迎任何建议。

Answer 1

您使用元组的 SQL 字符串格式错误。

更改:

df = pd.read_sql(('SELECT * FROM anime LIMIT %s OFFSET %s', (batchsize,offset)), con=db)

致:

df = pd.read_sql(('SELECT * FROM anime LIMIT %s OFFSET %s' % (batchsize,offset)), con=db)

使用元组绑定(bind)值时，必须在格式化字符串和元组之间使用 %。

>>> k = "uid"
>>> v = "sa"
>>> "%s=%s", (k, v)  
('%s=%s', ('uid', 'sa'))  
>>>   
>>> "%s=%s" % (k, v)
'uid=sa'

The whole expression evaluates to a string.
The first %s is replaced by the value of k;
the second %s is replaced by the value of v.
All other characters in the string (in this case, the equal sign) stay as they are.
Note that (k, v) is a tuple.

阅读:

• Python Library Reference: String Formatting Operations
• Dive Into Python: Formatting Strings