[Tutor] sqlite3 module

[Alex Kleider]

Thanks, Dennis, for your reply.
Sorry about the typos!
I've gone with your suggestion of using formatting rather than trying
to use the dict directly.
Here's an example of what works and which I think is what you've advised:
'''
#!/usr/bin/env python3

# File: enter.py

import sqlite3

schema = """
    ReceiptID INTEGER PRIMARY KEY,
    personID INTEGER NOT NULL,
    date_received TEXT NOT NULL,
    dues INTEGER DEFAULT NULL,
    dock INTEGER DEFAULT NULL,
    kayak INTEGER DEFAULT NULL,
    mooring INTEGER DEFAULT NULL,
    acknowledged TEXT DEFAULT NULL
"""

def main():
    db_file_name = '/home/alex/Git/Sql/Secret/club.db'
    db = sqlite3.connect(db_file_name)
    cursor = db.cursor()
    data = {"personID": 119,
            "date_received": "20230407",
            "dues": 50,
            "dock": 0,
            "kayak": 0,
            "mooring": 0,
            "acknowledged": "20230410",
            }
    f_keys = ", ".join([key for key in data.keys()])
    f_values = ", ".join([repr(value) for value in data.values()])
    query = ("INSERT INTO Receipts ({}) VALUES ({});"
                    .format(f_keys, f_values))
    print(query)
     cursor.execute(query, data)
    cursor.close()
    db.commit()
    db.close()

if __name__ == '__main__':
    main()
'''

Cheers,
Alex

On Sun, Apr 16, 2023 at 12:11 PM Dennis Lee Bieber
<wlfraed at ix.netcom.com> wrote:
>
> 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.
>
> _______________________________________________
> Tutor maillist  -  Tutor at python.org
> To unsubscribe or change subscription options:
> https://mail.python.org/mailman/listinfo/tutor



-- 
alex at kleider.ca  (he/him)
(sent from my current gizmo)