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

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

一、說明

pip install psycopg2==2.9.5
連線金倉資料庫的驅動是pg資料的驅動，金倉是從pg資料庫改造來的

這個驅動的版本和資料庫的版本要匹配否則會報錯

二、原始碼

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

#pip install psycopg2==2.9.5
#連線金倉資料庫的驅動是pg資料的驅動，金倉是從pg資料庫改造來的



#測試單表插入效能金倉

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 = psycopg2.connect(host='192.168.0.99', port=54321, user='user',  password='pass',  dbname='kingdb')



    # 運算元據庫
    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
                                        (
                                          %s,  	    %s,         %s,  			%s 
                                        );"""
           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資料庫的效能對比

金倉資料庫寫入時間變化

------i: 0
Function execution took 137.06369280815125 seconds
------i: 1
Function execution took 279.81020069122314 seconds
------i: 2
Function execution took 421.08003759384155 seconds
------i: 3
Function execution took 590.9331431388855 seconds
------i: 4
Function execution took 732.0582106113434 seconds
------i: 5
Function execution took 875.8131754398346 seconds
------i: 6
Function execution took 1017.1411633491516 seconds
------i: 7
Function execution took 1156.033727645874 seconds
------i: 8
Function execution took 1299.8666570186615 seconds
------i: 9
Function execution took 1441.633094549179 seconds
------i: 10
Function execution took 1584.4631068706512 seconds
------i: 11
Function execution took 1724.3116672039032 seconds
------i: 12
Function execution took 1869.6653730869293 seconds
------i: 13
Function execution took 2012.0115368366241 seconds
------i: 14
Function execution took 2154.4105224609375 seconds
------i: 15
Function execution took 2314.8699004650116 seconds
------i: 16
Function execution took 2477.3118846416473 seconds
------i: 17
Function execution took 2648.795977115631 seconds
------i: 18
Function execution took 2826.580755710602 seconds
------i: 19
Function execution took 2996.6565794944763 seconds
------i: 20
Function execution took 3164.4727120399475 seconds
------i: 21
Function execution took 3329.198910474777 seconds
------i: 22
Function execution took 3498.414920091629 seconds
------i: 23
Function execution took 3667.353389978409 seconds
------i: 24
Function execution took 3837.870841026306 seconds
------i: 25
Function execution took 4012.5578734874725 seconds
------i: 26
Function execution took 4184.695544719696 seconds
------i: 27
Function execution took 4362.310769319534 seconds
------i: 28
Function execution took 4538.177855968475 seconds
------i: 29
Function execution took 4718.559542417526 seconds
------i: 30
Function execution took 4897.362930297852 seconds
------i: 31
Function execution took 5068.418376207352 seconds
------i: 32
Function execution took 5239.50234746933 seconds
------i: 33
Function execution took 5406.020411729813 seconds
------i: 34
Function execution took 5576.049312353134 seconds
------i: 35
Function execution took 5743.429109096527 seconds
------i: 36
Function execution took 5913.702522277832 seconds
------i: 37
Function execution took 6081.306495189667 seconds
------i: 38
Function execution took 6250.989602804184 seconds
------i: 39
Function execution took 6413.501121759415 seconds
------i: 40
Function execution took 6582.304712057114 seconds
------i: 41
Function execution took 6745.334614038467 seconds
------i: 42
Function execution took 6895.19530749321 seconds
------i: 43
Function execution took 7047.076823472977 seconds
------i: 44
Function execution took 7193.030353546143 seconds
------i: 45
Function execution took 7339.327767133713 seconds
------i: 46
Function execution took 7487.651431083679 seconds
------i: 47
Function execution took 7631.036159515381 seconds
------i: 48
Function execution took 7773.516453504562 seconds
------i: 49
Function execution took 7916.619859218597 seconds
------i: 50
Function execution took 8059.901052474976 seconds
------i: 51
Function execution took 8203.629598140717 seconds
------i: 52
Function execution took 8337.648013591766 seconds
------i: 53
Function execution took 8477.78819656372 seconds
------i: 54
Function execution took 8624.042060613632 seconds
------i: 55
Function execution took 8772.094856977463 seconds
------i: 56
Function execution took 8916.355032682419 seconds
------i: 57
Function execution took 9060.581757307053 seconds
------i: 58
Function execution took 9201.546783447266 seconds
------i: 59
Function execution took 9342.666322231293 seconds
------i: 60
Function execution took 9488.702344179153 seconds
------i: 61
Function execution took 9632.587821006775 seconds
------i: 62
Function execution took 9782.06460428238 seconds
------i: 63
Function execution took 9928.704305171967 seconds
------i: 64
Function execution took 10072.439450740814 seconds
------i: 65
Function execution took 10216.646212816238 seconds
------i: 66
Function execution took 10362.69648718834 seconds
------i: 67
Function execution took 10511.83006310463 seconds
------i: 68
Function execution took 10655.702167749405 seconds
------i: 69
Function execution took 10801.797168254852 seconds
------i: 70
Function execution took 10948.921475172043 seconds
------i: 71
Function execution took 11092.970601320267 seconds
------i: 72
Function execution took 11246.672065258026 seconds
------i: 73
Function execution took 11399.950513362885 seconds
------i: 74
Function execution took 11544.739142417908 seconds
------i: 75
Function execution took 11689.111287355423 seconds
------i: 76
Function execution took 11830.510897874832 seconds
------i: 77
Function execution took 11974.54259467125 seconds
------i: 78
Function execution took 12117.716116189957 seconds
------i: 79
Function execution took 12264.565227270126 seconds

相關文章