SQLite Forum

ValueError: parameters are of unsupported type
Login

ValueError: parameters are of unsupported type

(1) By MichaelW on 2020-12-18 17:19:38 [link] [source]

Hi All
I have created a series of 'fields' all bar the first are obtained from Entries using Tkinter; the first field is serial number generated by the program.  These are the lines of code with which I am having a problem:-
def submit_new():
	conn = sqlite3.connect('NiCE_book.db')
	cur = conn.cursor()
	cur.execute("INSERT INTO NiCE_clients VALUES (:f1, :f2, :f3, :f4, :f5, :f6, :f7, :f8, :f9, :f10, :f11, :f12, :f13, :f14, :f15)",
				{
				'f1', Clnt_Num, 
				'f2', client_name.get(),
				'f3', address_name.get(),
				'f4', city_name.get(),
				'f5', state_name.get(),
				'f6', zipcode_name.get(),
				'f7', email_name.get(),
				'f8', IP_1_name.get(),
				'f9', IP_2_name.get(),
				'f10', Keys_ordered_name.get(),
				'f11', Order_number_name.get(),
				'f12', Date_ordered_name.get(),
				'f13', Agent_code_name.get(),
				'f14', Client_logo_name.get(),
				'f15', Billing_mth_name.get()
				})	
	comm.comit()
	com.close()	
No matter how I try to express Clnt_Num as a Str,Var, in () in '' etc I get the error ValueError: parameters are of unsupported type.
Hair is being torn out!!!!  Thanks for any help.

(2) By David Raymond (dvdraymond) on 2020-12-18 18:00:15 in reply to 1 [link] [source]

So what makes you sure it's Clnt_Num that's the problem child? Have you checked the types of all the other 14 parameters to make sure they're something ok?

Also, you're gonna hit a problem at the end there where you have M's in comm, whereas you have N's in conn above. Also, what is "com" that you are closing?

(3) By MichaelW on 2020-12-18 18:10:53 in reply to 2 [link] [source]

Silly me,  I have corrected the wrong spelling of com & con.
I don't think that it is any of the 14 because I created a mini version :-
root = Tk()	
root.geometry("400x400")

conn = sqlite3.connect('test.db')

c = conn.cursor()
'''
c.execute("""CREATE TABLE test (
		name text,
		addx text,
		zip integar
		)""")
'''

def submit():
	conn = sqlite3.connect('test.db')
	c = conn.cursor()

	c.execute("INSERT INTO test VALUES (:namexx, :addxx, :zipxx)",
			{
				'namexx': name.get(),
				'addxx': addx.get(),
				'zipxx': zipx.get()
			})
	conn.commit()
	conn.close()

	name.delete(0, END)
	addx.delete(0, END)
	zipx.delete(0, END)

name = Entry(root, width=30)
name.grid(row=0, column=1, padx=20)
addx = Entry(root, width=30)
addx.grid(row=1, column=1, padx=20)
zipx = Entry(root, width=30)
zipx.grid(row=2, column=1, padx=20)

name_label = Label(root, text="Name")
name_label.grid(row=0, column=0)
addx_label = Label(root, text="Address")
addx_label.grid(row=1, column=0)
zipx_label = Label(root, text="zipx")
zipx_label.grid(row=2,column=0)

but = Button(root, text="Add record", command=submit)
but.grid(row=3, column=0, columnspan=2, padx=10, pady=10, ipadx=100)

conn.commit()
conn.close()

root.mainloop()

This works just fine.
Cheers Michael

(4) By David Raymond (dvdraymond) on 2020-12-18 18:33:10 in reply to 3 [link] [source]

Nonetheless for debugging purposes I'd do something like


insertValues = {
'f1', Clnt_Num, 
'f2', client_name.get(),
'f3', address_name.get(),
'f4', city_name.get(),
'f5', state_name.get(),
'f6', zipcode_name.get(),
'f7', email_name.get(),
'f8', IP_1_name.get(),
'f9', IP_2_name.get(),
'f10', Keys_ordered_name.get(),
'f11', Order_number_name.get(),
'f12', Date_ordered_name.get(),
'f13', Agent_code_name.get(),
'f14', Client_logo_name.get(),
'f15', Billing_mth_name.get()
}
for key, value in insertValues.items():
    print(key, type(value), value)

cur.execute("INSERT INTO NiCE_clients VALUES (:f1, :f2, :f3, :f4, :f5, :f6, :f7, :f8, :f9, :f10, :f11, :f12, :f13, :f14, :f15)", insertValues)

And then paste the results, along with the full exception text.

(6) By MichaelW on 2020-12-18 19:13:34 in reply to 4 [link] [source]

Hi David Thanks for your help. I replaced my code with yours as followed:-

def submit_new(): conn = sqlite3.connect('NiCE_book.db') cur = conn.cursor()

