MySQL-数据库应用开发(Python篇)

3.3.0 MySQL-数据库应用开发(Python篇)

第1关：Python的数据库API规范与数据库模块：简单查询

#加载数据库模块#
import pymysql

#建立与数据库连接#
db = pymysql.connect(host='127.0.0.1',
                     user='root',
                     password='123123',
                     database='finance',
                     charset='utf8mb4')
#获取游标#
cursor =db.cursor() 

#执行SQL语句# 
sql = "select c_name,c_mail,c_phone from client where c_mail is not null;"

cursor.execute(sql)

#获取结果，并按要求展示结果#
result = cursor.fetchall()

print("姓名\t邮箱\t\t\t\t电话")
for row  in result:
    print(row[0]+"\t"+row[1]+"\t\t"+row[2])

#依次释放资源#
cursor.close()
db.close()

第2关：用户登录

#加载数据库模块#
import pymysql

#建立与数据库连接#
db = pymysql.connect(host='127.0.0.1',
                     user='root',
                     password='123123',
                     database='finance',
                     charset='utf8mb4')
# 提示输入用户名和密码，并保存到变量
c_mail=input("请输入用户名：")
c_password=input("请输入密码：")

# 构造SQL语句（或带占位符的语句和参数值）
sql="select* from client where c_mail=%s and c_password=%s;"
val=(c_mail,c_password)




# 获取游标并执行SQL语句
cursor = db.cursor()
cursor.execute(sql,val)

# 根据结果集的行数判断是否存在该用户且密码与输入一致
if cursor.rowcount == 1:
    print("登录成功。")
else: 
    print("用户名或密码错误！")   

cursor.close()
db.close()

第3关：添加新客户

# 连接mysql，连接数据库finance，创建连接并返回连接对象
def connect():
    conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='123123',
        database='finance',
        charset='utf8mb4'
    )
    return conn


# 向Client表中插入数据
# @param cid 客户编号
# @param cname 客户名称
# @param mail 客户邮箱
# @param idcard 客户身份证
# @param phone 客户手机号
# @param password 客户登录密码
def add_client(cid, cname, mail, idcard, phone, password):
    conn = connect()
    cursor = conn.cursor()
    sql = "INSERT INTO client VALUES (%s, %s, %s, %s, %s, %s)"
    val = (cid, cname, mail, idcard, phone, password)
    cursor.execute(sql, val)
    conn.commit()
    conn.close()

# 加载数据库模块
import pymysql

# 从键盘读取两行记录，字段与字段之间用空格隔开，一条记录占一行
# 调用add_client()将输入数据插入client表
for i in range(2):
    record = input().split()
    add_client(*record)

第4关：银行卡销户

# 连接mysql，连接数据库finance，创建连接并返回连接对象
def connect():
    conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='123123',
        database='finance',
        charset='utf8mb4'
    )
    return conn


# 向Client表中插入数据
#
# @param cid 客户编号
# @param cardNum 银行卡号
def removeBankCard(cid,cardNum):
    conn = connect()
    cursor = conn.cursor()
    sql = "DELETE FROM bank_card WHERE b_c_id = %s AND b_number = %s"
    result = cursor.execute(sql, (cid, cardNum))
    conn.commit()
    conn.close()
    if result > 0:
        print("已销卡数： 1")
    else:
        print("销户失败，请检查客户编号或银行卡号！")


# 加载数据库模块
import pymysql

# 从键盘读取两行记录，字段与字段之间用空格隔开，一条记录占一行
# 调用removeBankCard()注释银行卡
for i in range(1, 3):
    data = input()
    cid, cardNum = data.split()
    removeBankCard(cid, cardNum)

第5关：客户修改密码

# 连接mysql，连接数据库finance，创建连接并返回连接对象
def connect():
    conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='123123',
        database='finance',
        charset='utf8mb4'
    )
    return conn


# 更改用户密码
#
# @param cmail 用户名号
# @param cpass 用户密码
# @param newpass 新设密码
# return 1/2/3/4 meaning 用户不存在/用户密码不对/密码修改成功/密码修改异常
def passwd(cmail,cpass,newpass):
    conn = connect()
    cursor = conn.cursor()
# 检查用户是否存在
    cursor.execute("SELECT * FROM client WHERE c_mail=%s", (cmail,))
    result = cursor.fetchone()
    if result is None:
        return 1  # 用户不存在
 # 检查用户密码是否正确
    cursor.execute("SELECT * FROM client WHERE c_mail=%s AND c_password=%s", (cmail, cpass))
    result = cursor.fetchone()
    if result is None:
        return 2  # 用户密码不正确
    
    # 修改密码
    try:
        cursor.execute("UPDATE client SET c_password=%s WHERE c_mail=%s", (newpass, cmail))
        conn.commit()
        return 3  # 密码修改成功
    except:
        return 4  # 密码修改异常



