Sqlite not using default values

Question

When using:

import datetime
import sqlite3

db = sqlite3.connect('mydb.sqlite', detect_types=sqlite3.PARSE_DECLTYPES)
c = db.cursor()
db.text_factory = str

c.execute('create table if not exists mytable (date timestamp, title str, \
    custom str, x float, y float, z char default null, \
    postdate timestamp default null, id integer primary key autoincrement, \
    url text default null)')
c.execute('insert into mytable values(?, ?, ?, ?, ?)', \
    (datetime.datetime(2018,4,23,23,00), 'Test', 'Test2', 2.1, 11.1))

I have:

sqlite3.OperationalError: table mytable has 9 columns but 5 values were supplied

Why doesn't SQlite take default values (specified during table creation) in consideration to populate a new row?

(Also, as I'm reopening a project I wrote a few years ago, I don't find the datatypes str, char anymore in the sqlite3 doc, is it still relevant?)

score 3 · Accepted Answer · edited Feb 25 '18 at 10:25

Because you are saying that you want to insert all columns by not specifying the specific columns.

Change 'insert into mytable values(?, ?, ?, ?, ?)'

to 'insert into mytable (date, title, custom, x, y) values(?, ?, ?, ?, ?)'

Virtually any value for column type can be specified, the value will follow a set of rules and be converted to TEXT, INTEGER, REAL, NUMERIC or BLOB. However, you can store any type of value in any column.

STR will resolve to NUMERIC,
TIMESTAMP will resolve to NUMERIC,
FLOAT will resolve to REAL,
CHAR to TEXT.

Have a read of Datatypes In SQLite or perhaps have a look at How flexible/restricive are SQLite column types?

score 2 · Answer 2 · answered Feb 24 '18 at 00:40

2

If you're going to only supply values for some columns, you need to specify which columns. Otherwise the engine won't know where to put them. This line needs to be changed:

c.execute('insert into mytable values(?, ?, ?, ?, ?)', \
(datetime.datetime(2018,4,23,23,00), 'Test', 'Test2', 2.1, 11.1))

To this:

c.execute('insert into mytable (date, title, custom, x, y)values(?, ?, ?, ?, ?)', \
(datetime.datetime(2018,4,23,23,00), 'Test', 'Test2', 2.1, 11.1))

answered Feb 24 '18 at 00:40

mypetlion

2,415
5
18
22

Thank you. I just noticed that when using your solution, it's even not mandatory to create a table with `default null`: even if I remove all `default null`, it will still work with your solution, all these values will default to `None` anyway! – Basj Feb 24 '18 at 00:48
2

@Basj Yup. `Null` is the default default. – mypetlion Feb 24 '18 at 00:52

score 0 · Answer 3 · answered Mar 05 '19 at 18:00

Example Solution

cursor.execute('CREATE TABLE vehicles_record (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP)')

SQLite3 Query Above

cursor.execute("INSERT INTO vehicles_record(name) VALUES(?)", (name))

Result

id would be 1, name would be value of name var, current timestamp for last column.

Sqlite not using default values

3 Answers3

Example Solution

SQLite3 Query Above

Result