Insert null into Sqlite from Python

Question

I have a table called 'test' that I'm creating through my Python code.

db = sqlite3.connect("mydb.db")
cur = db.cursor()
cur.execute('''CREATE TABLE IF NOT EXISTS test 
            (
                "id" text primary key,
                "field1" integer,
                "fiedl2" integer,
                "field3" text
            )'''
           )

I have a file where each line is a json. I read the file line by line and insert data from the json of each line into the table.

file.txt:

{'id':'abcde', 'field1': 10, 'field2': 20, 'field3': "hello"}
{'id':'asdf', 'field1': 20, 'field2': 5, 'field3': "world"}
{'id':'qwerty', 'field1': 1, 'field2': 2, 'field3': None}

If a field in the json doesn't have a value, I want to insert null into my sqlite table. So as in the last line above, if field3 doesn't have a value, I put None into it because this is what I read in other related questions.

However, when I insert as below:

for line in readfile:
    line = ast.literal_eval(line)
    columns = '", "'.join([i.strip() for i in line.keys()])
    columns = "\"" + columns + '\"' 
    placeholders = ', '.join('?' * len(line))
    sql = 'INSERT INTO test ({}) VALUES ({})'.format(columns, placeholders)
    cur.execute(sql, ["'" + str(i) + "'" for i in line.values()])
    db.commit()

I get 'None' instead of null when I perform a select statement from the Sqlite console.

sqlite> select * from test where id='qwerty' limit 1;
'qwerty'|1|2|'None'|

Can anyone please help me fix this? I'm using Python 3.4

Have you considered testing if a value is None?

Ignacio Vazquez-Abrams
– Ignacio Vazquez-Abrams

2015-11-11 19:21:40 +00:00
Commented Nov 11, 2015 at 19:21 — Ignacio Vazquez-Abrams
– Ignacio Vazquez-Abrams, Commented Nov 11, 2015 at 19:21

sebastian · Accepted Answer · 2015-11-12 05:43:24Z

3

You're getting a "None" string, because you're converting the None into a string before passing it to the execute function:

cur.execute(sql, ["'" + str(i) + "'" for i in line.values()])

So, as far as python/sqlite is concerned, you're inserting the string "None" - not the None object.

The point of having the parameters as second argument in the execute function is that it's supposed to do the value-to-sql-string representation for you. Hence, you should provide the values in their native python datatypes.

So, using

cur.execute(sql, list(line.values()))

should actually give you a null instead of "None".

edited Nov 12, 2015 at 5:43

answered Nov 11, 2015 at 19:49

sebastian

9,71628 silver badges56 bronze badges

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

2 Comments

SanjanaS801 Over a year ago

Works perfectly! I had to use cur.execute(sql, list(line.values()) though

sebastian Over a year ago

Right - that's because you're using python3, where the values are returned as iterator - I missed that. I edited my answer accordingly.

Collectives™ on Stack Overflow

Insert null into Sqlite from Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related