MYSQL LOAD DATA INFILE 语句适用于工作台，但不适用于 python

我正在使用房利美抵押贷款数据试验 MySQL 和数据分析。为此，我创建了两个表（“perf”和“acq”）和几个 python 函数。我首先删除集合和表（如果存在），然后创建集合（mortgage_analysis）和两个表。然后我建立一个文件列表，这些文件对应于我想要执行的分析年数。所有这些都很好。

然后，我使用以下函数将表格中的数据加载到来自房利美的 perf 和 acq 文本文件中。相同的函数用于加载两个表。它每次都与“perf”表一起工作，而从不与“acq”表一起工作。如果我获取 SQL 语句并在 mySQL 工作台中执行它们，则这些语句每次都有效。我很难过，可以使用一些帮助。

在工作台中有效但在 Python 中无效的 SQL 语句是：

LOAD DATA  INFILE '/Users/<my user name>/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);

加载这个的python函数是：

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["base_path"]+env["path_separator"]+template, "r")
    _template = _file.readlines()
    try:
        conn = mysql.connector.connect(host=env["mySQL"]["host"],user=env["mySQL"]["user"], passwd=env['pw'])
        if conn.is_connected():
            print('Connected to MySQL database')
    except Error as e:
            print(e)
    cursor = conn.cursor()
    cursor.execute("USE mortgage_analysis;")
    cursor.execute("SET SESSION sql_mode = '';")
    print("starting table load")
    t0 = time.time()
    res = []
    for _file in file_list:
        _sql = _template[0].format(_file)
        print(f"\n{_sql}\n")
        try:
            res = cursor.execute(_sql)
            warn = cursor.fetchwarnings()
            #print(f"warn: {warn}")
        except Error as e:
            print(f"{_sql} \n{e}")

    t1 = time.time()
    print(f"Years: {env['years']} Table load time: {t1-t0}") 
    conn.close
    return env

没有发现错误（try 总是有效），也没有产生警告（fetchwarnings 总是空的）。

用于创建这两个表的 SQL 语句是：

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));