当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - Inserting table values from list iteration in Python

问题描述:

I try to iterate from result list and try to insert values in the table exdata but i look every where for the solution but nothing is working for me

if not result:

print 'list is empty'

else:

for i in xrange(0,len(result)):

for item in result[i].iteritems():

query="INSERT INTO exdata (cve_id,severity, published,modified) \

VALUES ('%s', '%s', '%s', '%s')" % (item[i], item[1][0],item[1][1],item[1][2])

cursor.execute(query)

网友答案:

You have to call commit to actually insert the values to the DB.
Also note that your execute is outside the inner loop, so it will execute only the query of the last iteration.

if not result:
    print 'list is empty'
else:
    for i in xrange(0,len(result)):
        for item  in  result[i].iteritems():
            query="INSERT INTO exdata (cve_id,severity, published,modified) \
                   VALUES ('%s', '%s', '%s', '%s')" % (item[i], item[1][0],item[1][1],item[1][2])
            cursor.execute(query)

            cursor.commit()
网友答案:

if you use MySQLdb and want to do a multi-row insert, try to use executemany:

import MySQLdb

db = MySQLdb.connect("myhost", "myuser", "mysecret", "mydb")
# users is a list of (userid, first_name, last_name, company)
c = db.cursor()
c.executemany("""INSERT INTO users
(userid, first_name, last_name, company)
VALUES (%s, %s, %s, %s)""", users)
db.commit()
db.close()

In MySQLdb, this is converted internally to a multi-row INSERT, which is reported to be 2-3 orders of magnitude faster.

网友答案:

I see 2 issues in your code:

1) need to execute query in loop
2) Add commit statement for fixing changes

if not result:
    print 'list is empty'
else:
    for i in xrange(0,len(result)):
        for item  in  result[i].iteritems():
            query="INSERT INTO exdata (cve_id,severity, published,modified) \
                   VALUES ('%s', '%s', '%s', '%s')" % (item[i], item[1][0],item[1][1],item[1][2])
            cursor.execute(query)
        connection.commit()
分享给朋友:
您可能感兴趣的文章:
随机阅读: