python: Call SQL Server Stored Procedure

 

sql script:

?
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
DROP TABLE InsuranceMoney
GO
create table InsuranceMoney
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    InsuranceName nvarchar(50),
    InsuranceCost float,
    IMonth int 
 )
 go
 
 insert into InsuranceMoney(InsuranceName, InsuranceCost, IMonth) values ('',200,1)
 go
 
 
 insert into InsuranceMoney(InsuranceName,InsuranceCost,IMonth) OUTPUT INSERTED.ID VALUES('geovindu',20,12);
 go
 
 select * from InsuranceMoney
 
 
 IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Insert_InsuranceMoney')
DROP PROCEDURE proc_Insert_InsuranceMoney
GO
CREATE PROCEDURE proc_Insert_InsuranceMoney
(
    --@BookKindID Int,
    @InsuranceName NVarChar(1000),
    @InsuranceCost float,
    @IMonth Int
)
AS
INSERT INTO InsuranceMoney
(
    [InsuranceName] ,
    [InsuranceCost],
    [IMonth]
)
    VALUES
(
    @InsuranceName ,
    @InsuranceCost,
    @IMonth
)
GO
 
exec proc_Insert_InsuranceMoney '养老',500,3
go
 
 select * from InsuranceMoney
 
 
IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_InsuranceMoney')
DROP PROCEDURE proc_Select_InsuranceMoney
GO
CREATE PROCEDURE proc_Select_InsuranceMoney
(
    @ID Int
)
AS
SELECT * FROM InsuranceMoney WHERE ID = @ID
GO

　　

?
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
"""
SQLServerDAL.py
SQL Server 数据库操作
date 2023-06-13
edit: Geovin Du,geovindu, 涂聚文
ide: PyCharm 2023.1 python 11
参考：https://learn.microsoft.com/zh-cn/sql/connect/python/pymssql/step-3-proof-of-concept-connecting-to-sql-using-pymssql?view=sql-server-ver16
DROP TABLE InsuranceMoney
GO
create table InsuranceMoney
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    InsuranceName nvarchar(50),
    InsuranceCost float,
    IMonth int
 )
 go
 
"""
import os
import sys
from pathlib import Path
import re
import pymssql  #sql server
import Insurance
import MsSQLHelper
 
