CODEWITHSUNDEEP
pythonsqlite
Eka
Eka

Reputation: 15000

Unsupported type error in sqlite3 using python

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.

Upvotes: 1

Views: 1730

Answers (1)

Alexey Smirnov
Alexey Smirnov

Reputation: 2613

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

Upvotes: 2

Related Questions