國產資料庫oceanBbase,達夢,金倉與mysql資料庫的效能對比 四、python讀mysql寫入達夢資料庫

万笑佛發表於2024-11-20
資料庫MySqlPython

一、說明

安裝達夢的驅動

pip install dmPython==2.5.5

引數接收那裡，其他資料庫都是用%,達夢要用?

二、原始碼

#coding=utf-8
import pymysql
import dmPython
import time
import uuid

#pip install dmPython==2.5.5

#測試單表插入效能達夢

try:
    start_time = time.time()  # 記錄開始時間
    connection1 = pymysql.connect(host='192.168.0.100', user='user', password='passwd', database='testdb', charset='gbk',  port=3306)


    #達夢
    connection2 = conn = dmPython.connect(host='192.168.0.99', port=5237, user='user',  password='pass' )



    # 運算元據庫
    cursor1 = connection1.cursor()  # 建立一個遊標

    # 運算元據庫
    cursor2 = connection2.cursor()  # 建立一個遊標

    # 定義SQL查詢語句，使用%s作為引數佔位符
    sql = "SELECT  ID,NAME,CONTENT,CREATE_TIME FROM DB_TEST_T"

    for i in range(80):
        print("------i:",i)
        # 執行SQL查詢
        cursor1.execute(sql)

        # 獲取查詢結果
        rows = cursor1.fetchall()
        data_to_insert = []
        for row in rows:

            # 插入資料到資料表的sql語句
            insert_data_sql = """insert into DB_TEST_T
                                        (
                                          ID,		NAME,		CONTENT,		CREATE_TIME
                                        )
                                        values
                                        (
                                          ?,  	    ?,          ?,  			?
                                        );"""
            random_uuid = uuid.uuid4()
			data = (str(random_uuid), row[1], row[2], row[3] ) 
            data_to_insert.append(data)

        batch_size = 50000
        for i in range(0, len(data_to_insert), batch_size):
            batch = data_to_insert[i:i + batch_size]
            # 批次插入
            cursor2.executemany(insert_data_sql, batch)
            connection2.commit()  # 提交事務


        end_time = time.time()  # 記錄結束時間
        execution_time = end_time - start_time  # 計算執行時間
        print(f"Function execution took {execution_time} seconds")

except pymysql.Error as e:
    print(f'錯誤:,{e}')

完整測試程式碼獲取：
（1）登入-註冊：http://resources.kittytiger.cn/
（2）搜尋：國產資料庫oceanBbase,達夢,金倉與mysql資料庫的效能對比

達夢資料庫寫入時間變化（共80次，這裡只列舉了前50次的寫入）

------i: 0
Function execution took 13.87256669998169 seconds
------i: 1
Function execution took 27.789175271987915 seconds
------i: 2
Function execution took 44.779128313064575 seconds
------i: 3
Function execution took 62.4614577293396 seconds
------i: 4
Function execution took 84.04464888572693 seconds
------i: 5
Function execution took 113.8495078086853 seconds
------i: 6
Function execution took 150.7498528957367 seconds
------i: 7
Function execution took 194.11006593704224 seconds
------i: 8
Function execution took 246.2607753276825 seconds
------i: 9
Function execution took 301.3220808506012 seconds
------i: 10
Function execution took 356.3666923046112 seconds
------i: 11
Function execution took 413.59015917778015 seconds
------i: 12
Function execution took 474.606484413147 seconds
------i: 13
Function execution took 537.5853796005249 seconds
------i: 14
Function execution took 604.6742069721222 seconds
------i: 15
Function execution took 673.5153102874756 seconds
------i: 16
Function execution took 745.709979057312 seconds
------i: 17
Function execution took 819.7094376087189 seconds
------i: 18
Function execution took 895.2133946418762 seconds
------i: 19
Function execution took 972.4913721084595 seconds
------i: 20
Function execution took 1049.0076823234558 seconds
------i: 21
Function execution took 1131.8306195735931 seconds
------i: 22
Function execution took 1210.6679604053497 seconds
------i: 23
Function execution took 1292.2835066318512 seconds
------i: 24
Function execution took 1373.588172197342 seconds
------i: 25
Function execution took 1455.4572558403015 seconds
------i: 26
Function execution took 1538.273905992508 seconds
------i: 27
Function execution took 1628.9478361606598 seconds
------i: 28
Function execution took 1718.6382749080658 seconds
------i: 29
Function execution took 1806.6923837661743 seconds
------i: 30
Function execution took 1896.9601435661316 seconds
------i: 31
Function execution took 1988.4968569278717 seconds
------i: 32
Function execution took 2079.7535495758057 seconds
------i: 33
Function execution took 2172.9220957756042 seconds
------i: 34
Function execution took 2265.50231218338 seconds
------i: 35
Function execution took 2360.3109443187714 seconds
------i: 36
Function execution took 2455.580892086029 seconds
------i: 37
Function execution took 2548.4238419532776 seconds
------i: 38
Function execution took 2643.2504029273987 seconds
------i: 39
Function execution took 2741.590777158737 seconds
------i: 40
Function execution took 2844.4359214305878 seconds
------i: 41
Function execution took 2943.111762523651 seconds
------i: 42
Function execution took 3038.9809834957123 seconds
------i: 43
Function execution took 3134.171258687973 seconds
------i: 44
Function execution took 3229.1782751083374 seconds
------i: 45
Function execution took 3327.891332387924 seconds
------i: 46
Function execution took 3424.7561354637146 seconds
------i: 47
Function execution took 3520.922253370285 seconds
------i: 48
Function execution took 3617.919780254364 seconds
------i: 49
Function execution took 3723.3062534332275 seconds
------i: 50
Function execution took 3821.1334459781647 seconds

相關文章