python - 如何使用 SQLAlchemy 的 connection.execute 插入 CURRENT TIMESTAMP 的值

Question

我想使用 connection.execute 插入多行，其中一列必须设置为数据库的 CURRENT_TIMESTAMP 函数的结果。

例如，给定这张表:

import sqlalchemy as sa

metadata = sa.MetaData()

foo = sa.Table('foo', metadata,
               sa.Column('id', sa.Integer, primary_key=True),
               sa.Column('ts', sa.TIMESTAMP))


# I'm using Sqlite for this example, but this question
# is database-agnostic.
engine = sa.create_engine('sqlite://', echo=True)
metadata.create_all(engine)

我可以像这样插入一行:

conn = engine.connect()
with conn.begin():
    ins = foo.insert().values(ts=sa.func.current_timestamp())
    conn.execute(ins)

但是当我尝试插入多行时:

with conn.begin():
    ins = foo.insert()
    conn.execute(ins, [{'ts': sa.func.current_timestamp()}])

引发了一个TypeError:

sqlalchemy.exc.StatementError: (builtins.TypeError) SQLite DateTime type only accepts Python datetime and date objects as input.                                                
[SQL: INSERT INTO foo (ts) VALUES (?)]                                                                                                                                          
[parameters: [{'ts': <sqlalchemy.sql.functions.current_timestamp at 0x7f3607e21070; current_timestamp>}]

用字符串“CURRENT_TIMESTAMP”替换函数会导致类似的错误。

有没有办法让数据库使用 connection.execute 将列设置为 CURRENT_TIMESTAMP？

我知道我可以通过在同一事务中查询 CURRENT_TIMESTAMP 的值并在 INSERT 值中使用该值，或执行和UPDATE 在 INSERT 之后。我特别想问这是否可以在 connection.execute 的 *multiparams 参数中完成。

Answer 1

这当然是一个 hack，但这似乎至少适用于 SQLite:

from datetime import datetime
from pprint import pprint

import sqlalchemy as sa

engine = sa.create_engine("sqlite:///:memory:")
metadata = sa.MetaData()

foo = sa.Table(
    "foo",
    metadata,
    sa.Column("id", sa.Integer, primary_key=True, autoincrement=True),
    sa.Column("ts", sa.TIMESTAMP),
    sa.Column("txt", sa.String(50)),
)
foo.create(engine)

with engine.begin() as conn:
    ins_query = str(foo.insert().compile()).replace(
        " :ts, ", " CURRENT_TIMESTAMP, "
    )
    print(ins_query)
    # INSERT INTO foo (id, ts, txt) VALUES (:id, CURRENT_TIMESTAMP, :txt)

    data = [{"id": None, "txt": "Alfa"}, {"id": None, "txt": "Bravo"}]
    conn.execute(sa.text(ins_query), data)

    print(datetime.now())
    # 2021-03-06 17:41:35.743452
    # (local time here is UTC-07:00)

    results = conn.execute(sa.text("SELECT * FROM foo")).fetchall()
    pprint(results, width=60)
    """
    [(1, '2021-03-07 00:41:35', 'Alfa'),
     (2, '2021-03-07 00:41:35', 'Bravo')]
    """