Python : How to insert a dictionary to a sqlite database?

Question

I have a sqlite database with a table with following columns :

id(int) , name(text) , dob(text)

I want to insert following dictionary to it :

{"id":"100","name":"xyz","dob":"12/12/12"}

Dictionary keys are the column names. How can i achieve it ?

Abettor

What have you tried? How are you connecting to the database? Are you using any kind of ORM? – Program

Can you please provide some code you have Tried ?? – Prefab

you cannot insert a dictionary easily, but you can insert a list. Have a look at the docs – Huggins

A possible solution would be to modify the Class IterChars() in the docs . I was not able to adapt it though. A look at sqlalchemy might help as well – Huggins

Does this answer your question? Insert Values from dictionary into sqlite database – Ignominy

Answer 1

To use dictionaries directly you can do:

user1 = {"id":100, "name": "Rumpelstiltskin", "dob": "12/12/12"}
c.execute("INSERT INTO users VALUES (:id, :name, :dob)", user1) 

Using along with instances/models:

class User:                                                                      

    def __init__(self, name, dob):                                            
        self.name = name                                                             
        self.dob = dao  

u1 = User("Rumpelstiltskin", "12/12/12")
c.execute("INSERT INTO users VALUES (:name, :dob)", u1.__dict__)

id is a keyword in Python, so if you want to use it as an identifier of an instance variable I would recommend using _id (and the same as your table's primary key name).

Geo

Answer 2

Looking at the documentation here you can add a single row:

c.execute("INSERT INTO stocks VALUES (?,?,?)", [dict["id"], dict["name"], dict["dob"]])

Or you can use a list and add multiple rows in one go:

# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

Rog

Maybe update this to show how a list of the asker's dictionaries can be inserted? With that I think your answer is better than mine. – Heinie

Answer 3

Here's a way which preserves parameter safety. (Might need polishing in the tablename department)

def post_row(conn, tablename, rec):
    keys = ','.join(rec.keys())
    question_marks = ','.join(list('?'*len(rec)))
    values = tuple(rec.values())
    conn.execute('INSERT INTO '+tablename+' ('+keys+') VALUES ('+question_marks+')', values)

row = {"id":"100","name":"xyz","dob":"12/12/12"}
post_row(my_db, 'my_table', row)

Rawlinson

Answer 4

If, for example, c = conn.cursor(), and your dictionary is named dict and your table tablename, then you can write

c.execute('insert into tablename values (?,?,?)', [dict['id'], dict['name'], dict['dob']])

Which will insert the elements of the dictionary into the table as you require.

Heinie

Answer 5

As per Gareth‘s response, if you're using MySQLdb you can use executemany and pass a list of values which you can get directly from your dict using dict.values()

Numerary

Be careful to sort when using this method as the cursor will INSERT based on order and dictionaries do not maintain order. – Lorenzoloresz

small update: Dictionaries now maintain order as of 3.6, and this behavior will be in language specification as of 3.7. – Skim

Answer 6

I wrote a function which uses the dictionary keys as table columns, the order is also not important with this approach, just that the table uses auto incremental id column:

def insert_into(table, dictionary):
    dictionary["id"] = None
    insert_query = f"INSERT INTO {table} "
    dictionary_keys = dictionary.keys()
    columns = ""
    values_placeholders = " VALUES "
    for index, column in enumerate(dictionary_keys):
        if index == 0:
            columns += "("
            values_placeholders += "("
        columns += f"{column}"
        values_placeholders += f":{column}"
        if index + 1 != len(dictionary_keys):
            columns += ", "
            values_placeholders += ", "
        else:
            columns += ")"
            values_placeholders += ")"

    insert_query += columns + values_placeholders

    cursor.execute(insert_query, dictionary)

insert_into('your_table', {"name":"xyz","dob":"12/12/12"})

Acarus