Python how to replace %s with actual values-CodePudding

Using the below code as an example, sometimes I want to be able to see what values are being entered into a select statement to verify the query is accurate. How do I do this so that what gets printed would look something like this: SQL with values: SELECT column FROM table WHERE id='1234' and name='bob'

sql = "SELECT column FROM table WHERE id=%s and name=%s"
args = [id, name]
query_string = ???
print('SQL with values: '   query_string)
with connection.cursor() as cursor:
    cursor.execute(sql, args)
    result = cursor.fetchone()

CodePudding user response：

You can use this

sql = "SELECT column FROM table WHERE id=%s and name=%s"
args = [id, name]
query_string = sql % (id, name)
print('SQL with values: '   query_string)
with connection.cursor() as cursor:
    cursor.execute(sql, args)
    result = cursor.fetchone()

CodePudding user response：

# ...
query_string = sql % (args[0], args[1])
# ...

CodePudding user response：

You still haven't answered what python package you're using.

If you're using the official mysql-connector-python package from MySQL and you're satisfied with printing the exact SQL after it was executed, then print(cursor._executed).

They don't seem to expose a simple processing function to display what would be executed, you'd have to lift some code from around here: https://github.com/mysql/mysql-connector-python/blob/master/lib/mysql/connector/cursor.py#L539 and make your own function if you need that.

You could approximate it somewhat with code like this:

def render_sql(sql, args):
    quoted = list()
    for arg in args:
        if type(arg) in (int, float):
            quoted.append(str(arg))
        elif type(arg) is str:
            quoted.append(f"'{arg}'")
        else:
            raise Exception(f"got an unknown type: {type(arg} for SQL param: {arg}")

    return sql % tuple(quoted)

sql = "SELECT column FROM table WHERE id=%s and name=%s"
print(render_sql(sql, (1, 1)))
# SELECT column FROM table WHERE id=1 and name=1
print(render_sql(sql, (1, 1.5)))
# SELECT column FROM table WHERE id=1 and name=1.5
print(render_sql(sql, (1, "test")))
# SELECT column FROM table WHERE id=1 and name='test'
print(render_sql(sql, (1, "text with spaces")))
# SELECT column FROM table WHERE id=1 and name='text with spaces'

You can extend that with additional types. But, as jarmod said, use this only for debugging, do not use it to execute on the database without reviewing the rendered SQL first.