python dictionary into sqlite
Question: I have built a sqlite db and table in Python 2.7 with 6 variables, based on
reading a URL file.

I used JSON and created a dictionary. The code reads everything well and loops
through the keys and values.

I need to insert this into my table. That is where I am a little lost. I will
provide the code and I think my hole will be obvious.

    
    
    import json
    import urllib2
    #Read file and print a line
    webFD=urllib2.urlopen("http://rasinsrv07.cstcis.cti.depaul.edu/CSC455/assignment4.txt")
    tweet = webFD.readline()
    tweet
    
    
    #create dictionary
    dictt=json.loads(tweet)
    
    #print dictionary
    dictt.keys()
    
    #print values
    dictt.values()
    
    #loop through tweets
    for (key, value) in dictt.items():
        print key, '->', value
    
    #Created the DB
    import sqlite3
    conn = sqlite3.connect('twitter.db')
    c = conn.cursor()
    
    #Created the table for the tweets
    c.execute("CREATE TABLE Tweet(created_at, id, text, source,    in_reply_to_user_ID,retweet_Count)")
    

Here is my disconnect. Want to load those tweets (6 keys and values in the
dict into the Tweet tables:

    
    
    for elt in tweet:
        currentRow = elt[:-1].split(", ")
        insert = """insert into Tweet values ('%s', '%s', '%s', '%s', '%s', '%s')""" %("created_at", "id", "text", 'source', 'in_reply_to_user_ID', 'retweet_Count')
        print insert
    


Answer: What you're doing here makes no sense:

    
    
    insert = """insert into Tweet values ('%s', '%s', '%s', '%s', '%s', '%s')""" %("created_at", "id", "text", 'source', 'in_reply_to_user_ID', 'retweet_Count')
    

Using `%`-formatting with literal strings just replaces each `%s` with the
literal string. So you'll get this:

    
    
    insert into Tweet values ('created_at', 'id', 'text', 'source', 'in_reply_to_user_ID', 'retweet_Count')
    

And that's obviously nonsense; you want to insert the _values_ , not the
_column names_.

You _could_ —but should not—fix this by putting the six values into the `%`
operation, like this:

    
    
    insert = """insert into Tweet values ('%s', '%s', '%s', '%s', '%s', '%s')""" % currentRow
    

But this is still a bad idea. What happens if one of those values could have a
quote in it? [This](http://xkcd.com/327/).

What you _want_ to do is this:

    
    
    c.execute("insert into Tweet values (?, ?, ?, ?, ?, ?)", currentRow)
    

This lets the database handle formatting the values, making sure they're
quoted properly, etc.

