Insert list into my database using Python [duplicate]

by

The answer to your original question is: No, you can’t insert a list like that.

However, with some tweaking, you could make that code work by using %r and passing in a tuple:

variable_1 = "HELLO"
variable_2 = "ADIOS"
varlist = [variable_1, variable_2]
print "INSERT INTO table VALUES %r;" % (tuple(varlist),)

Unfortunately, that style of variable insertion leaves your code vulnerable to SQL injection attacks.

Instead, we recommend using Python’s DB API and building a customized query string with multiple question marks for the data to be inserted: 

variable_1 = "HELLO"
variable_2 = "ADIOS"
varlist = [variable_1,variable_2]
var_string = ', '.join('?' * len(varlist))
query_string = 'INSERT INTO table VALUES (%s);' % var_string
cursor.execute(query_string, varlist)

The example at the beginning of the SQLite3 docs shows how to pass arguments using the question marks and it explains why they are necessary (essentially, it assures correct quoting of your variables).

More Related Contents:

  1. Not all parameters were used in the SQL statement (Python, MySQL)
  2. pip install mysqlclient returns “fatal error C1083: Cannot open file: ‘mysql.h’: No such file or directory
  3. How do I get a raw, compiled SQL query from a SQLAlchemy expression?
  4. Using a Python dict for a SQL INSERT statement
  5. Python Pandas write to sql with NaN values
  6. Dynamic SQL Queries with Python and mySQL
  7. Python read from txt and save into mysql
  8. How to use variables in SQL statement in Python?
  9. Lost connection to MySQL server during query
  10. Does Python support MySQL prepared statements?
  11. How to put parameterized sql query into variable and then execute in Python?
  12. How do I get the “id” after INSERT into MySQL database with Python?
  13. How to install MySQLdb (Python data access library to MySQL) on Mac OS X?
  14. Cannot return results from stored procedure using Python cursor
  15. MySQL — Joins Between Databases On Different Servers Using Python?
  16. How to retrieve SQL result column value using column name in Python?
  17. Pandas left outer join multiple dataframes on multiple columns
  18. Get data from pandas into a SQL server with PYODBC
  19. Complex foreign key constraint in SQLAlchemy
  20. Why are some mysql connections selecting old data the mysql database after a delete + insert?
  21. Is this Python code vulnerable to SQL injection? (SQLite3)
  22. pysqlite: Placeholder substitution for column or table names?
  23. How do i cast char to integer while querying in django ORM?
  24. flake8 complains on boolean comparison “==” in filter clause
  25. What is the best way to access stored procedures in Django’s ORM
  26. Python SQLite3 SQL Injection Vulnerable Code
  27. Python issue:Unable to find vcvarsall.bat [duplicate]
  28. How to check the existence of a row in SQLite with Python?
  29. Context manager for Python’s MySQLdb
  30. How to delete a record in Django models?

Leave a Comment