前面讲过 连接MySQL【一】,主要是去表里Select;这次说下Insert;
个人博客:https://blog.csdn.net/zyooooxie
autocommit 自动提交
说个 小技巧,是Connection类的autocommit :
先看下 Connection的注释
:param autocommit: Autocommit mode. None means use server default. (default: False)
默认是Fasle; 我现在使用 都是设置为True:
db = pymysql.connect('x.x.x.x', 'zyooooxie', 'csdn', 'db', autocommit=True)
唯一的好处是 自动提交,不必每次执行sql后 要Commit() ;
情景一 创建订单数据
某系统,要检验 某供应商 日结明细某些字段值 和 当天订单的相关字段值的关系(类似 某天全部订单交易手续费 和每笔交易手续费 的关系);
那就要往库表插入很多数据了;
先看下 字段【有省略】:
思路:
- vendor_id和vendor_name 肯定是固定的,order_id肯定是要不重复,receive_time为 毫秒级时间戳,order_status肯定是【已签收 | 已退款】;
- 从某天开始,到某天结束,连续造数据;start_date是实际订单的最早时间,假设系统上线时间为2020-01-01,后面一天才有订单,故其为2020-01-02;end_date是订单的结束时间,一般做为 当天(今天)的前一天;这2个时间点 当然也可以指定;
- 假设 价格 关系为 settlement_price + fee = supply_price;
def insert_data(self, vendor_info, start_date=None, end_date=None):
if start_date is None:
st = '20200102'
else:
st = start_date
if end_date is None:
en = (datetime.date.today() - datetime.timedelta(days=1)).strftime('%Y%m%d')
else:
en = end_date
dt_list = self.date_list(st, en) # 某时间段
db = pymysql.connect('xie.xie.xie.xie', 'zyooooxie', 'csdn', 'xie', autocommit=True)
cur = db.cursor()
for vendor in vendor_info:
vendor_id = vendor[0]
vendor_name = vendor[1]
for dt in dt_list:
receive_time = int(round(1000 * (time.mktime(time.strptime(dt + ' 12:20:30', "%Y%m%d %H:%M:%S")))))
data_list = random.sample(range(100000, 999999), 3) # 随机取3个不重复的值
for i in data_list:
# 订单id为 date + 某价格 【1. date不同 2. date相同,但价格不同;故不会出现相同订单id】
order_id = ''.join([dt, str(i)])
settlement_price = i
supply_price = settlement_price + 50
fee = 50 # 写死
sql = "INSERT INTO t_order (vendor_id, vendor_name, order_id, settlement_price, supply_price, fee, order_status, receive_time) VALUES ({}, '{}', {}, {}, {}, {}, 'zyooooxie', {});".format(vendor_id, vendor_name, order_id, settlement_price, supply_price, fee, receive_time)
cur.execute(sql)
time.sleep(0.01)
cur.close()
db.close()
情景二 大数据量的insert
pymysql Cursor类executemany()
在实际工作中,有时候可能会出现很大数据量,可能平均每天的订单数量就超过十万条,那怎么样插入数据 更快呢?
executemany()就是为此情景带来希望,可实现 多行插入;
这儿只说一点经验: 每个字段的字段值 都写入 all_data 【VALUES() 里面都是 %s,不能出现 某字段值】;
all_data = list()
all_data.append((vendor_id_name[0], vendor_id_name[1], 'IDR', order_id, settlement_price, supply_price, fee, 91, create_time, refund_time))
sql_1 = "INSERT INTO `t_order` (vendor_id, vendor_name, currency, order_id, settlement_price, supply_price, fee, status, receive_time, refund_time) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"
cur.executemany(sql_1, all_data)
sql语句
这儿说的是:一条sql语句插入多条数据;Insert into 表名 (某字段名a,某字段名b) Values (a1, b1), (a2, b2), (a3, a4);
正常连库,插入数据,其实本不用这样做,但某些情景下,只能用sql语句来做的时候,我觉着这样做 相对一条一条insert语句去执行,会高效些;
【最近有个联调环境,只能通过堡垒机去登录后,再去访问数据库;运维同事不给开放连接数据库的权限,我又偏偏要造大数据量,咋整呢?】
def test_0427(self, date_list, table, ssh_channel):
"""某时间段,每天插入40w,每条sql插入200条记录"""
for d in date_list:
Log.info('时间为 {}'.format(d))
kaishi = 1
jie = 400000
abc_list = range(kaishi, jie)
for st in range(0, math.ceil(len(abc_list) / 10000)):
Log.info('第{}个1w条数据'.format(st))
kai = kaishi + st * 10000
for abc in range(math.ceil(10000 / 200)):
Log.info('页数 {}'.format(abc))
start = kai + abc * 200
end = 200 + start
day_data = list()
# Log.info(start)
# Log.info(end)
for a in range(start, end):
gateway_order_id = int(''.join([d, "{:0>6d}".format(a)]))
gateway_amount = random.choice([a, a, a, a * 35])
gateway_transaction_status = random.choice([1, 9, 2, 3, 4, 5, 6])
transaction_order_date_timestamp = int(d)
day_data.append((gateway_order_id, gateway_amount, gateway_transaction_status,
transaction_order_date_timestamp))
sql = "insert into {} (gateway_order_id, gateway_amount,gateway_transaction_status,transaction_order_date_timestamp) VALUES {};".format(
table, tuple(day_data)).replace('((', '(').replace('))', ')')
ssh_channel.send(sql)
ssh_channel.send('\r')
这样做,insert 的速度 比起前一种差太多;而且sql语句是有SQL长度限制;
交流技术 欢迎+QQ 153132336 zy
个人博客 https://blog.csdn.net/zyooooxie