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?!

please show the contents of ´index´

tiktok
– tiktok

2014-11-09 10:49:37 +00:00
Commented Nov 9, 2014 at 10:49 — tiktok
– tiktok, Commented Nov 9, 2014 at 10:49

Daniel · Accepted Answer · 2014-11-09 10:52:09Z

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()

answered Nov 9, 2014 at 10:52

Daniel

42.9k4 gold badges57 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding data from python script into mysql database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related