Saturday, 22 September 2018

INSERT in SQLite and Using it in Python

Previously I looked at creating an SQLite database and  creating an empty table within it. The simplest way of getting data into a SQLite database table is to use the INSERT SQL command, and the simple version follows the format:
INSERT INTO tablename (column_list) VALUES (value_list);

Although not strictly necessary, I keep SQLite command and function names and keywords in UPPERCASE to remind me what they are. The other items in that command are variable. So if we go back to the table for historical figures we created, we might have an example of the INSERT function like:
INSERT INTO historicalfigures (Name, YearBirth, YearDeath, Role, Nation) VALUES ('Henry VIII', 1491, 1547, 'King', 'England');
That is just the SQL part. To get Python to do this, we need to do it via the cursor object (here given the variable name curs).
curs.execute("INSERT INTO historicalfigures (Name, YearBirth, YearDeath, Role, Nation) VALUES ('Henry VIII', 1491, 1547, 'King', 'England');")

That is longwinded, and not very clear. Do we need to do that every time we want to add a new row?
Yes and no.
Yes we need to get Python to tell SQL to do this, but no, we don't have to do this manually. This is where other parts of Python programming come in use in automating and easing this process.
Here is a script I have written to assist with this process:

import sqlite3
conn = sqlite3.connect("C:\\sqlite\\db\\history1.db")
curs = conn.cursor()

print("Historical Figure Data Entry")
nextentry = True
committed = False
while nextentry == True:
    nameentry = input("Name of person? ")
    birthentry = input("Year of Birth? ")
    if int(birthentry) < 2020:
        print("Valid Year")
    else:
        print("problem with year of birth")
    deathentry = input("Year of Death? ")
    if int(deathentry) < 2020:
        print("Valid Year")
    else:
        print("problem with year of death")
    roleentry = input("Role of person in history? ")
    nationentry = input("Main nation for person? ")
    print("Your data is: ", nameentry, birthentry, deathentry, roleentry, nationentry)
    keepit = input("Keep (Y) or Discard (N) ?")
    keepit = keepit.lower()
    if keepit == "y":
        committed = True
        curs.execute("INSERT INTO historicalfigures (Name, YearBirth, YearDeath, Role, Nation) VALUES ('"+nameentry+"', "+birthentry+", "+deathentry+", '"+roleentry+"', '"+nationentry+"');")
    nextchoice = input("Do you want to carry on with the next entry in the table? (Y/N)")
    nextchoice = nextchoice.lower()
    if nextchoice == 'y':
        nextentry = True
    else:
        nextentry = False
   
if committed == True:
    curs.execute("COMMIT;")
conn.close()

That's quite a bit, but there are aspects I want to point out to explain what is going on:
The bulk of the program is in a while loop so that it will repeat as long as the user wants to continue.
There are a lot of input statements to gather information from the user. Some of these change the flow of the program (by if or while structures) while others are the data fields that will be sent to the database.
This is shown in the really big line
        curs.execute("INSERT INTO historicalfigures (Name, YearBirth, YearDeath, Role, Nation) VALUES ('"+nameentry+"', "+birthentry+", "+deathentry+", '"+roleentry+"', '"+nationentry+"');")
that includes these variables in the executable statement.
The mix of single quotes and double quotes is to get round the problem that strings/text data in SQLite commands need to have quotes around them but Python will also be looking for the start and end of what it considers a string. As far as Python is concerned, a single quote inside a pair of double quotes is just a string character, not a delimiter (and this works vice versa).
At the end (second from last line) there is curs.execute("COMMIT;") - this is because changes to the database by INSERT and other commands are held in a temporary state by SQLite. If changes are made but then the COMMIT command is not used, the changes are discarded. This COMMIT command tells SQLite to keep the data changes.
However, if there are no changes, and then the COMMIT command is used, SQLite throws an error because there are no changes to be committed - hence the committed variable to tell the program if the COMMIT command should be sent to SQLite.

The end result? A more user-friendly way of entering data into our table:

=== RESTART: C:/Users/pc/Documents/Programming/historicalfigures_entry.py ===
Historical Figure Data Entry
Name of person? Leonardo da Vinci
Year of Birth? 1452
Valid Year
Year of Death? 1519
Valid Year
Role of person in history? Artist
Main nation for person? Italy
Your data is:  Leonardo da Vinci 1452 1519 Artist Italy
Keep (Y) or Discard (N) ?y
Do you want to carry on with the next entry in the table? (Y/N)n
>>>

No comments:

Post a Comment