title: MYSQL LOAD DATA INFILE statement works in workbench, but not python
body: <p>I am experimenting with MySQL and data analytics using the Fannie Mae mortgage data. To do this, I've created two tables, (&quot;perf&quot; and &quot;acq&quot;) and several python functions. I start by dropping the collection and tables if they exist, then create the collection (mortgage_analysis) and the two tables. Then I build a list of files that correspond to the number of years of analysis I want to perform. All of that works just fine.</p>
  <p>I then use the following function to load the tables with data from the perf and acq text files from Fannie Mae. The same function is used to load both tables. It works every time with the &quot;perf&quot; table and NEVER works with the &quot;acq&quot; table. If I take the SQL statements and execute them in the mySQL workbench, the statements work every time. I'm stumped and could use some help.</p>
  <p>The SQL statement that works in the workbench but not in Python is:</p>
  <pre class="lang-sql prettyprint-override"><code>LOAD DATA  INFILE '/Users/&lt;my user name&gt;/github/mortgage-analysis-example/data/text/acq/Acquisition_2000Q1.txt' 
  INTO TABLE acq 
  FIELDS TERMINATED BY '|' 
  LINES TERMINATED BY '\n' 
  (loan_id, orig_channel, seller_name, orig_interest_rate, orig_upb, orig_loan_term, 
   orig_date, first_pay_date, orig_ltv, orig_cltv, num_borrowers, dti, 
   borrower_credit_score, first_home_buyer, loan_purpose, property_type, num_units, 
   occupancy_status, property_state, zip, mortgage_insurance_percent, product_type, 
   coborrow_credit_score, mortgage_insurance_type, relocation_mortgage_ind);
  </code></pre>
  <p>The python function to load this is:</p>
  <pre class="lang-py prettyprint-override"><code>def loadTable(env_in, template, file_list):
      # env_in: (i know, uck global variable, holds info for this notebook common to all functions
      # template: SQL template file
      # file_list: python list element with fully qualified file names to use with SQL statement 
      env = env_in # environment info
      from mysql.connector import Error
      _file = open(env[&quot;base_path&quot;]+env[&quot;path_separator&quot;]+template, &quot;r&quot;)
      _template = _file.readlines()
      try:
          conn = mysql.connector.connect(host=env[&quot;mySQL&quot;][&quot;host&quot;],user=env[&quot;mySQL&quot;][&quot;user&quot;], passwd=env['pw'])
          if conn.is_connected():
              print('Connected to MySQL database')
      except Error as e:
              print(e)
      cursor = conn.cursor()
      cursor.execute(&quot;USE mortgage_analysis;&quot;)
      cursor.execute(&quot;SET SESSION sql_mode = '';&quot;)
      print(&quot;starting table load&quot;)
      t0 = time.time()
      res = []
      for _file in file_list:
          _sql = _template[0].format(_file)
          print(f&quot;\n{_sql}\n&quot;)
          try:
              res = cursor.execute(_sql)
              warn = cursor.fetchwarnings()
              #print(f&quot;warn: {warn}&quot;)
          except Error as e:
              print(f&quot;{_sql} \n{e}&quot;)
  
      t1 = time.time()
      print(f&quot;Years: {env['years']} Table load time: {t1-t0}&quot;) 
      conn.close
      return env
  </code></pre>
  <p>No errors are identified (try always works) and no warnings are generated (fetchwarnings is always empty).</p>
  <p>The SQL statements used to create the two tables are:</p>
  <pre><code>DROP TABLE IF EXISTS acq;
  CREATE TABLE acq (id DOUBLE AUTO_INCREMENT, loan_id DOUBLE, orig_channel VARCHAR(255), seller_name VARCHAR(255), orig_interest_rate DOUBLE, orig_upb DOUBLE, orig_loan_term DOUBLE, orig_date VARCHAR(255), first_pay_date VARCHAR(255), orig_ltv DOUBLE, orig_cltv DOUBLE, num_borrowers DOUBLE, dti DOUBLE, borrower_credit_score DOUBLE, first_home_buyer VARCHAR(255), loan_purpose VARCHAR(255), property_type VARCHAR(255), num_units DOUBLE, occupancy_status VARCHAR(255), property_state VARCHAR(255), zip DOUBLE, mortgage_insurance_percent DOUBLE, product_type VARCHAR(255), coborrow_credit_score DOUBLE, mortgage_insurance_type DOUBLE, relocation_mortgage_ind VARCHAR(255), PRIMARY KEY (id));
  DROP TABLE IF EXISTS perf;
  CREATE TABLE perf (id DOUBLE AUTO_INCREMENT, loan_id DOUBLE, monthly_reporting_period VARCHAR(255), servicer VARCHAR(255), interest_rate DECIMAL(6,3), current_actual_upb DECIMAL(12,2), loan_age DOUBLE, remaining_months_to_legal_maturity DOUBLE, adj_remaining_months_to_maturity DOUBLE, maturity_date VARCHAR(255), msa DOUBLE, current_loan_delinquency_status DOUBLE, mod_flag VARCHAR(255), zero_balance_code VARCHAR(255), zero_balance_effective_date VARCHAR(255), last_paid_installment_date VARCHAR(255), foreclosed_after VARCHAR(255), disposition_date VARCHAR(255), foreclosure_costs DOUBLE, prop_preservation_and_reair_costs DOUBLE, asset_recovery_costs DOUBLE, misc_holding_expenses DOUBLE, holding_taxes DOUBLE, net_sale_proceeds DOUBLE, credit_enhancement_proceeds DOUBLE, repurchase_make_whole_proceeds DOUBLE, other_foreclosure_proceeds DOUBLE, non_interest_bearing_upb DOUBLE, principal_forgiveness_upb VARCHAR(255), repurchase_make_whole_proceeds_flag VARCHAR(255), foreclosure_principal_write_off_amount VARCHAR(255), servicing_activity_indicator VARCHAR(255), PRIMARY KEY (id));
  </code></pre>