python动态创建MySQL分区

创建LIST分区表:

CREATE TABLE `partitioned` (
`id` int(11) NOT NULL,
`tag` int(11) NOT NULL,
PRIMARY KEY (`id`, `tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST (tag)
(PARTITION p0 VALUES IN (0))

后期想通过数据动态创建分区，每次先判断表中是否已创建分区。如果没有创建就添加分区：

def get_data_from_mysql(sql):
	"""执行sql，返回查到的数据"""
	pass
	return data

def had_partition(table_name, partition_name):
	sql = """
        select partition_name 
        from information_schema.`PARTITIONS` 
        where table_name = '{}'
        """
    res = get_data_from_mysql(sql.format(table_name))
    partitions = set(r[0] for r in res)
    # 分区名称总是字符串
    if str(partition_name) in partitions:
        return True
    else:
        return False

想要新增一条数据：

def update_data(sql):
	"""执行sql，更新数据"""
	pass

def create_partition(table_name, partition_name):
	sql = "alter table {} add partition (partition {} values in ({}))" \
		 .format(table_name, partition_name, partition_name)
	update_data(sql)

if __name__ == '__main__':
	id, tag = 1, 1
	sql = "insert into partitioned values({id},{tag})"
	if not had_partition('partitioned', tag):
		create_partition('partitioned', tag)
	update_sql(sql)

上面的例子运行时会出语法错误，因为分区名称不能是数字，需要把create_partition方法分区名左右加反引号(引号也会报错)。

def create_partition(table_name, partition_name):
	sql = "alter table {} add partition (partition `{}` values in ({}))" \
		 .format(table_name, partition_name, partition_name)
	update_data(sql)