# 加载数据库模块
import pymysql

# 从键盘读取两行记录，字段与字段之间用空格隔开，一条记录占一行。
# 检查两次密码输入是否一致，然后调用passwd()函数修改密码
for i in range(1,5):
    cmail, cpass, newpass, confirm_newpass = input().split()
    if newpass != confirm_newpass:
        print(f'第{i}组:两次输入的密码不一致')
    else:
        result = passwd(cmail, cpass, newpass)
        if result == 1:
            print(f'第{i}组:用户不存在')
        elif result == 2:
            print(f'第{i}组:用户密码不正确')
        elif result == 3:
            print(f'第{i}组:密码修改成功')
        else:
            print(f'第{i}组:密码修改异常')

第6关：事务与转账操作

# 加载数据库模块
import pymysql

# 连接数据库
def connect():
    conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='123123',
        database='finance',
        charset='utf8mb4'
    )
    return conn

def transfer_money(conn, src, dest, amount):
    cursor = conn.cursor()
    try:
        conn.autocommit = False

        # 扣减转出账户的金额
        sql = "UPDATE bank_card SET b_balance = b_balance - %s WHERE b_number = %s;"
        cursor.execute(sql, (amount, src))

        # 增加转入账户的金额
        sql = "UPDATE bank_card SET b_balance = b_balance + %s WHERE b_number = %s AND b_type = '储蓄卡';"
        cursor.execute(sql, (amount, dest))

        # 处理信用卡账户的金额
        sql = "UPDATE bank_card SET b_balance = b_balance - %s WHERE b_number = %s AND b_type = '信用卡';"
        cursor.execute(sql, (amount, dest))

        # 检查转出账户余额
        sql = "SELECT * FROM bank_card WHERE b_number = %s AND b_type = '储蓄卡';"
        cursor.execute(sql, (src,))
        result = cursor.fetchone()
        if not result:
            conn.rollback()
            return 1
        else:
            if result[3] < 0:  # Assuming b_balance is the 4th column
                conn.rollback()
                return 1
            else:
                sql = "SELECT * FROM bank_card WHERE b_number = %s;"
                cursor.execute(sql, (dest,))
                result = cursor.fetchone()
                if not result:
                    conn.rollback()
                    return 1
                else:
                    conn.commit()
                    return 0
    except Exception as e:
        print(e)
        conn.rollback()
        return 1
    finally:
        cursor.close()


# 从键盘读取转账需求：转出账户，转入账户，转账金额。三项之彰用空格隔开，一笔转账需求占一行。
# 对每一笔转账业务，调用transfer_money()函数转账，根据返回结果输出对应提示信息

conn = connect()
iteration = 1
for iteration in range(1,10) :
    try:
        input_data = input()
        commands = input_data.split(" ")

        payer_card = commands[0]

        payee_card = commands[1]

        amount = float(commands[2])
        if transfer_money(conn, payer_card, payee_card, amount) == 0:
            print(f"第{iteration}笔：转账成功")
        else:
            print(f"第{iteration}笔：转账失败，请核对卡号，卡类型及卡余额")
        iteration += 1

    except Exception as e:
        pass

第7关：把稀疏表格转为键值对存储

import pymysql

# 连接数据库函数
def connect():
    conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='123123',
        database='sparsedb',  # 确保数据库名称为sparsedb
        charset='utf8mb4'
    )
    return conn

# 向sc表中插入数据函数
#
# @param sno 学号
# @param colName 列名
# @param colValue 列值
def add_sc(cursor, sno, colName, colValue):
    sql = "INSERT INTO sc (sno, col_name, col_value) VALUES (%s, %s, %s);"
    val = (sno, colName, colValue)
    cursor.execute(sql, val)

# 加载数据库模块
db = connect()
cursor = db.cursor()

# 读取高考成绩entrance_exam表，并将非空列转存到sc表
sql = "SELECT * FROM entrance_exam"
cursor.execute(sql)
result = cursor.fetchall()

# 获取表的列名
cursor.execute("SHOW COLUMNS FROM entrance_exam")
columns = [col[0] for col in cursor.fetchall()]

for row in result:
    sno = row[0]  # 假设学号在第一个位置
    for i, colValue in enumerate(row[1:], start=1):  # 从第二列开始是各科成绩
        if colValue is not None :  # 检查非空列
            colName = columns[i]  # 获取列名
            add_sc(cursor, sno, colName, colValue)

# 提交事务
db.commit()

cursor.close()
db.close()