Doesn't roundtrip datetimes - at least in SQLite.

[scraperdragon]

The actual problem underlying #96.

If you put a datetime into the database, you get a unicode string back.
If you put the unicode string into the database, #96 stings you and it crashes.

(Roundtripping is important for "fetch this row from the DB, mutate it and save it back" scenarios.)

>>> import dataset
>>> db = dataset.connect("sqlite:///:memory:")
>>> table = db['table']
>>> import datetime
>>> table.insert({'datecol': datetime.datetime.now()})
1
>>> for row in table: pass
...
>>> row
OrderedDict([(u'id', 1), (u'datecol', u'2014-06-04 12:05:32.821552')])
>>> type(row['datecol'])
<type 'unicode'>
>>> table.insert({'datecol': row['datecol']})
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/venv/local/lib/python2.7/site-packages/dataset/persistence/table.py", line 67, in insert
    res = self.database.executable.execute(self.table.insert(row))
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1682, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 720, in execute
    return meth(self, multiparams, params)
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 817, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 884, in _execute_context
    None, None)
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1108, in _handle_dbapi_exception
    exc_info
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 880, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 572, in _init_compiled
    param.append(processors[key](compiled_params[key]))
  File "/home/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/type_api.py", line 864, in process
    return process_param(value, dialect)
  File "/home/venv/local/lib/python2.7/site-packages/dataset/persistence/util.py", line 87, in process_bind_param
    return (value / 1000 - self.epoch).total_seconds()
sqlalchemy.exc.StatementError: unsupported operand type(s) for /: 'unicode' and 'int' (original cause: TypeError: unsupported operand type(s) for /: 'unicode' and 'int') u'INSERT INTO "table" (datecol) VALUES (?)' []

[eads]

Having the same problem. At first I thought I could roll a simple fix, but it's a tough one to solve as SQLite doesn't have a dedicated date type.

Still, I'm confused as to why writing a datetime object works, but the raw string doesn't, unless Dataset and/or SQLAlchemy is converting to an integer on the way in and a string on the way out.

[pudo]

Should be fixed now, see #97 -- please can you guys test from master before I publish a release?

[eads]

Hmm, not quite there yet. The date is still fetched as a unicode string, which now causes an error because it isn't a datetime object. Here's the relevant section of the traceback:

  File "/Users/deads/.virtualenvs/graphics-digester/src/dataset/dataset/persistence/table.py", line 139, in update
    rp = self.database.executable.execute(stmt)
  File "/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1986, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1078, in _execute_context
    None, None)
  File "/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1339, in _handle_dbapi_exception
    exc_info
  File "/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1073, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 574, in _init_compiled
    param.append(processors[key](compiled_params[key]))
  File "/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/dialects/sqlite/base.py", line 440, in process
    raise TypeError("SQLite DateTime type only accepts Python "
sqlalchemy.exc.StatementError: (exceptions.TypeError) SQLite DateTime type only accepts Python datetime and date objects as input. [SQL: u'UPDATE projects SET story_url=?, date=?, graphic_slug=?, graphic_type=?, story_headline=?, contact=?, emailed=? WHERE projects.id = ?']

[pudo]

@eads: can you give me an indication of what you're trying to push in there? or you you just round-tripping a record with the date coming out of the db?

[eads]

I'm round-tripping a record. Here's a truncated version of the code:

def generate_email(days='2'):
    days = int(days)
    past = datetime.now() - timedelta(days=days)
    result = list(db.query('SELECT * from projects WHERE date >= Datetime(\'%s\') and emailed=0' % past))
    for row in result:
        _send_notification(row)
        _save_row(row)

This version of save_row works:

def _save_row(row):
    update = {
        'id': row['id'],
        'emailed': 1,
    }
    table.update(update, ['id'])

This fails with the stack trace above:

def _save_row(row):
    row['emailed'] = 1
    table.update(row, ['id'])

(Using 1s and 0s for bool since that's how Sqlite handles them anyway)

[pudo]

My guess is that somewhere in your program, you are writing string values to a datetime column in an sqlite (?) database. With sqlite not having any moral backbone or dignity to speak of, they will let you do that. I'm not quite sure how to handle it.

For now, I'd go with the targeted update that you posted in your last comment, the only alternative I can think of is actually trying to parse the date string in update which is bound to lead to more trouble.

[eads]

The program is writing datetimes that come from
datetime.utcfromtimestamp(timestamp),
so I don't think there's ever a chance that it could be writing strings.

maybe this just means I should give up on sqlite. It definitely lacks moral
fiber, but it is convenient for this project since our we'll never have
more than a few thousand rows and wanted to keep things... lite.

On Sat, May 23, 2015 at 10:24 AM, Friedrich Lindenberg <
notifications@github.com> wrote:

My guess is that somewhere in your program, you are writing string values
to a datetime column in an sqlite (?) database. With sqlite not having
any moral backbone or dignity to speak of, they will let you do that. I'm
not quite sure how to handle it.

For now, I'd go with the targeted update that you posted in your last
comment, the only alternative I can think of is actually trying to parse
the date string in update which is bound to lead to more trouble.

—
Reply to this email directly or view it on GitHub
#97 (comment).

David Eads | http://recoveredfactory.net

"Medical statistics will be our standard of measurement: we will weigh life
for life and see where the dead lie thicker, among the workers or among the
privileged." -- Rudolf Virchow

[pudo]

Issue seems to be resolved in master.