DatabaseError: Execution failed on sql: SELECT name FROM sqlite_master WHERE type='table' AND name=?;

[sanghunka]

from impala.dbapi import connect
import pandas as pd

conn = connect(host='localhost', port=10000,
               database='default',user='406449', password='password!',
               auth_mechanism='PLAIN')

sql_select ="select userid, movieid from u_data_new limit 10"
df_result = pd.read_sql(sql_select, conn)                                   # works well

df_result.to_sql('new_table', conn)    # raise error

DatabaseError: Execution failed on sql: SELECT name FROM sqlite_master WHERE type='table' AND name=?;
Error while compiling statement: FAILED: ParseException line 1:59 cannot recognize input near ''new_table'' ';' '<EOF>' in expression specification
unable to rollback

I installed hadoop, hive, hiveserver2 on my local mac. I can create hive engine. With this engine, i can use pandas read_sql(). but i can't use to_sql() though both method use same engine object.

In my opinion, there is parsing error while handling parameters.

Below is full error messages.

Thank you for this cool project impyla

---------------------------------------------------------------------------
HiveServer2Error                          Traceback (most recent call last)
/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1400             else:
-> 1401                 cur.execute(*args)
   1402             return cur

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/impala/hiveserver2.py in execute(self, operation, parameters, configuration)
    301         self.execute_async(operation, parameters=parameters,
--> 302                            configuration=configuration)
    303         log.debug('Waiting for query to finish')

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/impala/hiveserver2.py in execute_async(self, operation, parameters, configuration)
    342 
--> 343         self._execute_async(op)
    344 

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/impala/hiveserver2.py in _execute_async(self, operation_fn)
    361         self._debug_log_state()
--> 362         operation_fn()
    363         self._last_operation_active = True

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/impala/hiveserver2.py in op()
    339                                       configuration,
--> 340                                       async=True)
    341             self._last_operation = op

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/impala/hiveserver2.py in execute(self, statement, configuration, async)
   1026                                    runAsync=async)
-> 1027         return self._operation('ExecuteStatement', req)
   1028 

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/impala/hiveserver2.py in _operation(self, kind, request)
    956     def _operation(self, kind, request):
--> 957         resp = self._rpc(kind, request)
    958         return self._get_operation(resp.operationHandle)

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/impala/hiveserver2.py in _rpc(self, func_name, request)
    924         self._log_response(func_name, response)
--> 925         err_if_rpc_not_ok(response)
    926         return response

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/impala/hiveserver2.py in err_if_rpc_not_ok(resp)
    703             resp.status.statusCode != TStatusCode.STILL_EXECUTING_STATUS):
--> 704         raise HiveServer2Error(resp.status.errorMessage)
    705 

HiveServer2Error: Error while compiling statement: FAILED: ParseException line 1:59 cannot recognize input near ''new_table'' ';' '<EOF>' in expression specification

During handling of the above exception, another exception occurred:

NotSupportedError                         Traceback (most recent call last)
/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1404             try:
-> 1405                 self.con.rollback()
   1406             except Exception:  # pragma: no cover

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/impala/hiveserver2.py in rollback(self)
     77         # PEP 249
---> 78         raise NotSupportedError
     79 

NotSupportedError: 

During handling of the above exception, another exception occurred:

DatabaseError                             Traceback (most recent call last)
<ipython-input-20-c60572db3bb2> in <module>()
----> 1 df_result.to_sql('new_table', conn)

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/pandas/core/generic.py in to_sql(self, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
   1199         sql.to_sql(self, name, con, flavor=flavor, schema=schema,
   1200                    if_exists=if_exists, index=index, index_label=index_label,
-> 1201                    chunksize=chunksize, dtype=dtype)
   1202 
   1203     def to_pickle(self, path):

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/pandas/io/sql.py in to_sql(frame, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
    468     pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
    469                       index_label=index_label, schema=schema,
--> 470                       chunksize=chunksize, dtype=dtype)
    471 
    472 

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype)
   1500                             if_exists=if_exists, index_label=index_label,
   1501                             dtype=dtype)
-> 1502         table.create()
   1503         table.insert(chunksize)
   1504 

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/pandas/io/sql.py in create(self)
    582 
    583     def create(self):
--> 584         if self.exists():
    585             if self.if_exists == 'fail':
    586                 raise ValueError("Table '%s' already exists." % self.name)

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/pandas/io/sql.py in exists(self)
    570 
    571     def exists(self):
--> 572         return self.pd_sql.has_table(self.name, self.schema)
    573 
    574     def sql_schema(self):

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/pandas/io/sql.py in has_table(self, name, schema)
   1512                  "WHERE type='table' AND name=%s;") % wld
   1513 
-> 1514         return len(self.execute(query, [name, ]).fetchall()) > 0
   1515 
   1516     def get_table(self, table_name, schema=None):

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1407                 ex = DatabaseError("Execution failed on sql: %s\n%s\nunable"
   1408                                    " to rollback" % (args[0], exc))
