CODEWITHSUNDEEP
pythonmysqlsqlalchemy
Serguei Fedorov
Serguei Fedorov

Reputation: 7913

sqlalchemy: Call STR_TO_DATE on column

I am moving some of my code onto sqlalchemy from using raw MySQL queries.

The current issue I am having is that the datetime was saved in a string format by a C# tool. Unfortunately, the representation does not match up with Python's (as well as that it has an extra set of single quotes), thus making filtering somewhat cumbersome.

Here is an example of the format that the date was saved in:

'2016-07-01T17:27:01'

Which I was able to convert to a usable datetime using the following MySQL command:

 STR_TO_DATE(T.PredicationGeneratedTime, \"'%%Y-%%m-%%dT%%H:%%i:%%s'\")

However, I cannot find any documentation that describes how to invoke built-in functions such as STR_TO_DATE when filtering with sqlalchemy

The following Python code:

 session.query(Train.Model).filter(cast(Train.Model.PredicationGeneratedTime, date) < start)

is giving me:

 TypeError: Required argument 'year' (pos 1) not found

There does not seem to be a way to specify the format for the conversion.

Note: I realize the solution is to fix the way the datetime is stored, but in the mean time I'd like to run queries against the existing data.

Upvotes: 2

Views: 1761

Answers (2)

antonio_antuan
antonio_antuan

Reputation: 1313

You can try to use func.str_to_date(COLUMN, FORMAT_STRING) instead of cast

Upvotes: 3

mhawke
mhawke

Reputation: 87054

In the cast() you should be using sqlalchemy.DateTime, not (what I assume is) a datetime.date - that is the cause of the exception.

However, fixing that will not really help because of the embedded single quotes.

You are fortunate that the dates stored in your table are in ISO format. That means that lexicographic comparisons will work on the date strings themselves, without casting. As long as you use a string for start with the surrounding single quotes, it will work.

from datetime import datetime

start = "'{}'".format(datetime.now().isoformat())
session.query(Train.Model).filter(Train.Model.PredicationGeneratedTime < start)

Upvotes: 1

Related Questions