Unsupported type error in sqlite3 using python

Question

I am scrapping some websties and wants to insert those data to a sqlite3 database. I succesfully created and connected tables and the database

conn.execute('''
    CREATE TABLE datatable (id INTEGER PRIMARY KEY, Date TEXT,
                       Time TEXT, html_link BLOB, Desc BLOB, Tup BLOB)
''')

id is the auto_incriment value
date="03-09-2016"
time="12:34:56"
html="http://web1.com"
des="Oct 21, 2010 - In the code below row is a tuple of 200 elements (numbers) and listOfVars is a tuple of 200 strings that are variable names in the testTable ."
arr=(1,2,3)

I then tried to Insert values to this table

conn.execute('''INSERT INTO datatable (Date, Time, html_link, Desc, Tup)
                  VALUES(?,?,?,?,?)''', (date,time,html,des,arr))

But its throwing this error

VALUES(?,?,?,?,?)''', (date,time,html,des,arr)) sqlite3.InterfaceError: Error binding parameter 4 - probably unsupported type.

I know I made some error in asigning proper datatype to the sqlite table. How can I find the error causing column and how to solve this problem.

And what is the type of that parameter?

CL.
– CL.

2016-03-18 08:20:19 +00:00
Commented Mar 18, 2016 at 8:20 — CL.
– CL., Commented Mar 18, 2016 at 8:20

Alexey Smirnov · Accepted Answer · 2016-03-18 11:32:26Z

2

Try preserving tuple you are trying to store with pickle. Here is the working example.

import sqlite3
import pickle

conn = sqlite3.connect(":memory:")
conn.text_factory=str
cur=conn.cursor()
cur.execute('''
    CREATE TABLE datatable (id INTEGER PRIMARY KEY, Date TEXT,
                       Time TEXT, html_link BLOB, Desc BLOB, Tup BLOB)
''')

date="03-09-2016"
time="12:34:56"
html="http://web1.com"
des="Oct 21, 2010 - In the code below row is a tuple of 200 elements (numbers) and listOfVars is a tuple of 200 strings that are variable names in the testTable ."
arr=(1,2,3)



cur.execute('''INSERT INTO datatable (Date, Time, html_link, Desc, Tup) VALUES(?,?,?,?,?)''', (date,time,html,des,pickle.dumps( arr,2 )))
cur.execute('''SELECT * from datatable''')
print pickle.loads(cur.fetchall()[0][5])
conn.close()

edited Mar 18, 2016 at 11:32

answered Mar 18, 2016 at 8:44

Alexey Smirnov

2,62316 silver badges21 bronze badges

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

1 Comment

Eka Over a year ago

Its returning this error TypeError: buffer object expected

Collectives™ on Stack Overflow

Unsupported type error in sqlite3 using python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related