My Pandas Dataframe named as data_five_minutes:
| script_id | date_time | open | |
|---|---|---|---|
| 0 | 1 | 2019-01-11 09:35:00 | 25 |
| 1 | 1 | 2019-01-11 09:40:00 | 30 |
| 2 | 1 | 2019-01-11 09:45:00 | 48 |
Full ss:
Now I was trying to get only data which are having script_id as 1:
data = ps.sqldf(f"""SELECT d4.script_id,d4.date_time,d4.open,d4.high,d4.low,d4.close,d4.volume
FROM data_five_minutes d4 WHERE d4.script_id = {id}""")
this is just small condition but in original it has nested where clause so SQL query will be passed on original code as earlier i was passing the same query in database.
I am getting below error:
Traceback (most recent call last): File "C:\Python\lib\site-packages\sqlalchemy\engine\base.py", line 1752, in _e xecute_context cursor, statement, parameters, context File "C:\Python\lib\site-packages\sqlalchemy\engine\default.py", line 714, in do_executemany cursor.executemany(statement, parameters) sqlite3.InterfaceError: Error binding parameter 2 - probably unsupported type.
The above exception was the direct cause of the following exception:
Traceback (most recent call last): File "D:\python_projects\auto\testing.py", line 70, in FROM data_five_minutes d4 WHERE d4.script_id = 1""") File "C:\Python\lib\site-packages\pandasql\sqldf.py", line 156, in sqldf return PandaSQL(db_uri)(query, env) File "C:\Python\lib\site-packages\pandasql\sqldf.py", line 58, in call write_table(env[table_name], table_name, conn) File "C:\Python\lib\site-packages\pandasql\sqldf.py", line 121, in write_table
index=not any(name is None for name in df.index.names)) # load index into d b if all levels are named File"C:\Python\lib\site-packages\pandas\io\sql.py", line 518, in to_sql method=method, File "C:\Python\lib\site-packages\pandas\io\sql.py", line 1320, in to_sql table.insert(chunksize, method=method) File "C:\Python\lib\site-packages\pandas\io\sql.py", line 756, in insert exec_insert(conn, keys, chunk_iter) File "C:\Python\lib\site-packages\pandas\io\sql.py", line 670, in _execute_ins ert conn.execute(self.table.insert(), data) File "C:\Python\lib\site-packages\sqlalchemy\engine\base.py", line 1263, in ex ecute return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS) File "C:\Python\lib\site-packages\sqlalchemy\sql\elements.py", line 324, in _e xecute_on_connection self, multiparams, params, execution_options File "C:\Python\lib\site-packages\sqlalchemy\engine\base.py", line 1462, in _e xecute_clauseelement cache_hit=cache_hit, File "C:\Python\lib\site-packages\sqlalchemy\engine\base.py", line 1815, in e xecute_context e, statement, parameters, cursor, context File "C:\Python\lib\site-packages\sqlalchemy\engine\base.py", line 1996, in h andle_dbapi_exception sqlalchemy_exception, with_traceback=exc_info[2], from=e File "C:\Python\lib\site-packages\sqlalchemy\util\compat.py", line 207, in rai se raise exception File "C:\Python\lib\site-packages\sqlalchemy\engine\base.py", line 1752, in _e xecute_context cursor, statement, parameters, context File "C:\Python\lib\site-packages\sqlalchemy\engine\default.py", line 714, in do_executemany cursor.executemany(statement, parameters) sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) Error binding parameter 2 - probably unsupported type. [SQL: INSERT INTO data_five_minutes (script_id, date_time, open, high, low, clos e, volume) VALUES (?, ?, ?, ?, ?, ?, ?)] [parameters: ((1, '2021-05-25 10:30:00.000000', Decimal('1978.6'), Decimal('1985 '), Decimal('1978.1'), Decimal('1985'), Decimal('323')), (1, '2021-05-25 10:35:0 0.000000', Decimal('1985'), Decimal('1986.05'), Decimal('1982.75'), Decimal('198 3.85'), Decimal('954')), and so on...] (Background on this error at: https://sqlalche.me/e/14/rvf5)
Process returned 1 (0x1) execution time : 2.672 s Press any key to continue . . .
