KeyError while trying to execute an SQL query from Python-CodePudding

I have a KeyError showing up in Python when I try to execute a simple query. I am using SQLAlchemy to connect to Postgres Database and I'm positive that the connection is getting made. I did execute a very simple query to confirm it.

Here's the code:

query = '''
select *
from <table_name>
where data similar to '%(23658989187|23658988997|23658989130)%'
limit 10;
'''

df = pd.read_sql_query(query, connection)
df

And here's the error:

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-44-4e1b1b69ef79> in <module>
      6 '''
      7 
----> 8 df = pd.read_sql_query(query, connection)
      9 df

~/anaconda3/lib/python3.8/site-packages/pandas/io/sql.py in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype)
    434     """
    435     pandas_sql = pandasSQL_builder(con)
--> 436     return pandas_sql.read_query(
    437         sql,
    438         index_col=index_col,

~/anaconda3/lib/python3.8/site-packages/pandas/io/sql.py in read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype)
   1577         args = _convert_params(sql, params)
   1578 
-> 1579         result = self.execute(*args)
   1580         columns = result.keys()
   1581 

~/anaconda3/lib/python3.8/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1422     def execute(self, *args, **kwargs):
   1423         """Simple passthrough to SQLAlchemy connectable"""
-> 1424         return self.connectable.execution_options().execute(*args, **kwargs)
   1425 
   1426     def read_table(

~/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py in execute(self, statement, *multiparams, **params)
   1246             )
   1247 
-> 1248             return self._exec_driver_sql(
   1249                 statement,
   1250                 multiparams,

~/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _exec_driver_sql(self, statement, multiparams, params, execution_options, future)
   1545 
   1546         dialect = self.dialect
-> 1547         ret = self._execute_context(
   1548             dialect,
   1549             dialect.execution_ctx_cls._init_statement,

~/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1812 
   1813         except BaseException as e:
-> 1814             self._handle_dbapi_exception(
   1815                 e, statement, parameters, cursor, context
   1816             )

~/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1997                 )
   1998             else:
-> 1999                 util.raise_(exc_info[1], with_traceback=exc_info[2])
   2000 
   2001         finally:

~/anaconda3/lib/python3.8/site-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
    205 
    206         try:
--> 207             raise exception
    208         finally:
    209             # credit to

~/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1769                             break
   1770                 if not evt_handled:
-> 1771                     self.dialect.do_execute(
   1772                         cursor, statement, parameters, context
   1773                     )

~/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    715 
    716     def do_execute(self, cursor, statement, parameters, context=None):
--> 717         cursor.execute(statement, parameters)
    718 
    719     def do_execute_no_params(self, cursor, statement, context=None):

KeyError: '23658989187|23658988997|23658989130'

The same query works just fine on Postico. What am I doing wrong here?

CodePudding user response：

Replace all % signs with %%.

pd.read_sql_query interprets % signs specially, they have to be escaped.