Help with PySimpleGUI INSERT INTO sqlite3 database

[jrbond]

Brand new to the forum. I only started using Python this week. I'm trying to make a GUI where our operators can scan a barcode into the GUI (and a few other selections) and then pass those user input variables to the sqlite3 database. In the INSERT INTO statement, if I make the data static (i.e. putting each value on single quotes), then it passes the static info into the db. So I know that my connection to the db is good. All of my input fields have keywords, and inside the program, I can print the keyword results to the screen, so I know that works as well. But I can't seem to use the keywords in the INSERT INTO statement, to pass the user input values into the db. Any help would be appreciated. Thanks...

import PySimpleGUI as sg
import automationhat, sqlite3, datetime, time

# Logic to create the timestamp
con = sqlite3.connect ('BarcodeScans.db')
ts=time.gmtime()
readable=(time.strftime("%Y-%m-%d %H:%M:%S",ts))


#sg.theme('DarkAmber')   # Add a touch of color
# All the stuff inside your window.
layout = [[sg.Text('Scan Router Barcode', size=(30, 1), font=("Helvetica", 25), text_color='white')],
[sg.Text('_'  * 100, size=(70, 1))],
   [sg.Text('Scan Barcode')],
   [sg.InputText(key='-Barcode')],
[sg.Text('Station #')],
[sg.InputCombo(['1', '2', '3', '4', '5', '6', '7', '8', ], size=(10, 8), default_value='1', key='Station')],
[sg.Text('Run Attempt')],
[sg.InputCombo(['1', '2'], size=(10, 3), default_value='1', key='Attempt')],
[sg.Text('Scan Date')],
[sg.InputText(readable, size=(20, 1), key='ScanDate')],
[sg.Button('Ok'), sg.Button('Cancel')]]  #, sg.Button('Customized', button_color=('white', 'green'))

# Create the Window
window = sg.Window('Barcode Tracking', auto_size_text=True, default_element_size=(20, 1)).Layout(layout)
# Event Loop to process "events" and get the "values" of the inputs
while True:
    event, values = window.read()
    if event == sg.WIN_CLOSED or event == 'Cancel': # if user closes window or clicks cancel
        break
    print('')
    print(values['-Barcode'])
    print(values['Station'])
    print(values['Attempt'])
    print(values['ScanDate'])
    cursorObj=con.cursor()
    sqlite_insert_query="""INSERT INTO tblScans (Barcode, Station, Attempt, ScanDate) VALUES ('MAD12345', 1, 1, '2020-07-16 12:27:30')"""
    count=cursorObj.execute(sqlite_insert_query)

con.commit()
window.close()

Below is the line that I get stuck on. I feel like I've tried almost every syntax I can think of to get the values passed to the db, but evidently I haven't.

sqlite_insert_query="""INSERT INTO tblScans (Barcode, Station, Attempt, ScanDate) VALUES (['-Barcode'], ['Station'], ['Attempt'], [ScanDate'])"""

With the above line, I'm getting unrecognized token:

[jrbond]

sqlite_insert_query="""INSERT INTO tblScans (Barcode, Station, Attempt, ScanDate) VALUES (['-Barcode'], ['Station'], ['Attempt'], [ScanDate'])"""

With the above line, I'm getting unrecognized token:

EDIT: To clarify, I was missing the ' in front of my 'ScanDate' in the above line. It is correct in my code, and the code does not work. Could still use some assistance. Thanks...

sqlite_insert_query="""INSERT INTO tblScans (Barcode, Station, Attempt, ScanDate) VALUES (['-Barcode'], ['Station'], ['Attempt'], ['ScanDate'])"""

---

Sorry for blasting my own post, but I am continuously trying different things. Below is the latest attempt:

import PySimpleGUI as sg
import automationhat, sqlite3, datetime, time
from gpiozero import LED
from signal import pause
#def InserttVariable(Barcode, Station, Attempt, ScanDate)
#sg.theme('SandyBeach')   # Add a touch of color

con = sqlite3.connect ('BarcodeScans.db')
ts=time.localtime()
readable=(time.strftime("%Y-%m-%d %H:%M:%S",ts))

# All the stuff inside your window.
layout = [[sg.Text('Scan Router Barcode', size=(30, 1), font=("Helvetica", 25), text_color='white')],
[sg.Text('_'  * 100, size=(70, 1))],
   [sg.Text('Scan Barcode')],
   [sg.InputText(key='-Barcode')],
[sg.Text('Station #')],
[sg.InputCombo(['1', '2', '3', '4', '5', '6', '7', '8', ], size=(10, 8),default_value='1', key='Station')],
[sg.Text('Run Attempt')],
[sg.InputCombo(['1', '2'], size=(10, 3),default_value='1', key='Attempt')],
[sg.Text('Scan Date')],
[sg.InputText(readable, size=(20, 1), key='ScanDate')],
[sg.Button('Ok'), sg.Button('Cancel')]]  #, sg.Button('Customized', button_color=('white', 'green'))

# Create the Window
window = sg.Window('Barcode Tracking', auto_size_text=True, default_element_size=(20, 1)).Layout(layout)
# Event Loop to process "events" and get the "values" of the inputs
while True:
    event, values = window.read()
    if event == sg.WIN_CLOSED or event == 'Cancel': # if user closes window or clicks cancel
        break
    print('')
    print(values['-Barcode'])
    print(values['Station'])
    print(values['Attempt'])
    print(values['ScanDate'])
    cursorObj=con.cursor()
    sqlite_insert_query="""INSERT INTO tblScans (Barcode, Station, Attempt, ScanDate) VALUES (?, ?, ?, ?)""", ('-Barcode', 'Station', 'Attempt', 'ScanDate')
    count=cursorObj.execute(sqlite_insert_query)


    con.commit()

