How to Bind Nested Dict Values in Python SQL Queries (and When to Use Oracle JSON)

Learn why Python SQL placeholders can't access nested dict keys, how to flatten parameters safely, and when to bind JSON in Oracle Database with DB_TYPE_JSON.

When you pass a Python dict with nested data into a parameterized SQL statement, it can be tempting to reach into that structure directly from the SQL string. A common attempt looks like addressing person['country']['id'] inside a placeholder, expecting the database driver to resolve the path. The question is whether this is even possible and what to do instead.

Problem example

The pattern below illustrates the idea of binding a nested value by path inside the SQL template:

stmt_tpl = ("""
    INSERT INTO person 
        (id, name, country_id)
    VALUES 
        (%(pid)s, %(full_name)s, %(nation[id])s)
""")

row_data = {'pid': 1, 'full_name': 'First Last', 'nation': {'id': 1, 'name': 'USA'}}
db_cur.execute(stmt_tpl, row_data)

What’s going on and why it happens

The idea behind this code is straightforward: bind flat values for id and name, and also bind the nested key nation['id'] as country_id. The catch is in how bindings work. Placeholders are resolved by looking up keys in the mapping you pass to execute. Addressing nested structures by something like %(nation[id])s is, most likely, not supported. If you need a value from a nested dict, you would build the proper mapping in Python first or extract the needed keys before executing the statement. In other words, the driver isn’t expected to evaluate dictionary indexing expressions inside the SQL template.

Solution with JSON binding in Oracle Database

If you are on Oracle Database, you can store and bind the nested structure as JSON. That way you pass the whole object without trying to dereference parts of it inside the SQL string. The table definition would include a JSON column, and the Python binding would mark the parameter as JSON.

drop table if exists person;
create table person (id number, name varchar2(30), country_id json);

ins_text = """
    INSERT INTO person
    (id, name, country_id)
    VALUES
    (:id, :name, :country)
"""

payload = {'id': 1, 'name': 'First Last', 'country': {'id': 1, 'name': 'USA'}}

db_cur.setinputsizes(country=oracledb.DB_TYPE_JSON)
db_cur.execute(ins_text, payload)

This approach avoids the need to reference a nested key path in the SQL. Instead, the nested value is bound as a single JSON parameter.

Why this matters

It’s important to be clear about where data transformation happens. Trying to make the SQL template dig through a nested Python structure adds brittle magic to the query string. A cleaner approach is to prepare the data shape in Python—either by extracting specific values into a flat mapping or by persisting the nested structure as JSON when your database and schema design allow that. It’s also worth considering modeling. A point often raised is that this is a denormalized database: there should be a separate country table to which person is foreign-keyed. Another question that naturally follows is why a nested dictionary is used in the first place. These considerations help decide whether to flatten the data or store it as JSON.

Conclusion

Don’t rely on placeholder syntax to reach into nested dicts; most likely, that won’t work. If you need a scalar value, build the correct dict or extract the required keys before calling execute. If you are using Oracle Database and want to preserve the nested structure, bind it as JSON with an appropriate JSON column. Keeping the transformation logic in Python and the bindings simple in SQL leads to clearer code and fewer surprises.