python mysql分库分表_python脚本生成sql分库分表语句

使用场景§: 数据库做分库分表架构的，日常维护需要生成分片sql。

脚本如下®:

#!/usr/bin/python

#! _*_ coding:utf-8 _*_

import re

# 支持生成分表语句类型(delete,create,drop,update,alter,truncat,select)

mumber = 2 #生成分片的sql数量

c = """

select y.id,y.name,y.create_time FROM `sz_table` y where y.`id`=1000 and y.name='SZ';

SELECT

concat ( '20200616', fee_type_id, payment_type, ifnull ( inc_network_id, '' ), ifnull ( dec_network_id, '' ) ) AS collectno,

payment_type AS `type`,

fee_type_code feetypecode,

ifnull ( sum( change_cost ), 0 ) AS amount,

create_date AS billdate

FROM

spmi_apack_change_fee_bill

WHERE

fee_type_id = : 1

AND create_date = : 2

AND ( collect_no = : 3 OR collect_no IS NULL )

GROUP BY

collectno;

update `spm_commission_bill` set waybill_no=2000000 where id <1000;

CREATE TABLE `spm_sz_bill` (

`id` bigint(20) NOT NULL COMMENT '主键ID',

`waybill_no` varchar(30) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'yd编号',

PRIMARY KEY (`id`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='业务员qpj提成账单';

alter TABLE `spm_yl_bill` modify `waybill_no` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'yd编号';

truncate table `spm_sz_bill`;

DELETE from spm_sz_bill ss where ss.id id > 10000;

"""

def delete_table(i,mumber):

for n in range(0, mumber):

c1 = i.replace('DELETE ','delete ')

c2 = c1.replace('FROM ', 'from ')

result = re.findall(".*from(.*)where.*", c2)[0].strip().split(' ')[0]

if '`' in result:

print(c1.replace(result,result.split('`')[1]+'_'+str(n)))

continue

print(c1.replace(result, result+ '_' + str(n)))

def create_table(i,mumber):

for n in range(0, mumber):

c1 = i.replace('TABLE ','table ')

result = re.findall(".*table(.*)\(.*", c1)[0].strip()

if '`' in result:

print(c1.replace(result,result.split('`')[1]+'_'+str(n)))

continue

print(c1.replace(result, result+ '_' + str(n)))

def drop_table(i,mumber):

for n in range(0, mumber):

cl = i.split('`')[1:]

tn = 'DROP TABLE IF EXISTS ' + cl[0] + '_' + str(n)

print(tn)

def update_table(i,mumber):

for n in range(0, mumber):

c1 = i.replace('UPDATE ', 'update ')

c2 = c1.replace('SET ', 'set ')

result = re.findall(".*update(.*)set .*", c2)[0].strip()

if '`' not in result:

print(c2.replace(result, result + '_' + str(n)+' '))

print(c2.replace(result, result.split('`')[1] + '_' + str(n)+' '))

def alter_table(i,mumber):

for n in range(0, mumber):

c1 = i.replace('TABLE','table')

c2 = c1.replace('MODIFY','modify')

result = re.findall(".*table(.*)modify.*", c2)[0].strip()

if '`' not in result:

print(c2.replace(result, result+ '_' + str(n)))

continue

print(c2.replace(result,result.split('`')[1]+'_'+str(n)))

def truncate_table(i,mumber):

for n in range(0, mumber):

c1 = i.replace('TRUNCATE ','truncate ')

c2 = c1.replace('TABLE ','table ')

result = re.findall(".*table(.*)\;.*", c2)[0].strip().split(' ')[0]

if '`' not in result:

print(c2.replace(result, result+ '_' + str(n)))

continue

print(c2.replace(result, result.split('`')[1] + '_' + str(n)))

def select_table(i,mumber):

for n in range(0, mumber):

c3 = i.replace("\n", " ")

c1 = c3.replace('FROM', 'from')

c2 = c1.replace('WHERE','where')

# c3 = c2.replace('`','')

result = re.findall(".*from(.*)where.*", c2)[0].strip().split(' ')[0]

if '`' not in result:

print(c2.replace(result, result+ '_' + str(n)))

continue

print(c2.replace(result,result.split('`')[1]+'_'+str(n)))

for i in c.replace(';',';--|').split('--|'):

if 'CREATE ' in i or 'create ' in i:

create_table(i, mumber)

elif 'DROP ' in i or 'drop ' in i:

drop_table(i, mumber)

elif 'UPDATE ' in i or 'update ' in i:

update_table(i, mumber)

elif 'ALTER ' in i or 'alter ' in i:

alter_table(i, mumber)

elif 'truncate ' in i or 'TRUNCATE ' in i:

truncate_table(i, mumber)

elif 'select ' in i or 'SELECT ' in i or 'SELECT\n' in i or 'select\n' in i:

select_table(i, mumber)

elif 'delete ' in i or 'DELETE ' in i:

delete_table(i,mumber)

运行脚本输出结果&：

[root@localhost ~]# python tables_sharding.py

select y.id,y.name,y.create_time from sz_table_0 y where y.`id`=1000 and y.name='SZ';

select y.id,y.name,y.create_time from sz_table_1 y where y.`id`=1000 and y.name='SZ';

SELECT concat ( '20200616', fee_type_id, payment_type, ifnull ( inc_network_id, '' ), ifnull ( dec_network_id, '' ) ) AS collectno, payment_type AS `type`, fee_type_code feetypecode, ifnull ( sum( change_cost ), 0 ) AS amount, create_date AS billdate from spmi_apack_change_fee_bill_0 where fee_type_id = : 1 AND create_date = : 2 AND ( collect_no = : 3 OR collect_no IS NULL ) GROUP BY collectno;

SELECT concat ( '20200616', fee_type_id, payment_type, ifnull ( inc_network_id, '' ), ifnull ( dec_network_id, '' ) ) AS collectno, payment_type AS `type`, fee_type_code feetypecode, ifnull ( sum( change_cost ), 0 ) AS amount, create_date AS billdate from spmi_apack_change_fee_bill_1 where fee_type_id = : 1 AND create_date = : 2 AND ( collect_no = : 3 OR collect_no IS NULL ) GROUP BY collectno;

update spm_commission_bill_0 set waybill_no=2000000 where id <1000;

update spm_commission_bill_1 set waybill_no=2000000 where id <1000;

CREATE table spm_sz_bill_0 (

`id` bigint(20) NOT NULL COMMENT '主键ID',

`waybill_no` varchar(30) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'yd编号',

PRIMARY KEY (`id`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='业务员qpj提成账单';

CREATE table spm_sz_bill_1 (

`id` bigint(20) NOT NULL COMMENT '主键ID',

`waybill_no` varchar(30) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'yd编号',

PRIMARY KEY (`id`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='业务员qpj提成账单';

alter table spm_yl_bill_0 modify `waybill_no` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'yd编号';

alter table spm_yl_bill_1 modify `waybill_no` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'yd编号';

truncate table spm_sz_bill_0;

truncate table spm_sz_bill_1;

delete from spm_sz_bill_0 ss where ss.id id > 10000;

delete from spm_sz_bill_1 ss where ss.id id > 10000;