How can I Insert the value of CURRENT TIMESTAMP using SQLAlchemy's connection.execute

Question

I want to insert multiple rows using connection.execute, and one of the columns must be set to the result of the database's CURRENT_TIMESTAMP function.

For example, given this table:

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)

I can insert a single row like this:

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

However when I try to insert multiple rows:

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

a TypeError is raised:

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>}]

Replacing the function with the string "CURRENT_TIMESTAMP" results in a similar error.

Is there a way to get the database to set the column to CURRENT_TIMESTAMP using connection.execute?

I'm aware that I can work around this by querying for the value of CURRENT_TIMESTAMP within the same transaction and using that value in the INSERT values, or executing and UPDATE after the INSERT. I'm specifically asking whether this can be done in connection.execute's *multiparams argument.

Gord Thompson · Accepted Answer · 2021-03-07 00:47:47Z

1

It's a hack for sure, but this appears to work for SQLite at least:

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')]
    """

answered Mar 7, 2021 at 0:47

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

snakecharmerb Over a year ago

Hacky but effective :-) It works on MySQL, Postgres doesn't like 'id': None but that's not a big problem.

snakecharmerb Over a year ago

And now I realise that the multiparam values get quoted, so when I try to pass the function the db receives the string literal 'CURRENT_TIMESTAMP' instead of CURRENT_TIMESTAMP. Thanks!

Collectives™ on Stack Overflow

How can I Insert the value of CURRENT TIMESTAMP using SQLAlchemy's connection.execute

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related