sqlite - Inserting variable stored data into SQLite3 - Python 3

Question

I have been reading information on how to insert data into a database using data stored in a variable. I have not been able to get my data to load to my database and I am not sure why.

The program is written to check for an existing DB and if it does not exist than it creates it along with the tables and columns required, that all works fine.

I have tried to follow the methods from the various tutorials I have been reading but I must be missing something or doing something incorrectly.

The database and table creates properly (I did not include that part of the code, it is executed at the start of the program). Further into my program code I am using the following routine to enter the user input data by clicking of the "Submit" button

Button routine:

submit = Button(
    window3, 
    font=('arial',12,'bold'), text='Submit', 
    width=12, height=1, bg='aliceblue', 
    fg='steel blue', command = Submit
)

My Submit Routine:

def Submit():
    connect = sqlite3.connect('SSRB.db')
    connect.execute('''
    INSERT INTO ssrb (date, time_in, time_out, company, plate, province, driver, pass1,
     pass2, pass3, deliver, contact, entry_by, pi_yes, pi_no, pi_violations, pi_done_by,
     vi_yes, vi_no, vi_violations, vi_done_by)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
     %s), (Date, TimeIn, TimeOut, Company, Plate, Province, Driver, Pass1, Pass2, Pass3,
     Delivery, Contact, EntryBy, PIYES, PINO, PIFound, PIDoneBy, VIYES, VINO,
    VIFound, VIDoneBy)
    ''')
    connect.commit()
    connect.close()

The columns in the table are listed after the INSERT, the %s is placed after the VALUES and the variables holding the user input is listed last.

I am not seeing where I am going wrong... can someone please point out what I am doing incorrectly?

Much thanks as always.

Answer 1

Instead of

connect.execute('''
INSERT INTO ssrb (date, time_in, time_out, company, plate, province, driver, pass1,
 pass2, pass3, deliver, contact, entry_by, pi_yes, pi_no, pi_violations, pi_done_by,
 vi_yes, vi_no, vi_violations, vi_done_by)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
 %s), (Date, TimeIn, TimeOut, Company, Plate, Province, Driver, Pass1, Pass2, Pass3,
 Delivery, Contact, EntryBy, PIYES, PINO, PIFound, PIDoneBy, VIYES, VINO,
VIFound, VIDoneBy)
''')

use

connect.execute('''
INSERT INTO ssrb (date, time_in, time_out, company, plate, province, driver, pass1,
 pass2, pass3, deliver, contact, entry_by, pi_yes, pi_no, pi_violations, pi_done_by,
 vi_yes, vi_no, vi_violations, vi_done_by)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
 ?)''', (Date, TimeIn, TimeOut, Company, Plate, Province, Driver, Pass1, Pass2, Pass3,
 Delivery, Contact, EntryBy, PIYES, PINO, PIFound, PIDoneBy, VIYES, VINO,
VIFound, VIDoneBy)
)

They are 2 differences:

1. Symbol ? instead of %s as it is not a Python's syntax for the string's .format() method (or older % notation) but the DB-API’s parameter substitution symbol.
2. Ending ''' are moved up - where the INSERT INTO (parametrized) statement really ends.
   (The substitution itself is performed by the .execute() method by its 2^nd parameter - a tuple provided by you.)