starred/sandman2-jeffknupp
1
0
Fork 0
mirror of https://github.com/jeffknupp/sandman2.git synced 2026-04-25 00:25:49 +03:00
Code Issues 45 Projects Releases Packages Wiki Activity

[GH-ISSUE #84] Not able to insert DateTime into sqlite #52

New issue
Closed
opened 2026-02-26 01:33:00 +03:00 by kerem · 5 comments
kerem commented 2026-02-26 01:33:00 +03:00
Owner
Copy link

Originally created by @LorenzHenk on GitHub (Feb 18, 2019).
Original GitHub issue: https://github.com/jeffknupp/sandman2/issues/84

I run sandman2ctl sqlite+pysqlite:///storage.db with an sqlite database.

The following error occurs if I try to insert '2019-02-17T21:48:07.539Z' into a column with the type datetime:

[2019-02-17 22:48:27,888] ERROR in app: Exception on /t_purchase/ [POST]
Traceback (most recent call last):
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\base.py", line 1127, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\default.py", line 669, in _init_compiled
    param.append(processors[key](compiled_params[key]))
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\dialects\sqlite\base.py", line 610, in process
    raise TypeError("SQLite DateTime type only accepts Python "
TypeError: SQLite DateTime type only accepts Python datetime and date objects as input.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\flask\app.py", line 2292, in wsgi_app
    response = self.full_dispatch_request()
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\flask\app.py", line 1815, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\flask\app.py", line 1718, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\flask\_compat.py", line 35, in reraise
    raise value
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\flask\app.py", line 1813, in full_dispatch_request
    rv = self.dispatch_request()
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\flask\app.py", line 1799, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\flask\views.py", line 88, in view
    return self.dispatch_request(*args, **kwargs)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\flask\views.py", line 158, in dispatch_request
    return meth(*args, **kwargs)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sandman2\decorators.py", line 72, in decorated
    return func(instance, *args, **kwargs)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sandman2\service.py", line 152, in post
    db.session().commit()
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\session.py", line 954, in commit
    self.transaction.commit()
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\session.py", line 467, in commit
    self._prepare_impl()
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\session.py", line 447, in _prepare_impl
    self.session.flush()
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\session.py", line 2313, in flush
    self._flush(objects)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\session.py", line 2440, in _flush
    transaction.rollback(_capture_exception=True)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\util\compat.py", line 249, in reraise
    raise value
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\session.py", line 2404, in _flush
    flush_context.execute()
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 395, in execute
    rec.execute(self)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 560, in execute
    uow
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\persistence.py", line 181, in save_obj
    mapper, table, insert)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\persistence.py", line 872, in _emit_insert_statements
    execute(statement, params)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\base.py", line 1132, in _execute_context
    None, None)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\util\compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\util\compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\base.py", line 1127, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\default.py", line 669, in _init_compiled
    param.append(processors[key](compiled_params[key]))
  File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\dialects\sqlite\base.py", line 610, in process
    raise TypeError("SQLite DateTime type only accepts Python "
sqlalchemy.exc.StatementError: (builtins.TypeError) SQLite DateTime type only accepts Python datetime and date objects as input. [SQL: 'INSERT INTO t_purchase (date) VALUES (?)'] [parameters: [{'date': '2019-02-17T21:48:07.539Z'}]]
127.0.0.1 - - [17/Feb/2019 22:48:27] "POST /t_purchase/ HTTP/1.1" 500 -
Originally created by @LorenzHenk on GitHub (Feb 18, 2019). Original GitHub issue: https://github.com/jeffknupp/sandman2/issues/84 I run `sandman2ctl sqlite+pysqlite:///storage.db` with an sqlite database. The following error occurs if I try to insert `'2019-02-17T21:48:07.539Z'` into a column with the type `datetime`: ``` [2019-02-17 22:48:27,888] ERROR in app: Exception on /t_purchase/ [POST] Traceback (most recent call last): File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\base.py", line 1127, in _execute_context context = constructor(dialect, self, conn, *args) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\default.py", line 669, in _init_compiled param.append(processors[key](compiled_params[key])) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\dialects\sqlite\base.py", line 610, in process raise TypeError("SQLite DateTime type only accepts Python " TypeError: SQLite DateTime type only accepts Python datetime and date objects as input. The above exception was the direct cause of the following exception: Traceback (most recent call last): File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\flask\app.py", line 2292, in wsgi_app response = self.full_dispatch_request() File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\flask\app.py", line 1815, in full_dispatch_request rv = self.handle_user_exception(e) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\flask\app.py", line 1718, in handle_user_exception reraise(exc_type, exc_value, tb) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\flask\_compat.py", line 35, in reraise raise value File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\flask\app.py", line 1813, in full_dispatch_request rv = self.dispatch_request() File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\flask\app.py", line 1799, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\flask\views.py", line 88, in view return self.dispatch_request(*args, **kwargs) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\flask\views.py", line 158, in dispatch_request return meth(*args, **kwargs) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sandman2\decorators.py", line 72, in decorated return func(instance, *args, **kwargs) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sandman2\service.py", line 152, in post db.session().commit() File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\session.py", line 954, in commit self.transaction.commit() File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\session.py", line 467, in commit self._prepare_impl() File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\session.py", line 447, in _prepare_impl self.session.flush() File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\session.py", line 2313, in flush self._flush(objects) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\session.py", line 2440, in _flush transaction.rollback(_capture_exception=True) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\util\compat.py", line 249, in reraise raise value File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\session.py", line 2404, in _flush flush_context.execute() File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 395, in execute rec.execute(self) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 560, in execute uow File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\persistence.py", line 181, in save_obj mapper, table, insert) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\orm\persistence.py", line 872, in _emit_insert_statements execute(statement, params) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute return meth(self, multiparams, params) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement compiled_sql, distilled_params File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\base.py", line 1132, in _execute_context None, None) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception exc_info File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\util\compat.py", line 265, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\util\compat.py", line 248, in reraise raise value.with_traceback(tb) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\base.py", line 1127, in _execute_context context = constructor(dialect, self, conn, *args) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\engine\default.py", line 669, in _init_compiled param.append(processors[key](compiled_params[key])) File "c:\users\lorenz\appdata\local\programs\python\python36\lib\site-packages\sqlalchemy\dialects\sqlite\base.py", line 610, in process raise TypeError("SQLite DateTime type only accepts Python " sqlalchemy.exc.StatementError: (builtins.TypeError) SQLite DateTime type only accepts Python datetime and date objects as input. [SQL: 'INSERT INTO t_purchase (date) VALUES (?)'] [parameters: [{'date': '2019-02-17T21:48:07.539Z'}]] 127.0.0.1 - - [17/Feb/2019 22:48:27] "POST /t_purchase/ HTTP/1.1" 500 - ```
kerem closed this issue 2026-02-26 01:33:00 +03:00
kerem commented 2026-02-26 01:33:00 +03:00
Author
Owner
Copy link

@LorenzHenk commented on GitHub (Mar 14, 2019):

If anyone else has this problem as well, I've found the solution:
You need to create a custom type and cast the string to a datetime object yourself.

class MyDateTime(db.TypeDecorator):
    impl = db.DateTime
    
    def process_bind_param(self, value, dialect):
        if type(value) is str:
            return datetime.datetime.strptime(value, '%Y-%m-%dT%H:%M:%S')
        return value
<!-- gh-comment-id:473006453 --> @LorenzHenk commented on GitHub (Mar 14, 2019): If anyone else has this problem as well, I've found the solution: You need to create a custom type and cast the string to a datetime object yourself. ```python class MyDateTime(db.TypeDecorator): impl = db.DateTime def process_bind_param(self, value, dialect): if type(value) is str: return datetime.datetime.strptime(value, '%Y-%m-%dT%H:%M:%S') return value ```
kerem commented 2026-02-26 01:33:00 +03:00
Author
Owner
Copy link

@Ujapy commented on GitHub (May 2, 2019):

Thanks for the reply, but where do I put the "custom type"?

<!-- gh-comment-id:488628231 --> @Ujapy commented on GitHub (May 2, 2019): Thanks for the reply, but where do I put the "custom type"?
kerem commented 2026-02-26 01:33:00 +03:00
Author
Owner
Copy link

@LorenzHenk commented on GitHub (May 2, 2019):

Here is a usage example:

class Purchase(db.Model, Model):
    __tablename__ = 't_purchase'

    id = db.Column(db.Integer, primary_key=True)
    location = db.Column(db.String)
    date = db.Column(MyDateTime, default=datetime.datetime.now)
<!-- gh-comment-id:488653321 --> @LorenzHenk commented on GitHub (May 2, 2019): Here is a usage example: ``` class Purchase(db.Model, Model): __tablename__ = 't_purchase' id = db.Column(db.Integer, primary_key=True) location = db.Column(db.String) date = db.Column(MyDateTime, default=datetime.datetime.now) ```
kerem commented 2026-02-26 01:33:00 +03:00
Author
Owner
Copy link

@Ujapy commented on GitHub (May 2, 2019):

Thanks, I'll try it out and give you my feedback

On Thursday, May 2, 2019, Lorenz Henk notifications@github.com wrote:

Here is a usage example:

class Purchase(db.Model, Model):
tablename = 't_purchase'

id = db.Column(db.Integer, primary_key=True)
location = db.Column(db.String)
date = db.Column(MyDateTime, default=datetime.datetime.now)

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/jeffknupp/sandman2/issues/84#issuecomment-488653321,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AKZY7ZT34G2LYNV22VKPYDLPTLNAPANCNFSM4GYGBVEQ
.

<!-- gh-comment-id:488654527 --> @Ujapy commented on GitHub (May 2, 2019): Thanks, I'll try it out and give you my feedback On Thursday, May 2, 2019, Lorenz Henk <notifications@github.com> wrote: > Here is a usage example: > > class Purchase(db.Model, Model): > __tablename__ = 't_purchase' > > id = db.Column(db.Integer, primary_key=True) > location = db.Column(db.String) > date = db.Column(MyDateTime, default=datetime.datetime.now) > > — > You are receiving this because you commented. > Reply to this email directly, view it on GitHub > <https://github.com/jeffknupp/sandman2/issues/84#issuecomment-488653321>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/AKZY7ZT34G2LYNV22VKPYDLPTLNAPANCNFSM4GYGBVEQ> > . >
kerem commented 2026-02-26 01:33:00 +03:00
Author
Owner
Copy link

@AllanSchergerGitHub commented on GitHub (Jun 25, 2020):

with python 3.6.9 I had to make a couple updates.

def datetime_sqlalchemy(value):
return datetime.datetime.strptime(value, '%Y-%m-%d %H:%M:%S.%f')

and

datetime_created=datetime_sqlalchemy(str(datetime.datetime.now()))

and sqlalchemy is set up this way:

class Article(Base):
tablename = 'article'
datetime_created = Column(DateTime)
datetime_end = Column(DateTime)

Let me know if I'm doing this incorrectly ;but thought I'd leave an update on what worked for me.

<!-- gh-comment-id:649172790 --> @AllanSchergerGitHub commented on GitHub (Jun 25, 2020): with python 3.6.9 I had to make a couple updates. def datetime_sqlalchemy(value): return datetime.datetime.strptime(value, '%Y-%m-%d %H:%M:%S.%f') and datetime_created=datetime_sqlalchemy(str(datetime.datetime.now())) and sqlalchemy is set up this way: class Article(Base): __tablename__ = 'article' datetime_created = Column(DateTime) datetime_end = Column(DateTime) Let me know if I'm doing this incorrectly ;but thought I'd leave an update on what worked for me.
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
master
pyup-scheduled-update-2026-04-20
pyup-scheduled-update-2024-09-16
pyup-scheduled-update-2023-07-24
pyup-scheduled-update-2023-06-19
pyup-scheduled-update-2023-05-22
pyup-scheduled-update-2023-05-15
pyup-scheduled-update-2023-05-08
dependabot/pip/flask-2.3.2
pyup-scheduled-update-2023-05-01
pyup-scheduled-update-2023-04-03
dependabot/pip/werkzeug-2.2.3
pyup-scheduled-update-2022-11-14
pyup-scheduled-update-2022-10-03
pyup-scheduled-update-2022-08-01
pyup-scheduled-update-2022-06-13
pyup-scheduled-update-2022-05-30
pyup-scheduled-update-2022-03-07
pyup-scheduled-update-2022-01-10
pyup-scheduled-update-2021-11-01
pyup-scheduled-update-2020-10-05
pyup-scheduled-update-2019-09-30
pyup-scheduled-update-2019-07-29
pyup-scheduled-update-2019-07-22
pyup-scheduled-update-2019-05-27
pyup-scheduled-update-2019-03-11
marshmallow
pyup-scheduled-update-2019-02-25
pyup-scheduled-update-2019-02-18
pyup-scheduled-update-2019-01-28
pyup-scheduled-update-2019-01-14
docker
name-map-stupid
swagger
fix-pagination
doc-expansion
specify-schema
No results found.
Labels
Clear labels   
bug

   
duplicate

   
enhancement

   
help wanted

   
invalid

   
pull-request

Mirrored from GitHub Pull Request

  
question

   
refactoring

   
research

   
wontfix
No labels
bug
duplicate
enhancement
help wanted
invalid
pull-request
question
refactoring
research
wontfix
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
starred/sandman2-jeffknupp#52
No description provided.