SQLAlchemy执行SELECT报错ArgumentError: FROM expression expected求助

我在Jupyter Notebook中编写了如下代码（其中****为保密信息），用于连接PostgreSQL数据库并查询数据：

import psycopg2
import sqlalchemy as sa
import pandas as pds
from sqlalchemy import create_engine

# Create an engine instance
alchemyEngine   = create_engine('*****************************', pool_recycle=3600);
# engine = create_engine('**************************')

# Connect to PostgreSQL server
dbConnection    = alchemyEngine.connect();

# Read data from PostgreSQL database table and load into a DataFrame instance
team = sa.Table('dialog_logger', sa.MetaData(), autoload_with=dbConnection, schema='hca')

qry = sa.select(team.c.hos_name, team.c.hos_id, team.c.datetime, team.c.patient_cel_number,
                team.c.hospital_cel_number, team.c.message, team.c.direction).where(
                team.c.datetime > '2022-11-01 00:00:00').where(team.c.datetime < '2022-11-30 00:00:00')


dataFrame_argentina  = pds.read_sql_query(qry, dbConnection)

pds.set_option('display.expand_frame_repr', False);


# Close the database connection

dbConnection.close();

执行该代码时出现如下错误：

AttributeError                            Traceback (most recent call last)
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\sql\elements.py in __getattr__(self, key)
    722         try:
---> 723             return getattr(self.comparator, key)
    724         except AttributeError:

AttributeError: 'Comparator' object has no attribute 'selectable'

During handling of the above exception, another exception occurred:

AttributeError                            Traceback (most recent call last)
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\sql\selectable.py in _interpret_as_from(element)
     60     try:
---> 61         return insp.selectable
     62     except AttributeError:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\sql\elements.py in __getattr__(self, key)
    726                 "Neither %r object nor %r object has an attribute %r"
---> 727                 % (type(self).__name__, type(self.comparator).__name__, key)
    728             )

AttributeError: Neither 'Column' object nor 'Comparator' object has an attribute 'selectable'

During handling of the above exception, another exception occurred:

ArgumentError                             Traceback (most recent call last)
<ipython-input-3-ec4194d4c35c> in <module>
      1 qry = sa.select(team.c.hos_name, team.c.hos_id, team.c.datetime, team.c.patient_cel_number,
----> 2                 team.c.hospital_cel_number, team.c.message, team.c.direction).where(
      3                 team.c.datetime > '2022-11-01 00:00:00').where(team.c.datetime < '2022-11-30 00:00:00')
      4 
      5 

<string> in select(columns, whereclause, from_obj, distinct, having, correlate, prefixes, suffixes, **kwargs)

<string> in __init__(self, columns, whereclause, from_obj, distinct, having, correlate, prefixes, suffixes, **kwargs)

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\deprecations.py in warned(fn, *args, **kwargs)
    126                     )
    127 
---> 128             return fn(*args, **kwargs)
    129 
    130         doc = fn.__doc__ is not None and fn.__doc__ or ""

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\sql\selectable.py in __init__(self, columns, whereclause, from_obj, distinct, having, correlate, prefixes, suffixes, **kwargs)
   2977         if from_obj is not None:
   2978             self._from_obj = util.OrderedSet(
---> 2979                 _interpret_as_from(f) for f in util.to_list(from_obj)
   2980             )
   2981         else:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\_collections.py in __init__(self, d)
    363         self._list = []
    364         if d is not None:
---> 365             self._list = unique_list(d)
    366         set.update(self, self._list)
    367         else:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\_collections.py in unique_list(seq, hashfunc)
    777     seen_add = seen.add
    778     if not hashfunc:
---> 779         return [x for x in seq if x not in seen and not seen_add(x)]
    780     else:
    781         return [

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\_collections.py in <listcomp>(.0)
    777     seen_add = seen.add
    778     if not hashfunc:
---> 779         return [x for x in seq if x not in seen and not seen_add(x)]
    780     else:
    781         return [

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\sql\selectable.py in <genexpr>(.0)
   2977         if from_obj is not None:
   2978             self._from_obj = util.OrderedSet(
---> 2979                 _interpret_as_from(f) for f in util.to_list(from_obj)
   2980             )
   2981         else:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\sql\selectable.py in _interpret_as_from(element)
     61         return insp.selectable
     62     except AttributeError:
---> 63         raise exc.ArgumentError("FROM expression expected")
     64 
     65 

ArgumentError: FROM expression expected

调试发现代码在执行到qry = sa.select(......)语句前均运行正常，不确定是否因缺少依赖库导致该错误，请求协助排查解决。

问题原因 #

该错误并非缺少依赖库导致，核心是sa.select()用法错误：直接传入列对象时，SQLAlchemy无法自动推断这些列所属的表，无法生成合法的FROM子句，最终抛出报错。

修复方案 #

两种修复方式任选其一即可：

方式一：用select_from()指定表来源

在select后追加select_from()，明确查询的表：

qry = sa.select(team.c.hos_name, team.c.hos_id, team.c.datetime, team.c.patient_cel_number,
                team.c.hospital_cel_number, team.c.message, team.c.direction)\
        .select_from(team)\
        .where(team.c.datetime > '2022-11-01 00:00:00')\
        .where(team.c.datetime < '2022-11-30 00:00:00')

方式二：先选表再筛选列

先选中整个表，再用with_only_columns()指定需要的列：

qry = sa.select(team)\
        .with_only_columns([team.c.hos_name, team.c.hos_id, team.c.datetime, team.c.patient_cel_number,
                           team.c.hospital_cel_number, team.c.message, team.c.direction])\
        .where(team.c.datetime > '2022-11-01 00:00:00')\
        .where(team.c.datetime < '2022-11-30 00:00:00')

额外优化建议 #

• 日期条件可合并为between语句，简化代码：

.where(team.c.datetime.between('2022-11-01 00:00:00', '2022-11-30 00:00:00'))

• 使用上下文管理器管理数据库连接，避免遗漏关闭操作：

with alchemyEngine.connect() as dbConnection:
    team = sa.Table('dialog_logger', sa.MetaData(), autoload_with=dbConnection, schema='hca')
    # 编写并执行查询
    dataFrame_argentina = pds.read_sql_query(qry, dbConnection)

内容的提问来源于stack exchange，提问作者Salvador