CODEWITHSUNDEEP
pythonsqlitedatetimesqlalchemy
horcle_buzz
horcle_buzz

Reputation: 2171

Python datatime object issue with SQLAlchemy

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.

Upvotes: 1

Views: 1337

Answers (1)

jwodder
jwodder

Reputation: 57630

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.

Upvotes: 1

Related Questions