# SQL String Composition
In this chapter we'll learn how to construct SQL queries by using the psycopg2.sql module. The official documentation[1] for this module is great though, and I'd encourage you to read it as well!
Up to now we've seen how to pass arguments to a query by using %s, like in this query:
SELECT * FROM users WHERE name = %s;
But what if we wanted to have a dynamic table? For example, if we want to allow users to find data in a table of their choosing?
# How to change the table name in a query dynamically
We can do something like this:
from psycopg2 import sql
table_name = input("Enter table you want to search in: ")
column_name = input("Enter column you want to search by: ")
search_value = input("Enter what value you're looking for: ")
query = sql.SQL("SELECT * FROM {table} WHERE {column} = %s;")
query = query.format(
table=sql.Identifier(table_name),
column=sql.Identifier(column_name)
)
...
cursor.execute(query, (search_value,))
# How to pass variable fields to a query
Instead of the * in the SELECT above, you could pass in a variable number of fields. For example:
fields_csv = input("Enter fields you wish to retrieve, separated by commas: ")
fields= fields_csv.strip().split(",")
sql_fields = [sql.Identifier(field) for field in fields]
query = sql.SQL("SELECT {fields} FROM users;")
query = query.format(
fields=sql.SQL(",").join(sql_fields)
)
# Other sql module classes
The full breakdown is available here: https://www.psycopg.org/docs/sql.html#sql-objects (opens new window), including sql.Literal for values hardcoded into a query (but not column or table names) and sql.Placeholder for values to be passed later, in cursor.execute().