import pymysql
import array as arr

'''开启数据库会话'''
def CONNECT_SQL(host, port, user_name, pwd):
    conn = pymysql.connect(
        host=host,  # 指示host表明是本地MySQL还是远程
        port=port,
        user=user_name,  # 用户名
        password=pwd,  # 密码
        charset="utf8mb4",  # 指定字符集，可以解决中文乱码
        cursorclass=pymysql.cursors.DictCursor  # 固定写法，类似于jdbc里边的加载驱动
    )
    print(conn)
    return conn

'''连接数据库'''
#db 数据库名称
def CONNECT_SQLDB(host, port, user_name, pwd,db):
    conn = pymysql.connect(
        host=host,  # 指示host表明是本地MySQL还是远程
        port=port,
        user=user_name,  # 用户名
        password=pwd,  # 密码
        db=db,  # 所连接的数据库
        charset="utf8mb4",  # 指定字符集，可以解决中文乱码
        cursorclass=pymysql.cursors.DictCursor  # 固定写法，类似于jdbc里边的加载驱动
    )
    print(conn)
    return conn

'''插入数据'''
def insert(conn, TABLE_NAME, SQL_command):
    SQL = SQL_command
    conn = conn
    cursor = conn.cursor()
    sql = 'INSERT INTO' + ' ' + TABLE_NAME + ' ' + 'VALUES(' + SQL + ');'
    # 异常处理
    try:
        print(sql)
        # 执行SQL语句
        result = cursor.execute(sql)
        print(result)
    except:
        print('Unable to insert! Please check the insert statement')
    conn.commit()
    cursor.close()
    conn.close()


'''查询'''
# TABLE_NAME表名
# SQL_command查询条件

def SELECT(conn, TABLE_NAME, SQL_command):
    SQL = SQL_command
    conn = conn
    cursor = conn.cursor()
    sql = 'SELECT * FROM ' + ' ' + TABLE_NAME + ' ' + 'WHERE' + ' ' + SQL
    # 异常处理
    try:
        # 执行SQL语句
        result = cursor.execute(sql)
        # 获取所有的记录列表
        results = cursor.fetchall()
        print(result)
        # 遍历列表
        for row in results:
            # 打印列表元素
            print(row)
    except:
        print('Unable to fetch data!')
    print(sql)
    conn.commit()
    cursor.close()
    conn.close()



'''更新一条数据'''
# conn 数据库连接
# TABLE_NAME 表名
# column_NAME 列名
# line_name 行名称
# line_id 行id
# id 主键值

def update(conn, TABLE_NAME, column_NAME, column, line_name, line_id):
    conn = conn
    cursor = conn.cursor()
    sql = 'UPDATE ' + TABLE_NAME + ' SET ' + column_NAME + '=%s WHERE ' + column + ' = %s;'
    line = (line_name, line_id)
    # 异常处理
    try:
        # 执行SQL语句
        result = cursor.execute(sql, line)
        conn.commit()
        print(sql)
        print(result)
    except:
        conn.rollback()  # 错误时回滚
        print('Unable to update!')
    cursor.close()
    conn.close()


'''删除数据行'''
def DELETE(conn, TABLE_NAME, SQL_command):
    SQL = SQL_command
    conn = conn
    cursor = conn.cursor()
    sql = 'DELETE  FROM ' + ' ' + TABLE_NAME + ' ' + 'WHERE' + ' ' + SQL
    # 异常处理
    try:
        # 执行SQL语句
        result = cursor.execute(sql)
        conn.commit()
        print(result)
        print(sql)
    except:
        print('Unable to delete!')
        print(sql)
    cursor.close()
    conn.close()


'''建数据库'''
def CREATE_DATABASE(conn, DATABASE_NAME):
    conn = conn
    cursor = conn.cursor()
    sql = 'CREATE DATABASE' + ' ' + 'IF NOT EXISTS ' + DATABASE_NAME + ' '+'DEFAULT CHARSET utf8 COLLATE utf8_general_ci;'
    # 异常处理
    try:
        # 执行SQL语句
        result = cursor.execute(sql)
        print(sql)
        print(result)
    except:
        print(sql)
        print('Unable to create database')
    conn.commit()
    cursor.close()
    conn.close()


'''建表'''
# TABLE_NAME 表名
# TABLE_command 表命令
def CREATE_TABLE(conn, TABLE_NAME, TABLE_command):
    conn = conn
    cursor = conn.cursor()
    DROP_SQL = 'DROP TABLE ' + TABLE_NAME
    sql = 'CREATE TABLE ' + TABLE_NAME + '(' + TABLE_command + ')'
    # 异常处理
    try:
        # 执行SQL语句
        result = cursor.execute(sql)
        conn.commit()
        print(sql)
        print(result)
    except:
        cursor.execute(DROP_SQL)  # 建表失败删除同名列表
        conn.commit()
        print(DROP_SQL)
        print("删除同名列表")
    cursor.close()
    conn.close()



'''删除表'''
# TABLE_NAME 表名
def DROP_TABLE(conn, TABLE_NAME):
    conn = conn
    cursor = conn.cursor()
    sql ='DROP TABLE ' + TABLE_NAME
    # 异常处理
    try:
        # 执行SQL语句
        result = cursor.execute(sql)
        conn.commit()
        print(sql)
        print("删除成功")
    except:
        print("删除列表失败")
    cursor.close()
    conn.close()



'''自由执行语句'''
def MySQL_command(conn, SQL_command):
    SQL = SQL_command
    conn = conn
    cursor = conn.cursor()
    # 异常处理
    try:
        # 执行SQL语句
        result = cursor.execute(SQL)
        conn.commit()
        print(SQL)
        print(result)
    except:
        conn.rollback()  # 错误时回滚
        print('Unable to execute SQL command! Please check the insert statement!')
    cursor.close()
    conn.close()


# 示例
if __name__ == '__main__':
    conn = CONNECT_SQL('localhost', 3306, 'root', '123456')
    CREATE_DATABASE(conn, 'testdb')
    conn1 = CONNECT_SQLDB('localhost', 3306, 'root', '123456','1111')
    CREATE_TABLE(conn1, "test", "id int not null,name char(10),age int,address char(20),create_time datetime")
    # update(conn, "test", "class", "id", "joint", 9)
    # SELECT(conn,"test","id=3")
    # insert(conn,"test","1")
    # DELETE(conn,"test","id=1")

封装较为粗糙，见笑，希望对各位有所帮助。