【sql+python】数据库与程序实践——某自来水公司收费系统

MQiyirs

已于 2023-03-08 20:42:00 修改

阅读量1.4k

点赞数 2

文章标签：数据库 python

于 2022-12-10 16:11:27 首次发布

本文链接：https://blog.csdn.net/qq_63142181/article/details/128165519

版权

题目

某自来水公司收费管理系统

要求

实现客户信息、用水类型（类别号、类别名、水价）及业务员管理；

实现客户用水信息管理（客户号、月份、用水类别号、用水量）；

实现客户费用管理（客户号、月份、费用、收费标志），收费标志的默认值为‘未收’；

实现收费登记（客户、月份、应收费用、实收费用、业务员），并自动修改收费标志（用触发器实现）；

创建触发器，实现收费时自动更加应收费用和实收费用，计算本次结余，然后修改客户信息表中的结余金额；

创建存储过程统计指定月份应收费用和实收费用；

创建存储过程查询指定月份未交费的用户信息，以便催费；

建立表间关系。

工具

SQL Server 2008 R2（中文版）；

PyCharm 2021.3.2 （社区版）。

数据库建立

新建数据库Tap water charge。

新建查询：

注意：运行代码时，要一段代码一段代码的运行（已分好代码段），不能全部代码一起运行。

/* 客户信息表 */
CREATE TABLE 客户信息(												
	客户号 varchar(10),
	客户名 nchar(10),
	结余金额 float NOT NULL,
	PRIMARY KEY(客户号)
)

/* 业务员信息表 */
CREATE TABLE 业务员信息(											
	业务员号 varchar(10),
	业务员名 nchar(10),
	PRIMARY KEY(业务员号)
)

/* 用水类型表 */
CREATE TABLE 用水类型(												
	类别号 varchar(10),
	类别名 nchar(10),
	单位水价 float NOT NULL,
	PRIMARY KEY(类别号),
)

/* 用水信息管理表 */
CREATE TABLE 用水信息管理(											
	客户号 varchar(10) references 客户信息(客户号),
	月份 nchar(20),
	用水类别号 varchar(10) references 用水类型(类别号),
	用水量 float,
	PRIMARY KEY (客户号,月份)
)

/* 收费登记表 */
CREATE TABLE 收费登记(												
	客户号 varchar(10) references 客户信息(客户号),
	月份 nchar(20),
	应收费用 float DEFAULT 0,
	实收费用 float DEFAULT 0,
	业务员 varchar(10) references 业务员信息(业务员号),
	PRIMARY KEY(客户号,月份)	
)

/* 费用管理表 */
CREATE TABLE 费用管理(												
	客户号 varchar(10) references 客户信息(客户号),
	月份 nchar(20),
	费用 float ,
	收费标志 nchar(10) default ('未收费'),
	PRIMARY KEY(客户号,月份),
	CHECK (收费标志='已收费' or 收费标志='未收费')
)

/* 订单管理表 */
CREATE TABLE 订单管理													
(
	订单号 varchar(20),
	客户号 varchar(10) references 客户信息(客户号),
	月份 nchar(20),
	收取费用 float,
	业务员号 varchar(10) references 业务员信息(业务员号),
	PRIMARY KEY(订单号)
)

CREATE TRIGGER 用水消费记录_插入
ON 用水信息管理
AFTER INSERT
AS
DECLARE @客户号 varchar(10)
DECLARE @用水类别 varchar(10)
DECLARE @单位水价 float
DECLARE @用水量 float
DECLARE @水价 float
DECLARE @月份 nchar(20)
SELECT @用水量=用水量,@月份=月份,@客户号=客户号
FROM INSERTED
SELECT @单位水价=单位水价,@用水类别=类别号
FROM 用水类型
BEGIN
	INSERT INTO 收费登记(客户号,月份,应收费用) 
	VALUES((select @客户号 from inserted),(select @月份 from inserted),(@用水量*@单位水价))
END

CREATE TRIGGER 用水消费记录_更改	
ON 用水信息管理
AFTER UPDATE
AS
DECLARE @客户号 varchar(10)
DECLARE @用水类别 varchar(10)
DECLARE @单位水价 float
DECLARE @用水量 float
DECLARE @水价 float
DECLARE @月份 nchar(20)
SELECT @用水量=用水量,@月份=月份,@客户号=客户号
FROM INSERTED
SELECT @单位水价=单位水价,@用水类别=类别号
FROM 用水类型
BEGIN
	UPDATE 收费登记 
	SET 应收费用=@用水量*@单位水价 
	WHERE @客户号=收费登记.客户号 AND @月份=收费登记.月份
END

