Trying to insert a record into SQL Server with pyodbc and tkinter

Question

I am trying to write an application that will easily update a database when new equipment is added. So far I am able to connect to db and have created a GUI to show a few things.

Now I am stuck at trying to insert a new record from an entry that a user makes. Basically a user enters 3 values: EquipmentName, HostName, and ValidationName that will create a new record in the database.

I am getting an error:

line 32, in submit
'ValidationName': ValidationName.get() pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')

I have tried to google everything, but no luck in

Sorry if this doesn't make sense, this is my first time trying to code with python.

from tkinter import *
import pyodbc

#Create application and size
root = Tk()
root.title('Application')
root.geometry("400x400")

# Connect to SQL Server database
conn = pyodbc.connect('Driver={SQL Server};'
                  'Server=USGRE-PC0XLKJM\SQLEXPRESS;'
                  'Database=LAB;'
                  'Trusted_Connection=yes;')
#Create cursor
cursor = conn.cursor()
cursor.execute('SELECT * FROM LAB.dbo.LabSystem')

#Create submit function for database
def submit():
# Connect to SQL Server database
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=USGRE-PC0XLKJM\SQLEXPRESS;'
                      'Database=LAB;'
                      'Trusted_Connection=yes;')
# Create cursor
cursor = conn.cursor()
# Insert into table
cursor.execute("INSERT INTO LAB.dbo.LabSystem VALUES (:EquipmentName, :HostName, :ValidationName)",
               {
                   'EquipmentName': EquipmentName.get(),
                   'HostName': HostName.get(),
                   'ValidationName': ValidationName.get()
               })
# Commit changes
conn.commit()

# Close connection
conn.close()

# Clear the text boxes
EquipmentName.delete(0, END)
HostName.delete(0, END)
ValidationName.delete(0, END)

# Create query function
def query():
# Connect to SQl Server DB
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=USGRE-PC0XLKJM\SQLEXPRESS;'
                      'Database=LAB;'
                      'Trusted_Connection=yes;')

# Create cursor
cursor = conn.cursor()
# Query the database
cursor.execute("SELECT *,SystemID FROM LAB.dbo.LabSystem")
records = cursor.fetchall()
#print(records)

#Loop through results
print_records = ''
for record in records:
    print_records += str(record[0]) + " " +str(record[2]) + " " "\n"

query_label = Label(root, text=print_records)
query_label.grid(row=3, column=0, columnspan=2)

# Commit changes
conn.commit()

# Close connection
conn.close()

# Create text boxes
EquipmentName = Entry(root, width=30)
EquipmentName.grid(row=0, column=1, padx=20)

HostName = Entry(root, width=30)
HostName.grid(row=1, column=1)

ValidationName = Entry(root, width=30)
ValidationName.grid(row=2, column=1)

# Create labels for text boxes
EquipmentName_label = Label(root, text="Equipment Name")
EquipmentName_label.grid(row=0, column=0)

HostName_label = Label(root, text="Host Name")
HostName_label.grid(row=1, column=0)

ValidationName_label = Label(root, text="Validation Name")
ValidationName_label.grid(row=2, column=0)

# Create submit button to add entries
submit_btn = Button(root, text="Add record", command=submit)
submit_btn.grid(row=6, column=0, columnspan=2, pady=10, padx=10)

# Create a query button
query_btn = Button(root, text="Show Records", command=query) 
query_btn.grid(row=7, column=0, columnspan=2, pady=10, padx=10, ipadx=137)

#Commit changes
conn.commit()

# Close connection
conn.close()

root.mainloop()

score 1 · Accepted Answer · answered Oct 07 '19 at 23:59

1

As far as concerns, pyodbc does not support named parameters.

You could try using positional parameters instead of named parameters:

cursor.execute(
    "INSERT INTO LAB.dbo.LabSystem VALUES (?, ?, ?)",
    EquipmentName.get(),
    HostName.get(),
    ValidationName.get()
)

answered Oct 07 '19 at 23:59

GMB

216,147
25
84
135

Thanks for the reply! I am getting a different error now: line 31, in submit ValidationName.get() pyodbc.Error: ('21S01', '[21S01] [Microsoft][ODBC SQL Server Driver][SQL Server]Column name or number of supplied values does not match table definition. (213) (SQLExecDirectW); [21S01] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)') – jellisee Oct 08 '19 at 16:56
@jellisee: what is the structure of the table? You probably need to provide the names of the columns that you want to insert, something like `INSERT INTO LAB.dbo.LabSystems(EquipmentName, HostName, ValidationName) VALUES(?, ?, ?)` – GMB Oct 08 '19 at 17:04
This was it, I knew I was missing something simple! Thank you so much! Obviously I have to tell it which tables to update lol – jellisee Oct 09 '19 at 14:09

score 0 · Answer 2 · 2019-10-08T21:14:40.933

You're so close, you need to parse the dict values - create a function to parse the dict to an insert statement

def values_statement(lst):
    if lst:
        _ = [tuple([str(l).replace("'", "") for l in ls.values()])
             for ls in lst]
        return ','.join([str(i) for i in _])

I assume the final_dict now is just one row? Will be more efficient to make this a list of dicts. Is that feasible?

final_dict = [{
    'EquipmentName': EquipmentName.get(),
    'HostName': HostName.get(),
    'ValidationName': ValidationName.get()
}]

cursor.execute("INSERT INTO LAB.dbo.LabSystem VALUES (EquipmentName, HostName, ValidationName)".format(values_statement(final_dict))
)

Trying to insert a record into SQL Server with pyodbc and tkinter

2 Answers2