快捷導(dǎo)航

python批量插入數(shù)據(jù)到mysql的3種方法

更新時(shí)間：2023年10月26日 10:04:00 作者：Bobby?Wang

這篇文章主要給大家介紹了關(guān)于python批量插入數(shù)據(jù)到mysql的3種方法,在日常處理數(shù)據(jù)的過程中,我們都有批量寫入數(shù)據(jù)庫的需求,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下

自動化單條insert

# -*- coding:utf-8 -*-
import time
from pymysql import *

# 裝飾器，計(jì)算插入50000條數(shù)據(jù)需要的時(shí)間
def timer(func):
    def decor(*args):
        start_time = time.time()
        func(*args)
        end_time = time.time()
        d_time = end_time - start_time
        print("the running time is : ", d_time)

    return decor

@timer
def add_test_users():

    conn = connect(host='主機(jī)名', port='端口號', user='用戶名', password='密碼', database='數(shù)據(jù)庫名', charset='utf8')
    cs = conn.cursor()　?。！～@取游標(biāo)
    for num in range(0, 58000):
        try:
            sql = "insert into '表名'(字段名) values(值)"
            cs.execute(sql)

        except Exception as e:
            return
            
    conn.commit()　?。！√峤?
    cs.close()
    conn.close()
    print('OK')

add_test_users()

單條insert的話插入5w條數(shù)據(jù)大約用時(shí)5秒左右，相對來說效率不高

拼接sql語句，只執(zhí)行一次insert

# -*- coding:utf-8 -*-
import time
from pymysql import *

# 裝飾器，計(jì)算插入50000條數(shù)據(jù)需要的時(shí)間
def timer(func):
    def decor(*args):
        start_time = time.time()
        func(*args)
        end_time = time.time()
        d_time = end_time - start_time
        print("the running time is : ", d_time)

    return decor

@timer
def add_test_users():
    
    usersvalues = []
    for num in range(1, 50000):
        usersvalues.append(('需要插入的字段對應(yīng)的value'))  # 注意要用兩個(gè)括號擴(kuò)起來

    conn = connect(host='主機(jī)名', port='端口號', user='用戶名', password='密碼', database='數(shù)據(jù)庫名', charset='utf8')
    cs = conn.cursor()  # 獲取光標(biāo)
    # 注意這里使用的是executemany而不是execute，下邊有對executemany的詳細(xì)說明
    cs.executemany('insert into '表名'(字段名) values(%s,%s,%s,%s)', usersvalues)

    conn.commit()
    cs.close()
    conn.close()
    print('OK')

add_test_users()

使用這種批量插入方式插入5w條數(shù)據(jù)用時(shí)大約不到1秒

另外，針對executemany
execute(sql) : 接受一條語句從而執(zhí)行
executemany(templet,args)：能同時(shí)執(zhí)行多條語句，執(zhí)行同樣多的語句可比execute()快很多，強(qiáng)烈建議執(zhí)行多條語句時(shí)使用executemany
templet : sql模板字符串,　例如 ‘insert into table(id,name,age) values(%s,%s,%s)’
args: 模板字符串中的參數(shù)，是一個(gè)list，在list中的每一個(gè)元素必須是元組！??！　例如： [(1,‘mike’),(2,‘jordan’),(3,‘james’),(4,‘rose’)]

使用load data infile 加載到mysql

需要注意的是，這種方式本來是在mysql里邊執(zhí)行的命令，而這里是用代碼實(shí)現(xiàn)的命令

如果是在linux下，需要進(jìn)行下邊幾個(gè)步驟

在桌面寫個(gè)文本文件，里邊的內(nèi)容對應(yīng)下邊的數(shù)據(jù)表字段即可

cd /var/lib/mysql-files/
切換到這個(gè)目錄，將文本文件移動到這個(gè)目錄下，如果不在這個(gè)目錄下執(zhí)行的話加載的時(shí)候會出現(xiàn)錯(cuò)誤，當(dāng)然也可以選擇修改mysql配置，只不過這種方法相對比較簡單

