Passing strings to pymysql cursor using Python function

Question

I'm working on a script that will pull data from a database using pymysql and a simple SELECT statement. I'm going to run this statement many times, so I was hoping to simplify things by putting it in a function and passing the column name, table name, where clause and value as arguments to the function.

def retrieve_value_from_db(connection,column_name,table_name,where_clause,where_value):
    with connection:
        with connection.cursor() as cursor:
            sql = "SELECT %s FROM %s WHERE %s=%s"
            logging.debug(cursor.mogrify(sql,(column_name,table_name,where_clause,where_value)))
            cursor.execute(sql,(column_name,table_name,where_clause,where_value))
            result = cursor.fetchone()
        connection.commit()
    return result

However calling the function below returns the following error

retrieve_value_from_db(connection,"last_name","mother_table","id","S50000")

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 ''mother_table' WHERE 'id'='S50000'' at line 1")

Cursor.execute seems to be reading the quotation mark portion of the string, which is causing the programming error. So how do I pass a string argument to the function that cursor.execute can read? Is what I want to do even possible? Thanks in advance.

Tim Roberts · Accepted Answer · 2021-03-09 01:39:50Z

1

Perhaps surprisingly, you should not let the database substitution handle table names and column names. It tries to quote them as if they were fields, which is wrong.

sql = "SELECT %s FROM %s WHERE %s=%%s" % (column_name,table_name,where_clause)
...
cursor.execute(sql, (where_value,))

answered Mar 9, 2021 at 1:39

Tim Roberts

55.3k4 gold badges28 silver badges41 bronze badges

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

1 Comment

jharrison12 Over a year ago

Yes this explains a lot. Thank you!

Collectives™ on Stack Overflow

Passing strings to pymysql cursor using Python function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related