Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

#32 [WIP] Add mysql and pgsql database support #34

Draft
wants to merge 1 commit into
base: master
Choose a base branch
from
Draft

#32 [WIP] Add mysql and pgsql database support #34

wants to merge 1 commit into from

Conversation

duchenpaul
Copy link

  1. Tested in mysql and sqlite.
  2. I added var length and changed type of data key in table note as TEXT
  3. Need to review if the length is reasonable.

m0ngr31
m0ngr31 reviewed May 10, 2021
View reviewed changes
Dockerfile Show resolved Hide resolved
m0ngr31
m0ngr31 reviewed May 10, 2021
View reviewed changes
app/models.py Outdated Show resolved Hide resolved
@duchenpaul duchenpaul force-pushed the add_mysql_pgsql_support branch from ee10a9a to 223884c Compare May 10, 2021 15:24
@duchenpaul duchenpaul force-pushed the add_mysql_pgsql_support branch from 223884c to 343e0a9 Compare May 10, 2021 15:30
@m0ngr31
Copy link
Owner

m0ngr31 commented May 10, 2021

Everything looks good to me as long as it doesn't mess up sqlite support.

@duchenpaul
Copy link
Author

Try yourself, I did try mysql and sqlite

@m0ngr31
Copy link
Owner

m0ngr31 commented May 10, 2021

Yeah, I'll try and check later today. Thanks for putting the work in!

@duchenpaul duchenpaul closed this May 11, 2021
@duchenpaul
Copy link
Author

duchenpaul commented May 11, 2021 •
edited
Loading

Hang on, there is a issue when trying to save a note in mysql, why the data is save as binary? We could have a base64 encode to convert it as ASCIIs

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> a477f34dbaa4, Initial config
INFO  [alembic.runtime.migration] Running upgrade a477f34dbaa4 -> 9ca5901af374, cleanup
INFO  [alembic.runtime.migration] Running upgrade 9ca5901af374 -> 7bd1ee1840ca, Meta Table
INFO  [alembic.runtime.migration] Running upgrade 7bd1ee1840ca -> c440f31aff28, Add named unique constraint name for note title
INFO  [alembic.runtime.migration] Running upgrade c440f31aff28 -> 9bd71ed6ccff, Remove unique constraint name for note title
[2021-05-11 02:45:54 +0000] [1] [INFO] Starting gunicorn 20.1.0
[2021-05-11 02:45:54 +0000] [1] [INFO] Listening at: http://0.0.0.0:5000 (1)
[2021-05-11 02:45:54 +0000] [1] [INFO] Using worker: sync
[2021-05-11 02:45:54 +0000] [14] [INFO] Booting worker with pid: 14
/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py:1099: SAWarning: TypeDecorator GUID() will not produce a cache key because the ``cache_ok`` flag is not set to True.  Set this flag to True if this type object's state is safe to use in a cache key, or False to disable this warning.
  util.warn(
[2021-05-11 02:46:30,943] ERROR in app: Exception on /api/save_day [PUT]
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.8/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.DataError: (1366, "Incorrect string value: '\\xD9\\x04\\xBF{\\xA9E...' for column `dailynotes`.`note`.`data` at row 1")

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

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.8/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/lib/python3.8/site-packages/flask_jwt_extended/view_decorators.py", line 121, in decorator
    return fn(*args, **kwargs)
  File "/app/app/routes.py", line 83, in save_day
    db.session.flush()
  File "<string>", line 2, in flush
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3262, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3402, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3362, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 453, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 627, in execute
    util.preloaded.orm_persistence.save_obj(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 242, in save_obj
    _emit_insert_statements(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1219, in _emit_insert_statements
    result = connection._execute_20(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1582, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 320, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1451, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1813, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1994, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.8/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.DataError: (pymysql.err.DataError) (1366, "Incorrect string value: '\\xD9\\x04\\xBF{\\xA9E...' for column `dailynotes`.`note`.`data` at row 1")
[SQL: INSERT INTO note (uuid, user_id, data, title, is_date) VALUES (%(uuid)s, %(user_id)s, %(data)s, %(title)s, %(is_date)s)]
[parameters: {'uuid': '4b9300ef882b4cb2a777ef13cdadbf45', 'user_id': 'ca777ecb7072443185dcb5c1e161893d', 'data': b'V\xd9\x04\xbf{\xa9E\xf7W\xb1L>v_\x10s\xf5\xb8q\xec\x10\xaeD\xabVc\xd7\xa4\xa2\x9a|\xb6\xa9c\xcf6\x82', 'title': b'F\xb6\\\x118W[4\xb3\x8b', 'is_date': 1}]
(Background on this error at: http://sqlalche.me/e/14/9h9h)
[2021-05-11 02:47:19,926] ERROR in app: Exception on /api/save_day [PUT]
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.8/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.DataError: (1366, "Incorrect string value: '\\x93\\x00\\x18\\x12\\x8F\\xAE...' for column `dailynotes`.`note`.`data` at row 1")

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

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.8/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/lib/python3.8/site-packages/flask_jwt_extended/view_decorators.py", line 121, in decorator
    return fn(*args, **kwargs)
  File "/app/app/routes.py", line 83, in save_day
    db.session.flush()
  File "<string>", line 2, in flush
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3262, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3402, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3362, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 453, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 627, in execute
    util.preloaded.orm_persistence.save_obj(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 242, in save_obj
    _emit_insert_statements(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1219, in _emit_insert_statements
    result = connection._execute_20(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1582, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 320, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1451, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1813, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1994, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.8/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.DataError: (pymysql.err.DataError) (1366, "Incorrect string value: '\\x93\\x00\\x18\\x12\\x8F\\xAE...' for column `dailynotes`.`note`.`data` at row 1")
[SQL: INSERT INTO note (uuid, user_id, data, title, is_date) VALUES (%(uuid)s, %(user_id)s, %(data)s, %(title)s, %(is_date)s)]
[parameters: {'uuid': 'b952eae70dc446099a7eac1c1ce1f7d9', 'user_id': 'ca777ecb7072443185dcb5c1e161893d', 'data': b'[\x93\x00\x18\x12\x8f\xaeC\x86\x9f\x9b\xe9\xef\x19(\x0f\x95\xf0\x10r\xb5\xaa\xb1-?\x13v\xe1\x94\xd0&:$\xad\x08\x9c\xf6\xcfX\x1eR\xf8\\\xdc\x9e\xe4\x ... (1107 characters truncated) ... \\P\x04\x83\xcf\x8a\xb7*\x91\x91y\xa9\x08A\xb2^v\xb5}\x82B\x1a"g\n\r\x89\xdd,\xab4x\x9dB+\x04\xadi\xf6\xa3%?#\x07e\x0c(g\xcaz\xf8\xeb\x0b\xa7\xdb\xcd', 'title': b'F\xb6\\\x118W[4\xb3\x8b', 'is_date': 1}]
(Background on this error at: http://sqlalche.me/e/14/9h9h)

@duchenpaul duchenpaul reopened this May 11, 2021
@duchenpaul duchenpaul changed the title #32 Add mysql and pgsql database support #32 [WIP] Add mysql and pgsql database support May 11, 2021
@duchenpaul duchenpaul marked this pull request as draft May 11, 2021 03:37
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@m0ngr31 m0ngr31 m0ngr31 left review comments

Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.
3 participants
@duchenpaul @m0ngr31 @ctx-chend