Peewee silently inserts empty values for not-null columns without defaults into MySQL on strict mode

Question

There's no error raised when calling save(), create(), or insert().execute() on a model instantiated with one or more fields omitted, even on fields configured as null=False and default=None (the default setting for all fields) despite MySQL being configured to use strict mode globally:

mysql> SET GLOBAL sql_mode="TRADITIONAL";
Query OK, 0 rows affected (0.00 sec)

from rich import inspect

from peewee import Model, MySQLDatabase
from peewee import CharField, FixedCharField, BooleanField, DateTimeField

debug_db = MySQLDatabase(
    database = 'debug_db',
    user = 'DEBUG',
    host = 'localhost',
    password = 'secret'
)

class Person(Model):    
    first_name = CharField(32)
    last_name = CharField(32, null=False)
    email = FixedCharField(255)
    signup_time = DateTimeField()
    approved = BooleanField()
    
    class Meta:
        database = debug_db

debug_db.connect()
debug_db.create_tables([Person])

john_doe = Person(
    first_name = "John"
)

inspect(john_doe)

# │     approved = None                             │
# │ dirty_fields = [<CharField: Person.first_name>] │
# │        email = None                             │
# │   first_name = 'John'                           │
# │           id = None                             │
# │    last_name = None                             │
# │  signup_time = None                             │

john_doe.save()

# mysql> select * from person;
# +----+------------+-----------+-------+---------------------+----------+
# | id | first_name | last_name | email | signup_time         | approved |
# +----+------------+-----------+-------+---------------------+----------+
# |  1 | John       |           |       | 0000-00-00 00:00:00 |        0 |
# +----+------------+-----------+-------+---------------------+----------+
# 1 row in set (0.00 sec)

# Debug logger:
# ('SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE() AND table_type != %s ORDER BY table_name', ('VIEW',))
# ('INSERT INTO `person` (`first_name`) VALUES (%s)', ['John'])

On strict mode, the equivalent INSERT statement issued directly to MySQL throws an error:

# mysql> INSERT INTO person (first_name) VALUES ("John");
# ERROR 1364 (HY000): Field 'last_name' doesn't have a default value

As shown in the example, inspecting the instance reveals the omitted attributes are set to None internally. Interestingly, doing so manually triggers an error in Peewee and includes the None-valued field in the generated SQL:

john_doe = Person(
    first_name = "John",
    last_name = None
)

# peewee.IntegrityError: (1048, "Column 'last_name' cannot be null")

# Debug logger:
# ('INSERT INTO `person` (`first_name`, `last_name`) VALUES (%s, %s)', ['John', None])

In Peewee's documentation, the chapter on querying includes an example of gradually assigning values to an object created with some fields initially omitted, so allowing omission at instantiation must be intentional, but I would expect an error at some point before the row is inserted, either arising from the resulting SQL statement or when calling save().

By comparison, using SQLite instead of MySQL triggers an error in Peewee:

peewee.IntegrityError: NOT NULL constraint failed: person.last_name

I've also tested using playhouse.mysql_ext.MySQLConnectorDatabase, which produces the same result as the default MySQL driver.

I'm on Peewee 3.17, MySQL 8.0.31, and Python 3.10.5.

Answer 1

Peewee sets the sql_mode connection parameter by default, overriding any global SQL mode (including MySQL's default SQL mode), so as pointed out by coleifer, it's necessary to manually set sql_mode when establishing a connection to stop incomplete inserts with strict mode:

debug_db = MySQLDatabase(
    # ...
    sql_mode = 'TRADITIONAL' # One of 3 strict modes; not the MySQL default.
)

Specifically, Peewee overrides the sql_mode connection parameter (otherwise None in both PyMySQL and MySQL Connector/Python) in MySQLDatabase and its MySQLConnectorDatabase subclass in order to set the SQL mode PIPES_AS_CONCAT:

#line 4147 of peewee.py (within the MySQLDatabase class definition)
sql_mode = "PIPES_AS_CONCAT"

To keep PIPES_AS_CONCAT without overriding the global SQL mode, it's possible to just append it instead, using the init_command connection parameter while resetting Peewee's sql_mode override:

debug_db = MySQLDatabase(
    # ...
    sql_mode = None,
    init_command = "SET sql_mode=(SELECT CONCAT(@@sql_mode,',PIPES_AS_CONCAT'));"
)