[Tutor] sqlite3 module

[Dennis Lee Bieber]

On Sun, 16 Apr 2023 11:09:48 -0700, Alex Kleider <alexkleider at gmail.com>
declaimed the following:

>
>Must I provide a value for every field in the table?
>It seems that this shouldn't be necessary if default values are
>specified but the following code suggests that one must in fact
>supply a value for _every_ field.
>When I complied (up to a point, giving the data dict values for
>all columns _except_ the autoincrimenting primary key) I got the
>same error; It would seem to me that it shouldn't accept an entry
>for the primary key rather than insist on it.

	The INSERT operation will require a value for EACH FIELD NAMED.
However...

>sqlite> .schema Receipts
>CREATE TABLE Receipts (
>    ReceiptID INTEGER PRIMARY KEY,
>    personID INTEGER NOT NULL,
>    date_recieved TEXT NOT NULL,
>    dues INTEGER DEFAULT NULL,
>    dock INTEGER DEFAULT NULL,
>    kayak INTEGER DEFAULT NULL,
>    mooring INTEGER DEFAULT NULL,
>    acknowledged TEXT DEFAULT NULL
>                 --date value
>    );
>sqlite> .quit

>data = {"dues": 50,
>        "date_received": "20230407",
>        "date_acknowledged": "20230410",
>        }

...	There is no "date_acknowledged" in your schema. Furthermore, there is a
spelling difference between the schema "date_recieved" and Python
"date_received"....

>f_keys = ", ".join([":"+key for key in data.keys()])
>query = "INSERT INTO Receipts VALUES ({});".format(f_keys)

...	you aren't naming fields, so all fields are required. Use the form of
INSERT with field names listed

	INSERT INTO Receipts (field1, field2, ..., fieldn) VALUES (value1,
value2, ..., valuen)

>cursor.executemany(query, data)

... the use of .executemany() may be a confusing factor, too, as that
implies multiple sets of data (to populate multiple records). For your
example, a simple .execute() should be sufficient (especially as the use of
the dictionary precludes an ability to provide multiple rows of data).


	I'll have to confess -- I've never figured out how that strange
.format() method operates; the old % string interpolation always made sense
to me as a variant of C's formatting operations.

	Presuming the dictionary keys ARE required to match the schema names,
I'd end up with something like (for actual production I'd work at cleaning
it up some).

keys = data.keys()
fields = " ,".join(keys)
values = [data[k] for k in keys]
val_mark = " ,".join(["?" for k in keys])
cursor.execute(
	"INSERT INTO Receipts (%s) VALUES (%s)" % (fields, val_mark),
		values)

	The first %s receives the field names, in the order the values will be
provided; the second %s receives the API "?" placeholder for each provided
value.