MySQL database throws exception when create_tables is called

[tomtechnologies]

Issue

When calling create_tables() with mysql, the following exception is thrown decimal.InvalidOperation: [<class 'decimal.ConversionSyntax'>].

This appears to be the result of attempted type conversion from the result of the query in peewee.py when it returns a result. This error is buried in cursors.py
query = ('SELECT table_name FROM information_schema.tables '
'WHERE table_schema = DATABASE() AND table_type != %s '
'ORDER BY table_name')
return [table for table, in self.execute_sql(query, ('VIEW',))]

This is throws the following stack trace (trimmed)

Exception has occurred: InvalidOperation       (note: full exception trace is shown but execution is paused at: _run_module_as_main)
[<class 'decimal.ConversionSyntax'>]
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\MySQLdb\cursors.py", line 328, in _fetch_row
    return self._result.fetch_row(size, self._fetch_type)
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\MySQLdb\cursors.py", line 355, in _post_get_result
    self._rows = self._fetch_row(0)
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\MySQLdb\cursors.py", line 321, in _query
    self._post_get_result()
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\MySQLdb\cursors.py", line 206, in execute
    res = self._query(query)
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\peewee.py", line 3252, in execute_sql
    cursor.execute(sql, params or ())
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\peewee.py", line 4214, in get_tables
    return [table for table, in self.execute_sql(query, ('VIEW',))]
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\peewee.py", line 3419, in table_exists
    return table_name in self.get_tables(schema=schema)
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\peewee.py", line 6934, in table_exists
    return cls._schema.database.table_exists(M.table.__name__, M.schema)
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\peewee.py", line 6944, in create_table
    and cls.table_exists():
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\peewee.py", line 3441, in create_tables
    model.create_table(**options)

Environment

• Windows 11
• Python 3.9.5 x64
• MariaDB 11.1
• peewee 3.17.0
• mysqlclient 2.0.3

Example code

import peewee as pw


db = pw.MySQLDatabase('testdb',
                    user='testdb',
                    #password='',
                    host='127.0.0.1',
                    port=3306)

# db = pw.SqliteDatabase('people.db')

class BaseModel(pw.Model):
    class Meta:
        database = db

class User(BaseModel):
    username = pw.CharField(unique=True)
    email = pw.CharField(unique=True)
    password = pw.CharField()


class Tweet(BaseModel):
    user = pw.ForeignKeyField(User, backref='tweets')
    message = pw.TextField()
    created_date = pw.DateTimeField()

db.connect()
db.create_tables([User, Tweet])

Workaround

Found over on saltstack (saltstack/salt#65414)

pip install pymysql==1.1.0

[coleifer]

I have no control over what mysqlclient is doing... It seems like their code is broken somehow.

Please follow the discussion I've started on their "discussion" tracker and add any additional information that will help them debug this: PyMySQL/mysqlclient#677