[Tutor] sqlite3 module

Mon Apr 17 08:35:04 EDT 2023

Alex,

I see others have provided a decent way to just specify the fields you want to insert by using another form of the SQL command so the suggestion I made of altering the dict are not necessarily a good way to go, especially if a program may not be aware that an existing table has had additional fields added. That is not your case as you are creating the table.

What I suggested was a sort of template and after sending it, I realized the default of adding a Python NULL may not be what is wanted. There is an argument you can add to set it to anything you want such as an empty string or zero.

Humorously, I did not necessarily assume you were male so a Sir Name was not implied. I did add a line in German that does better with a comma after the first word to mean something like "Easy, isn't it" or perhaps "Simple, isn't that right." You are correct that your Surname was a misleading clue. I should stick to languages nobody speaks as a first language! Mine is a bit more enigmatic, LOL!

Good Luck with your project.

Facila ĉu ne?

Q?

Sent with Proton Mail secure email.

------- Original Message -------
On Sunday, April 16th, 2023 at 11:49 PM, Alex Kleider <alexkleider at gmail.com> wrote:

> 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+"Einfach%2C+nicht+wahr!"&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)