title: Executing several SQL queries with MySQLdb
body: <p>How would you go about executing several SQL statements (script mode) with python?</p>
  
  <p>Trying to do something like this:</p>
  
  <pre><code>import MySQLdb
  mysql = MySQLdb.connect(host='host...rds.amazonaws.com', db='dbName', user='userName', passwd='password')
  sql = """
  insert into rollout.version (`key`, `value`) VALUES ('maxim0', 'was here0');
  insert into rollout.version (`key`, `value`) VALUES ('maxim1', 'was here1');
  insert into rollout.version (`key`, `value`) VALUES ('maxim2', 'was here1');
  """
  mysql.query(sql)
  </code></pre>
  
  <p>Fails with:</p>
  
  <blockquote>
    <p>ProgrammingError: (2014, "Commands out
    of sync; you can't run this command
    now")</p>
  </blockquote>
  
  <p>I'm writing an deployment engine that would accept SQL delta changes from several people and apply them to the DB on version deployment.</p>
  
  <p>I've looked into this code <a href="http://sujitpal.blogspot.com/2009/02/python-sql-runner.html" rel="nofollow">http://sujitpal.blogspot.com/2009/02/python-sql-runner.html</a> and implemented __sanitize_sql:</p>
  
  <pre><code>def __sanitize_sql(sql):
      # Initial implementation from http://sujitpal.blogspot.com/2009/02/python-sql-runner.html
      sql_statements = []
  
      incomment = False
      in_sqlcollect = False
  
      sql_statement = None
      for sline in sql.splitlines():
          # Remove white space from both sides.
          sline = sline.strip()
  
          if sline.startswith("--") or len(sline) == 0:
              # SQL Comment line, skip
              continue
  
          if sline.startswith("/*"):
              # start of SQL comment block
              incomment = True
          if incomment and sline.endswith("*/"):
              # end of SQL comment block
              incomment = False
              continue
  
          # Collect line which is part of 
          if not incomment:
              if sql_statement is None:
                  sql_statement = sline
              else:
                  sql_statement += sline
  
              if not sline.endswith(";"):
                  in_sqlcollect = True
  
              if not in_sqlcollect:
                  sql_statements.append(sql_statement)
                  sql_statement = None
                  in_sqlcollect = False
  
      if not incomment and not sql_statement is None and len(sql_statement) != 0:
          sql_statements.append(sql_statement)
  
      return sql_statements
  
  if __name__ == "__main__":
      sql = sql = """update tbl1;
  /* This
  is my
  beautiful 
  comment*/
  /*this is comment #2*/
  some code...;
  -- comment
  sql code
  """
      print __sanitize_sql(sql)
  </code></pre>
  
  <p>Don't know if it's the best solution but seems to work for not too complex to parse SQL statements.</p>
  
  <p>The question now how to run this code, I can do something like <a href="http://eric.lubow.org/2009/python/pythons-mysqldb-2014-error-commands-out-of-sync/" rel="nofollow">this dude</a> but it seems ugly, I'm not a python expert (we've been doing python here for just the past 2 weeks) but it seems that abusing cursor this way is hackish and not a good practice.</p>
  
  <p>Ideas / blog posts would be helpful.</p>
  
  <p>Thanks you,<br>
  Maxim.</p>