-> 1409                 raise_with_traceback(ex)
   1410 
   1411             ex = DatabaseError(

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/pandas/compat/__init__.py in raise_with_traceback(exc, traceback)
    338         if traceback == Ellipsis:
    339             _, _, traceback = sys.exc_info()
--> 340         raise exc.with_traceback(traceback)
    341 else:
    342     # this version of raise is a syntax error in Python 3

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1403         except Exception as exc:
   1404             try:
-> 1405                 self.con.rollback()
   1406             except Exception:  # pragma: no cover
   1407                 ex = DatabaseError("Execution failed on sql: %s\n%s\nunable"

/Users/hyundai/.pyenv/versions/3.6.0/envs/py3/lib/python3.6/site-packages/impala/hiveserver2.py in rollback(self)
     76         """Impala doesn't support transactions; raises NotSupportedError"""
     77         # PEP 249
---> 78         raise NotSupportedError
     79 
     80     def cursor(self, user=None, configuration=None, convert_types=True,

DatabaseError: Execution failed on sql: SELECT name FROM sqlite_master WHERE type='table' AND name=?;
Error while compiling statement: FAILED: ParseException line 1:59 cannot recognize input near ''new_table'' ';' '<EOF>' in expression specification
unable to rollback

[kourou1034]

the 'conn' in df.to_sql is sqlalchemy engine. As explained at https://github.com/cloudera/impyla/issues/214, you can try this

[Debbiepimpo]

im using pymysql for the connection with the db as I have the server credentials. can I use sqlalchemy with it?

[manav3475]

import pandas as pd try: mydb = connection.connect(host="localhost",database="student",user="root",passwd="manav",use_pure=True) print(mydb.is_connected()) q = "CREATE TABLE STUDENT_INFO(SID INT AUTO_INCREMENT PRIMARY KEY,STUDENT_FIRST_NAME VARCHAR(30),STUDENT_LAST_NAME VARCHAR(30),"\ "STUDENT_REGISTRATION_DATE DATE,STUDENT_CLASS VARCHAR(20),STUDENT_SECTION VARCHAR(20))" cur = mydb.cursor() cur.execute(q) print("Table is Created!...") mydb.close() except Exception as e: print(str(e))

mydb = connection.connect(host="localhost",database="student",user="root",passwd="manav",use_pure=True) stud_data = pd.read_csv("stud_data.csv") stud_data

stud_data1 = pd.read_sql("SELECT * FROM STUDENT_DETAILS",mydb) stud_data1

stud_data1.to_sql('STUDENT_INFO',con = mydb)

---

ProgrammingError Traceback (most recent call last) ~\anaconda3\lib\site-packages\pandas\io\sql.py in execute(self, *args, *kwargs) 1680 try: -> 1681 cur.execute(args, **kwargs) 1682 return cur

~\anaconda3\lib\site-packages\mysql\connector\cursor.py in execute(self, operation, params, multi) 558 if psub.remaining != 0: --> 559 raise errors.ProgrammingError( 560 "Not all parameters were used in the SQL statement")

ProgrammingError: Not all parameters were used in the SQL statement

The above exception was the direct cause of the following exception:

DatabaseError Traceback (most recent call last)

in ----> 1 stud_data1.to_sql('STUDENT_INFO',con = mydb) ~\anaconda3\lib\site-packages\pandas\core\generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method) 2603 from pandas.io import sql 2604 -> 2605 sql.to_sql( 2606 self, 2607 name, ~\anaconda3\lib\site-packages\pandas\io\sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method) 587 ) 588 --> 589 pandas_sql.to_sql( 590 frame, 591 name, ~\anaconda3\lib\site-packages\pandas\io\sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method) 1825 dtype=dtype, 1826 ) -> 1827 table.create() 1828 table.insert(chunksize, method) 1829 ~\anaconda3\lib\site-packages\pandas\io\sql.py in create(self) 719 720 def create(self): --> 721 if self.exists(): 722 if self.if_exists == "fail": 723 raise ValueError(f"Table '{self.name}' already exists.") ~\anaconda3\lib\site-packages\pandas\io\sql.py in exists(self) 706 707 def exists(self): --> 708 return self.pd_sql.has_table(self.name, self.schema) 709 710 def sql_schema(self): ~\anaconda3\lib\site-packages\pandas\io\sql.py in has_table(self, name, schema) 1836 query = f"SELECT name FROM sqlite_master WHERE type='table' AND name={wld};" 1837 -> 1838 return len(self.execute(query, [name]).fetchall()) > 0 1839 1840 def get_table(self, table_name, schema=None): ~\anaconda3\lib\site-packages\pandas\io\sql.py in execute(self, *args, **kwargs) 1691 1692 ex = DatabaseError(f"Execution failed on sql '{args[0]}': {exc}") -> 1693 raise ex from exc 1694 1695 @staticmethod DatabaseError: Execution failed on SQL 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': Not all parameters were used in the SQL statement

[manav3475]

This is my code there having the same error can anyone ping me to solve this error

[007vasy]

same error here, python 3.8.10 pandas 1.3.3 sqlalchemy 1.4.25

[manav3475]

same error here, python 3.8.10 pandas 1.3.3 sqlalchemy 1.4.25

There May be some version error update the version then check it it will be come or not

[LBrogowski]

For me, this same message meant I had lost the database connection.

[shacker]

I'm also blocked by this error. Upgrading to snowflake-connector 2.8.2 (the latest) did not solve it.

[sj-ganwh]

+1