SQL Params

Warnings

• breaking Version 6.0.0 dropped support for Python 3.7 (which is End-of-Life). Additionally, the attributes `named` and `ordinal` on the `SQLParams` class were renamed to `in_style` and `out_style` respectively. The private attributes `match` and `replace` were also removed. Named parameters must now be valid identifiers and can no longer start with a digit.
  Fix: Upgrade to Python 3.8+ and update attribute access from `.named` to `.in_style` and `.ordinal` to `.out_style`. Ensure named parameters do not start with digits.
• gotcha When using tuple expansion (e.g., for `IN` clauses) with `SQLParams.formatmany()`, ensure that all tuples for a given parameter across *all* parameter sets have the exact same number of elements. If the tuple sizes vary, `formatmany()` will fail, and it's recommended to use `SQLParams.format()` in a loop instead for each parameter set.
  Fix: Maintain consistent tuple lengths across all parameter sets when using `formatmany()` with tuple expansion, or switch to calling `format()` individually for each parameter set.
• gotcha While `sqlparams` enables safe parameterized queries by converting parameter styles, it does not inherently prevent SQL injection if user input is directly concatenated into the SQL string *before* being processed by `sqlparams`. Always pass user-provided values exclusively through the parameters dictionary/list, never directly into the SQL query string itself.
  Fix: Strictly use `sqlparams.format()` or `sqlparams.formatmany()` with all user-supplied data placed in the `params` argument (as a dictionary or sequence), never concatenating it into the `sql` argument.

Install

• pip install sqlparams Install stable version

Imports

• SQLParams

  from sqlparams import SQLParams

  The attributes 'named' and 'ordinal' on SQLParams were renamed to 'in_style' and 'out_style' respectively in version 6.0.0.

Quickstart

Demonstrates initializing SQLParams to convert from named to qmark style, then using the `format` method for single and tuple parameters, and `formatmany` for multiple sets of parameters.

import sqlparams

# Convert from named style (e.g., ':name') to qmark style (e.g., '?')
query_converter = sqlparams.SQLParams('named', 'qmark')

# Example 1: Single parameter
sql_in = "SELECT * FROM users WHERE name = :name;"
params_in = {'name': "Thorin"}
sql_out, params_out = query_converter.format(sql_in, params_in)

print(f"Original SQL: {sql_in}")
print(f"Original Params: {params_in}")
print(f"Converted SQL: {sql_out}")
print(f"Converted Params: {params_out}\n")
# Expected: SELECT * FROM users WHERE name = ?; ['Thorin']

# Example 2: Tuple expansion for IN operator
sql_in_in = "SELECT * FROM users WHERE name IN :names;"
params_in_in = {'names': ("Dori", "Nori", "Ori")}
sql_out_in, params_out_in = query_converter.format(sql_in_in, params_in_in)

print(f"Original SQL (IN): {sql_in_in}")
print(f"Original Params (IN): {params_in_in}")
print(f"Converted SQL (IN): {sql_out_in}")
print(f"Converted Params (IN): {params_out_in}\n")
# Expected: SELECT * FROM users WHERE name in (?,?,?); ['Dori', 'Nori', 'Ori']

# Example 3: Multiple parameter sets for executemany
sql_many_in = "UPDATE users SET age = :age WHERE name = :name;"
params_many_in = [
    {'name': "Dwalin", 'age': 169},
    {'name': "Balin", 'age': 178}
]
sql_many_out, params_many_out = query_converter.formatmany(sql_many_in, params_many_in)

print(f"Original SQL (many): {sql_many_in}")
print(f"Original Params (many): {params_many_in}")
print(f"Converted SQL (many): {sql_many_out}")
print(f"Converted Params (many): {params_many_out}")
# Expected: UPDATE users SET age = ? WHERE name = ?; [[169, 'Dwalin'], [178, 'Balin']]