免费视频一区三区,亚洲精品高清在线,精品一区二区三区免费播放

主頁(yè) > 知識(shí)庫(kù) > python數(shù)據(jù)庫(kù)批量插入數(shù)據(jù)的實(shí)現(xiàn)(executemany的使用)

python數(shù)據(jù)庫(kù)批量插入數(shù)據(jù)的實(shí)現(xiàn)(executemany的使用)

正常情況下往數(shù)據(jù)庫(kù)多張表中批量插入1000條數(shù)據(jù)，若一條一條insert插入，則調(diào)用sql語(yǔ)句查詢插入需要執(zhí)行幾千次，花費(fèi)時(shí)間長(zhǎng)

現(xiàn)使用cursor.executemany（sql，args），可對(duì)數(shù)據(jù)進(jìn)行批量插入，
其中args是一個(gè)包含多個(gè)元組的list列表，每個(gè)元組對(duì)應(yīng)mysql當(dāng)中的一條數(shù)據(jù)

以下是實(shí)例：
往數(shù)據(jù)庫(kù)中的order表、order_detail表和pay表中插入1000條訂單數(shù)據(jù)，訂單詳情數(shù)據(jù)以及支付數(shù)據(jù)
1.pay表中的id字段是order表中的pay_id字段
2.order表中的id字段是order_detail表中的order_id字段

