国产数据库oceanBbase,达梦,金仓与mysql数据库的性能对比 五、python读mysql写入金仓数据库
一、说明
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
