Stack Exchange
Getting InterfaceError on PandaSQL Query in Jupyter Notebook
I am getting this error message
**Error Message:
**

pysqldf("SELECT * FROM em_tweets;")
pysqldf("SELECT * FROM em_tweets;")
InterfaceError                            Traceback (most recent call last)
File ~\anaconda3\lib\site-packages\sqlalchemy\engine\base.py:1799, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
1798     if not evt_handled:
1799         self.dialect.do_executemany(
1800             cursor, statement, parameters, context
1801         )
1802 elif not parameters and context.no_parameters:
File ~\anaconda3\lib\site-packages\sqlalchemy\engine\default.py:729, in DefaultDialect.do_executemany(self, cursor, statement, parameters, context)
728 def do_executemany(self, cursor, statement, parameters, context=None):
729     cursor.executemany(statement, parameters)
InterfaceError: Error binding parameter 3 - probably unsupported type.
The above exception was the direct cause of the following exception:
InterfaceError                            Traceback (most recent call last)
Cell In[15], line 1
1 pysqldf("SELECT * FROM em_tweets;")
Cell In[14], line 2, in (q)
1 from pandasql import sqldf
2 pysqldf = lambda q: sqldf(q, globals())
File ~\anaconda3\lib\site-packages\pandasql\sqldf.py:156, in sqldf(query, env, db_uri)
124 def sqldf(query, env=None, db_uri='sqlite:///:memory:'):
125     """
126     Query pandas data frames using sql syntax
127     This function is meant for backward compatibility only. New users are encouraged to use the PandaSQL class.
(...)
154     >>> sqldf("select avg(x) from df;", locals())
155     """
156     return PandaSQL(db_uri)(query, env)
File ~\anaconda3\lib\site-packages\pandasql\sqldf.py:58, in PandaSQL.call(self, query, env)
56         continue
57     self.loaded_tables.add(table_name)
58     write_table(env[table_name], table_name, conn)
60 try:
61     result = read_sql(query, conn)
File ~\anaconda3\lib\site-packages\pandasql\sqldf.py:120, in write_table(df, tablename, conn)
117 with catch_warnings():
118     filterwarnings('ignore',
119                    message='The provided table name '%s' is not found exactly as such in the database' % tablename)
120     to_sql(df, name=tablename, con=conn,
121            index=not any(name is None for name in df.index.names))
File ~\anaconda3\lib\site-packages\pandas\io\sql.py:695, in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method, engine, **engine_kwargs)
690 elif not isinstance(frame, DataFrame):
691     raise NotImplementedError(
692         "'frame' argument should be either a Series or a DataFrame"
693     )
695 return pandas_sql.to_sql(
696     frame,
697     name,
698     if_exists=if_exists,
699     index=index,
700     index_label=index_label,
701     schema=schema,
702     chunksize=chunksize,
703     dtype=dtype,
704     method=method,
705     engine=engine,
706     **engine_kwargs,
707 )
File ~\anaconda3\lib\site-packages\pandas\io\sql.py:1738, in SQLDatabase.to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method, engine, **engine_kwargs)
1726 sql_engine = get_engine(engine)
1728 table = self.prep_table(
1729     frame=frame,
1730     name=name,
(...)
1735     dtype=dtype,
1736 )
1738 total_inserted = sql_engine.insert_records(
1739     table=table,
1740     con=self.connectable,
1741     frame=frame,
1742     name=name,
1743     index=index,
1744     schema=schema,
1745     chunksize=chunksize,
1746     method=method,
1747     **engine_kwargs,
1748 )
1750 self.check_case_sensitive(name=name, schema=schema)
1751 return total_inserted
File ~\anaconda3\lib\site-packages\pandas\io\sql.py:1335, in SQLAlchemyEngine.insert_records(self, table, con, frame, name, index, schema, chunksize, method, **engine_kwargs)
1333     raise ValueError("inf cannot be used with MySQL") from err
1334 else:
1335     raise err
File ~\anaconda3\lib\site-packages\pandas\io\sql.py:1325, in SQLAlchemyEngine.insert_records(self, table, con, frame, name, index, schema, chunksize, method, **engine_kwargs)
1322 from sqlalchemy import exc
1324 try:
1325     return table.insert(chunksize=chunksize, method=method)
1326 except exc.SQLAlchemyError as err:
1327     # GH34431
1328     # https://stackoverflow.com/a/67358288/6067848
1329     msg = r"""((1054, "Unknown column 'inf(e0)?' in 'field list'"))(?#
1330     )|inf can not be used with MySQL"""
File ~\anaconda3\lib\site-packages\pandas\io\sql.py:946, in SQLTable.insert(self, chunksize, method)
943     break
945 chunk_iter = zip(*(arr[start_i:end_i] for arr in data_list))
946 num_inserted = exec_insert(conn, keys, chunk_iter)
947 # GH 46891
948 if is_integer(num_inserted):
File ~\anaconda3\lib\site-packages\pandas\io\sql.py:853, in SQLTable._execute_insert(self, conn, keys, data_iter)
841 """
842 Execute SQL statement inserting data
843
(...)
850    Each item contains a list of values to be inserted
851 """
852 data = [dict(zip(keys, row)) for row in data_iter]
853 result = conn.execute(self.table.insert(), data)
854 return result.rowcount
File ~\anaconda3\lib\site-packages\sqlalchemy\engine\base.py:1306, in Connection.execute(self, statement, *multiparams, **params)
1302     util.raise_(
1303         exc.ObjectNotExecutableError(statement), replace_context=err
1304     )
1305 else:
1306     return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File ~\anaconda3\lib\site-packages\sqlalchemy\sql\elements.py:332, in ClauseElement._execute_on_connection(self, connection, multiparams, params, execution_options, _force)
328 def _execute_on_connection(
329     self, connection, multiparams, params, execution_options, _force=False
330 ):
331     if _force or self.supports_execution:
332         return connection._execute_clauseelement(
333             self, multiparams, params, execution_options
334         )
335     else:
336         raise exc.ObjectNotExecutableError(self)
File ~\anaconda3\lib\site-packages\sqlalchemy\engine\base.py:1498, in Connection._execute_clauseelement(self, elem, multiparams, params, execution_options)
1486 compiled_cache = execution_options.get(
1487     "compiled_cache", self.engine._compiled_cache
1488 )
1490 compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
1491     dialect=dialect,
1492     compiled_cache=compiled_cache,
(...)
1496     linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
1497 )
1498 ret = self._execute_context(
1499     dialect,
1500     dialect.execution_ctx_cls._init_compiled,
1501     compiled_sql,
1502     distilled_params,
1503     execution_options,
1504     compiled_sql,
1505     distilled_params,
1506     elem,
1507     extracted_params,
1508     cache_hit=cache_hit,
1509 )
1510 if has_events:
1511     self.dispatch.after_execute(
1512         self,
1513         elem,
(...)
1517         ret,
1518     )
File ~\anaconda3\lib\site-packages\sqlalchemy\engine\base.py:1862, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
1859             branched.close()
1861 except BaseException as e:
1862     self._handle_dbapi_exception(
1863         e, statement, parameters, cursor, context
1864     )
1866 return result
File ~\anaconda3\lib\site-packages\sqlalchemy\engine\base.py:2043, in Connection.handle_dbapi_exception(self, e, statement, parameters, cursor, context)
2041     util.raise(newraise, with_traceback=exc_info[2], from_=e)
2042 elif should_wrap:
2043     util.raise_(
2044         sqlalchemy_exception, with_traceback=exc_info[2], from_=e
2045     )
2046 else:
2047     util.raise_(exc_info[1], with_traceback=exc_info[2])
File ~\anaconda3\lib\site-packages\sqlalchemy\util\compat.py:208, in raise_(failed resolving arguments)
205     exception.cause = replace_context
207 try:
208     raise exception
209 finally:
210     # credit to
211     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
212     # as the traceback object creates a cycle
213     del exception, replace_context, from_, with_traceback
File ~\anaconda3\lib\site-packages\sqlalchemy\engine\base.py:1799, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
1797                 break
1798     if not evt_handled:
1799         self.dialect.do_executemany(
1800             cursor, statement, parameters, context
1801         )
1802 elif not parameters and context.no_parameters:
1803     if self.dialect._has_events:
File ~\anaconda3\lib\site-packages\sqlalchemy\engine\default.py:729, in DefaultDialect.do_executemany(self, cursor, statement, parameters, context)
728 def do_executemany(self, cursor, statement, parameters, context=None):
729     cursor.executemany(statement, parameters)
InterfaceError: (sqlite3.InterfaceError) Error binding parameter 3 - probably unsupported type.
[SQL: INSERT INTO em_tweets (contributors, coordinates, created_at, display_text_range, entities, favorite_count, favorited, geo, id, id_str, in_reply_to_screen_name, in_reply_to_status_id, in_reply_to_status_id_str, in_reply_to_user_id, in_reply_to_user_id_str, is_quote_status, lang, place, retweet_count, retweeted, screen_name, source, text, truncated, user_id, possibly_sensitive, extended_entities, quoted_status_id, quoted_status_id_str, withheld_copyright, withheld_in_countries, withheld_scope) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
[parameters: ((None, None, '2008-08-04 17:28:51.000000', [0, 74], {'hashtags': [], 'symbols': [], 'urls': [], 'user_mentions': []}, 0, 0, None, 877418565, 877418565, None, None, None, None, None, 0, 'en', None, 0, 0, 'JohnBoozman', 'Twitter Web Client', 'On a conference call about a weekend trip to Iraq to visit Arkansas troops', 0, 5558312, None, None, None, None, None, None, None), (None, None, '2008-08-06 19:04:45.000000', [0, 25], {'hashtags': [], 'symbols': [], 'urls': [], 'user_mentions': []}, 0, 0, None, 879618172, 879618172, None, None, None, None, None, 0, 'nl', None, 0, 0, 'JohnBoozman', 'Twitter Web Client', 'Being interviewed by KTHV', 0, 5558312, None, None, None, None, None, None, None), (None, None, '2008-08-06 20:35:36.000000', [0, 65], {'hashtags': [], 'symbols': [], 'urls': [], 'user_mentions': []}, 0, 0, None, 879695803, 879695803, None, None, None, None, None, 0, 'en', None, 0, 0, 'JohnBoozman', 'Twitter Web Client', 'Being interviewed by KARN on his Arkansas World Trade Center trip', 0, 5558312, None, None, None, None, None, None, None), (None, None, '2008-08-07 13:52:52.000000', [0, 37], {'hashtags': [], 'symbols': [], 'urls': [], 'user_mentions': []}, 0, 0, None, 880393665, 880393665, None, None, None, None, None, 0, 'en', None, 0, 0, 'JohnBoozman', 'Twitter Web Client', "On KWHN in Fort Smith, that's 1320 AM", 0, 5558312, None, None, None, None, None, None, None), (None, None, '2008-08-07 15:12:05.000000', [0, 90], {'hashtags': [], 'symbols': [], 'urls': [], 'user_mentions': []}, 0, 0, None, 880474266, 880474266, None, None, None, None, None, 0, 'en', None, 0, 0, 'JohnBoozman', 'Twitter Web Client', 'Attending a Military of the Purple Heart Ceremony at the VA Medical Center in Fayetteville', 0, 5558312, None, None, None, None, None, None, None), (None, None, '2008-08-07 18:35:25.000000', [0, 45], {'hashtags': [], 'symbols': [], 'urls': [], 'user_mentions': []}, 0, 0, None, 880676101, 880676101, None, None, None, None, None, 0, 'en', None, 0, 0, 'JohnBoozman', 'Twitter Web Client', 'Touring Helath South Hospital in Fayetteville', 0, 5558312, None, None, None, None, None, None, None), (None, None, '2008-08-18 14:07:35.000000', [0, 121], {'hashtags': [], 'symbols': [], 'urls': [], 'user_mentions': []}, 0, 0, None, 891075719, 891075719, None, None, None, None, None, 0, 'en', None, 0, 0, 'JohnBoozman', 'Twitter Web Client', 'conducting a veterans affairs economic opportunity subcommittee field hearing at the northwest arkansas community college', 0, 5558312, None, None, None, None, None, None, None), (None, None, '2008-08-20 12:18:43.000000', [0, 32], {'hashtags': [], 'symbols': [], 'urls': [], 'user_mentions': []}, 0, 0, None, 893175219, 893175219, None, None, None, None, None, 0, 'en', None, 0, 0, 'JohnBoozman', 'Twitter Web Client', 'doing an interview on kurm radio', 0, 5558312, None, None, None, None, None, None, None)  ... displaying 10 of 1243370 total bound parameter sets ...  (None, None, '2017-06-06 17:15:57.000000', [0, 135], {'hashtags': [{'indices': [55, 58], 'text': 'VZ'}, {'indices': [67, 74], 'text': 'Maduro'}], 'symbols': [], 'urls': [{'display_url': 'reuters.com/arti ... (274 characters truncated) ... ent Trump', 'screen_name': 'POTUS'}, {'id': 37666984, 'id_str': '37666984', 'indices': [17, 28], 'name': 'Nikki Haley', 'screen_name': 'nikkihaley'}]}, 0, 0, None, 872140013416443906, 872140013416443904, None, None, None, None, None, 0, 'en', None, 0, 0, 'MarioDB', 'TweetDeck', 'Thank you @POTUS @NikkiHaley for strong stance against #VZ regime. #Maduro + his thugs must be held accountable\n#', 0, 37094727, 0.0, None, None, None, None, None, None), (None, None, '2017-06-06 17:16:00.000000', [0, 119], {'hashtags': [{'indices': [0, 15], 'text': 'WrongCHOICEAct'}], 'symbols': [], 'urls': [], 'user_mentions': []}, 0, 0, None, 872140026737336320, 872140026737336320, None, None, None, None, None, 0, 'en', None, 0, 0, 'PramilaJayapal', 'TweetDeck', '#WrongCHOICEAct will eliminate consumer protection and put Wall Street first again. It will hurt hardworking Americans.', 0, 193441812, None, None, None, None, None, None, None))]
(Background on this error at: https://sqlalche.me/e/14/rvf5)

I tried the following code in jupyter notebook :
In [14]:
from pandasql import sqldf
pysqldf = lambda q: sqldf(q, globals())

In [15]:
pysqldf("SELECT * FROM em_tweets;")



Try installing sqlalchemy 1.4.46 instead.

That's a hell of a lot of error from two lines of code. I think there are some hopes, prayers, and dreams underlying that.

@StuSztukowski I just did. Same error.

Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