CREATE TRIGGER 订单信息管理	
ON 订单管理
AFTER INSERT
AS
DECLARE @客户号 varchar(10)
DECLARE	@月份 nchar(20)
DECLARE @实收费用 float
DECLARE	@收取费用 float
DECLARE	@业务员号 varchar(10)
SELECT @客户号=客户号,@月份=月份,@收取费用=收取费用,@业务员号=业务员号
FROM INSERTED
SELECT @实收费用=实收费用 FROM 收费登记 where 客户号=@客户号 AND 月份=@月份
BEGIN
	IF (SELECT 收费标志 FROM 费用管理 WHERE 客户号=@客户号 AND 月份=@月份)='已收费'
		ROLLBACK
	ELSE
		UPDATE 收费登记 SET 业务员=@业务员号,@实收费用=@实收费用+@收取费用,实收费用=@实收费用 
		where @客户号=收费登记.客户号 and @月份=收费登记.月份	
		print(@实收费用)
END

/* 规则 */
CREATE RULE 月份			
AS
@月份 like'%年%月'

/* 客户费用管理统计存储过程 */
CREATE PROCEDURE 客户费用管理统计								
@月份 nchar(20)
AS
SELECT 收费登记.客户号,收费登记.应收费用,收费登记.实收费用,费用管理.收费标志
FROM 收费登记,费用管理
WHERE 收费登记.月份=@月份 AND 收费登记.客户号=费用管理.客户号 AND 费用管理.月份=@月份

/* 客户欠费统计存储过程 */
CREATE PROCEDURE 客户欠费统计									
@月份 nchar(20)
AS
SELECT 费用管理.客户号,客户信息.客户名,(应收费用实收费用) 拖欠费用
FROM 费用管理,客户信息,收费登记
WHERE 客户信息.客户号=费用管理.客户号 AND 费用管理.客户号=收费登记.客户号 AND 收费登记.月份=费用管理.月份 AND 费用管理.月份=@月份

创建用户：

第一步，点击新建登录名；

第二步，设定登录名和密码；

第三步，勾选所有的权限，并勾选自来水公司收费管理系统的数据库。

PyCharm连接数据库

首先，在PyCharm中引入pymssql库，作用是可以使用SQL语句。

方法：点击工具栏左上角File，点击Settings，点击Project，点击Python Interpreter，点击左上角加号，搜索pymssql然后安装。

之后，连接数据库：

import pymssql

connect = pymssql.connect('(local)', 'MQiyirs', '123456', 'Tap water charge')  # 建立连接
if connect:
    print("连接自来水公司收费管理系统成功!")
     # 代码内容
connect.close()

这里的(local)是服务器名称, MQiyirs是登录名, 123456是密码, Tap water charge是数据库名称。

下面是完整python代码（中文乱码已解决）：

import pymssql