1.初始化屬性(包括host、port、user、password和database）

def __init__(self):
        self.__db_host=XXX
        self.__db_port=XXX
        self.__db_user=XXX
        self.__db_password=XXX
        self.__db_database=XXX

2.連接數(shù)據(jù)庫(kù)

def isConnection(self):
        self.__db=pymysql.connect(
            host=self.__db_host,
            port=self.__db_port,
            user=self.__db_user,
            password=self.__db_password,
            database=self.__db_database,
            charset='utf8'
        )

3.批量往pay表中插入1000條數(shù)據(jù)

# 插入數(shù)據(jù)進(jìn)pay表
    def pay_insert(self,pay_value):
        try:
            # 連接數(shù)據(jù)庫(kù)
            self.isConnection()
            # 創(chuàng)建游標(biāo)
            global cursor
            cursor=self.__db.cursor()
            # 執(zhí)行
            cursor.executemany('insert into `pay表`(type,pay_money,pay_time,pay_no,STATUS,create_by,create_time,update_by,update_time) value (%s,%s,%s,%s,%s,%s,%s,%s,%s)',pay_value)
        except Exception as e:
            print e
        finally:
            cursor.close()
            self.__db.commit()
            self.__db.close()

    # 生成pay表所需字段，并調(diào)用sql
    def pay_data(self):
        pay_value=list()
        for i in range(1,1000):
            pay_value.append((0,8800,time.localtime(),str(random.randint(712300000000,712399999999)),3,49338,time.localtime(),49338,time.localtime()))
        now_time=time.localtime()
        self.pay_insert(pay_value)
        return now_time

4.pay表中生成的1000條數(shù)據(jù)，依次取出id

 # 獲取pay_id
    def get_pay_id(self,now_time):
        try:
            self.isConnection()
            global cursor
            cursor=self.__db.cursor()
            cursor.execute('select id from `pay表` where create_time >= %s',now_time)
            id_value=list()
            for i in range(1,1000):
                pay_id=cursor.fetchone()
                id_value.append(pay_id)
            return id_value
        except Exception as e:
            print e
        finally:
            cursor.close()
            self.__db.commit()
            self.__db.close()

以下是完整代碼：

# #!/usr/bin/python
# # -*- coding: UTF-8 -*-


import pymysql          # 先pip install pymysql
import random
import time

class DatabaseAcess:
    # 初始化屬性(包括host、port、user、password和database）
    def __init__(self):
        self.__db_host=XXX
        self.__db_port=XXX
        self.__db_user=XXX
        self.__db_password=XXX
        self.__db_database=XXX

    # 連接數(shù)據(jù)庫(kù)
    def isConnection(self):
        self.__db=pymysql.connect(
            host=self.__db_host,
            port=self.__db_port,
            user=self.__db_user,
            password=self.__db_password,
            database=self.__db_database,
            charset='utf8'
        )


    # 插入數(shù)據(jù)進(jìn)pay表
    def pay_insert(self,pay_value):
        try:
            # 連接數(shù)據(jù)庫(kù)
            self.isConnection()
            # 創(chuàng)建游標(biāo)
            global cursor
            cursor=self.__db.cursor()
            # 執(zhí)行
            cursor.executemany('insert into `pay表`(type,pay_money,pay_time,pay_no,STATUS,create_by,create_time,update_by,update_time) value (%s,%s,%s,%s,%s,%s,%s,%s,%s)',pay_value)

        except Exception as e:
            print e
        finally:
            cursor.close()
            self.__db.commit()
            self.__db.close()


    # 生成pay表所需字段，并調(diào)用sql
    def pay_data(self,data_number):
        pay_value=list()
        for i in range(1,data_number):
            pay_value.append((0,8800,time.localtime(),str(random.randint(712300000000,712399999999)),3,49338,time.localtime(),49338,time.localtime()))
        now_time=time.localtime()
        self.pay_insert(pay_value)
        return now_time


    # 獲取pay_id
    def get_pay_id(self,now_time,data_number):
        try:
            self.isConnection()
            global cursor
            cursor=self.__db.cursor()
            cursor.execute('select id from `pay表` where create_time >= %s',now_time)
            id_value=list()
            for i in range(1,data_number):
                pay_id=cursor.fetchone()
                id_value.append(pay_id)
            return id_value
        except Exception as e:
            print e
        finally:
            cursor.close()
            self.__db.commit()
            self.__db.close()


    # 插入數(shù)據(jù)進(jìn)order表
    def order_insert(self,order_value):
        try:
            self.isConnection()
            global cursor
            cursor=self.__db.cursor()
            cursor.executemany('insert into `order表` (student_name,student_id,school_id,school_name,tel,height,sex,pay_id,order_no,status,original_price,payment_price,order_type,create_by,create_time,update_by,update_time,purchase_id,dept_id,sub_order_mid,class_name,shoe_size,student_no,weight) value (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',order_value)
        except Exception as e:
            print e
        finally:
            cursor.close()
            self.__db.commit()
            self.__db.close()


    # 生成order表所需字段，并調(diào)用sql
    def order_data(self,id_value,data_number):
        order_value=list()
        for i in range(1,data_number):
            pay_id=str(id_value[i-1]).replace("L,)","").replace("(","")
            order_value.append(("周瑜",35999,346,"A城小學(xué)","13322222222",130,1,pay_id,str(random.randint(7100000000,7999999999)),2,8800,8800,1,49338,time.localtime(),49338,time.localtime(),405,121,564123698745632,"三年級(jí) 3班",30,30,30))
        sys_time=time.localtime()
        self.order_insert(order_value)
        return sys_time


    # 獲取order_id
    def get_order_id(self,sys_time,data_number):
        try:
            self.isConnection()
            global cursor
            cursor=self.__db.cursor()
            cursor.execute('select id from `order表` where create_time >= %s',sys_time)
            order_id_list=list()
            for i in range(1,data_number):
                order_id_list.append(cursor.fetchone())
            return order_id_list
        except Exception as e:
            print e
        finally:
            cursor.close()
            self.__db.commit()
            self.__db.close()


    # 插入數(shù)據(jù)進(jìn)order_detail表
    def order_detail_insert(self,detail_value):
        try:
            self.isConnection()
            global cursor
            cursor=self.__db.cursor()
            cursor.executemany('insert into `order_details表` (order_id,commodity_name,commodity_id,original_price,payment_price,img,number,status,create_by,create_time,update_by,update_time) value (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',detail_value)
        except Exception as e:
            print e
        finally:
            cursor.close()
            self.__db.commit()
            self.__db.close()


    # 生成order_detail表所需字段，并調(diào)用sql
    def order_detail_data(self,order_id_list,data_number):
        detail_value=list()
        for i in range(1,data_number):
            order_id=str(order_id_list[i-1]).replace("L,)","").replace("(","")
            detail_value.append((order_id,"A城小學(xué)春季校服","1382932636506902530",8800,8800,"https://ygxf-dev2.obs.cn-north-1.myhuaweicloud.com:443/image%2F1618551784845-589.jpg",1,2,49338,time.localtime(),49338,time.localtime()))
        self.order_detail_insert(detail_value)


if __name__ == '__main__':
    db=DatabaseAcess()
    data_number=3
    db.order_detail_data(order_id_list=db.get_order_id(sys_time=db.order_data(id_value=db.get_pay_id(now_time=db.pay_data(data_number=data_number),data_number=data_number),data_number=data_number),data_number=data_number),data_number=data_number)
    print ("{0}條數(shù)據(jù)插入完成".format(data_number-1))

到此這篇關(guān)于python數(shù)據(jù)庫(kù)批量插入數(shù)據(jù)的實(shí)現(xiàn)(executemany的使用)的文章就介紹到這了,更多相關(guān)python數(shù)據(jù)庫(kù)批量插入內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

python編程開發(fā)之textwrap文本樣式處理技巧
Python的文本常量與字符串模板之string庫(kù)
Python中使用subprocess庫(kù)創(chuàng)建附加進(jìn)程
Python超簡(jiǎn)單容易上手的畫圖工具庫(kù)推薦
python爬蟲請(qǐng)求庫(kù)httpx和parsel解析庫(kù)的使用測(cè)評(píng)
Python高級(jí)文件操作之shutil庫(kù)詳解
Python超簡(jiǎn)單容易上手的畫圖工具庫(kù)（適合新手）
python學(xué)習(xí)之panda數(shù)據(jù)分析核心支持庫(kù)
Python基礎(chǔ)之操作MySQL數(shù)據(jù)庫(kù)
Python繪圖庫(kù)Matplotlib的基本用法
Python爬蟲爬取愛奇藝電影片庫(kù)首頁(yè)的實(shí)例代碼
Python Excel處理庫(kù)openpyxl詳解
python使用openpyxl庫(kù)讀寫Excel表格的方法（增刪改查操作）
Python time庫(kù)的時(shí)間時(shí)鐘處理
Python基礎(chǔ)之常用庫(kù)常用方法整理
Python爬蟲之必備chardet庫(kù)
python中requests庫(kù)+xpath+lxml簡(jiǎn)單使用
Python格式化文本段落之textwrap庫(kù)

標(biāo)簽：臺(tái)灣三沙黃山宿遷景德鎮(zhèn) 喀什濟(jì)南欽州

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《python數(shù)據(jù)庫(kù)批量插入數(shù)據(jù)的實(shí)現(xiàn)(executemany的使用)》，本文關(guān)鍵詞 python,數(shù)據(jù)庫(kù),批量,插入,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。