How to use the same code for both sqlite and postgres-CodePudding

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()

CodePudding user response：

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.