嘗試從Python執行SQL查詢時出現KeyError

我有一個KeyError顯示出來的Python，當我試圖執行一個簡單的查詢。我正在使用SQLAlchemy連接到 Postgres 資料庫，我確信連接正在建立。我確實執行了一個非常簡單的查詢來確認它。

這是代碼：

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

df = pd.read_sql_query(query, connection)
df

這是錯誤：

---------------------------------------------------------------------------
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'

相同的查詢在 Postico 上作業得很好。我在這里做錯了什么？

uj5u.com熱心網友回復：

用 替換所有%符號%%。

pd.read_sql_query%特別解釋符號，它們必須被逃脫。

標籤：Python 蟒蛇-3.x

上一篇：將具有不同鍵順序的多個json檔案插入到postgres表中

下一篇：如何使用級聯串列值重新排序pythonOrderedDict