title: Dynamic SQL Queries with Python and mySQL
body: <p>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.</p>
  <p>I also ran into an issue where some of the values contain keys that throw an error in the SQL statement. For example, updating <code>WHERE email = t'est@email.com</code>.</p>
  <p>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?</p>
  <p>To connect:</p>
  <pre><code>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(&quot;Error connecting&quot;)    
  </code></pre>
  <p>SQL Statements:</p>
  <pre><code>key_id =  str(&quot;'&quot;) + self.GetCellValue(event.GetRow(),1) +  str(&quot;'&quot;)    
          target_col = self.GetColLabelValue(event.GetCol())
          key_col = self.GetColLabelValue(1)                                     
          nVal = str(&quot;'&quot;) + self.GetCellValue(event.GetRow(),event.GetCol()) +  str(&quot;'&quot;)
  
  #SQL STATEMENTS
          sql_update = &quot;UPDATE &quot; + tbl + &quot; SET &quot; +  target_col + &quot; = &quot; + nVal + &quot; WHERE &quot; + key_col  +  &quot; = &quot; + key_id + &quot;&quot;
          
  
  #INSERT
          sql_update = (&quot;INSERT INTO &quot; + str(self.tbl) + &quot;(&quot; + self.key_col + &quot;)&quot; + &quot;VALUES (&quot; + str(&quot;'&quot;) + str(val) + str(&quot;'&quot;) + &quot;)&quot;)
  
  #DELETE
          sql_update = &quot;DELETE FROM &quot; + tbl + &quot; WHERE &quot; + self.key_col + &quot; = &quot; + self.key_id + &quot;&quot;
  
  #SELECT
          sql_query = &quot;SELECT * FROM &quot; + self.tbl 
  
  #Excecute
          try:
              self.cursor.execute(sql_update)
              
              
          except:
              print('Error')
              self.db_con.rollback()
  </code></pre>