将临时表与 SQLAlchemy 一起使用 [英] Use temp table with SQLAlchemy

问题描述

我正在尝试将临时表与 SQLAlchemy 结合使用，并将其与现有表连接.这是我目前所拥有的

I am trying to use use a temp table with SQLAlchemy and join it against an existing table. This is what I have so far

engine = db.get_engine(db.app, 'MY_DATABASE')
df = pd.DataFrame({"id": [1, 2, 3], "value": [100, 200, 300], "date": [date.today(), date.today(), date.today()]})
temp_table = db.Table('#temp_table',
                      db.Column('id', db.Integer),
                      db.Column('value', db.Integer),
                      db.Column('date', db.DateTime))
temp_table.create(engine)
df.to_sql(name='tempdb.dbo.#temp_table',
          con=engine,
          if_exists='append',
          index=False)
query = db.session.query(ExistingTable.id).join(temp_table, temp_table.c.id == ExistingTable.id)
out_df = pd.read_sql(query.statement, engine)
temp_table.drop(engine)
return out_df.to_dict('records')

这不会返回任何结果，因为 to_sql 没有运行的插入语句(我认为这是因为它们使用 sp_prepexec 运行，但我我对此并不完全确定).

This doesn't return any results because the insert statements that to_sql does don't get run (I think this is because they are run using sp_prepexec, but I'm not entirely sure about that).

然后我尝试只写出 SQL 语句 (CREATE TABLE #temp_table..., INSERT INTO #temp_table..., SELECT [id]FROM...) 然后运行 ​​pd.read_sql(query, engine).我收到错误信息

I then tried just writing out the SQL statement (CREATE TABLE #temp_table..., INSERT INTO #temp_table..., SELECT [id] FROM...) and then running pd.read_sql(query, engine). I get the error message

这个结果对象不返回行.已自动关闭.

This result object does not return rows. It has been closed automatically.

我猜这是因为该语句不仅仅是SELECT?

I guess this is because the statement does more than just SELECT?

我该如何解决这个问题(两种解决方案都行，但第一种更可取，因为它避免了硬编码的 SQL).需要明确的是，我无法修改现有数据库中的架构 - 它是供应商数据库.

How can I fix this issue (either solution would work, although the first would be preferable as it avoids hard-coded SQL). To be clear, I can't modify the schema in the existing database—it's a vendor database.

推荐答案

如果要插入临时表的记录数量很少/中等，一种可能是使用字面子查询 或 values CTE 而不是创建临时表.

In case the number of records to be inserted in the temporary table is small/moderate, one possibility would be to use a literal subquery or a values CTE instead of creating temporary table.

# MODEL
class ExistingTable(Base):
    __tablename__ = 'existing_table'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
    # ...

假设还要将以下数据插入到 temp 表中:

Assume also following data is to be inserted into temp table:

# This data retrieved from another database and used for filtering
rows = [
    (1, 100, datetime.date(2017, 1, 1)),
    (3, 300, datetime.date(2017, 3, 1)),
    (5, 500, datetime.date(2017, 5, 1)),
]

创建包含该数据的 CTE 或子查询:

Create a CTE or a sub-query containing that data:

stmts = [
    # @NOTE: optimization to reduce the size of the statement:
    # make type cast only for first row, for other rows DB engine will infer
    sa.select([
        sa.cast(sa.literal(i), sa.Integer).label("id"),
        sa.cast(sa.literal(v), sa.Integer).label("value"),
        sa.cast(sa.literal(d), sa.DateTime).label("date"),
    ]) if idx == 0 else
    sa.select([sa.literal(i), sa.literal(v), sa.literal(d)])  # no type cast

    for idx, (i, v, d) in enumerate(rows)
]
subquery = sa.union_all(*stmts)

# Choose one option below.
# I personally prefer B because one could reuse the CTE multiple times in the same query
# subquery = subquery.alias("temp_table")  # option A
subquery = subquery.cte(name="temp_table")  # option B

使用所需的联接和过滤器创建最终查询:

Create final query with the required joins and filters:

query = (
    session
    .query(ExistingTable.id)
    .join(subquery, subquery.c.id == ExistingTable.id)
    # .filter(subquery.c.date >= XXX_DATE)
)

# TEMP: Test result output
for res in query:
    print(res)    

最后得到pandas数据框:

Finally, get pandas data frame:

out_df = pd.read_sql(query.statement, engine)
result = out_df.to_dict('records')

这篇关于将临时表与 SQLAlchemy 一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！