title: Python/MySQLdb import from CSV with data encapsulation
body: <p>I have been using WAMP to ingest some csv logs, and wanted to move to a more automated process by scripting some of the routine actions I need to take. </p>
  
  <p>I was using the direct CSV import function in PHPmyadmin to handle the dialect and specifics of the CSV. </p>
  
  <p>I have written an uploader in Python, using MySQLdb that parses the log file, however as the logs contain some unhelpful chars, I am finding that I need to do lots of running around sanitizing inputs where I probably don't want to be... </p>
  
  <p>Example, the log is some data from a directory scanner, and I have no control over the folder naming conventions folks use. I have this folder:-</p>
  
  <pre><code>"C:\user\NZ Business Roundtable_Download_13Feb2013, 400 Access"
  </code></pre>
  
  <p>and the <code>,</code>char is being read as a new field marker (it is csv after all). What I actually want it to do is to ignore all text inside the quote marks:- <code>"......"</code></p>
  
  <p>I see a similar issue with <code>'</code> chars, and I'm sure there will be more. </p>
  
  <p>I found this:- <a href="http://www.tech-recipes.com/rx/2345/import_csv_file_directly_into_mysql/" rel="nofollow">http://www.tech-recipes.com/rx/2345/import_csv_file_directly_into_mysql/</a> that shows how I could script the Python to function like the PHPmyadmin load routine. Mainly using this snippet: </p>
  
  <pre><code>load data local infile 'uniq.csv' into table tblUniq fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
  (uniqName, uniqCity, uniqComments)
  </code></pre>
  
  <p>However there is some in depth processing and changes to the table that I would like to protect that I have already scripted, so wondered if there was a way to "tell" MySQL that I want to use <code>""</code> as text encapsulation. The main processing I want to protect is that I give it a specific table name when creating the new table, and use that throughout the rest of the processing. </p>
  
  <p>Example of my table maker script:-</p>
  
  <pre><code>def make_table(self):
      query ="DROP TABLE IF EXISTS `atl`.`{}`".format(self.table)
      self.cur.execute(query)
      query = "CREATE TABLE IF NOT EXISTS `atl`.`{}` (`PK` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `ID` varchar(10), `PARENT_ID` varchar(10), `URI` varchar(284), \
          `FILE_PATH` varchar(230), `NAME` varchar(125), `METHOD` varchar(9), `STATUS` varchar(4), `SIZE` varchar(9), \
          `TYPE` varchar(9), `EXT` varchar(11), `LAST_MODIFIED` varchar(19), `EXTENSION_MISMATCH` varchar(20), `MD5_HASH` varchar(32), \
          `FORMAT_COUNT` varchar(2), `PUID` varchar(9), `MIME_TYPE` varchar(71), `FORMAT_NAME` varchar(59), `FORMAT_VERSION` varchar(7), \
          `delete_flag` tinyint, `delete_reason` VARCHAR(80), `move_flag` TINYINT, `move_reason` VARCHAR(80), \
          `ext_change_flag` TINYINT, `ext_change_reason` VARCHAR(80), `ext_change_value` VARCHAR(4), `fname_change_flag` TINYINT, `fname_change_reason` VARCHAR(80),\
          `fname_change_value` VARCHAR(80))".format(self.table)
      self.cur.execute(query)
      self.mydb.commit()
  </code></pre>
  
  <p>Example of my ingest script:-</p>
  
  <pre><code> def ingest_row(self, row):
      query = "insert"
      # Prepare SQL query to INSERT a record into the database.
      query = "INSERT INTO `atl`.`{0}` (`ID`, `PARENT_ID`, `URI`, `FILE_PATH`, `NAME`, `METHOD`, `STATUS`, `SIZE`, `TYPE`, `EXT`, \
          `EXTENSION_MISMATCH`, `LAST_MODIFIED`, `MD5_HASH`, `FORMAT_COUNT`, `PUID`, `MIME_TYPE`, `FORMAT_NAME`,  `FORMAT_VERSION`) \
          VALUES ('{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}')".format(self.table, row[0], row[1], row[2], row[3], row[4], \
           row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17])
      try:
          self.cur.execute(query)
          self.mydb.commit()
      except:
          print query
          quit()
  </code></pre>
  
  <p>Example of log:-</p>
  
  <pre><code>"ID","PARENT_ID","URI","FILE_PATH","NAME","METHOD","STATUS","SIZE","TYPE","EXT","LAST_MODIFIED","EXTENSION_MISMATCH","MD5_HASH","FORMAT_COUNT","PUID","MIME_TYPE","FORMAT_NAME","FORMAT_VERSION"
  "1","","file:/C:/jay/NZ%20Business%20Roundtable_Download_13Feb2013,%20400%20Access/","C:\jay\NZ Business Roundtable_Download_13Feb2013, 400 Access","NZ Business Roundtable_Download_13Feb2013, 400 Access",,"Done","","Folder",,"2013-06-28T11:31:36","false",,"",,"","",""
  "2","1","file:/C:/jay/NZ%20Business%20Roundtable_Download_13Feb2013,%20400%20Access/1993/","C:\jay\NZ Business Roundtable_Download_13Feb2013, 400 Access\1993","1993",,"Done","","Folder",,"2013-06-28T11:31:36","false",,"",,"","",""
  </code></pre>