Python datatime object issue with SQLite/SQLAlchemy

[horcle_buzz]

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][1]) that "SQLAlchemy’s own DateTime and related types provide date formatting and parsing functionality when SQlite is used..."

Note that when I use `DateTime` as a data type in my class 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.

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.

[mike bayer]

On 11/10/2016 08:10 PM, horcle_buzz wrote:
> I am using SQLAlchemy and am having an issue wrt to an SQLite error:
>
>
> |
> SQLiteDatetype only accepts Pythondate objects asinput.
> [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.

nor do I. You'd need to provide a complete test. Here is one, modify it
to show how you get your error.


from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

Base = declarative_base()


class ClinicalData(Base):

__tablename__ = 'clinical_data'
id = Column(Integer, primary_key=True, autoincrement=True)
patient_id = Column(Integer)

event_date = Column(Date)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

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

s = Session(e)
s.add(ClinicalData(event_date=valdate))
s.commit()


print s.query(ClinicalData).filter(ClinicalData.event_date >= valdate).all()

>
> 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:
>
> |
> classClinicalData(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][1] <

> http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html>) that

> "SQLAlchemy’s own DateTime and related types provide date formatting and
> parsing functionality when SQlite is used..."
>
> Note that when I use `DateTime` as a data type in my class model on the
> `event_date` attribute I get the following error
> |
> SQLiteDateTimetype only accepts Pythondatetime anddate objects asinput.
> |
>
> 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.
>
> 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.
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Greg M. Silverman]

Hi Mike,

Modifications of your test made to point to the actual database file I am using worked. The only difference in your test versus my code is that my ClinicalData class definition uses a Flask db.Model versus a straight declarative_base. I'll need to play around with this a bit more. Interesting.

Thanks!

Greg--

an email to sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy+unsubscribe@googlegroups.com>.

To post to this group, send email to sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/hJ12o7rGNp0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscribe@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--

Greg M. Silverman

 ›  flora-script ‹

 ›  grenzi.org  ‹

[Greg M. Silverman]

I should add: The specific queries I was running that were throwing the error all worked using your test case setup.

Greg--