How to put parameterized sql query into variable and then execute in Python?

by

Here is the call signature for cursor.execute:

Definition: cursor.execute(self, query, args=None)

    query -- string, query to execute on server
    args -- optional sequence or mapping, parameters to use with query.

So execute expects at most 3 arguments (args is optional).
If args is given, it is expected to be a sequence.
so

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

is not going to work, because 

cursor.execute(*sql_and_params)

expands the tuple sql_and_params into 4 arguments (and again, execute only expects 3).

If you really must use 

sql_and_params = "INSERT INTO table VALUES (%s, %s, %s)", var1, var2, var3

then you’ll have to break it apart when feeding it to cursor.execute:

cursor.execute(sql_and_params[0],sql_and_params[1:])

But I think it feels much more pleasant to just use two variables:

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

More Related Contents:

  1. SQL / Pandas equivalent
  2. python list in sql query as parameter [duplicate]
  3. Pandas read_sql with parameters
  4. How to create a large pandas dataframe from an sql query without running out of memory?
  5. Passing table name as a parameter in psycopg2
  6. Not all parameters were used in the SQL statement (Python, MySQL)
  7. Connecting to Microsoft SQL server using Python
  8. Getting the SQL from a Django QuerySet [duplicate]
  9. How can I get dict from sqlite query?
  10. Using a WHERE ___ IN ___ statement
  11. Django: Adding “NULLS LAST” to query
  12. return SQL table as JSON in python
  13. pyodbc – How to perform a select statement using a variable for a parameter
  14. LEFT JOIN Django ORM
  15. reading external sql script in python
  16. Python, SQLAlchemy pass parameters in connection.execute
  17. Python Pandas write to sql with NaN values
  18. How to see the raw SQL queries Django is running?
  19. How to log all sql queries in Django?
  20. Pandas query function not working with spaces in column names
  21. pyodbc insert into sql
  22. Python String Formats with SQL Wildcards and LIKE
  23. How to get rows which match a list of 3-tuples conditions with SQLAlchemy
  24. Dynamic SQL Queries with Python and mySQL
  25. How to perform OR condition in django queryset?
  26. What is an efficient way of inserting thousands of records into an SQLite table using Django?
  27. SQL join or R’s merge() function in NumPy?
  28. How to see the real SQL query in Python cursor.execute using pyodbc and MS-Access
  29. Remote connection to MS SQL – Error using pyodbc vs success using SQL Server Management Studio
  30. How to delete a record in Django models?

Leave a Comment