Entire JSON into One SQLite Field with Python
Question: I have what is likely an easy question. I'm trying to pull a JSON from an
online source, and store it in a SQLite table. In addition to storing the data
in a rich table, corresponding to the many fields in the JSON, I would like to
also just dump the entire JSON into a table every time it is pulled.

The table looks like:

    
    
    CREATE TABLE Raw_JSONs (ID INTEGER PRIMARY KEY ASC, T DATE DEFAULT (datetime('now','localtime')), JSON text);
    

I've pulled a JSON from some URL using the following python code:

    
    
    from pyquery import PyQuery
    from lxml import etree
    import urllib
    
    x = PyQuery(url='json')
    y = x('p').text()
    

Now, I'd like to execute the following INSERT command:

    
    
    import sqlite3
    
    db = sqlite3.connect('a.db')
    c = db.cursor()
    
    c.execute("insert into Raw_JSONs values(NULL,DATETIME('now'),?)", y)
    

But I'm told that I've supplied the incorrect number bindings (i.e. thousands,
instead of just 1). I gather it's reading the y variable as all the different
elements of the JSON.

Can someone help me store just the JSON, in it's entirety?

Also, as I'm obviously new to this JSON game, any online resources to
recommend would be amazing.

Thanks!


Answer: `.execute()` expects a _sequence_ , better give it a one-element tuple:

    
    
    c.execute("insert into Raw_JSONs values(NULL,DATETIME('now'),?)", (y,))
    

A Python string is a sequence too, one of individual characters. So the
`.execute()` call tried to treat each separate character as a parameter for
your query, and unless your string is one character short that means it'll not
provide the right number of parameters.

Don't forget to commit your inserts:

    
    
    db.commit()
    

or use the database connection as a context manager:

    
    
    with db:
        # inserts executed here will automatically commit if no exceptions are raised.
    

