如何调用存储过程
根据返回值类型
返回Table:
--示例表
create table Salary(
Sno varchar(20) NOT NULL REFERENCES Staff(Sno)ON DELETE CASCADE, --职工号
Base decimal(20,2) not null , --基础薪资
Bonus decimal(20,2) not null , --津贴奖金
Stock decimal(20,2) not null , --股份分红
constraint cons_Base check(Base >=2000 and Base <=50000),
constraint cons_Bonus check(Bonus<=20000 and Bonus>=0),
constraint cons_Stock check(Stock<=100000 and Stock>=0)
);
--返回结果集table的proc
create proc test_proc_select
as
select * from Salary
import pymssql
conn = pymssql.connect(host='127.0.0.1',user='sa',
password='xxxxxxxxxx',database='Bank',charset='GBK')
cursor = conn.cursor()
cursor.execute('exec test_proc_select')
result = cursor.fetchall() #得到结果集
for item in result:
print(item) #遍历打印查询结果集的数据
('642825', Decimal('15000.00'), Decimal('5000.00'), Decimal('5000.00'))
('666666', Decimal('7000.00'), Decimal('2000.00'), Decimal('1500.00'))
返回return ' int/string':
暂时不知道如何解决!!!望道友解答!
如何解决读取中文乱码问题
charset设置为'GBK';不是'utf8',因为SQL server默认的格式是GBK
import pymssql
conn = pymssql.connect(host='127.0.0.1',user='sa',
password='xxxxxxxxxxx',database='Bank',charset='GBK')
Connection Objects
Connection objects should respond to the following methods.
Connection methods
关闭connection对象
.commit()
Commit any pending transaction to the database.
执行任何被推迟的事务
执行connection对象下等待进行的sql语句
sql='''insert into test1
values(1)'''
cursor.execute(sql)
# 提交到数据库执行;没有此句,插入语句将不会执行
conn.commit()
在execute之后sql语句并未执行,在connection.commit()之后才去
回滚先前执行的操作,撤销对数据库造成的更改
Closing a connection without committing the changes first will cause an implicit rollback to be performed.
.cursor()
返回游标变量
from pymssql import connect
server='127.0.0.1'
user='sa'
password='xx94524253xx'
database='users'
conn=connect(host='%s'%(server),user='%s'%(user),password='%s'%(password),database='%s'%(database),charset='GBK')
cursor = conn.cursor()
sql='''insert into test1
values(1)'''
cursor.execute(sql)
try:
cursor.execute(sql)
# 提交到数据库执行;没有此句,插入语句将不会执行
conn.commit()
cursor.close()
except:
conn.rollback()
conn.close()
Cursor Objects
游标对象。即是一个connection对象创建多个cursor,彼此之间互联,一个游标造成的变化都将会反应在其他游标上,
Cursor attributes
包含七项
- name 属性名
- type_code 类型编码
- display_size
- internal_size
- precision
- scale
- null_ok
name and type_code是强制的,无论是否可以取到值;其他是可选的,若取值为空则相对应为空;
当游标操作并不返回结果集 return result set 时,.description返回None
cursor.execute('exec test_proc_select @int=1')
result = cursor.fetchall() #得到结果集
print('description:')
print(cursor.description)
print('fetch_result:'+str(result))
description:
(('Sno', 1, None, None, None, None, None), ('Base', 5, None, None, None, None, None), ('Bonus', 5, None, None, None, None, None), ('Stock', 5, None, None, None, None, None))
fetch_result:[('642825', Decimal('15000.00'), Decimal('5000.00'), Decimal('5000.00')), ('666666', Decimal('7000.00'), Decimal('2000.00'), Decimal('1500.00'))]
反应最近一次execute()影响的行数,初始值为0
sql='insert into test1 values(%d)'
cursor.executemany(sql,(11,12,13))
conn.commit()
print(str(cursor.rowcount)) # 3
Cursor methods
.callproc( procname [, parameters ] )
两种做法都行不通!!!
cursor.callproc('test_proc_select',(1))
cursor.callproc('test_proc_select',1)
.close()
关闭游标
.execute(operation [, parameters])
执行相应的SQL语句
.executemany( sql语句, 参数序列的元组)
以不同的参数同时执行同一格式语句
sql='insert into test1 values(%d)'
cursor.executemany(sql,(11,12,13))
conn.commit()
import pymssql
conn = pymssql.connect(host='127.0.0.1',user='sa',
password=pwd,database='Bank',charset='GBK')
cursor = conn.cursor()
cursor.execute('exec test_proc_select')
result = cursor.fetchone() #得到结果集
print('fetch_result:'+str(result))
count = 0
for item in result:
print('item_'+str(count)+':'+str(item)) #遍历打印查询结果集的数据
count = count+1
ps:这里附加关于py的print的一个小规则,如果是单独print打印非str变量,可以通过编译;若一个print里面同时包含非str变量和str变量,则是会报错的。举例如下:
test1=1
test2=2
print(test1+test2)#可以通过
test1=1
test2='2'
print(test1+test2)#不可以通过
.fetchone()每次执行时都向后取结果集的一个元素,如果一个result set只有两个元素,而调用了三次.fetchone(),则最后一次结果为None
print(str(cursor.fetchone()))
print(str(cursor.fetchone()))
print(str(cursor.fetchone()))
.fetchmany([size=cursor.arraysize])
#当list总共只有两个元素时只要size>=2,无论为多少
rs = cursor.fetchmany(size=2)
print(rs)
rs = cursor.fetchmany(size=3)
print(rs)
#输出结果一样
提取结果集所有元素,并跳至集尾
rs = cursor.fetchall() # 提取所有结果集赋值给rs变量,并使cursor直接调至集尾
相当于.fetchmany(szie=max_size)
如果先.fetchall(),后.fetchone()则取值为None
print(str(cursor.fetchall()))
print(str(cursor.fetchone()))
.nextset()
This method will make the cursor skip to the next available set, discarding any remaining rows from the current set.
该方法跳过当前的结果集单位(无论是list、tuple...相当于跳过当下剩余的所有元素)
当结果集下属仅2元素时,第一个fetchone()可取下属第一个元素,第二个fetchone()则为None
print(str(cursor.fetchone()))
cursor.nextset()
print(str(cursor.fetchone()))
This read/write attribute specifies the number of rows to fetch at a time with .fetchmany(). It defaults to 1 meaning to fetch a single row at a time.
Implementations must observe this value with respect to the .fetchmany() method, but are free to interact with the database a single row at a time. It may also be used in the implementation of .executemany().
无法通过!!!
result = cursor.fetchmany(size=cursor.arraysize)