PyMySQL query string builder

Question

So I'm using the PyMySQL package to query a remote database and I am finding it quite annoying to have to use the .execute() method.

I am looking for a wrapper that would be able to construct the raw MySQL queries in a more friendly fashion. Does anyone know of a package that would do this?

I have tried using pypika but the queries that it's building ('SELECT "id","username" FROM "users"') throw an error

pymysql.err.ProgrammingError: (1064, '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 \'"users"\' at line 1')

EDIT: this is a remote DB that I have no control over structure-wise and it is likely that the structure could change so I don't think I could use SQLAlchemy

EDIT 2: Here is the code that I used when I got that error above

from pypika import Query, Table, Field
import pymysql
users = Table('users')
q = Query.from_(users).select(users.id, users.username)
db = pymysql.connect(host=host,
                                  port=3306,
                                  user=user,
                                  passwd=password,
                                  db=db,
                                  cursorclass=pymysql.cursors.DictCursor)
db.execute(str(q))

Can you share us you code ?

GiovaniSalazar
– GiovaniSalazar

2019-11-14 17:19:47 +00:00
Commented Nov 14, 2019 at 17:19 — GiovaniSalazar
– GiovaniSalazar, Commented Nov 14, 2019 at 17:19
@GiovaniSalazar added!

J_C
– J_C

2019-11-14 17:25:32 +00:00
Commented Nov 14, 2019 at 17:25 — J_C
– J_C, Commented Nov 14, 2019 at 17:25

J_C · Accepted Answer · 2019-11-15 22:46:14Z

1

It looks like it's generating a query using double-quotes as identifier delimiters.

But MySQL uses back-ticks as the default identifier delimiter, so the query should look like this:

SELECT `id`,`username` FROM `users`

You can configure MySQL to use double-quotes, which is the proper identifier delimiter to comply with the ANSI SQL standard. To do this, you have to change the sql_mode to include the modes ANSI or ANSI_QUOTES.

Read https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html for full documentation on sql_mode behavior, and https://dev.mysql.com/doc/refman/8.0/en/identifiers.html for documentation on identifier delimiters.

I searched the pypika site and the docs are pretty sparse. But there's apparently a class for MySQLQuery that sets its own QUOTE_CHAR to ` which is what I would expect.

Are you using the proper query builder for MySQL?

edited Nov 15, 2019 at 22:46

J_C

3471 silver badge15 bronze badges

answered Nov 14, 2019 at 17:38

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

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

1 Comment

J_C Over a year ago

Thanks! I started using MySQLQuery as the instead of Query to build my queries and everything worked! You were correct about the backticks vs quotes so thanks for clarifying that for me :)

Mamba_24L · Accepted Answer · 2019-11-14 17:26:37Z

0

You can use peewee a a simple and small ORM. http://docs.peewee-orm.com/en/latest/peewee/quickstart.html#quickstart

answered Nov 14, 2019 at 17:26

Mamba_24L

91 bronze badge

Collectives™ on Stack Overflow

PyMySQL query string builder

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related