class SQLclass(object):
    """
     Sql server 存储过程操作
    """
 
    def __init__(self, strserver, struser, strpwd, strdatabase):
        """
 
        :param strserver:
        :param struser:
        :param strpwd:
        :param strdatabase:
        """
        self._strserver = strserver
        self._struser = struser
        self._strpwd = strpwd
        self._strdatabase = strdatabase
 
    def selectdu(self):
        myms = MsSQLHelper.MsSqlHelper(self._strserver, self._struser, self._strpwd , self._strdatabase)
        # print(myms)
        # myms.select()
        row=myms.execute('select * from InsuranceMoney;')
        print(row)
        return row
        #while row:
            #print(str(row[0]) + " " + str(row[1]) + " " + str(row[2]))  #返回的是字典
 
 
    def select(self):
        """
         查询所有记录
        :return:
        """
        conn = pymssql.connect(
 
            server=self._strserver,
            user=self._struser,
            password=self._strpwd,
            database=self._strdatabase
            #server='DESKTOP-NQK85G5\GEOVIN2008',
            #user='sa',
            #password='geovindu',
            #database='Student'
        )
        cursor = conn.cursor()
        cursor.execute('select * from InsuranceMoney;')
        row = cursor.fetchone()
        while row:
            print(str(row[0]) + " " + str(row[1]) + " " + str(row[2]))
            row = cursor.fetchone()
 
    def selectIdProc(self,id):
        """
         查询ID记录 存储过程
        :return:
        """
        conn = pymssql.connect(
 
            server=self._strserver,
            user=self._struser,
            password=self._strpwd,
            database=self._strdatabase
            #server='DESKTOP-NQK85G5\GEOVIN2008',
            #user='sa',
            #password='geovindu',
            #database='Student'
        )
        cursor = conn.cursor()
        args=(id,)
        cursor.callproc("dbo.proc_Select_InsuranceMoney", args)
        row = cursor.fetchone()
        while row:
            print(str(row[0]) + " " + str(row[1]) + " " + str(row[2]))
            row = cursor.fetchone()
 
    def insertdu(self,iobject):
        """
        插入操作
        param:iobject 输入保险类
        :return:
        """
        dubojd = iobject
        myms = MsSQLHelper.MsSqlHelper(self._strserver, self._struser, self._strpwd , self._strdatabase)
        #myms.insertbyvalues('InsuranceMoney',)
        columns=('InsuranceName','InsuranceCost','IMonth')
        print(dubojd)
        print(columns)
        val=[dubojd.getInsuranceName(), dubojd.getInsuranceCost(), dubojd.getIMonth()]
        print(val)
        myms.insertbycolumnandvalues("InsuranceMoney",columns,val)
 
 
    def insert(self,iobject):
        """
        插入操作
        param:iobject 输入保险类
        :return:
        """
 
        conn = pymssql.connect(
            server=self._strserver,
            user=self._struser,
            password=self._strpwd,
            database=self._strdatabase
        )
        cursor = conn.cursor()
        #cursor.callproc("")
        cursor.execute(
            "insert into InsuranceMoney(InsuranceName,InsuranceCost,IMonth) OUTPUT INSERTED.ID VALUES ('{0}', {1}, {2})".format(
                iobject.getInsuranceName(), iobject.getInsuranceCost(), iobject.getIMonth()))
        row = cursor.fetchone()
        while row:
            print("Inserted InsuranceMoney ID : " + str(row[0]))
            row = cursor.fetchone()
        conn.commit()
        conn.close()
 
    def insertProc(self,iobject):
        """
        插入操作  存储过程 http://www.pymssql.org/en/stable/pymssql_examples.html
        https://kontext.tech/article/893/call-sql-server-procedure-in-python
        https://www.programmerall.com/article/493081049/
        param:iobject 输入保险类
        :return:
        """
 
        conn = pymssql.connect(
            server=self._strserver,
            user=self._struser,
            password=self._strpwd,
            database=self._strdatabase
        )
        cursor = conn.cursor()
        #cursor.callproc("")
        args=(iobject.getInsuranceName(), iobject.getInsuranceCost(), iobject.getIMonth())
        #cursor.execute(f"exec 存储过程名称 @参数1='xxx',@参数2='xxx',@参数3='xxx',@参数4='xxx'")
        cursor.callproc("dbo.proc_Insert_InsuranceMoney",args)
        conn.commit()
        conn.close()
 
    def insertStr(self,InsuranceName, InsuranceCost, IMonth):
        """
        插入操作
        param:InsuranceName
        param:InsuranceCost
        param:IMonth
        :return:
        """
        conn = pymssql.connect(
            server=self._strserver,
            user=self._struser,
            password=self._strpwd,
            database=self._strdatabase
        )
        cursor = conn.cursor()
        cursor.execute(
            "insert into InsuranceMoney(InsuranceName,InsuranceCost,IMonth) OUTPUT INSERTED.ID VALUES('{0}',{1},{2})".format(
                InsuranceName, InsuranceCost, IMonth))
        row = cursor.fetchone()
        while row:
            print("Inserted InsuranceMoney ID : " + str(row[0]))
            row = cursor.fetchone()
        conn.commit()
        conn.close()

　　

调用：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
s=["医疗",400,1]
column=('InsuranceName','InsuranceCost','IMonth')
tbname="InsuranceMoney"
vls = str(s).replace("[", "").replace("]", "")
cls = str(column).replace("'", "").replace("'", "")
sqlstring = f"insert into {tbname} {cls} values ({vls})"
print(sqlstring)
 
ms=SQLServerDAL.SQLclass('DESKTOP-NQK85G5\GEOVIN2008','sa','geovindu','Student')
#ms.selectdu()
ms.selectIdProc(3)
#ms.insertdu(Insurance.Insurance("医疗",400,1))
ms.insertProc(Insurance.Insurance("医疗",800,5))

　　

 

posted @   ®Geovin Du Dream Park™  阅读(21)  评论(0)  编辑  收藏  举报
相关博文：
·  python: sql server insert record
·  python: pymssql stored procedures insert output
·  python操作MySQL SQL注入问题 二次确认 修改表SQL语句 视图
·  pymysql用insert向数据库插入
·  day22-MySQL入门
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
历史上的今天：
2015-06-17 csharp:Conversion Between DataTable and List
< 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
点击右上角即可分享
微信分享提示