[Tutor] sqlite3 module

[ThreeBlindQuarks]

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