title: _mysql_exceptions error(1064, "check the manual that corresponds to your MySQL server version for the right syntax to use near 'default) VALUES
body: <p>I am trying to import CSVs to mysql database automatically using python script. I used the script from <a href="https://bitbucket.org/richardpenman/csv2mysql" rel="nofollow noreferrer">https://bitbucket.org/richardpenman/csv2mysql</a> to achieve this task. Below is the code:</p>
  <pre><code>import os
  import re
  import sys
  import csv
  import time
  import argparse
  import collections
  import MySQLdb
  import warnings 
  # suppress annoying mysql warnings
  warnings.filterwarnings(action='ignore', category=MySQLdb.Warning) 
    
  def get_type(s):
      &quot;&quot;&quot;Find type for this string
      &quot;&quot;&quot;
      # try integer type
      try:
          v = int(s)
      except ValueError:
          pass
      else:
          if abs(v) &gt; 2147483647:
              return 'bigint'
          else:
              return 'int'
      # try float type
      try:
          float(s)
      except ValueError:
          pass
      else:
          return 'double'
  
      # check for timestamp
      dt_formats = (
          ('%Y-%m-%d %H:%M:%S', 'datetime'),
          ('%Y-%m-%d %H:%M:%S.%f', 'datetime'),
          ('%Y-%m-%d', 'date'),
          ('%H:%M:%S', 'time'),
      )
      for dt_format, dt_type in dt_formats:
          try:
              time.strptime(s, dt_format)
          except ValueError:
              pass
          else:
              return dt_type
     
      # doesn't match any other types so assume text
      if len(s) &gt; 255:
          return 'text'
      else:
          return 'varchar(255)'
  
  
  def most_common(l, default='varchar(255)'):
      &quot;&quot;&quot;Return most common value from list
      &quot;&quot;&quot;
      # some formats trump others
      if l:
          for dt_type in ('text', 'bigint'):
              if dt_type in l:
                  return dt_type
          return max(l, key=l.count)
      return default
  
  
  def get_col_types(input_file, max_rows=1000):
      &quot;&quot;&quot;Find the type for each CSV column
      &quot;&quot;&quot;
      csv_types = collections.defaultdict(list)
      print (os.getcwd())
      # os.chdir(&quot;scripts/CSV&quot;)
      reader = csv.reader(open(input_file))
      # test the first few rows for their data types
      for row_i, row in enumerate(reader):
          if row_i == 0:
              header = row
          else:
              for col_i, s in enumerate(row):
                  data_type = get_type(s)
                  csv_types[header[col_i]].append(data_type)
   
          if row_i == max_rows:
              break
  
      # take the most common data type for each row
      return [most_common(csv_types[col]) for col in header]
  
  
  def get_insert(table, header):
      &quot;&quot;&quot;Generate the SQL for inserting rows
      &quot;&quot;&quot;
      field_names = ', '.join(header)
      field_markers = ', '.join('%s' for col in header)
      return 'INSERT INTO %s (%s) VALUES (%s);' % \
          (table, field_names, field_markers)
  
  
  def format_header(row):
      &quot;&quot;&quot;Format column names to remove illegal characters and duplicates
      &quot;&quot;&quot;
      safe_col = lambda s: re.sub('\W+', '_', s.lower()).strip('_')
      header = []
      counts = collections.defaultdict(int)
      for col in row:
          col = safe_col(col)
          counts[col] += 1
          if counts[col] &gt; 1:
              col = '{}{}'.format(col, counts[col])
          header.append(col)
      return header
  
  
  def main(input_file, user, password, host, table, database, max_inserts=10000):
      print (&quot;Importing `%s' into MySQL database `%s.%s'&quot; % (input_file, database, table))
      db = MySQLdb.connect(host=host, user=user, passwd=password, charset='utf8')
      cursor = db.cursor()
      # create database and if doesn't exist
      cursor.execute('CREATE DATABASE IF NOT EXISTS %s;' % database)
      db.select_db(database)
  
      # define table
      print (&quot;Analyzing column types ...&quot;)
      col_types = get_col_types(input_file)
      print (col_types)
  
      header = None
      for i, row in enumerate(csv.reader(open(input_file))):
          if header:
              while len(row) &lt; len(header):
                  row.append('') # this row is missing columns so pad blank values
              cursor.execute(insert_sql, row)
              if i % max_inserts == 0:
                  db.commit()
                  print (&quot;commit&quot;)
          else:
              header = format_header(row)
              print (&quot;Inserting rows ...&quot;)
              # SQL string for inserting data
              insert_sql = get_insert(table, header)
  
      # commit rows to database
      print (&quot;Committing rows to database ...&quot;)
      db.commit()
      print (&quot;Done!&quot;)
  
  
  
  if __name__ == '__main__':
      parser = argparse.ArgumentParser(description='Automatically insert CSV contents into MySQL')
      parser.add_argument('--table', dest='table', help='Set the name of the table. If not set the CSV filename will be used')
      parser.add_argument('--database', dest='database', default=os.environ['MYSQL_DATABASE'], help='Set the name of the database. If not set the test database will be used')
      parser.add_argument('--user', dest='user', default=os.environ['MYSQL_USER'], help='The MySQL login username')
      parser.add_argument('--password', dest='password', default=os.environ['MYSQL_PASSWORD'], help='The MySQL login password')
      parser.add_argument('--host', dest='host', default=os.environ['MYSQL_CONTAINER_NAME'], help='The MySQL host')
      parser.add_argument('input_file', help='The input CSV file')
      args = parser.parse_args(sys.argv[1:])
      if not args.table:
          # use input file name for table
          args.table = os.path.splitext(os.path.basename(args.input_file))[0]
      
      main(args.input_file, args.user, args.password, args.host, args.table, args.database)
  
      os.environ.setdefault(&quot;DJANGO_SETTINGS_MODULE&quot;, &quot;WebApp.settings.local&quot;)
  
      from django.core.management import execute_from_command_line
  
      execute_from_command_line(sys.argv)
  </code></pre>
  <p>Even though I am able to read my csv file and import , it is throwing error for one particular table i.e</p>
  <pre><code>DROP TABLE IF EXISTS `param_system`;
  CREATE TABLE `param_system` (
  `ID` int(11) NOT NULL,
    `EXTERNAL_EDIT` int(11) DEFAULT '0',
    `INTERNAL_EDIT` int(11) DEFAULT '0',
    `FORTRAN_TYPE` varchar(50) DEFAULT NULL,
    `LABEL` varchar(255) DEFAULT NULL,
    `DESCRIPTION` varchar(255) DEFAULT NULL,
    `HELP_ID` int(11) DEFAULT '0',
    `HELP_TEXT` text DEFAULT NULL,
    `GROUPNAME` varchar(255) DEFAULT NULL,
    `ROWNUM` int(11) DEFAULT '0',
    `WIDGET` varchar(50) DEFAULT NULL,
    `OPTIONS` varchar(255) DEFAULT NULL,
    `DISABLED` int(11) DEFAULT '0',
    `READONLY` int(11) DEFAULT '0',
    `REQUIRED` int(11) DEFAULT '0',
    `UI` text DEFAULT NULL,
    `MIN_VALUE` varchar(50) DEFAULT NULL,
    `MAX_VALUE` varchar(50) DEFAULT NULL,
    `FORM_VAR_NAME` varchar(255) DEFAULT NULL,
    `PARAM` varchar(255) DEFAULT NULL,
    `VAL` varchar(255) DEFAULT NULL,
    `DEFAULT` varchar(255) DEFAULT NULL
  ) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8;
  </code></pre>
  <p>The error i.e being thrown is:</p>
  <blockquote>
  <p>_mysql_exceptions.ProgrammingError: (1064, &quot;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 'default) VALUES ('5', '0', '0', 'integer', '', '', '1', '', 'Base Parameters', '' at line 1&quot;)</p>
  </blockquote>
  <p>Below is the screen shot of the csv that I am trying to import:
  <a href="https://i.stack.imgur.com/Z1fk0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Z1fk0.png" alt="param_system.csv" /></a></p>
  <p>As you can see that it is not able to read the number &quot;1&quot; after &quot;Base Parameters&quot; and throwing the error. Could someone help me with what is going wrong?</p>