sql script:
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
"""
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()
调用：
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','','Student')
#ms.selectdu()
ms.selectIdProc(3)
#ms.insertdu(Insurance.Insurance("医疗",400,1))
ms.insertProc(Insurance.Insurance("医疗",800,5))