CODEWITHSUNDEEP
pythonmysqlsyntax-errorprimary-keypeewee
Cecilia
Cecilia

Reputation: 4721

SQL syntax error in Peewee example with primary key

I've got a simple Peewee database model following the quickstart tutorial and am trying to add an instance to the database. It returns the error,

'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'WHERE (image.url = \'foo\')\' at line 1'

I paired down the code as much as possible, but I can't locate my mistake. Here is my model in a minimal and reproducible (I hope, it reproduces on my machine) example. Edit the MySQLDatabase call to fit your setup. I started with an empty database named 'test'.

from peewee import *

database = MySQLDatabase('test', **{'password': '1234', 'user': 'root'})

class BaseModel(Model):
    class Meta:
        database = database

class Image(BaseModel):
    url = CharField(primary_key=True)

database.connect()
database.create_tables([Image])

image_url = 'foo'
image_entry = Image(url=image_url)
image_entry.save()

The error is thrown by the last line of the example code. If I take a look in my database, I can see that the table 'image' was successfully created. 

describe image;

returns

+-------+--------------+------+-----+---------+-------+  
| Field | Type         | Null | Key | Default | Extra |  
+-------+--------------+------+-----+---------+-------+  
| url   | varchar(255) | NO   | PRI | NULL    |       |  
+-------+--------------+------+-----+---------+-------+

The table is still empty as expected because the error comes during the save statement. 

select * from image:

returns 

Empty set(0.00 sec)

Upvotes: 3

Views: 1750

Answers (1)

Phate01
Phate01

Reputation: 1795

This may help you:

https://peewee.readthedocs.org/en/2.0.2/peewee/fields.html#non-integer-primary-keys

from peewee import Model, PrimaryKeyField, VarCharColumn

class UUIDModel(Model):
    # explicitly declare a primary key field, and specify the class to use
    id = CharField(primary_key=True)

Auto-increment IDs are, as their name says, automatically generated for you when you insert a new row into the database. The way peewee determines whether to do an INSERT versus an UPDATE comes down to checking whether the primary key value is None. If None, it will do an insert, otherwise it does an update on the existing value. Since, with our uuid example, the database driver won’t generate a new ID, we need to specify it manually. When we call save() for the first time, pass in force_insert = True:

inst = UUIDModel(id=str(uuid.uuid4()))
inst.save() # <-- WRONG!!  this will try to do an update

inst.save(force_insert=True) # <-- CORRECT

# to update the instance after it has been saved once
inst.save()

Upvotes: 3

Related Questions