[Tutor] sqlite3 module

[Alex Kleider]

Good of you ('Q' aka ThreeBlindQuarks:-) to respond.
Yes, I've been doing more researching and am realizing that one can in
fact use dicts but with None/Null as values that are not meant to be
touched (at least I think that's correct; haven't tested it yet!)
Amused by your German closing comment.
I think you meant "Isn't that so?" (and I agree that it is) but google
thinks otherwise:
https://duckduckgo.com/?t=ftsa&q=german+translation+of+%22Einfach%2C+nicht+wahr!%22&ia=translations
Correction, it's not Google! I seem to have set my default to ddg some time ago.
Thanks to you and all that contribute so much on this list.
Alex
PS Don't be fooled by the sir name! My first language was actually
Russian but that's a very long time ago and almost all is forgotten.
PPS Interesting that my postings seem to be getting to the list as
evidenced by the responses, and yet they don't come in to my inbox;
only the responses do.

On Sun, Apr 16, 2023 at 7:27 PM ThreeBlindQuarks
<threesomequarks at proton.me> wrote:
>
> Hi Alex,
>
> I won't comment on the SQL issues of the module you are using but just about Python aspects.
>
> You have an item you want to add to a SQL table using a dictionary with only some of the key/value pairs needed in which you expect many items to be some form of Null. Yet you are being forced to add all items even if SQL itself would happily initialize them to null because of the function you are using. You are updating from a dictionary you created that looks like:
>
> data = {"dues": 50,
>         "date_received": "20230407",
>         "date_acknowledged": "20230410",
>         }
>
> So suppose you have fields I will call a1 ... a3 that for your purposes are null. I suggest you change or replace data in place to update any fields not currently present to have value NULL. Any existing are left alone. Here is a function that accepts a dictionary and a list of keywords and returns the dictionary update with NULL in all the non-existent keys. You can specify any value if you change it, but this may work for your need.
>
> Here is the function:
>
> def adding_nulls(data, the_keys):
>   """
>   Given a dictionary and a list of keywords,
>   add the keys specified with value NULL but only
>   if the key does not already exist.
>   """
>   for a_key in the_keys:
>     data.setdefault(a_key)
>   return(data)
>
> Here is the result or running it using three new fields and an existing one to show it does no harm.:
>
> >>> data
> {'dues': 50, 'date_received': '20230407', 'date_acknowledged': '20230410', 'a1': None, 'a2': None, 'a3': None}
> >>> adding_nulls(data, ["a1", "a2", "a3", "dues"])
> {'dues': 50, 'date_received': '20230407', 'date_acknowledged': '20230410', 'a1': None, 'a2': None, 'a3': None}
> >>> data = adding_nulls(data, ["a1", "a2", "a3", "dues"])
> >>> data
> {'dues': 50, 'date_received': '20230407', 'date_acknowledged': '20230410', 'a1': None, 'a2': None, 'a3': None}
>
> So you can either modify the original or make a copy first. The larger dictionary may then do what you want as long as you set things up right.
>
> Einfach, nicht wahr!
>
> Q
>
>
>
>
> Sent with Proton Mail secure email.
>
> ------- Original Message -------
> On Sunday, April 16th, 2023 at 2:09 PM, Alex Kleider <alexkleider at gmail.com> wrote:
>
>
> > I'm running Python3 within a virtualenv on a Debian machine.
> >
> > My question has to do with the sqlite3 module.
> >
> > 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.
> >
> > Is there a way to make what I want to do possible?
> >
> > Thanks in advance for any guidance provided.
> >
> > """
> > (p9) alex at t460:~/Git/Sql$ sqlite3 ~/Git/Sql/Secret/club.db
> > SQLite version 3.34.1 2021-01-20 14:10:07
> > Enter ".help" for usage hints.
> > 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
> >
> > (p9) alex at t460:~/Git/Sql$ cat enter.py
> > #!/usr/bin/env python3
> >
> > # File: enter.py
> >
> > import sqlite3
> >
> > db_file_name = '/home/alex/Git/Sql/Secret/club.db'
> > db = sqlite3.connect(db_file_name)
> > cursor = db.cursor()
> > data = {"dues": 50,
> > "date_received": "20230407",
> > "date_acknowledged": "20230410",
> > }
> > f_keys = ", ".join([":"+key for key in data.keys()])
> > query = "INSERT INTO Receipts VALUES ({});".format(f_keys)
> > cursor.executemany(query, data)
> > cursor.close()
> > db.commit()
> > db.close()
> > (p9) alex at t460:~/Git/Sql$ ./enter.py
> > Traceback (most recent call last):
> > File "/home/alex/Git/Sql/./enter.py", line 16, in <module>
> >
> > cursor.executemany(query, data)
> > sqlite3.OperationalError: table Receipts has 8 columns but 3 values
> > were supplied
> > (p9) alex at t460:~/Git/Sql$
> > """
> > ... again, thank you.
> > a
> > --
> > alex at kleider.ca (he/him)
> > (sent from my current gizmo)
> > _______________________________________________
> > 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)