Adding data from python script into mysql database

Question

I was wondering if anyone could help me figure out why the data that my python script has gathered is not being input into the mysql database properly

Here is my code

/*** index is a dictionary that has the struture: 
 index = {links:[words], links: [words],.......} ***/
#There are multiple items in words
for k, v in index.iteritems():
 links = str(k)
 words = str(v) [1:-1]
 import MySQLdb
 db = MySQLdb.connect("localhost","root","","my_db" )
 cursor = db.cursor()
 try:
 cursor.execute(('insert into SITE(url, keywords) values("%s", "%s")' % \
 (links,words)))
 db.commit()
 except:
 db.rollback()
db.close()

Each row in my table should have two feilds: url and keywords Instead of insterting 6 rows it only inserts two. Please help?!

• tiktok: please show the contents of ´index´

Answer 1

Perhaps there is a problem, because you open a new connection for every item. Then you shouldn't format values into a SQL-statement, use parameters instead. Third, you shouldn't ignore exceptions, because then, you cannot figure out, what's going wrong. The representation of a list is nothing, to work with in production code, use join instead

import logging
import MySQLdb
db = MySQLdb.connect("localhost","root","","my_db" )
for links, words in index.iteritems():
 cursor = db.cursor()
 try:
 cursor.execute('insert into SITE(url, keywords) values(%s, %s)', (links, ','.join(words)))
 db.commit()
 except Exception:
 logging.exception('insert went wrong')
 db.rollback()
db.close()