make Sqllite preserve datetime object-CodePudding

I am trying to write a pytest fixture that returns some test data of type List[sa.engine.row.LegacyRow].

The issue is that sqllite converts datetime objects into str.

import sqlalchemy as sa
from datetime import datetime

# test data
data = {'id': '1',
        'date': datetime(2019, 12, 9, 14, 11, 2, 442076)}

sql = "SELECT "   ", ".join([f":{col} as {col}" for col in data]) # SELECT :col1 as col1, :col2 as col2

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

with engine.begin() as conn:
    row = conn.execute(sa.text(sql), data).fetchall()
    print(row)
    print(type(row[0][1]))  # str

How do I retain the datetime object?

CodePudding user response：

You can use TextClause.columns method to specify the type of a returned column.

For example, this code

with engine.begin() as conn:
    q = sa.text("""SELECT id, date FROM test WHERE id = :id AND date = :date""")
    q = q.columns(date=sa.DateTime)
    res = conn.execute(q, data)
    for row in res:
        for col in row:
            print(type(col), col)

produces this output:

<class 'int'> 1
<class 'datetime.datetime'> 2019-12-09 14:11:02.442076