title: In Python how do I escape a single quote within a string that will be used as a SQL statement?
body: <p>I built a simple statement to run a <code>load data local infile</code> on a MySQL table. I'm using Python to generate the string and also run the query. So I'm using the Python package <code>pymysql</code>.</p>
  
  <p>This is the line to build the string. Assume <code>metadata_filename</code> is a proper string:</p>
  
  <blockquote>
    <p>load_data_statement = """load data local infile """ + metadata_filename + """INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '\'';"""</p>
  </blockquote>
  
  <p>I originally had string substitution, and wanted to see if that was the issue, but it isn't. If I edit the statement above and commend out the <code>ENCLOSED BY</code> part, it is able to run, but not properly load data since I need the enclosed character</p>
  
  <p>If I <code>print(load_data_statement)</code>, I get what appears to be proper SQL code, but it doesn't seem to be read by the SQL connector. This is what's printed: </p>
  
  <blockquote>
    <p>load data local infile 'filename.txt' INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY ''';</p>
  </blockquote>
  
  <p>That all appears to be correct, but the Mysql engine is not taking it. What should I edit in Python to escape the single quote or just write it properly?</p>
  
  <p>Edit:</p>
  
  <p>I've been running the string substitution alternative, but still getting issues: <code>load_data_statement = """load data local infile 'tgt_metadata_%s.txt' INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '\'';""" % metadata_filename</code></p>
  
  <p>Also tried raw strings:<code>load_data_statement = r"""load data local infile 'tgt_metadata_%s.txt' INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '\'';""" % metadata_filename</code></p>
  
  <p>Also tried raw strings:<code>load_data_statement = r"""load data local infile 'tgt_metadata_%s.txt' INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '\'';""" % metadata_filename</code></p>
  
  <p>And tried adding extra escapes: Also tried raw strings:<code>load_data_statement = r"""load data local infile 'tgt_metadata_%s.txt' INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '\\'';""" % metadata_filename</code></p>
  
  <p>Also tried raw strings:<code>load_data_statement = r"""load data local infile \'tgt_metadata_%s.txt\' INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '\'';""" % metadata_filename</code></p>
  
  <p>The execute line is simple `cur.execute(load_data_statement)</p>
  
  <p>And the error I'm getting is odd: `pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tgt_metadta_mal.txt'.txt' INTO table tgt_metadata FIELDS TERMINATED BY ','; ENC' at line 1")</p>
  
  <p>I don't understand why the message starts at 'tgt_metadata_mal.txt and shows only the first 3 letters of ENCLOSED BY...</p>