How to use variables in SQL statement in Python

Question

Ok so I m not that experienced in Python   I have the following Python code    cursor execute  INSERT INTO table VALUES var1  var2  var3      where var1 is an integer  var2  amp  var3 are strings   How can I write the variable names without python including them as part of the query text

User · Accepted Answer

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   it does not do any escaping or quoting  it is prone to Uncontrolled string format attacks e g  SQL injection

User · Answer

Many ways  DON T use the most obvious one   s with    in real code  it s open to attacks   Here copy-paste d from pydoc of sqlite3     Never do this -- insecure  symbol    RHAT  c execute  SELECT   FROM stocks WHERE symbol     s     symbol     Do this instead t     RHAT    c execute  SELECT   FROM stocks WHERE symbol     t  print c fetchone      Larger example that inserts many records at a time purchases      2006-03-28    BUY    IBM   1000  45 00                  2006-04-05    BUY    MSFT   1000  72 00                  2006-04-06    SELL    IBM   500  53 00                 c executemany  INSERT INTO stocks VALUES               purchases    More examples if you need     Multiple values single statement execution c execute  SELECT   FROM stocks WHERE symbol   OR symbol       RHAT    MSO    print c fetchall   c execute  SELECT   FROM stocks WHERE symbol IN            RHAT    MSO    print c fetchall     This also works  though ones above are better as a habit as it s inline with syntax of executemany     but your choice  c execute  SELECT   FROM stocks WHERE symbol   OR symbol      RHAT    MSO   print c fetchall     Insert a single item c execute  INSERT INTO stocks VALUES                 2006-03-28    BUY    IBM   1000  45 00

User · Answer

The syntax for providing a single value can be confusing for inexperienced Python users  Given the query INSERT INTO mytable  fruit  VALUES   s   The value passed to cursor execute must still be a tuple even though it is a singleton  so we must provide a single element tuple  like this   value    cursor execute  quot  quot  quot INSERT INTO mytable  fruit  VALUES   s  quot  quot  quot     apple

User · Answer

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

User · Answer

Meanwhile there is another way of how to do it with f-strings  cursor execute f quot INSERT INTO table VALUES  var1    var2    var3   quot

User · Answer

http   www amk ca python writing DB-API html  Be careful when you simply append values of variables to your statements  Imagine a user naming himself   DROP TABLE Users   -- That s why you need to use sql escaping  which Python provides for you when you use the cursor execute in a decent manner  Example in the url is   cursor execute  insert into Attendees values              name  seminar  paid

[python] How to use variables in SQL statement in Python?

Examples related to python

Examples related to sql