Python 如何为 VALUES 子句传递元组列表

您可以使用.values()函数为您构造VALUES子句:

import sqlalchemy as sa

engine = sa.create_engine(
    "mssql+pyodbc://scott:tiger^5HHH@mssql_199"
)

values_data = [(1, "A"), (2, "B")]
values_clause = sa.values(
    sa.column("column_a", sa.Integer),
    sa.column("column_b", sa.String),
    name="temp_table",
).data(values_data)

statement = sa.select(sa.text("*")).select_from(values_clause)
engine.echo = True
with engine.begin() as conn:
    results = conn.execute(statement).all()
    """SQL emitted
    SELECT *
    FROM (VALUES (?, ?), (?, ?)) AS temp_table (column_a, column_b)
    [no key 0.00031s] (1, 'A', 2, 'B')
    """
    print(results)
    # [(1, 'A'), (2, 'B')]

发帖主题:Re:Kolibrios

您还可以编译VALUES子句，并将其嵌入到SQL文本中:

cte_body = (
    sa.select(sa.text("*"))
    .select_from(values_clause)
    .compile(engine, compile_kwargs={"literal_binds": True})
)
statement = f"""\
WITH cte1 AS (
{cte_body}
)
SELECT * FROM cte1 
"""

engine.echo = True
with engine.begin() as conn:
    results = conn.execute(sa.text(statement)).all()
    """SQL emitted
    WITH cte1 AS (
    SELECT * 
    FROM (VALUES (1, 'A'), (2, 'B')) AS temp_table (column_a, column_b)
    )
    SELECT * FROM cte1 
    """
    print(results)
    # [(1, 'A'), (2, 'B')]