python: generate model and DAL using Oracle

 sql:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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;

　　

 

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
# 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)

　　

生成存储过程：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
# 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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
# 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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
# 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           

　　

 

posted @   ®Geovin Du Dream Park™  阅读(6)  评论(0)  编辑  收藏  举报
相关博文：
· python: generator model using mysql9.0 or postgreSQL 17.0
· python: generator IDAL and DAL using sql server 2019
· 模型层 ORM 操作
· python-Django基础学习笔记
· MYSQL-ORM
阅读排行：
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示