如何在不使用参数化查询的情况下将数据从 CSV 插入 MySQL？

Question

我使用巨大的 CSV 数据文件，并计划在使用 Python 将数据逐行插入 MySQL 之前进行一些检查。由于数据文件非常大，打开文件需要花费大量时间。因此我的目标是加载它们而不手动分析它们。我将使用 Python 来为我进行分析。我已经开始编写代码，但在插入数据时陷入困境。我确信这是一个基本问题，但我无法弄清楚，因为我对 Python 有点陌生。演示数据：id,first_name,last_name,email,boole,coin1,Emilio,Pettie,[e-mail],true,1Lj8Z4Em68hwqRAUXZKW7C7h2KgH5cGpTe2,Raynard,Fairholme,[e-mail],true,1AEwLuECKYD1Bb6EGaBQC1TJS1mtvHBmy33,Zonda,Bampkin,[e-mail],false,14AHvnRjXExdgfqZBnWUyVi7aWZR8SFBoL4,Thurstan,Sherville,[e-mail],true,19iiiJ53zxmJnbmW7gKH2hoMwpiaqkit8E5,Jonathan,Jewkes,[e-mail],false,18E22TTK68ukQVLWK6oZNfFbzP2uHqaW7o6,Dolores,Carmichael,[e-mail],false,15BBePy5J3WY1QQLTjA79iYQMjDRubv2BD7,Kleon,Wesker,[e-mail],false,1NfYtAuq6M3cXGhDJuDBnCjdEBRSKsfRVJ8,Laureen,Writtle,[e-mail],true,14UgbrWz9wi2UptALs2dFeQRdUiMaLee579,Gypsy,Coombes,[e-mail],true,1Hn3JBtjytwbBMVJgM7ixAi1sXf56KFM3R10,Kevina,Boulger,[e-mail],false,1GABbcoRTVsX1qzD8uiGtsPtuD1kvzokK1代码 ：import stringimport csvimport mysql.connectormydb=mysql.connector.connect(host="localhost",user="root",password="password",autocommit=True)mycursor = mydb.cursor()sql_str=''sql_str1=''mycursor.execute("drop table if exists  rd.data")with open(r"C:\Users\rcsid\Documents\Office Programs\Working prog\MOCK_DATA.csv") as csvfile:    csv_reader = csv.DictReader(csvfile)    line_count = 0    for row in csv_reader:        if line_count == 0:            sql_str=f'create table rd.data ( {" varchar(50), ".join(row)} varchar(50))'            mycursor.execute(sql_str)        sql_str1=f'insert into rd.data values ( {", ".join(row)})'        print(sql_str1)        mycursor.execute(sql_str1)        line_count += 1并且插入的数据对于所有值都是空的。您能告诉我如何捕获 csv 中的数据吗？我知道这可能是基本语法。我也知道语法cur.execute('INSERT INTO table (columns) VALUES(%s, ....)', row) ，但不想使用它，因为我需要打开文件来检查标题部分。

Answer 1

我想不出您不应该使用参数化查询的充分理由。您已经知道 CSV 标题中的列名称（并且您可能总是知道这一点，否则您将如何创建表？），那么为什么不按照推荐的方式进行操作呢？当您这样做时，您已经打开了该文件with open...并使用DictReader. 尽管它不是面向公众的代码，但如果 CSV 中存在类似 SQL 注入的元素，您的数据库可能会崩溃。

DictReader作为字典读取该行。当您迭代字典时，您得到的是它的键而不是它的值。另外，请记住，您想要将它们插入到 varchar 列中，因此您需要将它们括在'

你需要做

col_vals = ", ".join([f"'{v}'" for v in row.values()])

sql_str1=f'insert into rd.data values ({colvals})'

我强烈建议您使用如下参数来执行此操作：

col_names = ",".join(row) # 'id,first_name,last_name,email,boole,coin'

params = ",".join("%s" for x in row) # '%s,%s,%s,%s,%s,%s'

query = f'insert into rd.data ({col_names}) values ({params})'

mycursor.execute(query, row.values())

Answer 2

因为您csv.DictReader可以轻松地从字典键中检索列。事实上，此方法会跳过第一行。此外，请考虑executemany仅对两个游标调用进行参数化：

with open(r"C:\Path\To\MOCK_DATA.csv") as csvfile:

    csv_reader = csv.DictReader(csvfile)

    data = [row for row in csv_reader]

    

    sql1 = f'CREATE TABLE rd.data ( {" VARCHAR(50), ".join(data[0].keys())} VARCHAR(50))'

    mycursor.execute(sql1)

    mydb.commit()

    sql2 = "INSERT INTO rd.data (`{cols}`) VALUES ({prms})"

    sql2 = sql2.format(cols="`, `".join(data[0].keys()), 

                       prms=", ".join(['%s'] * len(data[0])))  

    

    mycursor.executemany(sql2, [list(d.values()) for d in data])

    mydb.commit()

Online Demo （使用 SQLite 但应与 MySQL 保持一致）

Answer 3

我能够使用以下代码解决该问题：

mycursor.execute("drop table if exists  rd.data_with_header")

#r"C:\Users\rcsid\Documents\Office Programs\Working prog\MOCK_DATA.csv"

#re.sub('[^a-zA-Z0-9]\n\.', '_', row)

reader = csv.DictReader(open(r"C:\Users\rcsid\Documents\Office Programs\Working prog\MOCK_DATA.csv",encoding='utf-8',errors='ignore'), delimiter=',')

rowHeaders = reader.fieldnames

print(rowHeaders)

for i in rowHeaders:

    field_name.append(re.sub('[^A-Za-z0-9]+', '_', i))

print(field_name)

print(f'''create table rd.data_with_header ( {" varchar(100), ".join(field_name)} varchar(100))''')

sql_str=f'''create table rd.data_with_header ( {" varchar(100), ".join(field_name)} varchar(100))'''

mycursor.execute(sql_str)

for row in reader:

    sql_str1=f'''insert into rd.data values ('{"',' ".join(row.values())}')'''

    print(sql_str1)

    mycursor.execute(sql_str1)