sqlite backend can't add datetimes before 1900

[sqlalchemy-bot]

Issue created by Anonymous

---

In databases/sqlite.py, the DateTimeMixin class is converting datetime values to a string representation using their strftime() method.

Unfortunately, Python's strftime doesn't handle dates before 1900. This results in the below exception when you attempt to add such a date:

ValueError: year=1850 is before 1900; the datetime strftime() methods require year >= 1900

As a workaround, I changed this to convert value to a datetime (in case it was a date), and then used value.isoformat(" ") instead, which does seem to handle these dates, but I'm guessing this isn't a proper fix, as it looks like !format! is designed to be changable.

---

Attachments: sqlite_test.py | sqlite.pre1900.20080916.patch | sqlite_date_pre1900.patch

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

Removing milestone: 0.4.xx (automated comment)

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

and 0.5's is in 8d2fd5f87aaf837042d2dc5e692f84deb7dd1710, which does away with strftime() and strptime() altogether.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

0.4's version of this patch is in 9256e868627e64d2d877924c6a26f69312c886d9 with an adjustment in 188b3667e6c5525d55c9a7ffe0add69e84c4a086. 0.5 is going to do this differently.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

like, here's a start at a strftime replacement:

import datetime
import re

expr = re.compile(r'%(\w)')
repl = {
    "Y":lambda dt: "%4.4d" % dt.year,
    "m":lambda dt: "%2.2d" % dt.month,
    "d":lambda dt: "%2.2d" % dt.day,
    "H":lambda dt: "%2.2d" % dt.hour,
    "M":lambda dt: "%2.2d" % dt.minute,
    "S":lambda dt: "%2.2d" % dt.second,
    "c":lambda dt: '%06d' % dt.microsecond
}
legacy_repl = repl.copy()
legacy_repl['c']('c') = lambda dt: str(dt.microsecond)

def strftime(dt, format):
    return _strftime(dt, format, repl)

def legacy_strftime(dt, format):
    return _strftime(dt, format, legacy_repl)

def _strftime(dt, format, conversions):
    def go(m):
        return conversions[m.group(1)](m.group(1))(dt)
    return expr.sub(go, format)

format = "%Y-%m-%d %H:%M:%S.%c"

print strftime(datetime.datetime(2008, 12, 15, 10, 15, 27, 450), format)
print strftime(datetime.datetime(1890, 12, 15, 10, 15, 27, 450), format)

the above can be dropped in straight into 0.4 and 0.5, and have its own independent set of tests so that there's little impact on current code (mostly that we maintain the microsecond logic).

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

There's problems with this patch, in that it doesn't maintain backwards compatibility with the existing approach, which has to remain identical throughout 0.4 (and also 0.5 where we changed the microseconds format). Subsequent versions of SQLA within a major version must work perfectly with existing SQLite databases created by a previous version.

If I pass a datetime.date to a DateTime type, I'll get "YYYY-MM-DD" due to the conditional, when currently I'd get "YYYY-MM-DD 00:00:00". Similar behavior for datetime.time objects. So we need to keep the output format nailed to the specific TypeEngine type, regardless of the type of object passed. It suggests that each type would have to marshal the given object into its own type (such as a time becomes a datetime as needed) before using isoformat() - or alternatively (and actually this is what I would probably do), we just re-implement strftime() with a regular expression that encodes numerically.

Keep in mind that you can continue to workaround this issue by just supplying your own DateTime types which use isoformat().

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

sorry, this one fell off the radar....im gathering 6 months is too long huh ? (just kidding). I'll take a look at it now.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

the __format__ is not supremely important and we can move to isoformat() on this one - can you attach a patch ?

[sqlalchemy-bot]

Anonymous wrote:

---

Commenter from 2008-09-12 again. I've taken your suggested strftime function, turned it into a class (with a few mods) and integrated it with the current (0.4.7p1) code where it works fine. I've attached the resulting patch along with a basic set of tests.

[sqlalchemy-bot]

Anonymous wrote:

---

Tests for new date patch

[sqlalchemy-bot]

Anonymous wrote:

---

New patch for pre1900 dates

[sqlalchemy-bot]

Anonymous wrote:

---

Any ideas when this patch will get applied. We use a lot of pre 1900 dates with sqlite and it is a real pain for each developer to have to apply this patch (which definitely works btw!).

[sqlalchemy-bot]

Anonymous wrote:

---

OK. I've added a patch.

[sqlalchemy-bot]

Anonymous wrote:

---

Deal with pre-1900 dates in sqlite

[sqlalchemy-bot]

Changes by Michael Bayer (zzzeek): removed "0.4.xx" milestone

[sqlalchemy-bot]

Changes by Michael Bayer (zzzeek): set state to "resolved"

[sqlalchemy-bot]

Changes by Michael Bayer (zzzeek): changed priority from "major" to "critical"

[sqlalchemy-bot]

Changes by Michael Bayer (zzzeek): set milestone to "0.4.xx"

[sqlalchemy-bot]

Changes by Anonymous: set attachment to "sqlite_test.py"

[sqlalchemy-bot]

Changes by Anonymous: set attachment to "sqlite.pre1900.20080916.patch"

[sqlalchemy-bot]

Changes by Anonymous: set attachment to "sqlite_date_pre1900.patch"