I am trying to generate a variable that I could use to insert multiple lines into a MySQL database or save to a file.
我正在尝试生成一个变量,用于将多行插入到MySQL数据库或保存到文件中。
As I am new to python my mind is now buzzing with all the new concepts I am having to learn and I'm looking for a little reassurance that my approach is a good one.
由于我是python的新手,现在我的脑子里满是我要学习的新概念,我正在寻找一种让我确信我的方法是好的方法。
The SQL syntax for inserting multiple items into a table is this:
将多个项目插入到表中的SQL语法如下:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data3', 'data4'),
('data5', 'data6'),
('data7', 'data8');
This is the code I have:
这是我的代码:
import shelve
shelf = shelve.open('test.db', flag='r')
sql = "INSERT INTO 'myTableName' ( "
sql += ", ".join(shelf.itervalues().next())
sql = " ) VALUES "
for s in shelf:
sql += "( "
sql += ', '.join(['\'%s\'' % ( value ) for (key, value) in shelf[s].items()])
sql += " ),"
shelf.close()
print sql
It so nearly works (it has a trailing , instead of a ; on the final sql addition) but I'm sure there must be a more concise approach. Can you tell me what it is?
它几乎可以工作(它有一个尾,而不是a;关于最后的sql添加)但是我确信一定有一种更简洁的方法。你能告诉我是什么吗?
1 个解决方案
#1
6
Don't generate SQL from string concatenation. Use SQL parameters instead:
不要从字符串连接生成SQL。使用SQL参数:
cursor = connection.cursor()
cursor.executemany('INSERT INTO 'tablename' ('column1', 'column2') VALUES (%s, %s)',
[sub.values() for sub in shelf.values()])
The database can then reuse the INSERT
statement (it prepares a query plan for it), the database client layer will handle quoting for you, and you prevent SQL injection attacks to boot.
然后,数据库可以重用INSERT语句(它为它准备了一个查询计划),数据库客户端层将为您处理引用,并且您可以防止SQL注入攻击。
#1
6
Don't generate SQL from string concatenation. Use SQL parameters instead:
不要从字符串连接生成SQL。使用SQL参数:
cursor = connection.cursor()
cursor.executemany('INSERT INTO 'tablename' ('column1', 'column2') VALUES (%s, %s)',
[sub.values() for sub in shelf.values()])
The database can then reuse the INSERT
statement (it prepares a query plan for it), the database client layer will handle quoting for you, and you prevent SQL injection attacks to boot.
然后,数据库可以重用INSERT语句(它为它准备了一个查询计划),数据库客户端层将为您处理引用,并且您可以防止SQL注入攻击。