python query oracle database

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;

# 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       

# 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               

# 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            

# 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)            

# 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)