Python Sqlite3: INSERT INTO table VALUE(dictionary goes here)

Question

I would like to use a dictionary to insert values into a table, how would I do this?

import sqlite3

db = sqlite3.connect('local.db')
cur = db.cursor()

cur.execute('DROP TABLE IF EXISTS Media')

cur.execute('''CREATE TABLE IF NOT EXISTS Media(
                id INTEGER PRIMARY KEY, title TEXT, 
                type TEXT,  genre TEXT,
                onchapter INTEGER,  chapters INTEGER,
                status TEXT
                )''')


values = {'title':'jack', 'type':None, 'genre':'Action', 'onchapter':None,'chapters':6,'status':'Ongoing'}

#What would I Replace x with to allow a 
#dictionary to connect to the values? 
cur.execute('INSERT INTO Media VALUES (NULL, x)'), values)
cur.execute('SELECT * FROM Media')

meida = cur.fetchone()

print meida

Answer 1

If you're trying to use a dict to specify both the column names and the values, you can't do that, at least not directly.

That's really inherent in SQL. If you don't specify the list of column names, you have to specify them in CREATE TABLE order—which you can't do with a dict, because a dict has no order. If you really wanted to, of course, you could use a collections.OrderedDict, make sure it's in the right order, and then just pass values.values(). But at that point, why not just have a list (or tuple) in the first place? If you're absolutely sure you've got all the values, in the right order, and you want to refer to them by order rather than by name, what you have is a list, not a dict.

And there's no way to bind column names (or table names, etc.) in SQL, just values.

You can, of course, generate the SQL statement dynamically. For example:

columns = ', '.join(values.keys()) placeholders = ', '.join('?' * len(values)) sql = 'INSERT INTO Media ({}) VALUES ({})'.format(columns, placeholders) values = [int(x) if isinstance(x, bool) else x for x in values.values()] cur.execute(sql, values) 

However, this is almost always a bad idea. This really isn't much better than generating and execing dynamic Python code. And you've just lost all of the benefits of using placeholders in the first place—primarily protection from SQL injection attacks, but also less important things like faster compilation, better caching, etc. within the DB engine.

It's probably better to step back and look at this problem from a higher level. For example, maybe you didn't really want a static list of properties, but rather a name-value MediaProperties table? Or, alternatively, maybe you want some kind of document-based storage (whether that's a high-powered nosql system, or just a bunch of JSON or YAML objects stored in a shelve)?

---

An alternative using named placeholders:

columns = ', '.join(my_dict.keys()) placeholders = ':'+', :'.join(my_dict.keys()) query = 'INSERT INTO my_table (%s) VALUES (%s)' % (columns, placeholders) print query cur.execute(query, my_dict) con.commit() 

Answer 2

There is a solution for using dictionaries. First, the SQL statement

INSERT INTO Media VALUES (NULL, 'x'); 

would not work, as it assumes you are referring to all columns, in the order they are defined in the CREATE TABLE statement, as abarnert stated. (See SQLite INSERT.)

Once you have fixed it by specifying the columns, you can use named placeholders to insert data. The advantage of this is that is safely escapes key-characters, so you do not have to worry. From the Python sqlite-documentation:

values = {     'title':'jack', 'type':None, 'genre':'Action',      'onchapter':None,'chapters':6,'status':'Ongoing' } cur.execute(     'INSERT INTO Media (id, title, type, onchapter, chapters, status)      VALUES (:id, :title, :type, :onchapter, :chapters, :status);',      values ) 

Answer 3

You could use named parameters:

cur.execute('INSERT INTO Media VALUES (NULL, :title, :type, :genre, :onchapter, :chapters, :status)', values)

This still depends on the column order in the INSERT statement (those : are only used as keys in the values dict) but it at least gets away from having to order the values on the python side, plus you can have other things in values that are ignored here; if you're pulling what's in the dict apart to store it in multiple tables, that can be useful.

If you still want to avoid duplicating the names, you could extract them from an sqlite3.Row result object, or from cur.description, after doing a dummy query; it may be saner to keep them around in python form near wherever you do your CREATE TABLE.