Using ISO timestamp in Flask-SQLAlchemy

Question

I have a date which I obtain from an API. It is in the form of 2015-01-01T15:04:23Z.

How can I accept this date into a model using Flask-SQLAlchemy?

So far I have tried,

date = db.Column(db.DateTime)

and

date = db.Column(db.DateTime(timezone=True))

which gives me the error

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

Also, when I retrieve it using a get, I need it to be in the exact same format.

The dateutil python module parses it well but when I retrieve it from the table, I need to get 2015-01-01T15:04:23Z.

>>> from dateutil.parser import parse
>>> parse('2015-01-01T15:04:23Z')
datetime.datetime(2015, 1, 1, 15, 4, 23, tzinfo=tzutc())

"SQLite DateTime type only accepts Python datetime", parse it to a Python datetime object then: https://stackoverflow.com/questions/127803/how-to-parse-an-iso-8601-formatted-date. — Ilja Everilä, Aug 14 '17 at 10:48
@IljaEverilä And how do I get it back in the same format after retrieving it? — Sadar Ali, Aug 14 '17 at 10:51
The format in question is ISO 8601. Python has somewhat subpar support for it. There's [`datetime.isoformat()`](https://docs.python.org/3/library/datetime.html#datetime.datetime.isoformat) for formatting a datetime object as an ISO 8601 string, but that'll not allow you to use the shorthand "Z" for UTC, for example (which is not that big a thing anyway). All in all datetime handling in Python is a bit painful, especially if time zones are involved. — Ilja Everilä, Aug 14 '17 at 10:58

score 5 · Answer 1 · answered Aug 14 '17 at 10:58

5

You need to convert the string into a Python datetime object, which you can do using time.strptime:

 record.date = time.strptime(mytime, "%Y-%m-%dT%H:%M:%SZ")

Then you can safely set it on your instance and commit it.

To get it back in the same format, use time.strftime on the other side:

time.strftime("%Y-%m-%dT%H:%M:%SZ", record.date)

And of course remember to watch out that the order of the arguments is different between strptime and strftime :)

If you need to change/update the format, see time.strftime here : https://docs.python.org/2/library/time.html

answered Aug 14 '17 at 10:58

Seabass

1

`time.strptime` produces `time.struct_time` instances, not `datetime.datetime`. The SQLAlchemy SQLite dialect will not be happy. – Ilja Everilä Aug 14 '17 at 11:05
.strptime() is available for 3.8 and later https://docs.python.org/3/library/datetime.html?highlight=strptime#datetime.datetime.strptime – baumannalexj Jul 16 '21 at 16:45

1 Answers1