Dynamic SQL Queries with Python and mySQL

Question

I have multiple tables that are updated after a value is changed in a grid. These tables don't always have the same keys or columns so I cannot explicitly name the columns or formats. The only thing that is ever the same, is the column where the keys reside. I know the way I am currently doing this is not correct and leaves me open to injection attacks.

I also ran into an issue where some of the values contain keys that throw an error in the SQL statement. For example, updating WHERE email = t'[email protected].

I am not really sure of the proper way to write these statements. I did some research and see multiple methods for different purposes but am not sure which is proper. I am looking to do this as dynamically as possible. Can anyone point me in the right direction?

To connect:

import mysql.connector as sql
import MySQLdb

#Connect
self.db_name = 'database'
        self.server = 'server'
        self.user_id = 'user'
        self.pw = 'password'

        try:
            self.db_con = MySQLdb.connect(user=self.user_id,password=self.pw,database=self.db_name,
                                    host=self.server,charset='utf8',autocommit=True)
            self.cursor = self.db_con.cursor()
        except:
            print("Error connecting")

SQL Statements:

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("'")

#SQL STATEMENTS
        sql_update = "UPDATE " + tbl + " SET " +  target_col + " = " + nVal + " WHERE " + key_col  +  " = " + key_id + ""
        

#INSERT
        sql_update = ("INSERT INTO " + str(self.tbl) + "(" + self.key_col + ")" + "VALUES (" + str("'") + str(val) + str("'") + ")")

#DELETE
        sql_update = "DELETE FROM " + tbl + " WHERE " + self.key_col + " = " + self.key_id + ""

#SELECT
        sql_query = "SELECT * FROM " + self.tbl 

#Excecute
        try:
            self.cursor.execute(sql_update)
            
            
        except:
            print('Error')
            self.db_con.rollback()

From where do you get the values for the query, do you have any front-end for that? — Vaibhav Kakade
– Vaibhav Kakade, Commented Jul 11, 2021 at 6:00
The values database and server are explicit in my code. User and password is determined when the user logs in. Tbl is determined by selections in a combobox. Target_col, key_col, key_id, and nVal are pulled from the wxgrid tabl — cmccall95
– cmccall95, Commented Jul 11, 2021 at 6:11

snakecharmerb · Accepted Answer · 2021-07-12 18:54:20Z

2

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)

edited Jul 12, 2021 at 18:54

answered Jul 11, 2021 at 7:27

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cmccall95 Over a year ago

This works great! However, it should be self.cursor.execute

cmccall95 Over a year ago

Could you explain this method a bit more or point me to some reading material? I ws able to successfully make Insert and Delete Statements. However, I cannot make the Update statement work like this, and I don't understand why. sql_update = (f"UPDATE {tbl} SET ({target_col}) = (%s) WHERE ({key_col}) = {key_id}"). self.cursor.execute(sql_update, (nVal,))

Collectives™ on Stack Overflow

Dynamic SQL Queries with Python and mySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related