window.close()

Here is the results from the immediate (print) window:

MAD987654
1
1
2020-07-20 05:51:59
Traceback (most recent call last):
File "/home/pi/python-projects/ScanGUI.py", line 39, in <module>
count=cursorObj.execute(sqlite_insert_query)
ValueError: operation parameter must be str
>>>

As you can see, the print function displays the results from the keys, so I should be able to somehow send those resulting variables to my sqlite database. Correct?

[menator01]

Look at the difference in your print statement values and your insert values.

---

This works. You should remove the - in your barcode. It was giving problems.

#! /usr/bin/env python3
import sqlite3

query = 'create table tbl_scans(barcode varchar, station varchar, attempt varchar5, scan_date varchar)'

conn = sqlite3.connect('mydb.db')
cursor = conn.cursor()
conn.execute(query)
conn.commit()
values= {}

values['barcode'] = 'MAD987654'
values['station'] = 1
values['attempt'] = 1
values['scan_date'] = '2020-07-20 05:51:59'

print(values)
mylist = (values['barcode'], values['station'], values['attempt'], values['scan_date'])
query = f'insert into tbl_scans (barcode, station, attempt,scan_date) values(?,?,?,?)'
conn.execute(query, mylist)

conn.commit()
conn.close()

[jrbond]

Look at the difference in your print statement values and your insert values.
This works. You should remove the - in your barcode. It was giving problems.

We had the same thought as far as the -Barcode key. I removed the '-' from it a little while ago. I appreciate you taking a look at my problem. Unfortunately, your solution is still using static input

values['barcode'] = 'MAD987654'
values['station'] = 1
values['attempt'] = 1
values['scan_date'] = '2020-07-20 05:51:59'

I want the value passed to the sqlite db to be the actual user input variables that are gotten via the PySimpleGUI. That's where I'm having the problem. My latest attempt this morning was to seperate the INSERT INTO statement and the variables, so it wasn't a tuple. See below:

import PySimpleGUI as sg
import automationhat, sqlite3, datetime, time

con = sqlite3.connect ('BarcodeScans.db')
ts=time.localtime()
readable=(time.strftime("%Y-%m-%d %H:%M:%S",ts))

# Window content
layout = [[sg.Text('Scan Router Barcode', size=(30, 1), font=("Helvetica", 25), text_color='white')],
[sg.Text('_'  * 100, size=(70, 1))],
   [sg.Text('Scan Barcode')],
   [sg.InputText(key='Code')],
[sg.Text('Station #')],
[sg.InputCombo(['1', '2', '3', '4', '5', '6', '7', '8', ], size=(10, 8),default_value='1', key='Station')],
[sg.Text('Run Attempt')],
[sg.InputCombo(['1', '2'], size=(10, 3),default_value='1', key='Attempt')],
[sg.Text('Scan Date')],
[sg.InputText(readable, size=(20, 1), key='ScanDate')],
[sg.Button('Ok'), sg.Button('Cancel')]]  #, sg.Button('Customized', button_color=('white', 'green'))

# Create the Window
window = sg.Window('Barcode Tracking', auto_size_text=True, default_element_size=(20, 1)).Layout(layout)
# Event Loop to process "events" and get the "values" of the inputs
while True:
    event, values = window.read()
    if event == sg.WIN_CLOSED or event == 'Cancel': # if user closes window or clicks cancel
        break
    print(values['Code'])
    print(values['Station'])
    print(values['Attempt'])
    print(values['ScanDate'])
    cursorObj=con.cursor()
    sqlite_insert_query="""INSERT INTO 'tblScans' ('Barcode', 'Station', 'Attempt', 'ScanDate') VALUES (?, ?, ?, ?)"""
    count=cursorObj.execute(sqlite_insert_query,(Code, Station, Attempt, ScanDate))


    con.commit()

window.close()

It seems that no matter how I format the following line, it either throws error messages like 'not defined', 'not a valid column', or it sends the data as static with the ' ' around the keys.

    count=cursorObj.execute(sqlite_insert_query,(Code, Station, Attempt, ScanDate))

[menator01]

You do realize this print(values['Code']) is a dict right? Dicts have a key value pair. {'value':'key'}

[jrbond]

You do realize this print(values['Code']) is a dict right? Dicts have a key value pair. {'value':'key'}

I only just started programming with Python and PySimpleGUI last week, so everything I have learned and is in my code is just things I've gotten from Docs and Google. Not to diminish what the experts here can teach me :).

Thanks so much for your assistance with this. What just worked for me is:

sqlite_insert_query="""INSERT INTO 'tblScans' ('Barcode', 'Station', 'Attempt', 'ScanDate') VALUES (?, ?, ?, ?)"""
count=cursorObj.execute(sqlite_insert_query,(values['Code'], values['Station'], values['Attempt'], values['ScanDate']))

I'm certain that I've tried that before, but I don't think I tried it with this same format. I think when I tried that, I still had the columns and values on the same line, trying to execute together. Thanks so much for your assistance,and pointing me in the right direction! :)