python: sql server using Model,BLL,DAL

sql script:

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

/*
学生类:姓名、年龄、学号、成绩
 
班级类:班级名称、学生列表显示所有学生
根据学号查找学生
添加一个学生
删除一个学生(学生对象、学号)
根据学号升序排序
根据成绩降序排序
 
*/
 
--学生表
DROP TABLE StudentList
GO
create table StudentList
(
     StudentId INT IDENTITY(1,1) PRIMARY KEY,
     StudentName nvarchar(50),
     StudentNO varchar(50),                    --学号
     StudentBirthday datetime                  --学生生日
      
 
)
go
 
insert into StudentList(StudentName,StudentNO,StudentBirthday) values(N'刘三','001','2007-12-27')
go
insert into StudentList(StudentName,StudentNO,StudentBirthday) values(N'王二','002','2008-2-14')
go
 
select * from StudentList
go
 
 
--课程名称
DROP TABLE Course
GO
create table Course
(
   CourseId INT IDENTITY(1,1) PRIMARY KEY,
   CourseName nvarchar(50)                  --课程名称
)
go
 
insert into Course(CourseName) values(N'语文')
go
insert into Course(CourseName) values(N'英语')
go
insert into Course(CourseName) values(N'数学')
go
 
select * from Course
go
 
 
 
 
--班表名称
DROP TABLE GradeClass
GO
create table GradeClass
(
    ClassId INT IDENTITY(1,1) PRIMARY KEY,
    ClassName nvarchar(50)              --班级名称
)
go
 
insert into GradeClass(ClassName) values(N'二年级一班')
go
 
insert into GradeClass(ClassName) values(N'二年级二班')
go
 
select * from GradeClass
go
 
 
 
--成绩表
DROP TABLE StudentScore
GO
create table StudentScore
(
     ScoreId INT IDENTITY(1,1) PRIMARY KEY,
     StudentId int
        Foreign Key REFERENCES StudentList(StudentId),  --学生ID 外键
     CourseId int
        Foreign Key REFERENCES Course(CourseId),      --课程ID 外键
     Score float                                      --成绩
)
go
 
insert into StudentScore(StudentId,CourseId,Score) values(1,1,90)
insert into StudentScore(StudentId,CourseId,Score) values(1,2,56)
insert into StudentScore(StudentId,CourseId,Score) values(1,3,80)
go
 
insert into StudentScore(StudentId,CourseId,Score) values(2,1,92)
insert into StudentScore(StudentId,CourseId,Score) values(2,2,83)
insert into StudentScore(StudentId,CourseId,Score) values(2,3,78)
go
 
select * from StudentScore
go
 
 
 
 
---班级学生
DROP TABLE StudentClass
GO
create table StudentClass
(
    StudentClassId INT IDENTITY(1,1) PRIMARY KEY,
    StudentId int
        Foreign Key REFERENCES StudentList(StudentId),   --学生ID 外键
    ClassId int
        Foreign Key REFERENCES GradeClass(ClassId),     --班级ID 外键
)
go
 
insert into StudentClass(StudentId,ClassId) values(1,1)
go
insert into StudentClass(StudentId,ClassId) values(2,1)
go
select * from StudentClass
go
 
-- 成绩视图
select a.*,b.StudentNO,b.StudentName,b.StudentBirthday,c.CourseName from StudentScore as a--,as a,StudentList as b,Course as c StudentClass as d,GradeClass as f  
LEFT JOIN StudentList as b on a.StudentId=b.StudentId 
LEFT JOIN Course as c on a.CourseId =c.CourseId
LEFT JOIN StudentClass as d on d.StudentId=b.StudentId
go
 
-- 班级视图
select a.*,b.StudentNO,b.StudentNO,b.StudentName,d.ClassName from StudentClass as a
left join StudentList as b on a.StudentId=b.StudentId
left join GradeClass as d on a.ClassId=d.ClassId
go

"""
StudentListInfo.py
学生类
date 2023-06-16
edit: Geovin Du,geovindu, 涂聚文
ide:  PyCharm 2023.1 python 11
"""
 
import datetime
import sys
import os
 
class StudentList(object):
    """
    学生类
    """
    def __init__(self,StudentName:str, StudentNO:str,StudentBirthday:datetime.datetime):
        self._StudentName=StudentName
        self._StudentNO=StudentNO
        self._StudentBirthday=StudentBirthday
        self._StudentId=0
        self._age=0
 
    def __del__(self):
        print(f"{self._StudentName}")
 
    def setStudentName(self,StudentName):
        self._StudentName=StudentName
 
    def getStudentName(self):
        return self._StudentName
 
    def setStudentNO(self,StudentNO):
        self._StudentNO=StudentNO
 
    def getStudentNO(self):
        return self._StudentNO
 
    def setStudentId(self,StudentId):
        self._StudentId=StudentId
 
    def getStudentId(self):
        return  self._StudentId
 
    def setStudentBirthday(self,StudentBirthday):
        self._StudentBirthday=StudentBirthday
 
    def setAge(self,age):
        self._age=age
 
    def getAge(self):
        return  self._age
 
    def __str__(self):
        return f"{self._StudentId},{self._StudentName},{self._StudentNO},{self._StudentBirthday}{self._age}"
 
 
 
