Python sqlite3: how to insert data with missing key/value?

Question

I want to add new data entries, in the json format, into a database using sqlite3. The example database 'test.db' has a table 'cars' that takes 3 values, 'MAKE', 'MODEL' and 'COST'. The new data contains entries with missing name/value pairs:

entries = {
  "CARS":[
 {
    "MAKE": "VW",
    "MODEL":"Passat",
    "COST":23000
 },
 {
    "MAKE":"Honda",
    "MODEL":"Civic"
 }
]
}

To get around the KeyError raising, I used setdefault() and added key/None pairs if they are missing in each entry:

import sqlite3

keys = ["MAKE", "MODEL", "COST"]
with sqlite3.connect("test.db") as conn:
    c = conn.cursor()

    for car in entries['CARS']:
        for key in keys:
            car.setdefault(key, None)
        c.execute('INSERT INTO cars VALUES(?,?,?)', (car['MAKE'],   car['MODEL'], car['COST']))

Although this seems to work, is there also a way to add entries with missing name/value pairs by using the SQL syntax?

Instead of .setdefault(), how about c.execute("...", (car.get("MAKE"), car.get("MODEL", car.get("COST"))). Since dict.get returns None if the key isn't found, this should do just what you want. — Robᵩ
– Robᵩ, Commented Jan 5, 2017 at 19:52

Robᵩ · Accepted Answer · 2017-01-05 20:11:49Z

1

When you use parameter binding, you have to supply as many arguments as there are parameters in the string. It isn't a question of SQL syntax, it is an issue of parameter binding syntax. I don't see any way around that.

You could use a combination of collections.defaultdict() and named parameter binding to make the code a little more readable:

import sqlite3
from collections import defaultdict

keys = ["MAKE", "MODEL", "COST"]
with sqlite3.connect(":memory:") as conn:
    c = conn.cursor()
    c.execute("create table cars (make, model, cost)")

    for car in entries['CARS']:
        car = defaultdict(lambda: None, car)
        c.execute('INSERT INTO cars VALUES(:MAKE,:MODEL,:COST)', car)

answered Jan 5, 2017 at 20:11

Robᵩ

170k20 gold badges251 silver badges323 bronze badges

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

Comments

SashaZd · Accepted Answer · 2017-01-05 18:50:29Z

0

You would need to alter the column in your table to have a default value incase you don't supply it with an entry.

# Connecting to the database file
conn = sqlite3.connect(sqlite_file)
c = conn.cursor()

Adding a new column with a default row value

c.execute("ALTER TABLE {tn} ADD COLUMN '{cn}' {ct} DEFAULT '{df}'"\
        .format(tn=table_name, cn=new_column2, ct=column_type, df=default_val))

There are 3 ways of inserting data into an SQL table:

With the "VALUES" keyword: creates one or more new rows in an existing table.
With the "SELECT" statement instead of a VALUES clause. A new entry is inserted into the table for each row of data returned by executing the SELECT statement.
With DEFAULT VALUES. The INSERT ... DEFAULT VALUES statement inserts a single new row into the named table. Each column of the new row is populated with its default value, or with a NULL if no default value is specified as part of the column definition in the CREATE TABLE statement.

To understand how you can INSERT into a table and see more examples see source

edited Jan 5, 2017 at 18:50

answered Jan 5, 2017 at 18:10

SashaZd

3,3291 gold badge30 silver badges48 bronze badges

2 Comments

ddyke Over a year ago

Would this, or setting default values when creating the table, work only if the new entry at least contains all the value names?

SashaZd Over a year ago

I edited the answer, and added in more details about how insert works with (and without) default values in the query.

Collectives™ on Stack Overflow

Python sqlite3: how to insert data with missing key/value?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related