How to use the same code for both sqlite and postgres

Question

My sqlalchemy code needs to support both sqlite and postgres, but right now it is not working for sqlite.

sqlalchemy.exc.StatementError: (builtins.TypeError) SQLite DateTime type only accepts Python datetime and date objects as input.

I checked Error - "SQLite DateTime type only accepts Python " "datetime and date objects as input." but making this change in my entire code base is not possible as it has more than one place where a date string is used instead of datetime

This is my code, it works for postgres engine, but it does not work for sqlite, can I modify anything other than what the above link suggests so my code runs on both sqlite and postgres

from sqlalchemy import create_engine, Column, Integer
from sqlalchemy.orm import declarative_base, Session
from sqlalchemy.types import DateTime

Base = declarative_base()

class Foo(Base):
    __tablename__ = "foo"
    id = Column(Integer, primary_key=True)
    col = Column(DateTime)

engine = create_engine("postgresql://tony:tony@localhost:5432")
# engine = create_engine("sqlite:///db.db")
Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add(Foo(col='2023-01-07T11:08:31Z'))
    session.commit()

ljmc · Accepted Answer · 2023-01-12 09:50:13Z

1

The DB-API spec expects SQL DATETIME to be provided as python datetime.datetime (docs).

I believe psycopg2 offers an extenstion that will handle ISO 8601 formatted strings, but this is an extension.

If you want to be most compatible, use datetime.datetime object to pass and retrieve dates.

Also why import DateTime from sqlalchemy.types ? It's available under sqlalchemy directly.

from datetime import datetime

from sqlalchemy import Column, Integer, create_engine, DateTime
from sqlalchemy.orm import Session, declarative_base

Base = declarative_base()

class Foo(Base):
    __tablename__ = "foo"
    id = Column(Integer, primary_key=True)
    col = Column(DateTime)

engine = create_engine("postgresql+psycopg2://postgres:postgres@localhost:5432/postgres") # OK
engine = create_engine("sqlite:///db.db") # OK

Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add(Foo(col=datetime.fromisoformat("2023-01-07T11:08:31Z")))
    session.commit()

If you need compatibility for runtime (not tests), you can use the following TypeDecorator.

Look further into the docs if you want more than coercing bind parameters, but this will allow your to input ISO 8601 str in SQLite.

from datetime import datetime

from sqlalchemy import Column, Integer, create_engine, DateTime
from sqlalchemy.orm import Session, declarative_base
from sqlalchemy.types import TypeDecorator

Base = declarative_base()

class ISO8601DateTime(TypeDecorator):
    impl = DateTime
    cache_ok = True

    def process_bind_param(self, value, dialect):
        if dialect.name == "sqlite" and isinstance(value, str):
            value = datetime.fromisoformat(value)
        return value

class Foo(Base):
    __tablename__ = "foo"
    id = Column(Integer, primary_key=True)
    col = Column(ISO8601DateTime)  # NOTE: decorated type

engine = create_engine("postgresql+psycopg2://postgres:postgres@localhost:5432/postgres") # OK
engine = create_engine("sqlite:///db.db") # OK

Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add(Foo(col=datetime.fromisoformat("2023-01-07T11:08:31Z")))
    session.add(Foo(col="2023-01-07T11:08:31Z"))
    session.commit()

NB. for tests, if unit testing, mock the database adapter, if integration (and higher) testing, use the same DB as you'll use in production.

edited Jan 12, 2023 at 9:50

answered Jan 11, 2023 at 18:39

ljmc

5,3732 gold badges11 silver badges30 bronze badges

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

5 Comments

Tony Over a year ago

thank you for your reply and for the import suggestion, while doing datetime.fromisoformat("2023-01-07T11:08:31Z") makes the code work, as mentioned in my question, there is more than one occurrence where I have this, is there anyway I can monkeypatch or something so I can change in one place and have it reflected?

ljmc Over a year ago

What is your use case here ? Unit testing, integration testing or making your program compatible to run on both ?

Tony Over a year ago

the program has to run on both, if you care about the reason, it was decided to not use postgres instances for "free accounts" for cost reasons, so these free accounts will be in an environment where the database would be sqlite

ljmc Over a year ago

Alright then you can have a look at my edit about a TypeDecorator which parses the ISO 8601 string if a string is passed for datetime in SQLite dialect. But if you have any possibility, I would recomment refactoring to handle datetime as datetime objects.

Tony Over a year ago

thank you so much, I agree with your recommendation for future usecase, right now this is exactly what I wanted to have

Collectives™ on Stack Overflow

How to use the same code for both sqlite and postgres

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related