"""
CourseInfo.py
课程类
date 2023-06-16
edit: Geovin Du,geovindu, 涂聚文
ide:  PyCharm 2023.1 python 11
"""
 
class Course(object):
    """
 
    """
 
    def __init__(self,CourseName:str):
        self._CourseName=CourseName
        self._CourseId=0
 
    def __del__(self):
        print(f"{self._CourseName}")
 
    def setCourseId(self,CourseId):
        self._CourseId=CourseId
 
    def getCourseId(self):
        return self._CourseId
 
    def setCourseName(self,CourseName):
        self._CourseName=CourseName
 
    def getCourseName(self):
        return self._CourseName
 
    def __str__(self):
        return f"CourseId:{self._CourseId},CourseName:{self._CourseName}"

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

"""
StudentDALListDAL.py
数据业务处理层 Data Access Layer (DAL)
SQL Server 数据库操作
date 2023-06-21
edit: Geovin Du,geovindu, 涂聚文
ide: PyCharm 2023.1 python 11
"""
 
import os
import sys
from pathlib import Path
import re
import pymssql  #sql server
import Model.StudentListInfo
import UtilitieDB.MsSQLHelper
 
class StudentDal(object):
    """
    数据业务处理层  学生
    数据库连接可以放在这里，通过配置读取数据连接参数
    """
 
    def __init__(self):
        """
        构造函数，方法
        :param strserver:
        :param struser:
        :param strpwd:
        :param strdatabase:
        """
        self._strserver = ""
        self._struser = ""
        self._strpwd = ""
        self._strdatabase =""
 
    def selectSql(self):
        """
        查询数据 self._strserver, self._struser, self._strpwd, self._strdatabase
        :return:
        """
 
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper()
        row=myms.execute('select * from DuStudentList;')
        return row
 
    def selectSqlOrder(self,order:str)->list:
        """
 
        :param order:  studenName desc/asc
        :return:
        """
        students=[]
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper()
        strsql=f"select * from DuStudentList order by {order};"
        row=myms.execute(f'select * from DuStudentList order by {order};')
        return row
 
    def selectIdSql(self,StudentId:int):
        """
 
        :param StudentId: 主键ID
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper()
        row=myms.execute(f'select * from DuStudentList where StudentId={StudentId};')
        return row
    def selectProc(self):
        """
        存储过程
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper()
        args = ()
        row = myms.executeCallProc("proc_Select_StudentListAll",args)
        return row
 
    def selectIdProc(self,StudentId:int):
        """
        存储过程
        :param StudentId: 主键ID
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper()
        args = (StudentId,)
        row = myms.executeCallProc('dbo.proc_Select_StudentList', args)
        return row
 
    def addSql(self,info:Model.StudentListInfo.StudentList):
        """
        添加，要考虑添加返回ID值
        :param info:学生实体类
        :return:
        """
        myms=UtilitieDB.MsSQLHelper.MsSqlHelper();
        column=("StudentName","StudentNO","StudentBirthday")
        vales=[info.getStudentName(),info.getStudentNO(),info.getStudentBirthday()]
        myms.insertByColumnaAndValues("dbo.DuStudentList",column,vales)
 
    def addProc(self,info:Model.StudentListInfo.StudentList):
        """
        添加，要考虑添加返回ID值
        :param info:学生实体类
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        args=(info.getStudentName(),info.getStudentNO(),info.getStudentBirthday())
        myms.insertCallProc("dbo.proc_Insert_StudentList",args)
 
    def addOutProc(self,info:Model.StudentListInfo.StudentList):
        """
        添加，要考虑添加返回ID值
        :param info:学生实体类
        :return: 返回增加的学生的ID
        """
        id=0
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        outid = pymssql.output(int)
        args = (info.getStudentName(), info.getStudentNO(), info.getStudentBirthday(),outid)
        print(args)
        id=myms.insertOutCallProc("dbo.proc_Insert_StudentListOutput", args)
        return id
    def editSql(self,info:Model.StudentListInfo.StudentList):
        """
 
        :param info:学生实体类
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        args = {"StudentName":f"{info.getStudentName()}","StudentNO":f"{info.getStudentNO()}","StudentBirthday":f"{info.getStudentBirthday()}"}  #"StudentId":6
        where = f"StudentId={info.getStudentId()}" #
        #print(args,where)
        myms.updateByKeyValues("DuStudentList",where,args)
 
    def editProc(self, info: Model.StudentListInfo.StudentList):
        """
 
        :param info: 学生实体类
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        args = (info.getStudentId(),info.getStudentName(),info.getStudentNO(),info.getStudentBirthday())
        myms.updateProc("[dbo].[proc_Update_StudentList]",args)
 
 
    def delSql(self,StudentId:int):
        """
        sql语句删除
        :param StudentId: 主键ID
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        where={f"StudentId":StudentId}
        myms.deleteByKeyValues("DuStudentList",where)
 
    def delProc(self, studentId):
        """
        删除 存储过程 删除多个ID，后面增加
        :param StudentId: 主键ID
        :return:
        """
        myms = UtilitieDB.MsSQLHelper.MsSqlHelper();
        args =studentId
        myms.deleteProc("dbo.proc_Delete_StudentList",args)
 
 
 
"""
StudentListBLL.py
业务层 Business Logic Layer (BLL)
date 2023-06-19
edit: Geovin Du,geovindu, 涂聚文
ide:  PyCharm 2023.1 python 11
"""
 
import os
import sys
from pathlib import Path
import re
import pymssql  #sql server
from datetime import date
import DAL.StudentListDAL
import DAL.ConfigDAL
import Model.StudentListInfo
 
class StudentBll(object):
    """
    学生信息操作业务类
    """
 
    dal = DAL.StudentListDAL.StudentDal()
    """
    类属性 操作DAL
    """
    def __init__(self):
        """
 
        """
        self._name = "geovindu"
 
 
 
    def __del__(self):
        print(f"{self._name}挂失了")
 
    def selectSql(cls)->list:
        """
        元组数据
        :return: list 学生列表
        """
        students = []
        data = cls.dal.selectSql()
        stus = list(data)  # 如C# 强制转换
        '''
        for a in data:
            for i in a:
                print("II",i)
        '''
 
        for ii in stus:
            for i in ii:
                students.append(Model.StudentListInfo.StudentList(i[0],i[1],i[2],i[3]))
        return students
 
    def selectSqlOrder(cls, order: str)->list:
        """
        元组数据
        :param order: studenName desc/asc
        :return:
        """
        studentsorder = []
        students=[]
        data = cls.dal.selectSqlOrder(order)
        (studentsorder) = data # 如C# 强制转换
        '''
        for i in range(len(studentsorder)):
            print("rrr",type(studentsorder[i]))
            for duobj in studentsorder[i]:
                print(type(duobj))
                print(duobj)
        '''
        for obj in studentsorder:
            for i in obj:
                students.append(Model.StudentListInfo.StudentList(i[0], i[1], i[2], i[3]))
        return students
 
 
    def selectIdSql(cls,StudentId:int)->list:
        """
 
        :param StudentId:学生ID
        :return:
        """
        students = []
        data = cls.dal.selectIdSql(StudentId)
        students=data
        for ii in students:
            for i in ii:
                students.append(Model.StudentListInfo.StudentList(i[0],i[1],i[2],i[3]))
        return students
 
 
    def selectProc(cls):
        """
 
        :return:
        """
        students=[]
        data = cls.dal.selectProc()
        for i in data:
            students.append(Model.StudentListInfo.StudentList(i[0],i[1],i[2],i[3]))
        return students
 
    def selectIdProc(cls,StudentId:int)->list:
        """
 
        :param StudentId:
        :return:
        """
        students = []
        data = cls.dal.selectIdProc(StudentId)
        for i in data:
            students.append(Model.StudentListInfo.StudentList(i[0],i[1],i[2],i[3]))
        return students
 
    def addSql(cls,info:Model.StudentListInfo.StudentList):
        """
 
        :param info:学生实体类
        :return:
        """
        cls.dal.addSql(info)
 
    def addProc(cls,info:Model.StudentListInfo.StudentList):
        """
 
        :param info:学生实体类
        :return:
        """
        #print(info)
        cls.dal.addProc(info)
 
    def addOutProc(cls,info:Model.StudentListInfo.StudentList)->int:
        """
 
        :param info: 学生实体类
        :return: 返回增加的学生ID
        """
        print(info)
        return cls.dal.addOutProc(info)
 
    def editSql(cls,info:Model.StudentListInfo.StudentList):
        """
 
        :param info:学生实体类
        :return:
        """
        #print(info)
        cls.dal.editSql(info)
 
    def editProc(cls, info: Model.StudentListInfo.StudentList):
        """
 
        :param info:学生实体类
        :return:
        """
        cls.dal.editProc(info)
 
    def delSql(cls, StudentId: int):
        """
 
        :param StudentId:
        :return:
        """
        cls.dal.delSql(StudentId)
 
    def delProc(cls, StudentId):
        """
 
        :param StudentId:
        :return:
        """
        cls.dal.delProc(StudentId)