Search code examples
pythonsqlitejsonpathjson-extract

Python named style parameter substitution in a JSON path in a SQLite query?

I am trying to substitute a part of a JSON path with a Python variable in a SQLite select query that includes a JSON extract function. The query works if I do not use substitution:

c.execute("insert into rd_test values (?, ?)",
                ["test_record", json.dumps(k[0]["data"]["children"][0])])

sample_query = c.execute('''select json_extract(data, '$.data.title') from rd_test;''').fetchall()
print(sample_query)

This correctly returns [('sample_title',)]. However, since I have to work with many different JSON records that are all on the same path level in the JSON file, I would like to create a function in which I just have to enter the part of the path that is different.

I would like to use named style for the substitution:

sample_query = c.execute('''select json_extract(data, '$.data.:var1') from rd_test;''', {"var1": 'title'}).fetchall()
print(sample_query)

This returns [(None,)]. Where is my mistake?

Solution

  • Placeholders aren't substituted inside strings. Use string concatenation to combine '$.data.' with :var1.

    sample_query = c.execute('''select json_extract(data, '$.data.' || :var1) from rd_test;''', {"var1": 'title'}).fetchall()