insertValues = {
'f1', Clnt_Num, 
'f2', client_name.get(),
'f3', address_name.get(),
'f4', city_name.get(),
'f5', state_name.get(),
'f6', zipcode_name.get(),
'f7', email_name.get(),
'f8', IP_1_name.get(),
'f9', IP_2_name.get(),
'f10', Keys_ordered_name.get(),
'f11', Order_number_name.get(),
'f12', Date_ordered_name.get(),
'f13', Agent_code_name.get(),
'f14', Client_logo_name.get(),
'f15', Billing_mth_name.get()
}

for key, value in insertValues.items():

print(key, type(value), value)

cur.execute("INSERT INTO NiCE_clients VALUES (:f1, :f2, :f3, :f4, :f5, :f6, :f7, :f8, :f9, :f10, :f11, :f12, :f13, :f14, :f15)", insertValues)

conn.comit()
conn.close()	

You can see the I # out the print lines as these showed an error. When I ran the new code I get the following error:-

ValueError: parameters are of unsupported type

I can confirm that the Clnt_Num, Keys_ordered_name and the Billing_mth_name have been assigned as Intergers in the database the rest are Strings

Cheers Michael

(7) By Keith Medcalf (kmedcalf) on 2020-12-18 20:04:39 in reply to 4 [source]

That should be, of course:

insertValues = {
'f1': Clnt_Num, 
'f2': client_name.get(),
'f3': address_name.get(),
'f4': city_name.get(),
'f5': state_name.get(),
'f6': zipcode_name.get(),
'f7': email_name.get(),
'f8': IP_1_name.get(),
'f9': IP_2_name.get(),
'f10': Keys_ordered_name.get(),
'f11': Order_number_name.get(),
'f12': Date_ordered_name.get(),
'f13': Agent_code_name.get(),
'f14': Client_logo_name.get(),
'f15': Billing_mth_name.get()
}
for key, value in insertValues.items():
    print(key, type(value), value)

cur.execute("INSERT INTO NiCE_clients VALUES (:f1, :f2, :f3, :f4, :f5, :f6, :f7, :f8, :f9, :f10, :f11, :f12, :f13, :f14, :f15)", insertValues)

because .items() only applies to dictionary-like objects and not set-like objects; the use of .items() by implication indicating that you expect insertValues to be a dictionary-like object. Also, the second parameter to the .execute method must be a dictionary like object since the SQL is using named parameters.

(8) By Keith Medcalf (kmedcalf) on 2020-12-18 20:27:22 in reply to 7 [link] [source]

Python 3.8.6 (tags/v3.8.6:db45529, Sep 23 2020, 15:52:53) [MSC v.1927 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> db = sqlite3.connect('', isolation_level=None)
>>> setobj = {'a', 'b', 'c', 'd'}
>>> dictobj = {'a': 'b', 'c': 'd'}
>>> db.cursor().execute('select :a, :c', setobj).fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: parameters are of unsupported type
>>> db.cursor().execute('select :a, :c', dictobj).fetchall()
[('b', 'd')]
>>>

(5.2) By Keith Medcalf (kmedcalf) on 2020-12-18 19:07:37 edited from 5.1 in reply to 1 [link] [source]

If you expect the construct to be a dictionary then you need to use a : after the key. This is basic python. What you are constructing is a set and the parameters argument to .execute needs to be either a sequence (list, tuple, etc) if the placeholders in the statement are positional; or, if they are "named" then must be a dictionary like object. A set object is neither of these and is therefore a ValueError.

def submit_new():
	conn = sqlite3.connect('NiCE_book.db')
	cur = conn.cursor()
	cur.execute("INSERT INTO NiCE_clients VALUES (:f1, :f2, :f3, :f4, :f5, :f6, :f7, :f8, :f9, :f10, :f11, :f12, :f13, :f14, :f15)",
				{
				'f1': Clnt_Num, 
				'f2': client_name.get(),
				'f3': address_name.get(),
				'f4': city_name.get(),
				'f5': state_name.get(),
				'f6': zipcode_name.get(),
				'f7': email_name.get(),
				'f8': IP_1_name.get(),
				'f9': IP_2_name.get(),
				'f10': Keys_ordered_name.get(),
				'f11': Order_number_name.get(),
				'f12': Date_ordered_name.get(),
				'f13': Agent_code_name.get(),
				'f14': Client_logo_name.get(),
				'f15': Billing_mth_name.get()
				})	
	conn.commit()
        cur.close()
	conn.close()	

Of course, we have no idea what the first column of the table NiCE_clients is, but you need to have your Clnt_Num be the appropriate type. The rest are all text strings as that is what the .get method of TkInter returns (also note that .get() probably does not return None for an empty field but rather probably returns an empty text string, and in any event probably indiscriminate padding with spaces that will appear "invisible" but will significantly affect any queries that you issue against this table).