How to Use Variables in SQL Statement in Python

How to use variables in SQL statement in Python?

cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (var1, var2, var3))

Note that the parameters are passed as a tuple.

The database API does proper escaping and quoting of variables. Be careful not to use the string formatting operator (%), because

  1. it does not do any escaping or quoting.
  2. it is prone to Uncontrolled string format attacks e.g. SQL injection.

How to use variables in Python SQL query?

You can use %s as a placeholder for the bound value:

query = """SELECT * FROM users WHERE email = %s"""
result = cursor.execute(query, (val,))

How to use variables in SQL statement in Python using IN statement

use:

pd.read_sql(f"select id, email from xdb where email in {tuple(df.email.values)}", conn)

Python and Prepared SQL Statements using Variables

Ok I have resolved this issue now.
I had to update the sybase module in order to get it to work with None > NULL.

As posted in the updated question. the below is how I was running the queries.

cursor.execute("SELECT * FROM DB..table where app_name=@app_name", {"@app_name": app_name})
or
params = {"@appname": app.name. "@appver": app.version}
sql = "INSERT INTO DB..table (app_name, app_version) VALUES (@appname, @appversion)
cursor.execute(sql, params)

How to use variable in SQL Alchemy

As Larnu points out in their comment, using f-strings or other string formatting techniques exposes an application to SQL injection attacks, and in any case can be error-prone.

SQLAlchemy supports parameter substitution, allowing values to be safely inserted into SQL statements.

from sqlalchemy import text

# Make a dictionary of values to be inserted into the statement.
values = {'year': 2019}
# Make the statement text into a text instance, with a placeholder for the value.
stmt = text('DELETE FROM [dbo].table1 where dbo.table1.[Year Policy] = :year')
# Execute the query.
result = connection.execute(stmt, values)

Variable table name in BigQuery Python Client



Related Topics



Leave a reply



Submit