Using a Python dict for a SQL INSERT statement

by

I think the comment on using this with MySQL is not quite complete. MySQLdb doesn’t do parameter substitution in the columns, just the values (IIUC) – so maybe more like 

placeholders=", ".join(['%s'] * len(myDict))
columns=", ".join(myDict.keys())
sql = "INSERT INTO %s ( %s ) VALUES ( %s )" % (table, columns, placeholders)
# valid in Python 2
cursor.execute(sql, myDict.values())
# valid in Python 3
cursor.execute(sql, list(myDict.values()))

You’re not getting escaping on the columns though, so you might want to check them first….

See http://mail.python.org/pipermail/tutor/2010-December/080701.html for a more complete solution

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. Insert list into my database using Python [duplicate]
  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. Lost connection to MySQL server during query
  9. How to execute raw SQL in Flask-SQLAlchemy app
  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. Escape string Python for MySQL
  14. How to install MySQLdb (Python data access library to MySQL) on Mac OS X?
  15. Cannot return results from stored procedure using Python cursor
  16. MySQL — Joins Between Databases On Different Servers Using Python?
  17. How to retrieve SQL result column value using column name in Python?
  18. Pandas left outer join multiple dataframes on multiple columns
  19. How do I escape % from python mysql query
  20. Bulk insert with SQLAlchemy ORM
  21. Parsing SQL with Python
  22. RuntimeError: working outside of application context
  23. Complex foreign key constraint in SQLAlchemy
  24. Using Django database layer outside of Django?
  25. Is this Python code vulnerable to SQL injection? (SQLite3)
  26. pysqlite: Placeholder substitution for column or table names?
  27. How do i cast char to integer while querying in django ORM?
  28. flake8 complains on boolean comparison “==” in filter clause
  29. What is the best way to access stored procedures in Django’s ORM
  30. Python SQLite3 SQL Injection Vulnerable Code

Leave a Comment