CODEWITHSUNDEEP
pythonsqlalchemy
pistacchio
pistacchio

Reputation: 58893

SqlAlchemy: insert statement, actual SQL and skipping fields

I have the following SqlAlchemy model:

class SchemaVersion(Base):
    __tablename__ = 'schema_version'

    timestamp = Column(DateTime, default=datetime.datetime.utcnow, primary_key=True)
    version   = Column(String)
    notes     = Column(String)

The closest I could come is:

statement = insert(SchemaVersion).values(version='v1.0.0',
                                         notes='Initial schema')

print(statement.compile(engine, compile_kwargs={'literal_binds': True}))

engine is the engine I'm using (Postgres)

The resulting printed SQL is:

INSERT INTO schema_version (timestamp, version, notes) VALUES (%(timestamp)s, 'v1.0.0', 'Initial schema')

The problem is of course %(timestamp)s

How can I pass now() as value or let SqlAlchemy to use the default for me? If I try:

statement = insert(SchemaVersion).values(timestamp=datetime.datetime.utcnow(),
                                         version='v1.0.0',
                                         notes='Initial schema')

I get the error:

NotImplementedError: Don't know how to literal-quote value datetime.datetime(2018, 8, 21, 9, 50, 11, 732957)

Upvotes: 0

Views: 405

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52949

Though it's a bit unclear why you're interested in producing an SQL string with bound literals, you could avoid the fact that only simple types such as int and str are supported in this case by producing the timestamp in the database using for example CURRENT_TIMESTAMP:

statement = insert(SchemaVersion).values(timestamp=func.current_timestamp(),
                                         version='v1.0.0',
                                         notes='Initial schema')

This would compile to something along the lines of

INSERT INTO schema_version (timestamp, version, notes)
VALUES (CURRENT_TIMESTAMP, 'v1.0.0', 'Initial schema')

Upvotes: 2

Related Questions