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

Question

我处理巨大的CSV数据文件，并计划在使用Python将数据逐行插入MySQL之前进行一些检查。由于数据文件相当大，打开文件需要花费大量时间。因此，我的目标是在不手动分析的情况下加载它们。我将使用Python为我做分析。我已经开始编写代码，但在插入数据时遇到了问题。我确信这是一个基本的问题，我还没有弄清楚，因为我对Python有点陌生。 演示数据：

id,first_name,last_name,email,boole,coin
1,Emilio,Pettie,epettie0@craigslist.org,true,1Lj8Z4Em68hwqRAUXZKW7C7h2KgH5cGpTe
2,Raynard,Fairholme,rfairholme1@wisc.edu,true,1AEwLuECKYD1Bb6EGaBQC1TJS1mtvHBmy3
3,Zonda,Bampkin,zbampkin2@google.nl,false,14AHvnRjXExdgfqZBnWUyVi7aWZR8SFBoL
4,Thurstan,Sherville,tsherville3@umich.edu,true,19iiiJ53zxmJnbmW7gKH2hoMwpiaqkit8E
5,Jonathan,Jewkes,jjewkes4@nba.com,false,18E22TTK68ukQVLWK6oZNfFbzP2uHqaW7o
6,Dolores,Carmichael,dcarmichael5@blogtalkradio.com,false,15BBePy5J3WY1QQLTjA79iYQMjDRubv2BD
7,Kleon,Wesker,kwesker6@buzzfeed.com,false,1NfYtAuq6M3cXGhDJuDBnCjdEBRSKsfRVJ
8,Laureen,Writtle,lwrittle7@tripadvisor.com,true,14UgbrWz9wi2UptALs2dFeQRdUiMaLee57
9,Gypsy,Coombes,gcoombes8@home.pl,true,1Hn3JBtjytwbBMVJgM7ixAi1sXf56KFM3R
10,Kevina,Boulger,kboulger9@sakura.ne.jp,false,1GABbcoRTVsX1qzD8uiGtsPtuD1kvzokK1

守则：

import string
import csv
import mysql.connector
mydb=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

我能够创建表格和标题部分。但是我无法加载数据。print(sql_str1)输出为：

insert into rd.data values ( id, first_name, last_name, email, boole, coin)
insert into rd.data values ( id, first_name, last_name, email, boole, coin)
insert into rd.data values ( id, first_name, last_name, email, boole, coin)
insert into rd.data values ( id, first_name, last_name, email, boole, coin)

对于所有值，插入的数据都为空。请告诉我如何在csv中捕获数据。我知道这可能是一个基本语法。我也知道语法cur.execute('INSERT INTO table (columns) VALUES(%s, ....)', row)，但不想使用它，因为我需要打开文件来检查头部分

Answer 1

因为您使用^{}，所以可以轻松地从字典键检索列。实际上，此方法跳过第一行。另外，考虑只有两个游标调用的参数化的{a2}：

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 2

我能够通过以下代码解决此问题：

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)

Answer 3

我想不出一个好的理由，你应该而不是使用参数化查询。您已经知道CSV标题中的列名（并且您可能总是知道这一点，因为否则您将如何创建表？），那么为什么不按建议的方式创建呢？您已经在执行with open...操作时打开了该文件，并使用DictReader命令逐行读取它。虽然它不是面向公众的代码，但如果CSV中有类似SQL注入的元素，则数据库可能会中断

^{}作为字典在行中读取。当你迭代字典时，你得到的是它的键而不是它的值。另外，请记住，您希望将它们插入到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())