Menu
I have the following Python code:
cursor.execute("INSERT INTO table VALUES var1, var2, var3,")
where var1 is an integer, var2 and var3 are strings.
How can I write the variable names without Python including them as part of the query text?
495
Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.
You can pass parameters/arguments to your SQL statements by programmatically creating the SQL string using Scala/Python and pass it to sqlContext. sql(string). Note the 's' in front of the first """. This lets you substitute $param's in a Scala string.
In order to do reference of a variable in query, you need to use @ . Instead of filter value we are referring the column which we want to use for subetting or filtering. {0} takes a value of variable myvar1. Incase you want to pass multiple columns as variables in query.
We often need to pass variables to SQL select query in where clause to check some conditions. In the user signup form user enter his/her details. You can take those values in Python variables and insert them into a table.
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
84
Different implementations of the Python DB-API are allowed to use different placeholders, so you'll need to find out which one you're using -- it could be (e.g. with MySQLdb):
cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (var1, var2, var3))
or (e.g. with sqlite3 from the Python standard library):
cursor.execute("INSERT INTO table VALUES (?, ?, ?)", (var1, var2, var3))
or others yet (after VALUES you could have (:1, :2, :3) , or "named styles" (:fee, :fie, :fo) or (%(fee)s, %(fie)s, %(fo)s) where you pass a dict instead of a map as the second argument to execute). Check the paramstyle string constant in the DB API module you're using, and look for paramstyle at http://www.python.org/dev/peps/pep-0249/ to see what all the parameter-passing styles are!
84
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
