0

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?

2
  • Please provide a minimal reproducible example. Your code here has an undefined variable. Also, what database engine are you using? Commented Nov 3, 2021 at 22:44
  • 2
    I'm using SQLAlchemy. I'm pretty sure the connection is getting established properly. I have run a simple SQL query select * from <table_name> limit 10 and it works just fine. It's only when I add the similar to clause it has a problem. The same exact query with the similar to clause, works just fine in SQL editor Postico. Commented Nov 3, 2021 at 22:47

1 Answer 1

2

Replace all % signs with %%.

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

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.