connect = pymssql.connect('(local)', 'MQiyirs', '123456', 'Tap water charge')  # 建立连接
if connect:
    print("连接华北自来水公司收费管理系统成功!")

    print("选择您要办的业务编号：1.客户信息管理 2.用水类型管理 3.业务员信息管理 4.用水登记 5.订单登记 6.未缴费信息")
    key = int(input())

    if key == 1:
        print("选择您要办的业务编号：1.增加信息 2.删除信息 3.更改信息 4.查询信息")
        key1 = int(input())
        if key1 == 1:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            sql = 'insert into 客户信息(客户号,客户名,结余金额) values(%s,%s,%s)'
            print("客户号:")
            a = input()
            print("客户名:")
            b = input()
            print("结余金额:")
            c = input()
            cursor.execute(sql, (a, b, c))
            connect.commit()
            cursor.close()

        elif key1 == 2:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            print("客户号:")
            a = input()
            rows = cursor.execute('delete from 户信息 where 客户号=%s ', a)
            connect.commit()  # 确认
            cursor.close()

        elif key1 == 3:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            print("客户号:")
            a = input()
            print("结余金额:")
            b = input()
            rows = cursor.execute('update 客户信息 set 结余金额=%s where 客户号=%s' % (b, a))
            connect.commit()
            cursor.close()

        elif key1 == 4:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            cursor.execute("select 客户号, 客户名, 结余金额 from 客户信息")  # 执行sql语句
            row = cursor.fetchone()  # 读取查询结果
            while row:  # 循环读取所有结果
                print("客户号:%s,客户名:%s,结余金额:%s" % (row[0], row[1], row[2]))  # 输出结果
                row = cursor.fetchone()
            cursor.close()

        else:
            print("未知错误！")

    elif key == 2:
        print("选择您要办的业务编号：1.增加信息 2.删除信息 3.更改信息 4.查询信息")
        key2 = int(input())

        if key2 == 1:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            sql = 'insert into 用水类型(类别号,类别名,单位水价) values(%s,%s,%s)'
            print("类别号:")
            a = input()
            print("类别名:")
            b = input()
            print("单位水价:")
            c = input()
            cursor.execute(sql, (a, b, c))
            connect.commit()
            cursor.close()

        elif key2 == 2:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            print("类别号:")
            a = input()
            rows = cursor.execute('delete from 用水类型 where 类别号=%s', a)
            connect.commit()  # 确认
            cursor.close()

        elif key2 == 3:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            print("类别号:")
            a = input()
            print("单位水价:")
            b = input()
            rows = cursor.execute('update 用水类型 set 单位水价=%s where 类别号=%s' % (b, a))
            connect.commit()
            cursor.close()

        elif key2 == 4:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            cursor.execute("select 类别号, 类别名, 单位水价 from 用水类型")  # 执行sql语句
            row = cursor.fetchone()  # 读取查询结果
            while row:  # 循环读取所有结果
                print("类别号:%s,类别名:%s,水价:%s" % (row[0], row[1], row[2]))  # 输出结果
                row = cursor.fetchone()
            cursor.close()

        else:
            print("未知错误！")

    elif key == 3:
        print("选择您要办的业务编号：1.增加信息 2.删除信息 3.查询信息")
        key3 = int(input())

        if key3 == 1:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            sql = 'insert into 业务员信息(业务员号,业务员名) values(%s,%s)'
            print("业务员号:")
            a = input()
            print("业务员名:")
            b = input()
            cursor.execute(sql, (a, b))
            connect.commit()
            cursor.close()

        elif key3 == 2:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            print("业务员号:")
            a = input()
            rows = cursor.execute('delete from 业务员信息 where 业务员号=%s', a)
            connect.commit()  # 确认
            cursor.close()

        elif key3 == 3:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            cursor.execute("select 业务员号, 业务员名 from 业务员信息")  # 执行sql语句
            row = cursor.fetchone()  # 读取查询结果
            while row:  # 循环读取所有结果
                print("业务员号:%s,业务员名:%s" % (row[0], row[1]))  # 输出结果
                row = cursor.fetchone()
            cursor.close()

        else:
            print("未知错误！")

    elif key == 4:
        print("选择您要办的业务编号：1.增加信息 2.查询信息 3.删除信息")
        key4 = int(input())

        if key4 == 1:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            sql = 'insert into 用水信息管理(客户号,月份,用水类别号,用水量) values(%s,%s,%s,%s)'
            print("客户号:")
            a = input()
            print("月份:")
            b = input()
            print("用水类别号:")
            c = input()
            print("用水量:")
            d = input()
            cursor.execute(sql, (a, b, c, d))
            connect.commit()
            cursor.close()

        elif key4 == 2:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            cursor.execute("select 客户号, 月份, 用水类别号, 用水量 from 用水信息管理")  # 执行sql语句
            row = cursor.fetchone()  # 读取查询结果
            while row:  # 循环读取所有结果
                print("客户号:%s,月份:%s,用水类别号:%s,用水量:%s" % (row[0], row[1], row[2], row[3]))  # 输出结果
                row = cursor.fetchone()
            cursor.close()

        elif key4 == 3:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            print("客户号:")
            a = input()
            print("月份:")
            b = input()
            rows = cursor.execute('delete from 用水信息管理 where 客户号=%s and 月份=%s', (a, b))
            connect.commit()  # 确认
            cursor.close()

        else:
            print("未知错误！")

    elif key == 5:
        print("选择您要办的业务编号：1.增加信息 2.查询信息 3.删除信息")
        key5 = int(input())

        if key5 == 1:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            sql = 'insert into 订单管理(订单号,客户号,月份,收取费用,业务员号) values(%s,%s,%s,%s,%s)'
            print("订单号:")
            a = input()
            print("客户号:")
            b = input()
            print("月份:")
            c = input()
            print("收取费用:")
            d = input()
            print("业务员号:")
            e = input()
            cursor.execute(sql, (a, b, c, d, e))
            connect.commit()
            cursor.close()

        elif key5 == 2:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            cursor.execute("select 订单号,客户号,月份,收取费用,业务员号 from 订单管理")  # 执行sql语句
            row = cursor.fetchone()  # 读取查询结果
            while row:  # 循环读取所有结果
                print("订单号:%s,客户号:%s,月份:%s,收取费用:%s,业务员号:%s" % (row[0], row[1], row[2], row[3], row[4]))  # 输出结果
                row = cursor.fetchone()
            cursor.close()

        elif key5 == 3:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            print("订单号:")
            a = input()
            rows = cursor.execute('delete from 订单管理 where 订单号:=%s', a)
            connect.commit()  # 确认
            cursor.close()

        else:
            print("未知错误！")

    elif key == 6:
        print("选择您要办的业务编号：1.查询信息 2.删除信息")
        key6 = int(input())

        if key6 == 1:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            cursor.execute("select 客户号,月份,费用,收费标志 from 费用管理 where 收费标志='未收费'")  # 执行sql语句
            row = cursor.fetchone()  # 读取查询结果
            while row:  # 循环读取所有结果
                print("客户号:%s,月份:%s,费用:%s,收费标志:%s" % (row[0], row[1], row[2], row[3]))  # 输出结果
                row = cursor.fetchone()
            cursor.close()

        elif key6 == 2:
            cursor = connect.cursor()  # 创建一个游标对象python里的sql语句都要通过cursor来执行
            print("客户号:")
            a = input()
            print("月份:")
            b = input()
            rows = cursor.execute('delete from 用水信息管理 where 客户号=%s and 月份=%s', (a, b))
            connect.commit()  # 确认
            cursor.close()

        else:
            print("未知错误！")

    else:
        print("未知错误！")

connect.close()