Fix python Sqlalchemy model can not insert mysql syntax error

Question

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?

tadman · Accepted Answer · 2018-06-21 04:15:34Z

1

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.

answered Jun 21, 2018 at 4:15

tadman

212k23 gold badges237 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tuan huynh Over a year ago

I know this query but i want fix in sqlachemy model

tadman Over a year ago

Maybe you need an upgraded version of that library or you need to file a bug report if this isn't being done automatically in the latest version of both MariaDB/MySQL and SQLAlchemy.

tuan huynh Over a year ago

sqlalchemy upgraded fix my error. Thanks for your's help

Collectives™ on Stack Overflow

Fix python Sqlalchemy model can not insert mysql syntax error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related