Python “InterfaceError: Error binding parameter 2 - probably unsupported type.”

Question

When I run the following code, I keep getting the "InterfaceError: Error binding parameter 2 - probably unsupported type" error, and I need help identifying where the problem is. Everything works fine up until I try to send the data to sql through.

anagramsdf.to_sql('anagrams',con=conn,if_exists='replace',index=False)
cdf=pd.read_sql("select (distinct ID) from anagrams;",conn)

import pandas as pd
import sqlite3

conn = sqlite3.connect("anagrams")
xsorted=sorted(anagrams,key=sorted)
xunique=[x[0] for x in anagrams]

xunique=pd.Series(xunique)
xanagrams=pd.Series(anagrams)
anagramsdf=pd.concat([xunique,dfcount,xanagrams],axis=1)
anagramsdf.columns=['ID','anagram_count','anagram_list']

c=conn.cursor()
c.execute("create table anagrams(ID, anagram_count, anagram_list)")
conn.commit()

anagramsdf.to_sql('anagrams',con=conn,if_exists='replace',index=False)
cdf=pd.read_sql("select (distinct ID) from anagrams;",conn)

cdf=pd.read_sql("select max(anagram_count) from anagrams;",conn)
cdf
def print_full(x):
    pd.set_option('display.max_rows', len(x))
    print(x)
    pd.reset_option('display.max_rows')

cdf=pd.read_sql("select * from anagrams where anagram_count=12;",conn)
pd.set_option('max_colwidth',200)

Full traceback error:

Traceback (most recent call last):
  File "sqlpandas.py", line 88, in <module>
    anagramsdf.to_sql('anagrams',con=conn,if_exists='replace',index=False)
  File "/Users/andrewclark/anaconda/lib/python2.7/site-packages/pandas/core/generic.py", line 982, in to_sql
    dtype=dtype)
  File "/Users/andrewclark/anaconda/lib/python2.7/site-packages/pandas/io/sql.py", line 549, in to_sql
    chunksize=chunksize, dtype=dtype)
  File "/Users/andrewclark/anaconda/lib/python2.7/site-packages/pandas/io/sql.py", line 1567, in to_sql
    table.insert(chunksize)
  File "/Users/andrewclark/anaconda/lib/python2.7/site-packages/pandas/io/sql.py", line 728, in insert
    self._execute_insert(conn, keys, chunk_iter)
  File "/Users/andrewclark/anaconda/lib/python2.7/site-packages/pandas/io/sql.py", line 1357, in _execute_insert
    conn.executemany(self.insert_statement(), data_list)
sqlite3.InterfaceError: Error binding parameter 2 - probably unsupported type.

Snippet from Dataframe:

             ID  anagram_count           anagram_list
0            aa              1                  (aa,)
1      anabaena              1            (anabaena,)
2      baaskaap              1            (baaskaap,)
3      caracara              1            (caracara,)
4      caragana              1            (caragana,)

Can you reduce the example to a couple of lines? All the code with urllib, itertools, print statements, etc., doesn't add anything useful. See How to create a Minimal, Complete, and Verifiable example — Peter Wood
Also, depending upon which line the error is raised, this could be an issue with pandas, and the question needs appropriate tagging. — Peter Wood
Can you show anagramsdf.info()? Or ideally, make a reproducible example to make up the dataframe. — joris
The anagram_list column contains tuples, which is not supported by sqlite to insert in a dataframe. Therefore converting it to strings solves the issue. — joris

Andrew Andrew · Accepted Answer · 2015-11-05T01:47:01

I used the following code to change the datatypes to strings, and this solved the problem:

anagramsdf.dtypes
anagramsdf['ID']= anagramsdf['ID'].astype('str')
anagramsdf['anagram_list']= anagramsdf['anagram_list'].astype('str')
anagramsdf.to_sql('anagramsdf',con=conn,if_exists='append',index=False)

Python “InterfaceError: Error binding parameter 2 - probably unsupported type.”

3 Answers