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+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@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.
--
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.
