创建删除MYSQL数据库、显示非系统创建数据库、解决删除数据库内容后自增ID不连续情况将函数封装使用

1. 创建数据库

import pymysql
def bd_database(db_name):
    # 连接到MySQL数据库
    conn = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        charset='utf8mb4'
    )
    # 创建游标对象
    cursor = conn.cursor()
    # 创建数据库
    #字符串中添加变量三种方法
    #sql = f"CREATE DATABASE IF NOT EXISTS {db_name} DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;"
    #sql = f"CREATE DATABASE IF NOT EXISTS {db_name} DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;".format(db_name=db_name)
    sql = "CREATE DATABASE IF NOT EXISTS %s DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;"%(db_name)
    cursor.execute(sql)
    # 关闭游标和数据库连接
    cursor.close()
    conn.close()
if __name__ == '__main__':
    db_name=input("请输入要创建的数据库名称：")
    bd_database(db_name)

2.删除数据库

import pymysql
def del_db(db_name):
    # 连接MySQL数据库
    db = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='%s'%(db_name)
    )
    # 获取游标对象
    cursor = db.cursor()
    # 删除数据库
    drop_db_sql = "DROP DATABASE %s"%(db_name)
    cursor.execute(drop_db_sql)
    # 关闭游标和数据库连接
    cursor.close()
    db.close()
if __name__ == '__main__':
    db_name=input("请输入要删除的数据库名称：")
    del_db(db_name)

3.显示非系统创建的数据库

import pymysql
def show_db():
    # 连接到MySQL数据库
    conn = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        charset='utf8mb4'
    )
    # 创建游标对象
    cursor = conn.cursor()
    # 查询所有的数据库
    cursor.execute('SHOW DATABASES')
    databases = [database[0] for database in cursor.fetchall() if
                 database[0] not in ['mysql', 'information_schema', 'performance_schema', 'sys']]
    # 打印非系统自带的数据库
    print('非系统自带的数据库：')
    for database in databases:
        print(database)
    # 关闭游标和数据库连接
    cursor.close()
    conn.close()
if __name__ == '__main__':
    show_db()

4.解决删除数据库的数据后自增ID不连续情况

import pymysql
def new_db_ID(table_name):#用于在删除数据库的某些数据后，自增ID不连续问题，重新给入ID自增。
    # 创建数据库连接
    connection = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        db='saeco_hc',
        cursorclass=pymysql.cursors.DictCursor
    )
    # 查询数据，按照自增ID排序
    #table_name=input("请输入表名称：")
    with connection.cursor() as cursor:
        cursor.execute('SELECT * FROM '+table_name+' ORDER BY `id`')
        rows = cursor.fetchall()
    # 重新排序自增ID
    with connection.cursor() as cursor:
        for i, row in enumerate(rows):
            cursor.execute('UPDATE '+table_name+' SET `id`=%s WHERE `id`=%s', (i+1, row['id']))
        connection.commit()
    # 关闭数据库连接
    connection.close()
new_db_ID("hcm_department")