当前位置： 首页 > news >正文

python: generate model and DAL using Oracle

news 2025/1/5 18:49:54

sql:

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 TableModelBllclass 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"passdef __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"""@propertydef {pli.FieldName}(self):\"""{pli.FieldDescription}\"""return self._{pli.FieldName}@{pli.FieldName}.setterdef {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 TableModelclass 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 = []passdef __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")passdef 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 = 0for 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+1self.__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 = 0for 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+1self.__sb.append("\n)\nVALUES\n(\n\t")i = 0for 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+1self.__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) - 1self.__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 = 0for 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 + 1self.__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) - 1self.__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) - 1self.__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) - 1self.__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)

生成的代码：

MODEL:


# 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"""電話號碼"""@propertydef SchoolId(self):"""主鍵primary key，學校編號"""return self._SchoolId@SchoolId.setterdef SchoolId(self, schoolId):"""主鍵primary key，學校編號:param SchoolId::return:"""self._SchoolId = schoolId       @propertydef SchoolName(self):"""學校名稱"""return self._SchoolName@SchoolName.setterdef SchoolName(self, schoolName):"""學校名稱:param SchoolName::return:"""self._SchoolName = schoolName       @propertydef SchoolTelNo(self):"""電話號碼"""return self._SchoolTelNo@SchoolTelNo.setterdef SchoolTelNo(self, schoolTelNo):"""電話號碼:param SchoolTelNo::return:"""self._SchoolTelNo = schoolTelNo

DAL:


# 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 ISchoolclass 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 rowdef 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 rowdef 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 rowdef selectIdSql(cls,schoolId:str)->list:""":param SchoolId: 主键ID:return:"""row=cls.myms.execute(f"select * from school where SchoolId=schoolId;")  #考虑数据类型#cls.myms.close()return rowdef selectProc(cls)->list:"""存储过程 游标:return:"""row = cls.myms.executeCursor("procSelectschoolAll")return rowdef selectIdProc(cls,SchoolId:str)->list:"""存储过程 游标:param SchoolId: 主键ID:return:"""argsvalue = (SchoolId,)         args = 'paramSchoolId'row = cls.myms.executeParmCursor(args,argsvalue,'procSelectschool')return rowdef 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 = 0try:outSchoolId =('char(5)',) #输出，元组类型 考虑数据类型去转换print(info)args = [info.SchoolName,info.SchoolTelNo,outSchoolId]print(args)result=cls.myms.insertOutCallProc("procInsertschoolOutput", args)print(result)id = resultexcept Exception as ex:print(ex)return iddef 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 =SchoolIdk=cls.myms.deleteProc("procDuDeleteschool", args)return k


http://www.mrgr.cn/news/82366.html

相关文章：

  • 洛谷：P1540 [NOIP2010 提高组] 机器翻译
  • Couchbase 和数据湖技术的区别、联系和相关性分析
  • django StreamingHttpResponse fetchEventSource实现前后端流试返回数据并接收数据的完整详细过程
  • wordpress报错open_basedir restriction in effect
  • Nginx与frp结合实现局域网和公网的双重https服务
  • vue字符串的数字比较大小有问题
  • 25年1月更新。Windows 上搭建 Python 开发环境：PyCharm 安装全攻略（文中有安装包不用官网下载）
  • 行为模式2.命令模式------灯的开关
  • 【数据库系统概论】绪论--复习
  • STM32 拓展 电源控制
  • 实际开发中,常见pdf|word|excel等文件的预览和下载
  • 前言(1)
  • flink cdc oceanbase（binlog模式）
  • 二、AI知识（神经网络）
  • Speedtest 测试客户的上/下行带宽
  • 泊松融合调研
  • 机器学习 学习知识点
  • Spark-Streaming有状态计算
  • Qt之简易音视频播放器设计（十五）
  • JAVA-制作小游戏期末实训
  • 服务端错误的处理和web安全检测
  • 六年之约day5
  • 软件项目的灵魂拷问：“要做什么？”和“做成了什么？”
  • Win11电脑Cursor默认打开markdown文件，如何修改markdown文件默认打开方式为Typora？
  • 鸿蒙开发汇总
  • HarmonyNext 鸿蒙开发中，在H5 页面如何下载图片保存到媒体库。