使用MySQLdb将整数和字符串添加到MySQL服务器

Question

我正在使用 MySQLdb 将大量数据从文本文件上传到 MySQL 服务器。如果我手动准备一个字符串，比如 'as', '123', 12, 23，这没问题，但我不知道怎么通过循环一个列表来生成这样的字符串，因为我需要把字符串和整数连接起来。

一个有效的插入语句示例如下：

""" INSERT INTO ACS(ST, CODE, BC001, BC002, BC003) 
VALUES ('AK', '1234567', 20, 30, 40)""" 

这是我尝试从列表生成这个语句的方法：

import MySQLdb

# sample data
table = 'TestTable'
header = ['ST', 'CODE', 'BC001', 'BC002', 'BC003']
values = [['AA', '1234567', 20, 30, 40], ['BB', '1234567', 20, 30, 40], ['CC', '1234567', 20, 30, 40],['DD', '1234567', 20, 30, 40]]


# local SQL server on my computer
db = MySQLdb.connect (host = 'localhost', user = 'root', passwd = '', db = 'test')
# prepare a cursor object using cursor() method
cursor = db.cursor()

# header columns
sql1 = '('
for i in range(len(header)):
    sql1 += header[i] + ','
sql1 = 'INSERT INTO ' + table + sql1[:-1] + ')'

# now loop through data values and combine with header each time
for i in range(len(values)):

    sql2 = ''
    for j in range(len(values[i])):
        sql2 += values[i][j] + ','        #error occurs here

    # structure: sql2 = """ VALUES ('AA', '1234567', 20, 30, 40)"""
    sql2 = 'VALUES ' + table + sql2[:-1] + ')'     
    sql = sql1 + sql2

    try:
       # Execute the SQL command
       cursor.execute(sql)
       # Commit your changes in the database
       db.commit()
    except:
       # Rollback in case there is any error
       db.rollback()

# disconnect from server
db.close()

我收到的错误信息是

TypeError: unsupported operand type(s) for +: 'int' and 'str'

我明白为什么会出现这个错误，但我找不到其他生成这个字符串的方法。有没有更好的办法来生成这些字符串呢？

我在 Win7 64 位上使用 Python27。

Answer 1

其他帖子已经正确指出了错误，但没有回答你关于更好字符串格式化的方法的问题。

我不确定这个格式化是否完全符合你的要求，因为你的代码有点难以阅读。你最终会得到像这样的查询：

INSERT INTO TestTable (ST,CODE,BC001,BC002,BC003) VALUES ('AA','1234567',20,30,40)

这是代码：

sql = (('INSERT INTO {0} ({1}) VALUES ({2})'.format(table, ','.join(header), 
                         ','.join(("'" + v + "'" if isinstance(v, str) 
                             else str(v)) for v in val))) for val in values)

然后你只需要这样做：

for q in sql:
    try:
        # Execute the SQL command
        cursor.execute(q)
        # Commit your changes in the database
        db.commit()
    except:
        # Rollback in case there is any error
        db.rollback()

它会给字符串加上引号，并使用 str.join 和 str.format。

其实，你应该使用参数化查询，这样可以把值的处理交给数据库连接器或者数据库本身。

Answer 2

你有没有试过把 values[i][j] 用 str() 包裹起来，比如 str(values[i][j])？

看起来你也可以这样做：

sql2 = ','.join([str(x) for x in values[i]]) + ','

如果你想让它更简单一点的话。（不确定最后的逗号是否必要，但我加上它是为了和你的代码保持一致。）

举个例子：

>>> my_list = ['abc', 1, 2,3, 'do','re','mi']
>>> [str(x) for x in my_list]
['abc', '1', '2', '3', 'do', 're', 'mi']
>>> ','.join([str(x) for x in my_list])
'abc,1,2,3,do,re,mi'

另一方面，如果你需要在最终的查询中把原本是字符串的对象用单引号包起来，看起来你确实需要做一些更复杂的操作，比如：

sql2 = ''
for x in values[i]:
    if isinstance(x, basestring):
        sql2 += "'"+x+"',"
    else:
        sql2 += str(x) +","

Answer 3

MySQLdb已经可以做到这一点，你不需要手动转换你的变量类型。

你想要这样做，因为MySQLdb会自动为你加上引号，并在一定程度上保护你免受SQL注入攻击。这在实际工作中非常重要，如果你打算做任何专业的数据库工作，这点你应该习惯。

使用MySQLdb的游标对象时，execute()命令会自动把你的整数、字符串和其他变量格式化好，这样它们就能在你的INSERT语句中正常工作。它们是通过一种特殊的格式字符串来实现的。

c=db.cursor()
max_price = 5
min_price = 1
c.execute("""SELECT spam, eggs, sausage FROM breakfast
          WHERE price < %s AND price > %s""", (max_price, min_price))

注意两点：首先，所有变量，无论类型如何，都应该在这些特殊格式字符串中表示为%s。这就是它的工作方式。第二，传给execute()的第二个参数应该是一个元组，所以如果你只有一个变量要用，你需要把它写成(max_price,)。括号后面的逗号告诉Python这是一个元组。