python: generate model and DAL using Oracle

drop table IF EXISTS School; 

create table School  --創建表
(
	SchoolId char(5) NOT NULL,  --  
    SchoolName nvarchar2(500) NOT NULL,
    SchoolTelNo  varchar(8)  NULL,        
  PRIMARY KEY (SchoolId)   --#主鍵
);

--对表的说明
comment on table School is '學校表';
--对表中列的说明
comment on column School.SchoolId is '學校編號';
comment on column School.SchoolName is '學校名稱';
comment on column School.SchoolTelNo is '電話號碼';

select * from School order by SchoolId;

# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : windows 10
# database  ：sql server 2019,mysql 9.0,postgetsql 17.0, oracle 21c
# Datetime  : 2024/12/28 22:41
# User      : geovindu
# Product   : PyCharm
# Project   : pyGenerator
# File      : oracleGenerator/CreateModel.py
# explain   : 学习

import os
import re
import sys
import io
import datetime
from common.fileHelper import FileHelper
from common.strHelper import StrHelper
from bll.tableNameAndKey import TableNameAndKeyBll
from bll.tableForegin import TableForeginBll
from bll.tableModel import TableModelBll


class CreateModel(object):
    """

    """

    def __init__(self):
        self.__modelpath = os.path.join(os.getcwd() + "/oracletest/model/")
        self.__file = FileHelper()
        self.__blltablekey = TableNameAndKeyBll()
        self.__bllTableModel = TableModelBll()
        self.__database = "Oracle21c"
        self.__os = "windows 10"
        self.__product = "PyCharm"
        self.__project = "IctGame"
        self.__name = "CreateModel"
        pass

    def __del__(self):
        """

        :return:
        """
        print(f"{self.__name} ERASE MEMORY")
    def addpackagefile(self):
        """
        生成包配置文件
        :return:
        """
        now = datetime.datetime.now()
        #bll = TableNameAndKeyBll()
        sb = []
        sbt = f"""
# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : windows 10
# Datetime  : {now.strftime('%Y-%m-%d %H:%M:%S')}
# database  ：{self.__database}
# User      : geovindu
# Product   : {self.__project}
# Project   : {self.__project}    
# File      : model/__init__.py
# explain   : 学习
                    """
        sb.append(sbt)
        for li in self.__blltablekey.selectSql():
            sbp=f"""
from model.{StrHelper.firstLower(li.TableName)} import {StrHelper.firstUper(li.TableName)}Info
"""
            sb.append(sbp)
        if not os.path.exists(self.__modelpath):
            os.makedirs(self.__modelpath)
        filepath = self.__modelpath + '__init__.py'
        content = ''.join(sb)
        ok = self.__file.createModelFile(filepath, content)



    def add(self):
        """
        生成实体
        :return:
        """
        #fileHelper=FileHelper()
        now = datetime.datetime.now()
        #bll = TableNameAndKeyBll()
        for li in self.__blltablekey.selectSql():
            sb = []
            sbt=f"""
# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : windows 10
# Datetime  : {now.strftime('%Y-%m-%d %H:%M:%S')}
# database  ：{self.__database}
# User      : geovindu
# Product   : PyCharm
# Project   : IctGame
# File      : model/{li.TableName}.py
# explain   : 学习
                    
class {StrHelper.firstUper(li.TableName)}Info(object):
    \"""
    {li.TableDescription}                    
    \""" 
"""
            sb.append(sbt)
            #tbll = TableModelBll()
            sinhead = f"""
    def __init__(self):
        \"""
            構造
        \"""               

              """
            sb.append((sinhead))
            for tli in self.__bllTableModel.selectSql(li.TableName):
                sin=f"""
        self._{tli.FieldName} = None
        \"""
        {tli.FieldDescription}
        \"""
        """
                sb.append(sin)
            for pli in self.__bllTableModel.selectSql(li.TableName):
                psb=f"""
    @property
    def {pli.FieldName}(self):
        \"""
        {pli.FieldDescription}
        \"""
        return self._{pli.FieldName}

    @{pli.FieldName}.setter
    def {pli.FieldName}(self, {StrHelper.firstLower(pli.FieldName)}):
        \"""
       {pli.FieldDescription}
        :param {pli.FieldName}:
        :return:
        \"""
        self._{pli.FieldName} = {StrHelper.firstLower(pli.FieldName)}       
    """
                sb.append(psb)

            if not os.path.exists(self.__modelpath):
                os.makedirs(self.__modelpath)
            filepath=self.__modelpath+StrHelper.firstLower(li.TableName)+'.py'
            content=''.join(sb)
            ok = self.__file.createModelFile(filepath,content)

# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : windows 10
# database  ：sql server 2019,mysql 9.0,postgetsql 17.0, oracle 21c
# Datetime  : 2024/12/9 12:23
# User      : geovindu
# Product   : PyCharm
# Project   : pyGenerator
# File      : oracleGenerator/createStoredProcedure.py
# explain   : 学习

import os
import re
import sys
import io
import datetime
from common.fileHelper import FileHelper
from common.strHelper import StrHelper
from bll.tableNameAndKey import TableNameAndKeyBll
from bll.tableForegin import TableForeginBll
from bll.tableModel import TableModelBll
from model.schemasName import SchemasNameInfo
from model.tableNameAndKey import TableNameAndKey
from model.tableModel import TableModel


class CreateStoredProcedure(object):
    """
    生成存储过程
    """
    def __init__(self):
        self.__modelpath = os.path.join(os.getcwd() + "/oracletest/sql/")
        self.__file = FileHelper()
        self.__blltablekey = TableNameAndKeyBll()
        self.__bllTableModel = TableModelBll()
        self.__database = "oracle21c"
        self.__os = "windows 10"
        self.__product = "PyCharm"
        self.__project = "IctGame"
        self.__name = "CreateStoredProcedure"
        self.__SchemasNameInfo = SchemasNameInfo()
        self.__sb = []
        pass

    def __del__(self):
        """

        :return:
        """
        print(f"{self.__name} ERASE MEMORY")

    def Add(self):
        """
        添加存储过程文件
        :return:
        """
        self.__SchemasNameInfo.SchemasName = "technologygame"
        self.__SchemasNameInfo.Prefix = ""
        for table in self.__blltablekey.selectSql():
            pro = CreateStoredProcedure()
            pro.Insert(table, 's.sql')
            pro.InsertOutput(table, 's.sql')
            pro.Update(table, 's.sql')
            pro.Delete(table, 's.sql')
            pro.Select(table, 's.sql')
            pro.SelectAll(table, 's.sql')
            pro.save(table)


    def Insert(self,table:TableNameAndKey,path:str):
        """
        插入存儲過程
        :param table
        :param path
        :return:
        """
        now = datetime.datetime.now()
        sbt = f"""
/*
# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : windows 10
# Datetime  : {now.strftime('%Y-%m-%d %H:%M:%S')}
# database  ：{self.__database}
# User      : geovindu
# Product   : {self.__project}
# Project   : {self.__project}     
*/"""
        self.__sb.append(sbt)
        self.__SchemasNameInfo.SchemasName = "technologygame"
        self.__SchemasNameInfo.Prefix = ""
        self.__sb.append("\nDROP PROCEDURE IF EXISTS procInsert" + StrHelper.firstUper(table.TableName) + ";\n")
        self.__sb.append("CREATE PROCEDURE procInsert" + StrHelper.firstUper(table.TableName) + "\n(\n")
        ls = list[TableModel]
        ls = self.__bllTableModel.selectSql(table.TableName)  #參數
        c=len(ls)
        i=0
        #for info in ls:
            #print(",\n".join("\tin param" + info.FieldName + " "+ info.FieldType for info in ls))
            #self.__sb.append("\tin  param" + info.FieldName + " " + info.FieldType)
        self.__sb.append(",\n".join("\t param" + info.FieldName + " "+ info.FieldType for info in ls))
        self.__sb.append("\n)\n as\n begin\n")
        self.__sb.append("INSERT INTO " + self.__SchemasNameInfo.Prefix + table.TableName + "\n(\n")
        self.__sb.append(",\n".join("\t" + info.FieldName for info in ls))
        self.__sb.append("\n)\nVALUES\n(\n")
        self.__sb.append(",\n".join("\tparam" + info.FieldName for info in ls))
        self.__sb.append("\n);\nend if;\nend procInsert" + StrHelper.firstUper(table.TableName) + ";\n")
        pass

    def InsertOutput(self,table:TableNameAndKey,  path:str):
        """
        存储过程
        :param table:
        :param path:
        :return:
        """
        self.__SchemasNameInfo.SchemasName = "technologygame"
        self.__SchemasNameInfo.Prefix = ""
        self.__sb.append("\n\nDROP PROCEDURE IF EXISTS procInsert" + StrHelper.firstUper(table.TableName) + "Out;\n")
        self.__sb.append("CREATE PROCEDURE procInsert" + StrHelper.firstUper(table.TableName) + "Out\n(\n\t")
        ls = list[TableModel]
        ls = self.__bllTableModel.selectSql(table.TableName)  # 參數
        c = len(ls)
        i = 0
        for info in ls:
            if table.TableKey != info.FieldName:  # 主键字段, 如果是自增长, 不需要考虑
                if i < c:
                    self.__sb.append(" param" + info.FieldName + " " + info.FieldType+",\n\t")
                else:
                    self.__sb.append(" param" + info.FieldName + " " + info.FieldType + "\n\t")

            i=i+1
        self.__sb.append(" out " + info.FieldName + " " + info.FieldType + "\n")
        self.__sb.append("\n)\nas\nbegin\n")
        self.__sb.append("INSERT INTO " + self.__SchemasNameInfo.Prefix + table.TableName + "\n(\n\t")
        i = 0
        for info in ls:
            if table.TableKey != info.FieldName:  # 主键字段, 如果是自增长, 不需要考虑
                if i < c:
                    self.__sb.append(info.FieldName + ",\n\t")
                else:
                    self.__sb.append(info.FieldName + "\n")
            i=i+1
        self.__sb.append("\n)\nVALUES\n(\n\t")
        i = 0
        for info in ls:
            if table.TableKey != info.FieldName:  # 主键字段, 如果是自增长, 不需要考虑
                if i < c:
                    self.__sb.append(" param" + info.FieldName + " " + info.FieldType+",\n\t")
                else:
                    self.__sb.append(" param" + info.FieldName + " " + info.FieldType + "\n")
            i=i+1
        self.__sb.append("\n);\nSELECT LAST_INSERT_ID() into "+table.TableKey+";\nend if;\nend procInsert" + StrHelper.firstUper(table.TableName) + "Out;\n")


    def Update(self, table:TableNameAndKey,path:str):
        """
        存储过程
        :param table:
        :param path:
        :return:
        """
        self.__SchemasNameInfo.SchemasName = "technologygame"
        self.__SchemasNameInfo.Prefix = ""
        self.__sb.append("\n\nDROP PROCEDURE IF EXISTS procUpdate" + StrHelper.firstUper(table.TableName) + ";\n")
        self.__sb.append("CREATE PROCEDURE procUpdate" + StrHelper.firstUper(table.TableName) + "\n(\n")
        ls = list[TableModel]
        ls = self.__bllTableModel.selectSql(table.TableName)  # 參數
        c = len(ls) - 1
        self.__sb.append(",\n".join("\t param" + info.FieldName + " " + info.FieldType for info in ls))
        self.__sb.append("\n)\n as \nbegin\n")
        self.__sb.append("UPDATE " + self.__SchemasNameInfo.Prefix + table.TableName + "\n\t")
        i = 0
        for info in ls:
            if table.TableKey != info.FieldName:
                if i < c:
                    self.__sb.append(info.FieldName + "=param" + info.FieldName + " ,\n\t")
                else:
                    self.__sb.append(info.FieldName + "=param" + info.FieldName + " \n")
            i = i + 1
        self.__sb.append("where\n\t")
        self.__sb.append(table.TableKey + "=param" + table.TableKey + " \n")
        self.__sb.append("\n);\nend procUpdate" + StrHelper.firstUper(table.TableName) + ";\n")


    def Delete(self, table:TableNameAndKey, path:str):
        """
        存储过程
        :param table:
        :param path:
        :return:
        """
        self.__SchemasNameInfo.SchemasName = "technologygame"
        self.__SchemasNameInfo.Prefix = ""
        self.__sb.append("\n\nDROP PROCEDURE IF EXISTS procDelete" + StrHelper.firstUper(table.TableName) + ";\n")
        self.__sb.append("CREATE PROCEDURE procDelete" + StrHelper.firstUper(table.TableName) + "\n(\n")
        ls = list[TableModel]
        ls = self.__bllTableModel.selectSql(table.TableName)  # 參數
        c = len(ls) - 1
        self.__sb.append("\t param" + table.TableKey + " "+table.TypeName+"\n)\n")
        self.__sb.append("AS \nBEGIN\n");
        self.__sb.append("DELETE FROM\n\t" + self.__SchemasNameInfo.Prefix + table.TableName + "\n\tWHERE\n\t\t" + table.TableKey + " = param" + table.TableKey)
        self.__sb.append(";\nend procDelete" + StrHelper.firstUper(table.TableName) + ";")


    def Select(self, table:TableNameAndKey, path:str):
        """
         存储过程查询   p_cursor
        :param table:
        :param path:
        :return:
        """
        self.__SchemasNameInfo.SchemasName = "technologygame"
        self.__SchemasNameInfo.Prefix = ""
        self.__sb.append("\n\nDROP PROCEDURE IF EXISTS procSelect" + StrHelper.firstUper(table.TableName) + ";\n")
        self.__sb.append("CREATE PROCEDURE procSelect" + StrHelper.firstUper(table.TableName) + "\n(\n")
        ls = list[TableModel]
        ls = self.__bllTableModel.selectSql(table.TableName)  # 參數
        c = len(ls) - 1
        self.__sb.append("\t param" + table.TableKey + " " + table.TypeName + "\n")
        self.__sb.append("\t p_cursor OUT SYS_REFCURSOR\n)\n")
        self.__sb.append("AS \nBEGIN\n");
        self.__sb.append("\tOPEN p_cursor FOR\n");
        self.__sb.append("SELECT *  FROM\n\t " + self.__SchemasNameInfo.Prefix + table.TableName + "\n\tWHERE\n\t\t" + table.TableKey + " = param" + table.TableKey)
        self.__sb.append(";\nend procSelect" + StrHelper.firstUper(table.TableName) + ";")

    def SelectAll(self, table:TableNameAndKey, path:str):
        """
        存储过程查询   p_cursor
        :param table:
        :param path:
        :return:
        """
        self.__SchemasNameInfo.SchemasName = "technologygame"
        self.__SchemasNameInfo.Prefix = ""
        self.__sb.append("\n\nDROP PROCEDURE IF EXISTS procSelect" + StrHelper.firstUper(table.TableName) + "All;  \n")
        self.__sb.append("CREATE PROCEDURE  procSelect" + StrHelper.firstUper(table.TableName) + "All\n(\n\tp_cursor OUT SYS_REFCURSOR\n)\n")
        ls = list[TableModel]
        ls = self.__bllTableModel.selectSql(table.TableName)  # 參數
        c = len(ls) - 1
        self.__sb.append("AS \nBEGIN\n\tOPEN p_cursor FOR\n");
        self.__sb.append("SELECT * FROM\n\t " + self.__SchemasNameInfo.Prefix + table.TableName)
        self.__sb.append(";\nend procSelect" + StrHelper.firstUper(table.TableName) + "All;")


    def save(self,table:TableNameAndKey, path="proedure.sql"):
        """
        保存文件
        :return:
        """
        if not os.path.exists(self.__modelpath):
            os.makedirs(self.__modelpath)
        filepath = self.__modelpath + table.TableName+'.sql'
        content = ''.join(self.__sb)
        ok = self.__file.createModelFile(filepath, content)

# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : windows 10
# Datetime  : 2025-01-02 18:23:12
# database  ：Oracle21c
# User      : geovindu
# Product   : PyCharm
# Project   : IctGame
# File      : model/school.py
# explain   : 学习
                    
class SchoolInfo(object):
    """
    學校表 School Table                    
    """ 

    def __init__(self):
        """
            構造
        """               

              
        self._SchoolId = None
        """
        主鍵primary key，學校編號
        """
        
        self._SchoolName = None
        """
         學校名稱
        """
        
        self._SchoolTelNo = None
        """
        電話號碼
        """
        
    @property
    def SchoolId(self):
        """
        主鍵primary key，學校編號
        """
        return self._SchoolId

    @SchoolId.setter
    def SchoolId(self, schoolId):
        """
       主鍵primary key，學校編號
        :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
# Datetime  : 2025-01-02 18:23:12
# database  ：oracle21c
# 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 from DBUtility.oracleHelper import OracleHelper
from interface.school import ISchool

class SchoolDal(ISchool):
    """
    學校表 School Table                    
    """ 

    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:
         """
     
         row = cls.myms.executeCursor("procSelectschoolAll")
         return row

    def selectIdProc(cls,SchoolId:str)->list:
         """
         存储过程 游标
         :param SchoolId: 主键ID
         :return:
         """
    
         argsvalue = (SchoolId,)         
         args = 'paramSchoolId'
         row = cls.myms.executeParmCursor(args,argsvalue,'procSelectschool')
         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(5)',) #输出，元组类型 考虑数据类型去转换
              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