OpenGauss数据库-L.应用开发(Python)

第一关、简单查询

# 加载数据库模块
import psycopg2

# 连接数据库,创建连接并返回连接对象
def connect():
    conn = psycopg2.connect(database="finance", user="gaussdb", password="Passwd123@123", host="localhost" )
    return conn

# 建立与数据库连接
mydb = connect()

# 获取游标
mycursor = mydb.cursor();

# 执行SQL语句 
sql = "SELECT c_name,c_mail,c_phone FROM client WHERE c_mail IS NOT NULL;"

# 获取结果，并按要求展示结果  
mycursor.execute(sql)
myresult = mycursor.fetchall()
print("姓名\t邮箱\t\t\t\t\t电话")
for row in myresult:
    print(f"{row[0]}\t{row[1]}\t\t{row[2]}")

# 依次释放资源
connect().close()
mydb.close()

第二关、用户验证

##*********begin*********
# 加载数据库模块
import psycopg2

# 连接数据库
db = psycopg2.connect(
    host = "localhost",
    database = "finance",
    user = "gaussdb",
    password="Passwd123@123"
)

# 提示输入用户名和密码，并保存到变量
usr = input("请输入用户名：")
pwd = input("请输入密码：")

# 构造SQL语句（带占位符 ）
sql = f"SELECT c_mail,c_password FROM client WHERE c_mail = %s AND c_password = %s;"

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

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

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

第三关、添加客户

# 连接数据库,创建连接并返回连接对象(连接参数已设置好)
def connect():
    conn = psycopg2.connect(database="finance", user="gaussdb", password="Passwd123@123", host="localhost")
    return conn

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

# 加载数据库模块
import psycopg2

# 从键盘读取两行记录，字段与字段之间用空格隔开，一条记录占一行
# 调用add_client()将输入数据插入client表中
#编写实现代码
inputs = input() + '\n' + input()

insertlines = []
for line in inputs.splitlines():
    insertlines.append(line.split(' '))  # 这里是空格不是没有东西。

for line in insertlines:
    add_client(line[0],line[1],line[2],line[3],line[4],line[5])

第四关、银行卡销户

# 连接数据库,创建连接并返回连接对象
def connect():
    conn = psycopg2.connect(database="finance", user="gaussdb", password="Passwd123@123", host="localhost" )
    return conn

 
# 从Client表中删除数据
#
# @param cid 客户编号
# @param cardNum 银行卡号
def removeBankCard(cid,cardNum):
    db = connect()
    cursor = db.cursor()
    sql = "DELETE FROM bank_card WHERE b_c_id = %s AND b_number = %s"
    cursor.execute(sql,(cid,cardNum))
    db.commit()
    return cursor.rowcount

# 加载数据库模块
import psycopg2

# 从键盘读取两行记录，字段与字段之间用空格隔开，一条记录占一行
# 调用removeBankCard()注销银行卡

inputs = input() + '\n' + input()

insertlines = []
for line in inputs.splitlines():
    insertlines.append(line.split(' '))

for line in insertlines:
    row = removeBankCard(line[0],line[1])
    if row > 0:
        print("已销卡数：",row)
    else:
        print("销户失败，请检查客户编号或银行卡号！")

第五关、修改密码

# 连接数据库,创建连接并返回连接对象
def connect():
    conn = psycopg2.connect(database="finance", user="gaussdb", password="Passwd123@123", host="localhost" )
    return conn

 ##*********begin*********
# 更改用户密码
#
# @param cmail 用户名号
# @param cpass 用户密码
# @param newpass 新设密码
# return 1/2/3/4 meaning 用户不存在/用户密码不对/密码修改成功/密码修改异常
def passwd(cmail,cpass,newpass):
    try:
        db = connect()
        cursor = db.cursor()
        sql_change = "UPDATE client SET c_password = %s WHERE c_mail = %s AND c_password = %s"
        sql_search = "SELECT c_mail,c_password FROM client WHERE c_mail = %s"

        cursor.execute(sql_change,(newpass,cmail,cpass))
        db.commit()

        if cursor.rowcount >= 1:
            return 3
        else:
            cursor.execute(sql_search,(cmail,))
            if cursor.rowcount >= 1:
                return 2
            else:
                return 1
    except Exception as e:
        print(e)
        return 4

# 加载数据库模块
import psycopg2

# 从键盘读取4行记录，字段与字段之间用空格隔开，一条记录占一行。
# 检查两次新密码输入是否一致，然后调用passwd()函数修改密码

inputs = ""

for i in range(4):
    inputs += input() + '\n'

