Dynamic SQL Queries with Python and mySQL

by

Databases have different notations for “quoting” identifiers (table and column names etc) and values (data).

MySQL uses backticks to quote identifiers. For values, it’s best to use the parameter substitution mechanism provided by the connector package: it’s more likely to handle tricky cases like embedded quotes correctly, and will reduce the risk of SQL injection.

Here’s an example for inserts; the same techniques can be used for the other types of query.

key_id =  str("'") + self.GetCellValue(event.GetRow(),1) +  str("'")    
target_col = self.GetColLabelValue(event.GetCol())
key_col = self.GetColLabelValue(1)                                     
nVal = str("'") + self.GetCellValue(event.GetRow(),event.GetCol()) +  str("'")
        

#INSERT (using f-strings for brevity)
sql_update = (f"INSERT INTO `{self.tbl}` (`{self.key_col}`) VALUES (%s)")

# Pass the statement and values to cursor.execute.  
# The values are assumed to be a sequence, so a single value should be 
# placed in a tuple or list.
self.cursor.execute(sql_update, (nVal,))

If you have more than one column / value pair you could do something like this:

cols = ['A', 'B', 'C']
vals = ['a', 'b', 'c']

col_names=",".join([f'`{c}`' for c in cols])
values_placeholder=",".join(['%s'] * len(cols))

sql_update = (f"INSERT INTO `{self.tbl}` (col_names) VALUES ({values_placeholder})")
self.cursor.execute(sql_update, vals)

Values are not only data for insertion, but also data that we are using for comparison, for example in WHERE clauses. So an update statement with a filter might be created like this:

sql_update = (f"UPDATE `{tbl}` SET (`{target_col}`) = (%s) WHERE (`{key_col}`) = %s")
self.cursor.execute(sql_update, (nVal, key_id))

However sometimes the target of a SET or WHERE clause may be a column, for example we want to do an update based on other values in the row. For example, this statement will set target_col to the value of other_col for all rows where key_col is equal to other_key_col:

sql_update = (f"UPDATE `{tbl}` SET (`{target_col}`) = `{other_col}` WHERE (`{key_col}`) = `{other_key_col}`")
self.cursor.execute(sql_update)

More Related Contents:

  1. Installing specific package version with pip
  2. Database does not update automatically with MySQL and Python
  3. Not all parameters were used in the SQL statement (Python, MySQL)
  4. mysql-python install error: Cannot open include file ‘config-win.h’
  5. How to insert pandas dataframe via mysqldb into database?
  6. pip install mysqlclient returns “fatal error C1083: Cannot open file: ‘mysql.h’: No such file or directory
  7. How do I get a raw, compiled SQL query from a SQLAlchemy expression?
  8. Install mysql-python (Windows)
  9. When to close cursors using MySQLdb
  10. Using a Python dict for a SQL INSERT statement
  11. Python MYSQL update statement
  12. Python 3.4.0 with MySQL database
  13. How can I use executemany to insert into MySQL a list of dictionaries in Python
  14. MySQL: Get column name or alias from query
  15. Insert list into my database using Python [duplicate]
  16. Python Pandas write to sql with NaN values
  17. ImportError: No module named MySQLdb
  18. Print the actual query MySQLdb runs?
  19. Can’t install mysql-python (newer versions) in Windows
  20. What’s the difference between MySQLdb, mysqlclient and MySQL connector/Python?
  21. How to use python mysqldb to insert many rows at once
  22. ‘pip install MySQL-python’ fails with ‘IndexError’
  23. What is PyMySQL and how does it differ from MySQLdb? Can it affect Django deployment?
  24. How to disable query cache with mysql.connector
  25. How do I connect to a MySQL Database in Python?
  26. Python mysqldb: Library not loaded: libmysqlclient.18.dylib
  27. reading external sql script in python
  28. How to efficiently use MySQLDB SScursor?
  29. How to run Django’s test database only in memory?
  30. What is an efficient way of inserting thousands of records into an SQLite table using Django?

Leave a Comment