tuan huynh
tuan huynh

Reputation: 727

Fix python Sqlalchemy model can not insert mysql syntax error

Python Model:

 class SYSLocation(SYSModel):
        __tablename__ = 'sys_location'
        rank = db.Column(db.Integer)

Call: db.session.add(model)

It generate mysql script:

 INSERT INTO `sys_location` ( rank) VALUES (13000)

sql 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 'rank)
VALUES (13000)' at line 1

I check this query run error mysql version 8.0.11 because rank is keyword. But this sql can run mysql version 10.1.25-MariaDB. How to fix my Sqlalchemy model run all version of mysql?

Upvotes: 0

Views: 419

Answers (1)

tadman
tadman

Reputation: 211750

As always, when dealing with reserved keywords wrap it in backticks like you did for sys_location:

INSERT INTO `sys_location` (`rank`) VALUES (13000)

You can escape everything if you want, but it's often not necessary. Keywords are a case where it might be necessary because these keywords do change, if infrequently.

Upvotes: 1

Related Questions