Syntax error in MySQL query: check the syntax near '?'. Where does the '?' come-CodePudding

I'm writing a module to use connector in Python in a more clever way (in terms of my needs) and I'm stuck into an error raised by self.__cursor.execute(query, values_escaped).

Here's the code:

class MySQL:
    # [...]
    def query(self, query: str, values_escaped: tuple = ()) -> MySQLCursorPrepared | MySQLCursorBuffered:
        # [...] <-- Here I don't change any parameter of the ones up, they stay untouched until reaching the bugged row below
        try:
            if values_escaped:
                self.__cursor = self.__conn.cursor(prepared = True)
                self.__cursor.execute(query, values_escaped) # <-- this is the bugged row
                
            else: 
                self.__cursor = self.__conn.cursor(buffered = True)
                self.__cursor.execute(query)
        # [...]
    # [...]


def test() -> None:
    with MySQL(database = "lockfile") as conn:
        res = conn.query("SELECT * FROM %s", ("fileslocked",)) # <-- I can't see any '?' sign! :(
        print(f"Rows: {res.rowcount}")

# Output:
Traceback (most recent call last):
  File "e:\xampp\htdocs\it-exercises\python\mylockfile\database.py", line 61, in query
    self.__cursor.execute(query, values_escaped)
  File "E:\Apps\Python310\lib\site-packages\mysql\connector\cursor_cext.py", line 1031, in execute
    self._stmt = self._cnx.cmd_stmt_prepare(operation)
  File "E:\Apps\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 517, in cmd_stmt_prepare
    raise InterfaceError(str(err)) from err
mysql.connector.errors.InterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?' at line 1
Rows: 0

I searched online but I've only found this solution: the prepared parameter in the cursor method. I found it in some unofficial docs of connector while in the official ones I couldn't find anything useful (I may have left something behind, but I've been struggling with this for some days), but it doesn't seem to work.

Moreover, where does the '?' sign come from?

CodePudding user response：

? represents the placeholder in the query, which is represented by %s in Python.
The issue is that you're attempting to dynamically bind a tablename, which isn't possible - placeholders can only be dynamically bound to values, not object names. Remove the placeholder and you should be OK:

res = conn.query("SELECT * FROM fileslocked")

CodePudding user response：

I think the ? comes from the string formatting syntax %s after the string was changed to a sql statement under the hood by the library you are using to create a connection.

I have seen a similar syntax when using string sql statements for sqlite3 - using ? for the variables passed to the sql statement. e.g.

db.conn.execute('SELECT * FROM table_1 WHERE id=?', id)

CodePudding user response：

First step is checking if your query works fine without variables:

res = conn.query("SELECT * FROM fileslocked")

If it works, you can try to use the %(name) style:

res = conn.query("SELECT * FROM %(database_name)", {"database_name": "fileslocked"})