时间戳

首页 > 解决方案 > python MySQL插入大数据

python - python MySQL插入大数据

问题描述

使用 python，我循环通过 csv 文件读取数据，然后我在准备好的行上进行一些修改并调用保存函数将修改后的数据插入 MySQL。

def save(Id, modifiedData,):
    try:
       mydb = mysql.connector.connect(host="localhost",user="use",password="pass",database="data")
       sql = "INSERT INTO data (Id, modifiedData)  VALUES (%s, %s)"
       recordTuple = (Id, modifiedData)
       mycursor = mydb.cursor()
       mycursor.execute(sql,recordTuple)
       mydb.commit()
       print("Record inserted successfully into table")

    except mysql.connector.Error as error:
        print("Failed to insert into MySQL table {}".format(error))

def main():
            for row in csv:    
             #modify row 
             #creat Id
             save(Id, modifiedData,)

但我认为这不是每次迭代进行 MYSQL 连接和插入数据的好解决方案，这将耗费时间和资源，特别是当我在生产中移动到真实服务器时如何改进我的解决方案？

标签： pythonmysql

解决方案

您只需要创建一次连接，并且应该在 functionmain中，然后将连接传递给函数save，如下所示：

def save(mydb, Id, modifiedData):
    try:
       sql = "INSERT INTO data (Id, modifiedData)  VALUES (%s, %s)"
       recordTuple = (Id, modifiedData)
       mycursor = mydb.cursor()
       mycursor.execute(sql,recordTuple)
       mydb.commit()
       print("Record inserted successfully into table")
    except mysql.connector.Error as error:
        print("Failed to insert into MySQL table {}".format(error))

def main():
    try:
        mydb = mysql.connector.connect(host="localhost",user="use",password="pass",database="data")
    except mysql.connector.Error as error:
        print("Failed to create connection: {}".format(error))
        return            
    for row in csv:    
         #modify row 
         #creat Id
         save(mydb, Id, modifiedData)

为了获得更好的性能，您可以尝试executemany：

def save(mydb, modified_records):
    try:
       sql = "INSERT INTO data (Id, modifiedData)  VALUES (%s, %s)"
       mycursor = mydb.cursor()
       mycursor.executemany(sql, modified_records)
       mydb.commit()
       print("Records inserted successfully into table")
    except mysql.connector.Error as error:
        print("Failed to insert into MySQL table {}".format(error))

def main():
    try:
        mydb = mysql.connector.connect(host="localhost",user="use",password="pass",database="data")
    except mysql.connector.Error as error:
        print("Failed to create connection: {}".format(error))
        return
    modified_records = []
    for row in csv:    
        #modify row 
        #creat Id
        modified_records.append([id, modifiedData])
    save(mydb, modified_records)

推荐阅读