python3结合pymysql模块对数据库进行建表，增, 改，查

jhshanyu00

2020-04-20

# ！/usr/bin/python3
# -*- coding: utf-8 -*-

import pymysql

OPTION = {
    "check_sql_data": "0",
    "create_tables": "1",
    "add_sql_data": "2",
    "put_sql_data": "3",
}

def __get_connect():
    # 连接数据库
    connect = pymysql.Connect(
        host=host,
        port=port,
        user=user,
        passwd=‘password‘,
        db="db_name",
        charset=‘utf8‘
    )
    return connect

def create_tables():
    tables = "5"
    # 创建数据库表
    create_table = """
    create table my{}(
    id int unsigned primary key auto_increment,
    first_name char(10) not null,
    last_name char(10) not null,
    age int  unsigned,
    sex tinyint,
    money float
    )
    """.format(tables)

    try:
        connect = __get_connect()
        cursor = connect.cursor()
        cursor.execute(create_table)

    except Exception as e:
        connect.rollback()  # 事务回滚
        print(‘事务处理失败‘, e)
    else:
        connect.commit()  # 事务提交
        print(‘事务处理成功‘, cursor.rowcount)

    # 关闭连接
    cursor.close()
    connect.close()

def check_sql_data():
    # 数据库查询
    check_sql = "select * from my23"

    try:
        connect = __get_connect()
        cursor = connect.cursor()
        cursor.execute(check_sql)
        data = cursor.fetchmany(11)

    except Exception as e:
        connect.rollback()  # 事务回滚
        print(‘数据查询失败‘, e)
        return "数据查询失败"
    else:
        connect.commit()  # 事务提交
        print("查询到{}条数据," .format(cursor.rowcount), data)

    # 关闭连接
    cursor.close()
    connect.close()

def add_sql_data():
    # 往数据库表增加数据
    add_sql = "insert into myt033(first_name,last_name,age,sex,money) values(%s,%s,%s,%s,%s)"

    try:
        connect = __get_connect()
        cursor = connect.cursor()
        cursor.executemany(add_sql, [("Liu", "Mick", 90, 1, 9.9), ("L", "M", 12, 3, 44)])
    except Exception as e:
        connect.rollback()  # 事务回滚
        print(‘数据新增失败‘, e)
    else:
        connect.commit()  # 事务提交
        print("新增{}数据成功".format(cursor.rowcount))

    # 关闭连接
    cursor.close()
    connect.close()

def put_sql_data():
    # 修改数据库数据
    put_sql = "update my23 set first_name = %s where id = %s"

    try:
        connect = __get_connect()
        cursor = connect.cursor()
        cursor.executemany(put_sql, [("test11", 1), ("test211", 2)])
        print("修改{}条数据成功".format(cursor.rowcount))
    except Exception as e:
        connect.rollback()  # 事务回滚
        print(‘数据修改失败‘, e)
    else:
        connect.commit()  # 事务提交
        print("{}条数据成功".format(cursor.rowcount))

    # 关闭连接
    cursor.close()
    connect.close()

def option_in(arg1):
    if arg1 == OPTION.get("check_sql_data"):
        check_sql_data()
    elif arg1 == OPTION.get("create_tables"):
        create_tables()
    elif arg1 == OPTION.get("add_sql_data"):
        add_sql_data()
    elif arg1 == OPTION.get("put_sql_data"):
        put_sql_data()
    else:
        print("您输入的参数无效,请输入数字, 只支持0~3")

if __name__ == "__main__":
    arg = input("请输入：")
    option_in(arg)
    # python3交流群: 305357273

python3 pymysql