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.

I guess that Date, TimeIn, TimeOut and so on are variables in Python? If so the variable names can't be enclosed in the string and send to Sqlite as they are — Michael Butscher
– Michael Butscher, Commented Jan 3, 2018 at 22:46
You're hardcoding what looks like variable names into the string, so when you do "INSERT INTO... (Date)" you're inserting the string "Date", not the value of your Date variable. I think your ''' have gone too far :-) — Savir
– Savir, Commented Jan 3, 2018 at 22:51
Moreover, use ? as parameter substitution for Sqlite instead of %s — Michael Butscher
– Michael Butscher, Commented Jan 3, 2018 at 22:53
@Michael Butscher the variables names are as you thought... such as "Date, TimeIn, TimeOut". I have then set as StringVar() in this fashion: Date=StringVar() - converting any input through the textbox to a StringVar(). Should I or would it be better if I use something like Date = Date instead? I did not think to try that. — DT2000
– DT2000, Commented Jan 4, 2018 at 4:48

MarianD · Accepted Answer · 2018-01-03 23:38:10Z

0

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:

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.
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.)

edited Jan 3, 2018 at 23:38

answered Jan 3, 2018 at 23:12

MarianD

14.4k12 gold badges50 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

DT2000 Over a year ago

I appreciate the insight and I had tried it the way you have posted before asking here using both the ? instead of the %s and I also moved up the ' ' ' to the end of the ? statement. I have just modified it again and give it a try but it is still not inserting the data to the db. I have checked the table column names and the variable names over and over again to make sure there are no errors or mismatches and they are all proper. Could there be something else that is wrong?

MarianD Over a year ago

If your field names are in the same order as in your table try omit them completely, i. e. use INSERT INTO ssrb VALUES (?, ?, ...).

DT2000 Over a year ago

#MarianD I have given this a try and it does not write to the data either. I am wondering if it might be how I have the variables collecting the input data, but I don't think it should mater as long as the variable is not flushed prior to inserting to the DB. Each variable is collecting the user input in separate def routines, would this cause this problem of not writing? I am using labels with frames to run in the main window3. When the program runs everything appears properly. Is there a way to have the variable display what it contains so I can check to see if the data is there?

MarianD Over a year ago

There may be problem with the scope of variables - the variable in the inner scope (your function definition) with the same name is different (= newly created) from variable in the outer scope if you try changing its value (e.g. by assign to it). Try perform your commands from Python interpreter with (in advance) manually assigning values to your variables.

DT2000 Over a year ago

@MarionD I am not sure I follow you on your comment regarding the scope of the variables. I'm still quite new to python programming. Here is what I've done. Defined a routine for a user to enter the data into the entry box as follows: date = Entry(window3, font=('arial',12), textvariable= Date, bg = 'aliceblue',bd=2, insertwidth=2) I then have the textvariable declared as Date=StringVar() I then have the INSERT INTO DB. This should work as I understand it but the variable Date=StringVar() doesn't seem to hold the data from the entry box for it to load to the DB.

|

rask004 · Accepted Answer · 2018-01-05 00:53:47Z

0

See the execute function description in the sqlite documentation.

Statement variables should be in a second argument to execute(...), a tuple containing all the parameters.

Replace the %s in the statement string with ? as per the documentation specifications, for sanity.

You need code like this:

def Submit():
    conn = sqlite3.connect('SSRB.db')
    conn.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))
    connect.commit()
    connect.close()

Addendum 1: Variables in the parameters tuple should be primitives (integers, floats etc).

edited Jan 5, 2018 at 0:53

answered Jan 3, 2018 at 23:03

rask004

5806 silver badges15 bronze badges

6 Comments

DT2000 Over a year ago

I have tried that exactly a number of time but it has not been working. I just ran it again and I am getting the following error message: File "C:\Users\Home\Documents\Thonny Workshop\SSRB\New SSRB-4.py", line 63, in Submit Province, Driver, Pass1, Pass2, Pass3, Delivery, Contact, EntryBy, PIYES, PINO, PIFound, PIDoneBy, VIYES, VINO, VIFound, VIDoneBy)) sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type. Could you shed some light on the error?

rask004 Over a year ago

Saw other comments regarding Date.get(). Sqlite only accepts "primitives" for parameter values - integers, floats, strings, booleans. An unsupported type error means a value is something else, which sqlite cannot parse.

DT2000 Over a year ago

I declared the variables as follows: date = Entry(window3, font=('arial',12), textvariable= date, bg = 'aliceblue',bd=2, insertwidth=2) Then I declared the textvariable as: date=StringVar() Then this variable was attempted to be used in the INSERT string for the db. However it did not work out the way it should have and I am a bit confused why it was such a chore. (still very new to coding python)

rask004 Over a year ago

Sqlite itself can only interpret integers, floats, strings, booleans and strings / integers representing timestamps. The sqlite3 module tries to map supplied variables to these types, but it needs to know how to do this. It would not know how to map an Entry object, in general. Either there is a way to register the Entry class, so it knows how to map such objects, or you convert the Entry instance to a value already recognised ( hence, the Date.get() invokation ).

DT2000 Over a year ago

I am following what you have said, or I believe I do however looking at my above comment showing my flow does it not then take what is held in the entry box named "date=Entry(..." then become a declared or known string by stating "date=StringVar()" whereby the value of the entry would be then assigned to the StringVar() "date" using the entry in textvariable=date?

|

Collectives™ on Stack Overflow

Inserting variable stored data into SQLite3 - Python 3

2 Answers 2

9 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related