對應(yīng)load data infile　可以在mysql里邊直接執(zhí)行文件即可
load data infile ‘/var/lib/mysql-files/data.txt’ into table ‘表名’(字段名)　　　　　這個(gè)命令

load data [low_priority] [local] infile ‘file_name txt’ [replace | ignore]
into table tbl_name
[fields
[terminated by’t’]
[OPTIONALLY] enclosed by ‘’]
[escaped by’’ ]]
[lines terminated by’n’]
[ignore number lines]
[(col_name, )]

這個(gè)是load data infile的完整命令，帶[ ]的是可選參數(shù) 下面解釋幾個(gè)比較常用的參數(shù)

[terminated by’t’] 這個(gè)意思就是文本字段里邊以什么分隔

如果文本文件是這樣:

小一，12，男
小二，13，女
小三，12，男

那么就是應(yīng)該terminated by　’ , ’ ，不設(shè)置默認(rèn)的就是制表符分隔，上邊定義的文本文件正好也就是制表符，因此沒有做設(shè)置，按照的就是默認(rèn)值

==enclosed by == 這個(gè)的意思是字段是以什么包裹著

如果文本文件是這樣:

“小一”，“12”，“男”
“小二”，“13”，“女”
“小三”，“12”，“男”

那么參數(shù)就應(yīng)該這樣寫:enclosed by “”

綜合以上三種方法比較，第三種方法效率最高，因此建議用第三種方法

一、大量信息插入

python向mysql插入大量數(shù)據(jù)時(shí)注意事項(xiàng)

通過python向mysql插入大量數(shù)據(jù)時(shí)：

1、for + cursor.execute(sql)，最后集中提交（commit()）

2、cursor.executemany(sql,list)

兩種方法效率上和功能上有一定差異。26萬條數(shù)據(jù)，使用第一種方法需要約1.5小時(shí)，使用第二種方法只需要10幾秒。

executemany的用法：

import pymysql
list_1 = [('a',1),('b',2),(None,3)]  # 元素是不是元組都可以
sql_1 = 'insert into 表明(字段1,字段2) values(%s,%s)' 
# 注意，這里的%s不需要加引號，使用execute需要加引號。
# 另外，executemany 在執(zhí)行過程中能夠?qū)ython的None轉(zhuǎn)為sql的null
db = pymysql.connect(host= ....)
cursor = db.cursor()
cursor.executemany(sql_1,list_1)
db.commit()
cursor.close()
db.close()
print('完成')

二、自動提交

在創(chuàng)建連接的時(shí)候，增加參數(shù) autocommit = 1 ，當(dāng)發(fā)生update等操作時(shí)，會實(shí)時(shí)更新到數(shù)據(jù)庫內(nèi)。否則要通過 conn.commit() 來提交到數(shù)據(jù)庫進(jìn)行具體操作。

如果沒有設(shè)置自動提交，也沒有手動提交，當(dāng)進(jìn)行插入或更新等操作時(shí)，只在本地客戶端能看到更新，在其他客戶端或數(shù)據(jù)庫內(nèi)，數(shù)據(jù)無變化。

# 實(shí)時(shí)操作，適合隨時(shí)少量、頻繁的更新

import pymysql
conn = pymysql.connect(
    host='XXX.XXX.XXX.XXX', 
    user='XXXXX', 
    password='XXXXX', 
    db='XXXXX', 
    port=3306, 
    charset='utf8',
    autocommit = 1 ,
    )
 
# 集中大批量操作數(shù)據(jù)
 
import pymysql
conn = pymysql.connect(
    host='XXX.XXX.XXX.XXX', 
    user='XXXXX', 
    password='XXXXX', 
    db='XXXXX', 
    port=3306, 
    charset='utf8',
    )
 
SQl相關(guān)操作略
 
conn.commit()  # 提交