How to get a buffered cursor that can be used for prepared statements?

  cursor, mysql, mysql-connector, python

I am trying to write a method that can execute every type of queries.
At the moment the method is this:

def execute_query(connection, stmt, values):
  try:
    cursor = connection.cursor(prepared=True, buffered=True)
    cursor.execute(stmt, values)
    connection.commit()
    result = cursor.fetchall()
    return result
  except Exception as e:
    print(f"Error occurred in executing query. Statement: {stmt}, Values: {values}, Error: {e}")

The doc says that connection.cursor with buffered=True can only be used in combination with dictionary and named_tuple (in fact, with the written method I get the error Cursor not available with given criteria: buffered, prepared.)
I need also prepared=True since I’d like to use prepared statement to prevent SQL injections.
Imagine the query:

SELECT * FROM users WHERE id='123'

I’d like to use the method wrote before like this:

select_user_stmt = "SELECT * FROM users WHERE id = %s"
values = (123,)

res = execute_query(connection, select_user_stmt, values)

So, how to use a cursor that can be used to prevent SQL injections but that is also able to store items to return?

Source: Python Questions

LEAVE A COMMENT