Thanks. It turned out that I was sending an empty string for date (instead
of null), and that was being translated as 0000-00-00 (your script works
except I was using mysql+pysql). I didn't saw it since google developer
tools logs, wrongly, that a null is being sent.
Thanks a lot for the help.
Marco
On Tuesday, June 14, 2016 at 1:46:19 PM UTC+1, Mike Bayer wrote:
>
> Let's do an MCVE:
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
>
> class A(Base):
> __tablename__ = 'a'
> id = Column(Integer, primary_key=True)
> end_date = Column(Date(),nullable=True)
>
> e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
> Base.metadata.drop_all(e)
> Base.metadata.create_all(e)
>
>
> s = Session(e)
> s.add(A(end_date=None))
> s.commit()
>
> print s.query(A.end_date).all()
>
>
> on the Python side, the output is (after it checks for the table etc):
>
> CREATE TABLE a (
> id INTEGER NOT NULL AUTO_INCREMENT,
> end_date DATE,
> PRIMARY KEY (id)
> )
>
>
> 2016-06-14 08:39:17,680 INFO sqlalchemy.engine.base.Engine ()
> 2016-06-14 08:39:17,699 INFO sqlalchemy.engine.base.Engine COMMIT
> 2016-06-14 08:39:17,701 INFO sqlalchemy.engine.base.Engine BEGIN
> (implicit)
> 2016-06-14 08:39:17,702 INFO sqlalchemy.engine.base.Engine INSERT INTO a
> (end_date) VALUES (%s)
> 2016-06-14 08:39:17,702 INFO sqlalchemy.engine.base.Engine (None,)
> 2016-06-14 08:39:17,703 INFO sqlalchemy.engine.base.Engine COMMIT
> 2016-06-14 08:39:17,708 INFO sqlalchemy.engine.base.Engine BEGIN
> (implicit)
> 2016-06-14 08:39:17,708 INFO sqlalchemy.engine.base.Engine SELECT
> a.end_date AS a_end_date
> FROM a
> 2016-06-14 08:39:17,709 INFO sqlalchemy.engine.base.Engine ()
> [(None,)]
>
> it's not actually possible to load a date of 0000-00-00 in Python, so
> making sure on the MySQL side:
>
> [classic@photon2 sqlalchemy]$ mysql -u root
> Welcome to the MariaDB monitor. Commands end with ; or \g.
> Your MariaDB connection id is 3
> Server version: 10.1.14-MariaDB MariaDB Server
>
> Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
>
> Type 'help;' or '\h' for help. Type '\c' to clear the current input
> statement.
>
> MariaDB [(none)]> use test;
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
>
> Database changed
> MariaDB [test]> select * from a;
> +----+----------+
> | id | end_date |
> +----+----------+
> | 1 | NULL |
> +----+----------+
> 1 row in set (0.00 sec)
>
>
>
> so you'd want to check is:
>
> 1. does this test case do something different for you ? Can you modify
> it to show your result?
>
> 2. what version of MySQL / MariaDB is this?
>
> 3. what kinds of settings do you have for SQL_MODE , storage engine,
> etc. Is this definitely the type "DATE" ? (TIMESTAMP acts more
> unusually for example)
>
> 4. what database driver ? (what version?)
>
> 5. etc. etc.
>
>
> I can tell you right off that SQLAlchemy doesn't do anything with MySQL
> dates, they are passed through straight to the driver.
>
> Looking over stackoverflow the main reason for this 0000-00-00 thing is
> people inserting invalid dates as strings. The MySQL python drivers
> should not be able to do that.
>
>
>
>
>
>
>
>
> On 06/14/2016 07:20 AM, Marco Correia wrote:
> > Hi,
> >
> > I have a date column which is optional, therefore I created it like
> this:
> >
> > end_date = Column(Date(),nullable=True)
> >
> > Apparently, if I do not specify the date, the database (mysql) stores
> > "0000-00-00". This is a problem later, when I do queries using clauses
> > like the following:
> >
> > or_(Job.end_date.is_(None),Job.end_date>=date)
> >
> > I find it a bit odd that when using sqlalchemy something that gets
> > stored as None is retrieved as 0000-00-00, so I guess I'm making some
> > mistake.
> >
> > Trying to find a solution to this problem led me to do
> >
> > end_date = Column(Date(),nullable=True,server_default=text("NULL"))
> >
> > but it makes no difference.
> >
> > Can someone help? Thanks!
> >
> > Marco
> >
> > --
> > 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 <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
