python: generate model and DAL using Oracle
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 'ID';
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/28 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)\n")
self.__sb.append("\t p_cursor OUT SYS_REFCURSOR\n)\n")
self.__sb.append("\tOPEN p_cursor FOR\n");
self.__sb.append("AS \nBEGIN\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)
生成的代码：
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
"""
電話號碼
"""
@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
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 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
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号