Python SQLAlchemy ORM 转换为 Pandas DataFrame
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29525808/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLAlchemy ORM conversion to pandas DataFrame
提问by Jared
This topic hasn't been addressed in a while, here or elsewhere. Is there a solution converting a SQLAlchemy <Query object>to a pandas DataFrame?
有一段时间没有在这里或其他地方讨论过这个话题。是否有将 SQLAlchemy 转换为<Query object>Pandas DataFrame的解决方案？
Pandas has the capability to use pandas.read_sqlbut this requires use of raw SQL. I have two reasons for wanting to avoid it: 1) I already have everything using the ORM (a good reason in and of itself) and 2) I'm using python lists as part of the query (eg: .db.session.query(Item).filter(Item.symbol.in_(add_symbols)where Itemis my model class and add_symbolsis a list). This is the equivalent of SQL SELECT ... from ... WHERE ... IN.
Pandas 可以使用，pandas.read_sql但这需要使用原始 SQL。我有两个想要避免它的原因：1）我已经拥有使用 ORM 的所有东西（这本身就是一个很好的理由）和 2）我使用 python 列表作为查询的一部分（例如：我的模型类.db.session.query(Item).filter(Item.symbol.in_(add_symbols)在哪里Item并且add_symbols是一个列表）。这相当于 SQL SELECT ... from ... WHERE ... IN。
Is anything possible?
有什么可能吗？
采纳答案by van
Below should work in most cases:
以下应该在大多数情况下工作：
df = pd.read_sql(query.statement, query.session.bind)
See pandas.read_sqldocumentation for more information on the parameters.
有关pandas.read_sql参数的更多信息，请参阅文档。
回答by Johan Dahlin
If you want to compile a query with parameters and dialect specific arguments, use something like this:
如果要使用参数和方言特定参数编译查询，请使用以下内容：
c = query.statement.compile(query.session.bind)
df = pandas.read_sql(c.string, query.session.bind, params=c.params)
回答by Chandan Purohit
Just to make this more clear for novice pandas programmers, here is a concrete example,
为了让熊猫新手程序员更清楚这一点，这里有一个具体的例子，
pd.read_sql(session.query(Complaint).filter(Complaint.id == 2).statement,session.bind)
Here we select a complaint from complaints table (sqlalchemy model is Complaint) with id = 2
这里我们从投诉表（sqlalchemy 模型是 Complaint）中选择一个 id = 2 的投诉
回答by jorr45
The selected solution didn't work for me, as I kept getting the error
所选的解决方案对我不起作用，因为我不断收到错误消息
AttributeError: 'AnnotatedSelect' object has no attribute 'lower'
AttributeError: 'AnnotatedSelect' 对象没有属性 'lower'
I found the following worked:
我发现以下工作：
df = pd.read_sql_query(query.statement, engine)
回答by Akshay Salvi
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine('postgresql://postgres:postgres@localhost:5432/DB', echo=False)
Base = declarative_base(bind=engine)
Session = sessionmaker(bind=engine)
session = Session()
conn = session.bind
class DailyTrendsTable(Base):
__tablename__ = 'trends'
__table_args__ = ({"schema": 'mf_analysis'})
company_code = Column(DOUBLE_PRECISION, primary_key=True)
rt_bullish_trending = Column(Integer)
rt_bearish_trending = Column(Integer)
rt_bullish_non_trending = Column(Integer)
rt_bearish_non_trending = Column(Integer)
gen_date = Column(Date, primary_key=True)
df_query = select([DailyTrendsTable])
df_data = pd.read_sql(rt_daily_query, con = conn)
