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;")