使用pymysql简单创建mysql数据库

最近写爬虫时候, 因为一直要手动创建数据库,嫌麻烦,就自己写了一个简单的创建mysql数据库代码.
例如我想同时创建多个库,每个库里面有多个表,每个表里有n个字段.库要定义成字典的形式,比如这样:
db_name 库名
tb_name 表名
columns 字段名
元组中第一个值为字段名,第二个值为字段类型,第三个值是能否为空.

DATABASES_INFO = [
    {'db_name': 'my_db',
     'tb_info': [{'tb_name': 'user_tb1',
                  'columns': [('name', 'varchar(20)', 1),
                              ('age', 'int', 1),
                              ]
		                  },
		                 {'tb_name': 'user_tb2',
		                  'columns': [('name', 'varchar(20)', 1),
		                              ('age', 'int', 1),
		                              ]
                 		 }, ]
     }, ]

每次建库只需要定义好字典就可以了
代码如下,整体过程很简单,就是先创建库,再建表,完了创建字段,最后关闭链接.
(整体print提示语句比较多)

class CreateDb(object):
    def __init__(self):
        self.db_info = DATABASES_INFO
        self.conn = pymysql.connect(HOST, USER, PASSWORD, charset=CHARSET)
        self.cursor = self.conn.cursor()

    @staticmethod
    def abs_null(tb_info, num):
        if tb_info['columns'][num][2] == 0:
            return ''
        else:
            return 'NOT NULL'

    def create_database(self):
        self.cursor.execute('show databases;')
        tables_tup = self.cursor.fetchall()
        for db_info in self.db_info:
            print(f"开始创建{db_info['db_name']}......")
            if f"('{db_info['db_name']}',)" in str(tables_tup):
                print(f'{db_info["db_name"]}已经存在,开始检查表结构......')
            else:
                try:
                    self.cursor.execute(f'CREATE DATABASE {db_info["db_name"]} character set utf8mb4;')
                except Exception as e:
                    print(f'error:{e}')
                    self.conn.rollback()
            self.create_table(db_info)
            print(f'{db_info["db_name"]}创建完毕!')
        self.close_mysql()

    def create_table(self, db_info):
        self.cursor.execute(f'use {db_info["db_name"]};')
        self.cursor.execute('SHOW tables;')
        table_list_info = self.cursor.fetchall()
        for tb_info in db_info['tb_info']:
            if f"('{tb_info['tb_name']}',)" in str(table_list_info):
                print(f'表{tb_info["tb_name"]}已经存在,开始添加字段......')
            else:
                my_table = f'CREATE TABLE {tb_info["tb_name"]}(' \
                    f'id INT NOT NULL AUTO_INCREMENT,' \
                    f'PRIMARY KEY (id)' \
                    f')CHARSET="utf8mb4"'
                try:
                    self.cursor.execute(my_table)
                except Exception as e:
                    print(f'error:{e}')
                    self.conn.rollback()
            self.add_column(db_info, tb_info)

    def add_column(self, db_info, tb_info):
        self.cursor.execute(f'SHOW COLUMNS FROM {tb_info["tb_name"]};')
        column_list = self.cursor.fetchall()
        for i in range(len(tb_info['columns'])):
            if f"('{tb_info['columns'][i][0]}'" in str(column_list):
                print(f'字段{tb_info["columns"][i][0]}已经存在!尝试创建其他字段......')
            else:
                ad_col = f'alter table {tb_info["tb_name"]} add ' \
                    f'{tb_info["columns"][i][0]} ' \
                    f'{tb_info["columns"][i][1]} ' \
                    f'{self.abs_null(tb_info, i)}'
                try:
                    self.cursor.execute(ad_col)
                except Exception as e:
                    print(f'error:{e}')
                    self.conn.rollback()
        print(f'数据库{db_info["db_name"]}中表{tb_info["tb_name"]}创建完毕!')

    def close_mysql(self):
        self.conn.commit()
        self.cursor.close()
        self.conn.close()


if __name__ == '__main__':
    create_db = CreateDb()
    create_db.create_database()