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)