insertlines = []
for line in inputs.splitlines():
    insertlines.append(line.split(' '))

for i in range(len(insertlines)):
    if insertlines[i][2] != insertlines[i][3]:
   # 注意：以下所有的冒号都是英文冒号。
        print(f"第{i+1}组:两次输入的密码不一致")
    else:
        result = passwd(insertlines[i][0],insertlines[i][1],insertlines[i][2])
        if result == 1:
            print(f"第{i+1}组:用户不存在")
        elif result == 2:
            print(f"第{i+1}组:用户密码不正确")
        elif result == 3:
            print(f"第{i+1}组:密码修改成功")
        elif result == 4:
            print(f"第{i+1}组:密码修改异常")

第六关、事务与转账

# 连接数据库,创建连接并返回连接对象
def connect():
    conn = psycopg2.connect(database="finance", user="gaussdb", password="Passwd123@123", host="localhost" )

    return conn

##*********begin*********
# 转账
#
# @param src 转出账户
# @param dest 转入账户
# @param amount 转账金额
# return 0/1 meaning 转账成功/转账失败(不细分失败原因)
def transfer_money(src,dest,amount):
    db = connect()
    mycursor = db.cursor()

    sql_search = "SELECT b_number,b_type,b_balance FROM bank_card WHERE b_number = %s"
    sql_change = "UPDATE bank_card SET b_balance = %s WHERE b_number = %s"
    mycursor.execute(sql_search,(src,))
    result = mycursor.fetchone()

    if result is None:
        return 1
    elif result[1].strip() == "信用卡":
        return 1
    elif float(result[2]) < float(amount):
        return 1

    mycursor.execute(sql_change,(float(result[2]) - float(amount),src))

    mycursor.execute(sql_search,(dest,))
    result = mycursor.fetchone()

    if result is None:
        db.rollback()
        return 1

    if result[1].strip() == "信用卡":
        mycursor.execute(sql_change,(float(result[2]) - float(amount),dest))
    else:
        mycursor.execute(sql_change,(float(result[2]) + float(amount),dest))

    db.commit()
    return 0


# 加载数据库模块
import psycopg2
import sys
# 从键盘读取转账需求：转出账户，转入账户，转账金额。三项之间用空格隔开，一笔转账需求占一行(测试用例行数不确定)。
# 对每一笔转账业务，调用transfer_money()函数转账，根据返回结果输出对应提示信息

inputs = []
while True:
    try:
        inputs.append(input())
    except:
        break

insertlines = []
for line in inputs:
    insertlines.append(line.split(' '))

for i in range(len(insertlines)):
    result = transfer_money(insertlines[i][0],insertlines[i][1],insertlines[i][2])
# 以下标点符号均为中文。
    if result == 0:
        print(f"第{i+1}笔：转账成功")
    elif result == 1:
        print(f"第{i+1}笔：转账失败，请核对卡号，卡类型及卡余额")

第七关、稀疏表转为键值对

import psycopg2
from psycopg2 import sql

# 连接数据库,创建连接并返回连接对象
def connect():
    conn = psycopg2.connect(database="finance", user="gaussdb", password="Passwd123@123", host="localhost")
    return conn

# 向sc表中插入数据
def add_sc(sno, colName, colValue):
    if colValue is None:  # 跳过空值
        return
    
    conn = None
    try:
        conn = connect()
        cur = conn.cursor()
        
        # 使用参数化查询防止SQL注入
        insert_query = sql.SQL("INSERT INTO sc(sno, col_name, col_value) VALUES (%s, %s, %s)")
        cur.execute(insert_query, (sno, colName, str(colValue)))
        
        conn.commit()
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()

# 主程序
def main():
    conn = None
    try:
        conn = connect()
        cur = conn.cursor()
        
        # 获取entrance_exam表的所有列名(排除sno)
        cur.execute("""
            SELECT column_name 
            FROM information_schema.columns 
            WHERE table_name = 'entrance_exam' AND column_name != 'sno'
        """)
        columns = [row[0] for row in cur.fetchall()]
        
        # 查询entrance_exam表中的所有数据
        cur.execute("SELECT * FROM entrance_exam")
        rows = cur.fetchall()
        
        # 获取列名索引映射
        col_index = {desc[0]: idx for idx, desc in enumerate(cur.description)}
        
        for row in rows:
            sno = row[col_index['sno']]
            for col in columns:
                col_value = row[col_index[col]]
                if col_value is not None:  # 只处理非空值
                    add_sc(sno, col, col_value)
        
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()

if __name__ == '__main__':
    main()