PyMySQL query string builder

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))

Solution

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?