agiap agiap - 5 months ago 19
SQL Question

MySQL Python query works alone but fails when parameterized

Basically, I have a MySQL table like this:

CREATE TABLE markets (
id INTEGER AUTO_INCREMENT NOT NULL,
root_symbol VARCHAR(64) NOT NULL,
metadata JSON NOT NULL,
PRIMARY KEY (id)
);


The metadata column contains a JSON document like this:

metadata = {
'tick_size': 0.01,
'currency': 'USD'
}


In Python, the following 2 lines work:

cur.execute("SELECT root_symbol FROM markets WHERE JSON_EXTRACT(metadata, '$.currency') = 'USD'")
cur.execute("SELECT root_symbol FROM markets WHERE JSON_EXTRACT(metadata, '$.tick_size') = 0.25")


Yet when parameterized, these 2 don't:

cur.execute("SELECT root_symbol FROM markets WHERE JSON_EXTRACT(metadata, '$.%s') = %s", ('currency', "USD"))
cur.execute("SELECT root_symbol FROM markets WHERE JSON_EXTRACT(metadata, '$.%s') = %s", ('tick_size', 0.25))


Can somebody explain to me why and how to fix it? Thank you!

Answer

You need to parameterize the whole field value for the JSON_EXTRACT():

cur.execute("""
   SELECT root_symbol 
   FROM markets 
   WHERE JSON_EXTRACT(metadata, %s) = %s""", ('$.currency', "USD"))

cur.execute("""
   SELECT root_symbol 
   FROM markets 
   WHERE JSON_EXTRACT(metadata, %s) = %s""", ('$.tick_size', 0.25))

Note the absence of quotes around the placeholders - the database driver would put it automatically if needed.