MySQL Python query works alone but fails when parameterized

Question

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!

alecxe · Accepted Answer · 2016-06-13 15:34:22Z

1

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.

answered Jun 13, 2016 at 15:34

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

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

Collectives™ on Stack Overflow

MySQL Python query works alone but fails when parameterized

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related