使用python和MySQLdb将多个字典对象插入MySQL数据库 [英] Inserting multiple dictionary objects into a MySQL database using python and MySQLdb
问题描述
我一直在努力工作数小时,我觉得现在哭泣,因为我无法知道发生了什么。
这里是一个简化的版本我的资料:
mydata = [{'id':123,'thing':'ghi','value':1 },{'id':456,'thing':'xyz','value':0}]
这是我有的代码:
import MySQLdb as mdb
/ pre>
con = None
con = mdb.connect('localhost','testing','anothervalue','andanother');
cur = con.cursor()
sql =INSERT INTO`tablename`(`id`,`thing`,`value`)VALUES事件),%(值)s)
cur.executemany(sql,(我的mydata中的我的))
con.close()
我预期会发生的是,2行会插入 tablename 。实际发生的是脚本执行没有任何错误,没有行插入。
我做错了什么?如果我手动插入一个单独的INSERT插入到表中,所以我想知道它不是MySQL数据库的问题,而是我如何将变量传递到数据库。
另一个问题是,如何插入值作为浮点数?目前我在表中将值定义为 TINYTEXT NOT NULL ,我希望这是 FLOAT NOT NULL ,但我'不知道如何处理上面的子类。
解决方案没有必要使用生成器循环
mydata
。直接传入mydata
:cur.executemany(sql,mydata )
数据库适配器将循环
mydata
对于你和一个生成器表达式只插入一个不必要的额外循环。
如果你没有收到任何错误消息,但没有更改,请检查以下内容:
-
确保提交事务;在
.executemany()
调用后运行con.commit()
。 -
mydata / ul>
数据库适配器将正确处理您的浮点值;如果一个列被标记为
FLOAT NOT NULL
,并且传递该列的Python浮点值,Things Just Work。这就是SQL参数,正确处理不同数据类型的引用。I have been struggling with this for hours and I feel like crying now as I'm unable to fathom out what is happening.
Here is a simplified version of my data:
mydata = [ { 'id': 123, 'thing': 'ghi', 'value': 1 }, { 'id': 456, 'thing': 'xyz', 'value': 0 } ]
This is the code I have:
import MySQLdb as mdb con = None con = mdb.connect('localhost', 'testing', 'anothervalue', 'andanother'); cur = con.cursor() sql = "INSERT INTO `tablename` ( `id`, `thing`, `value` ) VALUES ( %(id)s, %(thing)s, %(value)s )" cur.executemany( sql, ( mine for mine in mydata ) ) con.close()
What I expected to happen was that 2 rows would be inserted into tablename. What actually happens is that the script executes without any errors and no rows are inserted.
What am I doing wrong? If I do a single INSERT by hand it inserts into the table properly so I think know its not a problem with the MySQL database but rather how I'm passing the variables into the database.
An additional question I have is how to I insert value as a float? At the moment I have the definition of value in the table as TINYTEXT NOT NULL, I would like this to be FLOAT NOT NULL but I'm not sure how to handle the substition above.
解决方案There is no need to use a generator to loop over
mydata
. Just pass inmydata
directly:cur.executemany(sql, mydata)
The database adapter will loop over
mydata
for you and a generator expression only inserts a needless extra loop.If you do not get any error messages but there are no changes either, check the following:
Make sure you commit the transaction; run
con.commit()
after the.executemany()
call.Tripple-check that
mydata
is not empty.
The database adapter will correctly handle float values for you; if a column is marked as
FLOAT NOT NULL
and you pass in a Python float value for that column, Things Just Work. That's what SQL parameters are for, handling quoting of different datatypes correctly.这篇关于使用python和MySQLdb将多个字典对象插入MySQL数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!