我正在尝试使用 python 脚本自动将 CSV 导入 mysql 数据库。我使用了 https://bitbucket.org/richardpenman/csv2mysql 中的脚本来完成这个任务。下面是代码:

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):
    """Find type for this string
    """
    # try integer type
    try:
        v = int(s)
    except ValueError:
        pass
    else:
        if abs(v) > 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) > 255:
        return 'text'
    else:
        return 'varchar(255)'


def most_common(l, default='varchar(255)'):
    """Return most common value from list
    """
    # 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):
    """Find the type for each CSV column
    """
    csv_types = collections.defaultdict(list)
    print (os.getcwd())
    # os.chdir("scripts/CSV")
    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):
    """Generate the SQL for inserting rows
    """
    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):
    """Format column names to remove illegal characters and duplicates
    """
    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] > 1:
            col = '{}{}'.format(col, counts[col])
        header.append(col)
    return header


def main(input_file, user, password, host, table, database, max_inserts=10000):
    print ("Importing `%s' into MySQL database `%s.%s'" % (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 ("Analyzing column types ...")
    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) < 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 ("commit")
        else:
            header = format_header(row)
            print ("Inserting rows ...")
            # SQL string for inserting data
            insert_sql = get_insert(table, header)

    # commit rows to database
    print ("Committing rows to database ...")
    db.commit()
    print ("Done!")



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("DJANGO_SETTINGS_MODULE", "WebApp.settings.local")

    from django.core.management import execute_from_command_line

    execute_from_command_line(sys.argv)

即使我能够读取我的 csv 文件并导入，它也会为一个特定的表抛出错误，即

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;

抛出的错误是:

_mysql_exceptions.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 'default) VALUES ('5', '0', '0', 'integer', '', '', '1', '', 'Base Parameters', '' at line 1")

下面是我尝试导入的 csv 的屏幕截图:

如您所见，它无法读取“基本参数”后的数字“1”并抛出错误。有人可以帮我解决问题吗？

最佳答案

_mysql_exceptions.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 'default) VALUES ('5', '0', '0', 'integer', '', '', '1', '', 'Base Parameters', '' at line 1")

您看到的是 INSERT 语句的片段。它没有向您显示整个 INSERT 语句，而是将其截断。您说您认为它没有读取输入数据的 ROWNUM 字段中的“1”，但您误解了错误消息。

您在错误消息中看到彼此相邻的两个单引号纯属巧合。错误消息的格式如下:

_mysql_exceptions.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 '...' at line 1")

其中 ... 将是长 SQL 语句的片段，从混淆解析器的第一个标记开始，在这种情况下继续 80 个字符。这个 80 个字符的片段是:

default) VALUES ('5', '0', '0', 'integer', '', '', '1', '', 'Base Parameters', '

纯属偶然，第80个字符是单引号，然后错误信息中的下一个字符也是单引号。它不是代替您希望从输入中读取的值 '1' 的空字符串。事实上，我假设它是从输入中读取数据值。

所以报错中的问题是你使用的是SQL reserved word DEFAULT 作为列名。此 Python 脚本未对其进行定界。因此 INSERT 语句中保留字的出现使解析器感到困惑。

我相信您可以在 Python 脚本中通过格式化 INSERT 语句中反引号内的列名称来解决此问题:

def get_insert(table, header):
    """Generate the SQL for inserting rows
    """
    field_names = ', '.join('`%s`' % col for col in header)
    field_markers = ', '.join('%s' for col in header)
    return 'INSERT INTO %s (%s) VALUES (%s);' % \
        (table, field_names, field_markers)

您也可以编辑输入的 CSV 文件，以避免在 header 中定义的列名中使用 SQL 保留字。

关于python - _mysql_exceptions 错误(1064，默认为 "check the manual that corresponds to your MySQL server version for the right syntax to use near ')VALUES，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/47421646/