What is the reason for providing %s in the insert command while entering values into database using python pyscopg2

Question

I was trying to enter values into my database table in PostgreSQL, so I used the psycopg2 module.

The syntax I used is as follows:

Load_data= """

insert into stocks (Date, Symbol, Open, High, Low, Close, Volume, Delivery, Delivery_percent)
values (%s, %s, %s, %s, %s, %s, %s, %s, %s);

"""

I'm calling this with my cursor.execute(Load_data) and conn.commit(). I was able to load the data, but I want to know the reason why am I supposed to provide %s in the values.

I'm unable to think of why it is required. Thanks for your answer.

Answer 1

The %s (or %(var_name)s if passing a dictionary of parameters) syntax represents a parameter to be filled with a value when the transaction is executed. This syntax allows psycopg to translate Python values to SQL values (e.g. None to NULL) which is pretty handy if you consider all the different types that need translation.

This syntax also allows for simpler notation when passing parameters to a query so that the same query text can be reused with different values. For example:

def insert_row(name):
    query_text = "INSERT INTO my_table (name) VALUES (%s);"
    cursor.execute(query_text, [name]) # or cursor.execute(query_text, (name,))

Finally, and, in my opinion, most importantly, psycopg also sanitizes parameter values in order to avoid SQL injection attacks.

You might wonder why not just use string interpolation or concatenation to build the query string with the desired values, like this:

def insert_row(name):
    query_text = f"INSERT INTO my_table (name) VALUES ('{name}');"
    cursor.execute(query_text)

This approach would work fine for most uses (especially as string translation doesn't require much effort), however what if the name comes from a user and that user provides a name value of "my_name'); DROP TABLE my_table;".

Then the interpolated query_text becomes "INSERT INTO my_table (name) VALUES ('my_name'); DROP TABLE my_table;');". This will insert the string 'my_name' as the name value and the delete the my_table table, which is probably something you want to avoid.

Using parameter notation here (%s) will cause the inserted name value to be the provided string ("my_name'); DROP TABLE my_table;"), rather than allowing that string to execute arbitrary SQL in the database.

Relevant documentation.