{"action":"created","issue":{"url":"https://api.github.com/repos/pudo/dataset/issues/97","labels_url":"https://api.github.com/repos/pudo/dataset/issues/97/labels{/name}","comments_url":"https://api.github.com/repos/pudo/dataset/issues/97/comments","events_url":"https://api.github.com/repos/pudo/dataset/issues/97/events","html_url":"https://github.com/pudo/dataset/issues/97","id":34952763,"number":97,"title":"Doesn't roundtrip datetimes - at least in SQLite.","user":{"login":"scraperdragon","id":1957682,"avatar_url":"https://avatars.githubusercontent.com/u/1957682?v=3","gravatar_id":"","url":"https://api.github.com/users/scraperdragon","html_url":"https://github.com/scraperdragon","followers_url":"https://api.github.com/users/scraperdragon/followers","following_url":"https://api.github.com/users/scraperdragon/following{/other_user}","gists_url":"https://api.github.com/users/scraperdragon/gists{/gist_id}","starred_url":"https://api.github.com/users/scraperdragon/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/scraperdragon/subscriptions","organizations_url":"https://api.github.com/users/scraperdragon/orgs","repos_url":"https://api.github.com/users/scraperdragon/repos","events_url":"https://api.github.com/users/scraperdragon/events{/privacy}","received_events_url":"https://api.github.com/users/scraperdragon/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2014-06-04T12:09:16Z","updated_at":"2015-05-21T17:34:26Z","closed_at":"2015-05-21T15:19:35Z","body":"The actual problem underlying #96.\r\n\r\nIf you put a datetime into the database, you get a unicode string back.\r\nIf you put the unicode string into the database, #96 stings you and it crashes.\r\n\r\n(Roundtripping is important for \"fetch this row from the DB, mutate it and save it back\" scenarios.)\r\n\r\n```\r\n>>> import dataset\r\n>>> db = dataset.connect(\"sqlite:///:memory:\")\r\n>>> table = db['table']\r\n>>> import datetime\r\n>>> table.insert({'datecol': datetime.datetime.now()})\r\n1\r\n>>> for row in table: pass\r\n...\r\n>>> row\r\nOrderedDict([(u'id', 1), (u'datecol', u'2014-06-04 12:05:32.821552')])\r\n>>> type(row['datecol'])\r\n<type 'unicode'>\r\n>>> table.insert({'datecol': row['datecol']})\r\nTraceback (most recent call last):\r\n  File \"<stdin>\", line 1, in <module>\r\n  File \"/home/venv/local/lib/python2.7/site-packages/dataset/persistence/table.py\", line 67, in insert\r\n    res = self.database.executable.execute(self.table.insert(row))\r\n  File \"/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py\", line 1682, in execute\r\n    return connection.execute(statement, *multiparams, **params)\r\n  File \"/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py\", line 720, in execute\r\n    return meth(self, multiparams, params)\r\n  File \"/home/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py\", line 317, in _execute_on_connection\r\n    return connection._execute_clauseelement(self, multiparams, params)\r\n  File \"/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py\", line 817, in _execute_clauseelement\r\n    compiled_sql, distilled_params\r\n  File \"/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py\", line 884, in _execute_context\r\n    None, None)\r\n  File \"/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py\", line 1108, in _handle_dbapi_exception\r\n    exc_info\r\n  File \"/home/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py\", line 185, in raise_from_cause\r\n    reraise(type(exception), exception, tb=exc_tb)\r\n  File \"/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py\", line 880, in _execute_context\r\n    context = constructor(dialect, self, conn, *args)\r\n  File \"/home/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py\", line 572, in _init_compiled\r\n    param.append(processors[key](compiled_params[key]))\r\n  File \"/home/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/type_api.py\", line 864, in process\r\n    return process_param(value, dialect)\r\n  File \"/home/venv/local/lib/python2.7/site-packages/dataset/persistence/util.py\", line 87, in process_bind_param\r\n    return (value / 1000 - self.epoch).total_seconds()\r\nsqlalchemy.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 (?)' []\r\n```"},"comment":{"url":"https://api.github.com/repos/pudo/dataset/issues/comments/104366196","html_url":"https://github.com/pudo/dataset/issues/97#issuecomment-104366196","issue_url":"https://api.github.com/repos/pudo/dataset/issues/97","id":104366196,"user":{"login":"eads","id":634572,"avatar_url":"https://avatars.githubusercontent.com/u/634572?v=3","gravatar_id":"","url":"https://api.github.com/users/eads","html_url":"https://github.com/eads","followers_url":"https://api.github.com/users/eads/followers","following_url":"https://api.github.com/users/eads/following{/other_user}","gists_url":"https://api.github.com/users/eads/gists{/gist_id}","starred_url":"https://api.github.com/users/eads/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/eads/subscriptions","organizations_url":"https://api.github.com/users/eads/orgs","repos_url":"https://api.github.com/users/eads/repos","events_url":"https://api.github.com/users/eads/events{/privacy}","received_events_url":"https://api.github.com/users/eads/received_events","type":"User","site_admin":false},"created_at":"2015-05-21T17:34:26Z","updated_at":"2015-05-21T17:34:26Z","body":"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:\r\n\r\n```\r\n  File \"/Users/deads/.virtualenvs/graphics-digester/src/dataset/dataset/persistence/table.py\", line 139, in update\r\n    rp = self.database.executable.execute(stmt)\r\n  File \"/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/engine/base.py\", line 1986, in execute\r\n    return connection.execute(statement, *multiparams, **params)\r\n  File \"/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/engine/base.py\", line 914, in execute\r\n    return meth(self, multiparams, params)\r\n  File \"/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/sql/elements.py\", line 323, in _execute_on_connection\r\n    return connection._execute_clauseelement(self, multiparams, params)\r\n  File \"/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/engine/base.py\", line 1010, in _execute_clauseelement\r\n    compiled_sql, distilled_params\r\n  File \"/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/engine/base.py\", line 1078, in _execute_context\r\n    None, None)\r\n  File \"/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/engine/base.py\", line 1339, in _handle_dbapi_exception\r\n    exc_info\r\n  File \"/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/util/compat.py\", line 199, in raise_from_cause\r\n    reraise(type(exception), exception, tb=exc_tb)\r\n  File \"/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/engine/base.py\", line 1073, in _execute_context\r\n    context = constructor(dialect, self, conn, *args)\r\n  File \"/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/engine/default.py\", line 574, in _init_compiled\r\n    param.append(processors[key](compiled_params[key]))\r\n  File \"/Users/deads/.virtualenvs/graphics-digester/lib/python2.7/site-packages/sqlalchemy/dialects/sqlite/base.py\", line 440, in process\r\n    raise TypeError(\"SQLite DateTime type only accepts Python \"\r\nsqlalchemy.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 = ?']\r\n```"}}