oracle sql script:
drop table IF EXISTS GEOVINDU.School;
create table GEOVINDU.School --創建表
(
SchoolId char(5) NOT NULL, --
SchoolName varchar(500) NOT NULL,
SchoolTelNo varchar(8) NULL,
PRIMARY KEY (SchoolId) --#主鍵
);
--对表的说明
comment on table GEOVINDU.School is '学校表';
--对表中列的说明
comment on column GEOVINDU.School.SchoolId is 'ID';
comment on column GEOVINDU.School.SchoolName is '名称';
comment on column GEOVINDU.School.SchoolTelNo is '电话号码';
select * from GEOVINDU.School order by SchoolId;
代码自己写的生成器生成。
MODEL:
# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author : geovindu,Geovin Du 涂聚文.
# IDE : PyCharm 2023.1 python 3.11
# OS : windows 10
# database : mysql 9.0 sql server 2019, poostgreSQL 17.0 oracle 11g
# Datetime : 2024-11-20 14:35:21
# database ：sql server 2019
# User : geovindu
# Product : PyCharm
# Project : IctGame
# File : model/School.py
# explain : 学习
class SchoolInfo(object):
"""
学校表
"""
def __init__(self):
"""
構造
"""
self._SchoolId = None
"""
ID，主键
"""
self._SchoolName = None
"""
校名
"""
self._SchoolTelNo = None
"""
学校电话
"""
@property
def SchoolId(self):
"""
ID，主键
"""
return self._SchoolId
@SchoolId.setter
def SchoolId(self, schoolId):
"""
ID，主键
:param SchoolId:
:return:
"""
self._SchoolId = schoolId
@property
def SchoolName(self):
"""
校名
"""
return self._SchoolName
@SchoolName.setter
def SchoolName(self, schoolName):
"""
校名
:param SchoolName:
:return:
"""
self._SchoolName = schoolName
@property
def SchoolTelNo(self):
"""
学校电话
"""
return self._SchoolTelNo
@SchoolTelNo.setter
def SchoolTelNo(self, schoolTelNo):
"""
学校电话
:param SchoolTelNo:
:return:
"""
self._SchoolTelNo = schoolTelNo
IDAL:
# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author : geovindu,Geovin Du 涂聚文.
# IDE : PyCharm 2023.1 python 3.11
# OS : windows 10
# database : mysql 9.0 sql server 2019, poostgreSQL 17.0 oracle 11g
# Datetime : 2024-11-20 14:35:21
# database ：sql server 2019
# User : geovindu
# Product : PyCharm
# Project : IctGame
# File : interface/School.py
# explain : 学习
from __future__ import annotations
from abc import ABC, abstractmethod
import os
import sys
from model.school import SchoolInfo
class ISchool(ABC):
"""
学校表
"""
@classmethod
def __subclasshook__(cls, subclass):
return (hasattr(subclass, 'load_data_source') and
callable(subclass.load_data_source) and
hasattr(subclass, 'extract_text') and
callable(subclass.extract_text) or
NotImplemented)
@abstractmethod
def selectSql(cls) -> list:
"""
:return:
"""
pass
@abstractmethod
def selectSqlCount(cls) -> list:
"""
查询数据 总数
:return:
"""
pass
@abstractmethod
def selectSqlOrder(cls, order: str) -> list:
"""
:param order:
:return:
"""
pass
@abstractmethod
def selectSort(cls,field:str,isOrder:bool)->list:
"""
:param field
:param order: desc/asc
:return:
"""
pass
@abstractmethod
def selectIdSql(cls, schoolId: str):
"""
:param SchoolId:
:return:
"""
pass
@abstractmethod
def selectProc(cls):
"""
:return:
"""
pass
@abstractmethod
def selectIdProc(cls, schoolId):
"""
:param SchoolId:
:return:
"""
pass
@abstractmethod
def addSql(cls, info: SchoolInfo):
"""
:param info:
:return:
"""
pass
@abstractmethod
def addProc(cls, info: SchoolInfo):
"""
:param info:
:return:
"""
pass
@abstractmethod
def addOutProc(cls, info: SchoolInfo):
"""
:param info:
:return:
"""
pass
@abstractmethod
def editSql(cls, info: SchoolInfo):
"""
:param info:
:return:
"""
pass
@abstractmethod
def editProc(cls, info: SchoolInfo):
"""
:param info:
:return:
"""
pass
@abstractmethod
def delSql(cls, schoolId):
"""
:param SchoolId:
:return:
"""
pass
@abstractmethod
def delProc(cls, schoolId):
"""
:param SchoolId:
:return:
"""
pass
DAL:
# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author : geovindu,Geovin Du 涂聚文.
# IDE : PyCharm 2023.1 python 3.11
# OS : windows 10
# database : mysql 9.0 sql server 2019, poostgreSQL 17.0 oracle 11g
# Datetime : 2024-11-20 14:35:21
# database ：sql server 2019
# User : geovindu
# Product : PyCharm
# Project : IctGame
# File : dal/School.py
# explain : 学习
from __future__ import annotations
from abc import ABC, abstractmethod
import os
import sys
from model.school import SchoolInfo
from DBUtility.oracleHelper import OracleHelper
from interface.school import ISchool
class SchoolDal(ISchool):
"""
学校表
"""
myms = OracleHelper()
def __init__(self):
"""
构造函数，方法
:param strserver:
:param struser:
:param strpwd:
:param strdatabase:
"""
self._strserver = ""
self._struser = ""
self._strpwd = ""
self._strdatabase =""
def selectSql(cls)->list:
"""
查询数据 self._strserver, self._struser, self._strpwd, self._strdatabase
:return:
"""
row=cls.myms.execute("select * from School")
#cls.myms.close()
return row
def selectSqlCount(cls)->list:
"""
查询数据 总数
:return:
"""
row=cls.myms.execute("select count(*) as total from School")
#cls.myms.close()
return row[0]
def selectSqlOrder(cls,order:str)->list:
"""
:param order: SchoolId desc/asc
:return:
"""
students=[]
strsql=f"select * from School order by {order}"
row=cls.myms.execute(f"select * from School order by {order}")
return row
def selectSort(cls,field:str,isOrder:bool)->list:
"""
:param field SchoolId
:param order: desc/asc
:return:
"""
order='desc'
if isOrder==True:
order='desc'
else:
order='asc'
strsql=f"select * from School order by {field} {order}"
row=cls.myms.execute(f"select * from School order by {field} {order}")
#cls.myms.close()
return row
def selectIdSql(cls,schoolId:str)->list:
"""
:param SchoolId: 主键ID
:return:
"""
row=cls.myms.execute(f"select * from School where SchoolId=schoolId") #考虑数据类型
#cls.myms.close()
return row
def selectProc(cls)->list:
"""
存储过程
:return:
"""
args = ()
row = cls.myms.executeCallProc("procSelectSchoolAll",args)
return row
def selectIdProc(cls,SchoolId:str)->list:
"""
存储过程
:param SchoolId: 主键ID
:return:
"""
args = (SchoolId,)
row = cls.myms.executeCallProc('procSelectSchool', args)
return row
def addSql(cls,info:SchoolInfo)->int:
"""
添加，要考虑添加返回ID值
:param info:实体类
:return:
"""
column=("SchoolId","SchoolName","SchoolTelNo")
vales=[info.SchoolId,info.SchoolName,info.SchoolTelNo]
return cls.myms.insertByColumnaAndValues("School",column,vales)
def addProc(cls,info:SchoolInfo)->int:
"""
添加，要考虑添加返回ID值
:param info:实体类
:return:
"""
args=[info.SchoolId,info.SchoolName,info.SchoolTelNo]
return cls.myms.insertCallProc("procInsertSchool",args)
def addOutProc(cls,info:SchoolInfo) -> int:
"""
添加，要考虑添加返回ID值
:param info:实体类
:return: 返回增加的ID
"""
id = 0
try:
outSchoolId =('char',) #输出，元组类型 考虑数据类型去转换
print(info)
args = [info.SchoolName,info.SchoolTelNo,outSchoolId]
print(args)
result=cls.myms.insertOutCallProc("procInsertSchoolOutput", args)
print(result)
id = result
except Exception as ex:
print(ex)
return id
def editSql(cls,info:SchoolInfo)->int:
"""
:param info:实体类
:return:
"""
args = {"SchoolId":f"{info.SchoolId}","SchoolName":f"{info.SchoolName}","SchoolTelNo":f"{info.SchoolTelNo}"}
where = f"SchoolId={info.SchoolId}" #
#print(args,where)
return cls.myms.updateByKeyValues("School",where,args)
def editProc(cls, info: SchoolInfo)->int:
"""
:param info: 实体类
:return:
"""
args = [info.SchoolId,info.SchoolName,info.SchoolTelNo]
return cls.myms.updateProc("procUpdateSchool",args)
def delSql(cls,SchoolId:str)->int:
"""
sql语句删除
:param SchoolId: 主键ID
:return:
"""
where={f"SchoolId":SchoolId}
return cls.myms.deleteByKeyValues("School",where)
def delProc(cls, SchoolId:str)->int:
"""
删除 存储过程 删除多个ID，后面增加
:param SchoolId: 主键ID
:return:
"""
args =SchoolId
k=cls.myms.deleteProc("procDuDeleteSchool", args)
return k
BLL:
# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author : geovindu,Geovin Du 涂聚文.
# IDE : PyCharm 2023.1 python 3.11
# OS : windows 10
# database : mysql 9.0 sql server 2019, poostgreSQL 17.0 oracle 11g
# Datetime : 2024-11-20 14:35:21
# database ：sql server 2019
# User : geovindu
# Product : PyCharm
# Project : IctGame
# File : bll/School.py
# explain : 学习
from __future__ import annotations
from abc import ABC, abstractmethod
import os
import sys
from model.school import SchoolInfo
from factory.AbstractFactory import AbstractFactory
class SchoolBll(object):
"""
学校表
"""
dal=AbstractFactory.createSchool
"""
类属性 接口DAL
"""
def __init__(self):
"""
"""
self.__name = "SchoolBll"
def __del__(self):
print(f"{self.__name} ERASE MEMORY")
def selectData(self) -> list:
"""
:return:
"""
data = self.dal().selectSql()
return data
def select(self) -> list[SchoolInfo]:
"""
:return:
"""
schools = []
data = self.dal().selectSql()
if len(data) > 0:
for SchoolId,SchoolName,SchoolTelNo in data[0]:
info = SchoolInfo()
info.SchoolId = SchoolId
info.SchoolName = SchoolName
info.SchoolTelNo = SchoolTelNo
schools.append(info)
return schools
def selectSql(cls) -> list[SchoolInfo]:
"""
元组数据
:return: list 列表
"""
schools = []
data = cls.dal().selectSql()
if len(data) > 0:
for SchoolId,SchoolName,SchoolTelNo in data[0]:
info=SchoolInfo()
info.SchoolId = SchoolId
info.SchoolName = SchoolName
info.SchoolTelNo = SchoolTelNo
schools.append(info)
return schools
def selectSqlCount(cls) -> int:
"""
查询数据 总数
:return:
"""
#print(cls.dal().selectSqlCount()[0][0])
total=cls.dal().selectSqlCount()[0][0]
return total
def Count(self) -> int:
"""
查询数据 总数
:return:
"""
total=self.dal().selectSqlCount()[0][0]
return total
def selectSqlOrder(cls, order: str) -> list[SchoolInfo]:
"""
元组数据
:param order: SchoolName desc/asc
:return:
"""
schools=[]
data = cls.dal().selectSqlOrder(order)
if len(data) > 0:
for SchoolId,SchoolName,SchoolTelNo in data[0]:
info=SchoolInfo()
info.SchoolId = SchoolId
info.SchoolName = SchoolName
info.SchoolTelNo = SchoolTelNo
schools.append(info)
return schools
def selectSort(cls,field:str,isOrder:bool)->list[SchoolInfo]:
"""
:param field SchoolId
:param order: desc/asc
:return:
"""
schools=[]
data = cls.dal().selectSort(field,isOrder)
if len(data) > 0:
for SchoolId,SchoolName,SchoolTelNo in data[0]:
info = SchoolInfo()
info.SchoolId = SchoolId
info.SchoolName = SchoolName
info.SchoolTelNo = SchoolTelNo
schools.append(info)
return schools
def selectIdSql(cls,SchoolId:str) -> list[SchoolInfo]:
"""
:param SchoolId:ID
:return:
"""
schools = []
data = cls.dal().selectIdSql(SchoolId)
#print(data)
if len(data)>0:
for SchoolId,SchoolName,SchoolTelNo in data[0]:
info = SchoolInfo()
info.SchoolId = SchoolId
info.SchoolName = SchoolName
info.SchoolTelNo = SchoolTelNo
schools.append(info)
return schools
def selectProc(cls) -> list[SchoolInfo]:
"""
:return:
"""
schools=[]
data = cls.dal().selectProc()
#print(data)
if len(data) > 0:
for SchoolId,SchoolName,SchoolTelNo in data:
info = SchoolInfo()
info.SchoolId = SchoolId
info.SchoolName = SchoolName
info.SchoolTelNo = SchoolTelNo
schools.append(info)
return schools
def selectIdProc(cls,SchoolId:str) -> list[SchoolInfo]:
"""
:param SchoolId:
:return:
"""
schools = []
data = cls.dal().selectIdProc(SchoolId)
if len(data) > 0:
for SchoolId,SchoolName,SchoolTelNo in data:
info = SchoolInfo()
info.SchoolId = SchoolId
info.SchoolName = SchoolName
info.SchoolTelNo = SchoolTelNo
schools.append(info)
return schools
def addSql(cls,info:SchoolInfo) -> int:
"""
:param info:实体类
:return:
"""
return cls.dal().addSql(info)
def add(self,info:SchoolInfo) -> int:
"""
:param info:实体类
:return:
"""
return self.dal().addSql(info)
def addProc(cls,info:SchoolInfo) -> int:
"""
:param info:实体类
:return:
"""
#print(info)
return cls.dal().addProc(info)
def addOutProc(cls,info:SchoolInfo)->int:
"""
:param info: 实体类
:return: 返回增加的ID
"""
print(info)
return cls.dal().addOutProc(info)
def editSql(cls,info:SchoolInfo) -> int:
"""
:param info:实体类
:return:
"""
#print(info)
return cls.dal().editSql(info)
def edit(self,info:SchoolInfo) -> int:
"""
:param info:实体类
:return:
"""
#print(info)
return self.dal().editSql(info)
def editProc(cls, info: SchoolInfo) -> int:
"""
:param info:实体类
:return:
"""
return cls.dal().editProc(info)
def delSql(cls, SchoolId: str) -> int:
"""
:param SchoolId:
:return:
"""
return cls.dal().delSql(SchoolId)
def delinfo(self, SchoolId: str) -> int:
"""
:param SchoolId:
:return:
"""
return self.dal().delSql(SchoolId)
def delProc(cls, SchoolId:str) -> int:
"""
:param SchoolId:
:return:
"""
return cls.dal().delProc(SchoolId)
gui:
# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author : geovindu,Geovin Du 涂聚文.
# IDE : PyCharm 2023.1 python 3.11
# OS : windows 10
# database : mysql 9.0 sql server 2019, poostgreSQL 17.0 oracle 11g
# Datetime : 2024/12/24 20:28
# User : geovindu
# Product : PyCharm
# Project : pyOracleDemo
# File : main.py
# explain : 学习
import bll
from bll.school import SchoolBll
from model.school import SchoolInfo
import ttkbootstrap as ttk
from ttkbootstrap.constants import *
from ttkbootstrap.tableview import Tableview
class MainWidnow(ttk.Window):
"""
"""
def __init__(self):
"""
"""
super().__init__(themename="cosmo", title="塗聚文學習進行中")
# self.Window(themename="cosmo") #superhero
self.maxsize = 300
# self.geometry('{}x{}'.format(1350, 900))
self.first_var = ttk.Variable()
self.title = "main"
self.themename = 'superhero'
self.last_var = ttk.Variable()
self.occupation_var = ttk.Variable()
self.colors = self.style.colors
self.coldata = [
{"text": "编号", "stretch": False},
"名称",
{"text": "电话", "stretch": False},
]
bl = bll.SchoolBll()
infos = bl.selectSql()
self.rowdata = []
for info in infos:
row=[]
row.append(info.SchoolId)
row.append(info.SchoolName)
row.append(info.SchoolTelNo)
self.rowdata.append(row)
self.dt = Tableview(
master=self,
coldata=self.coldata,
rowdata=self.rowdata,
paginated=True,
pagesize=15,
searchable=True,
bootstyle=PRIMARY,
stripecolor=(self.colors.light, None),
)
self.dt.pack(fill=BOTH, expand=YES, padx=10, pady=10)
# dt.hide_selected_column(cid=0) #隱藏第一列
self.dt.view.bind("<Double-1>", self.rowselected)
# dt.view.bind("<<TreeviewSelect>>", rowselected)
b1 = ttk.Button(self, text="Open", bootstyle="success") # ,command=self.openwindows
b1.pack(side=LEFT, padx=5, pady=10)
# b1.bind("<Double-1>",openwindows)
b1.bind("<Button-1>", self.openwindows)
b2 = ttk.Button(self, text="New", bootstyle="info-outline")
b2.pack(side=LEFT, padx=5, pady=10)
def rowselected(self, event) -> None:
try:
iid = self.dt.view.selection()[0]
# print(iid)
values = self.dt.view.item(iid, 'values')
self.first_var.set(values[0])
self.last_var.set(values[1])
self.occupation_var.set(values[2])
print(values[0], values[1], values[2])
data = [values[0], values[1], values[2]]
subwindow = ChildNewWindow(data)
except IndexError as err:
pass
def openwindows(self, event):
"""
"""
try:
print('open windows')
iid = self.dt.view.selection()[0]
values = self.dt.view.item(iid, 'values')
data = [values[0], values[1], values[2]]
subwindow = ChildNewWindow(data)
self.update()
except IndexError as err:
pass
class ChildNewWindow(ttk.Window):
"""
彈出子窗口 ttk.Toplevel
"""
def __init__(self, data):
"""
:param master:
"""
super().__init__(title='Child Window')
self.geometry('{}x{}'.format(850, 900))
self.title = 'Child Window'
self.label = ttk.Label(self, text=data[0])
self.label.pack()
self.labe2 = ttk.Label(self, text=data[1])
self.labe2.pack()
self.labe3 = ttk.Label(self, text=data[2])
self.labe3.pack()
if __name__ == '__main__':
print('PyCharm')
mainwindow = MainWidnow()
mainwindow.mainloop()
bl=bll.SchoolBll()
infos=bl.selectSql()
for info in infos:
print(info.SchoolId,info.SchoolName,info.SchoolTelNo)
输出：
标签：info,SchoolId,return,python,self,oracle,query,def,cls From： https://www.cnblogs.com/geovindu/p/18628664