Peewee: PrimaryKey issue with peewee and pymysql

[ckoepp]

I ran into an issue with primary keys when using pymysql in combination with peewee. Somehow the default value for the integer PK always is 0 which of course leads to problems when more than one object is created. A minimal example is the following code using a (rather stupid) Cat object and adding two cats:

from peewee import *

db = MySQLDatabase(
    database = "test",
    user = "test",
    passwd = "test",
    host = "localhost",
    port = 3306
    )

class Cat(Model):
    catid = IntegerField(primary_key=True)
    name = CharField()

    class Meta:
        database = db

db.create_tables((Cat,))

Cat.create(name="Winston")
Cat.create(name="Churchill")

When I run this code (using pymysql) I receive the following exception:

(...)/python3.4/site-packages/pymysql/cursors.py:134: Warning: Field 'catid' doesn't have a default value
  result = self._query(query)
Traceback (most recent call last):
  File "(...)/python3.4/site-packages/peewee.py", line 2869, in execute_sql
    cursor.execute(sql, params or ())
  File "(...)/python3.4/site-packages/pymysql/cursors.py", line 134, in execute
    result = self._query(query)
  File "(...)/python3.4/site-packages/pymysql/cursors.py", line 282, in _query
    conn.query(q)
  File "(...)/python3.4/site-packages/pymysql/connections.py", line 768, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "(...)/python3.4/site-packages/pymysql/connections.py", line 929, in _read_query_result
    result.read()

(...)

  File "(...)/python3.4/site-packages/pymysql/err.py", line 120, in raise_mysql_exception
    _check_mysql_exception(errinfo)
  File "(...)/python3.4/site-packages/pymysql/err.py", line 112, in _check_mysql_exception
    raise errorclass(errno, errorvalue)
pymysql.err.IntegrityError: (1062, "Duplicate entry '0' for key 'PRIMARY'")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "./cats.py", line 22, in <module>
    Cat.create(name="Churchill")
  File "(...)/python3.4/site-packages/peewee.py", line 3755, in create
    inst.save(force_insert=True)
  File "(...)/python3.4/site-packages/peewee.py", line 3890, in save
    pk_from_cursor = self.insert(**field_dict).execute()
  File "(...)/python3.4/site-packages/peewee.py", line 2685, in execute
    return self.database.last_insert_id(self._execute(), self.model_class)
  File "(...)/python3.4/site-packages/peewee.py", line 2243, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "(...)/python3.4/site-packages/peewee.py", line 2877, in execute_sql
    self.commit()

  (...)

  File "(...)/python3.4/site-packages/pymysql/err.py", line 112, in _check_mysql_exception
    raise errorclass(errno, errorvalue)
peewee.IntegrityError: (1062, "Duplicate entry '0' for key 'PRIMARY'")

Using sqlite3 with the very same model works, so I assume pymsql and peewee do have a problem when it comes to primary keys. Instead of auto-incrementing the PK field, it just uses 0 as default value. Thus my sql table looks like this after running the code above:

mysql> select * from cat;
+-------+---------+
| catid | name    |
+-------+---------+
|     0 | Winston |
+-------+---------+
1 row in set (0.00 sec)

[coleifer]

If you use IntegerField then it will not be auto-incrementing. Instead you should use PrimaryKeyField which contains the appropriate "AUTO INCREMENT" clause.