Python datatime object issue with SQLAlchemy

Question

I am using SQLAlchemy and am having an issue wrt to an SQLite error:

SQLite Date type only accepts Python date objects as input. 
[SQL: 'SELECT anon_1.patient_sid AS sid FROM 
(SELECT clinical_data.patient_sid AS patient_sid FROM clinical_data 
WHERE clinical_data.event_date >= ?) AS anon_1']

I understand perfectly the meaning of the error, but I do not understand why it is happening in my case.

The parameter that I am passing to do the date comparison in the query above clinical_data.event_date >= ?, is set as:

valdate = datetime.strptime('1776-01-01 00:00:00', "%Y-%m-%d %H:%M:%S").date()

and, I have validated that the data type of valdate is <type 'datetime.date'>

The class used to construct the query is:

class ClinicalData(db.Model):
    __tablename__ = 'clinical_data'
    id = Column(Integer, primary_key=True, autoincrement=True)
    patient_id = Column(Integer)
    patient_sid  = Column(Integer)
    string_value = Column(String(255))
    double_value = Column(Float)
    data_type_id = Column(Integer)
    event_date = Column(Date)
    ontology_id = Column(Integer)
    attribute_id = Column(Integer)
    project_id = Column(Integer)
    replaced_by_id = Column(Integer)
    date_record_added = Column(DateTime)
    parent = Column(Integer)
    num_children = Column(Integer)
    lft = Column(Integer)
    rgt = Column(Integer)

The SQLAlchemy documentation for SQLite states (see SQLAlchemy SQLite documentation) that "SQLAlchemy’s own DateTime and related types provide date formatting and parsing functionality when SQlite is used..."

I am not asking how to convert my string object to a python datetime object, nor am I asking what the error means. I am not sure exactly why I keep getting this error when everything appears to be sufficiently defined.

EDIT

Note that when I use DateTime in my model on the event_date attribute I get the following error SQLite DateTime type only accepts Python datetime and date objects as input. For this I define valdate = datetime.strptime('1776-01-01 00:00:00', "%Y-%m-%d %H:%M:%S") without the date() method. As expected, type(valdate) in this case yields <type 'datetime.datetime'>.

I have tried every combination of creating the variable valdate with the event_date attribute of my class.

jwodder · Accepted Answer · 2016-11-10 20:39:34Z

1

The error says:

SQLite Date type only accepts Python date objects as input.

But you don't have a date object — you have a datetime object! To turn your datetime into a date, just call its date() method — though judging by the fact that your strptime format includes time fields (%H:%M:%S), you might want to change event_date from Column(Date) to Column(DateTime) instead.

answered Nov 10, 2016 at 20:39

jwodder

58.1k12 gold badges116 silver badges134 bronze badges

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

3 Comments

horcle_buzz Over a year ago

I had tried that earlier and got the same error. Also, as noted above, I did a 'type(valdate)` and it returned <type 'datetime.datetime'>, which to me certainly looks and smells like a datetime object. I had also putzed around with the data type in my class. I tried virtually every combination under the sun.

jwodder Over a year ago

@horcle_buzz: You seem to have missed the important part of my answer: datetime and date are two different types. SQLAlchemy wants a date, but you're giving it a datetime.

horcle_buzz Over a year ago

See corrected/edited question above for further clarification.

Collectives™ on Stack Overflow

Python datatime object issue with SQLAlchemy

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related