insert dictionaries as rows in sqlite table-CodePudding

I have dictionaries like this:

{'id': 8, 'name': 'xyzzy', 'done': False}

the table is already created with the correct column names (keys of the dictionary). How can I insert the values in the respective columns? I want to create a new row for each dictionary.

Note that for 'done' the type defined is originally Integer since sqlite does not offer bool type.

cur = connection().cursor()

query = "insert .... tablename"

CodePudding user response：

You can pass the dictionary to cursor execute and it will do the right thing as long as the values placeholders in the SQL statement used the :named format (that is, the dict key prefixed by a colon ":").

stmt = """INSERT INTO mytable (id, name, done) VALUES (:id, :name, :done)"""
cur.execute(stmt, {'id': 8, 'name': 'xyzzy', 'done': False})
conn.commit()

This method ensures that values are correctly quoted before being inserted into the database and protects against SQL injection attacks.

CodePudding user response：

You could use .format() method to insert into a query string however this is much more straightforward.

dic = {'id': 8, 'name': 'xyzzy', 'done': False}

cur.execute("INSERT INTO tablename VALUES (:id,:name,:done)",{"id" : dic["id"],"name" : dic["name"],"done" : dic["done"]})