Python操作SQL Server(三)数据表查询和fetchall()与fetchone()的区别

数据表查询

第一步.

连接数据库(至于怎么连接我前面的文章有讲解可以去看一下这里就不赘述了)，创建游标对象。

connect = pymssql.connect(host='localhost',server='服务器名称',user='用户名',password='用户密码',database='数据库名称',port='端口号')
cour = connect.cursor()  #创建游标对象

第二步.

创建查询命令。
这里的查询命令和SQL Sever中的命令格式是一样的，只需将查询命令写入引号中即可。

cour.execute('SELECT * FROM number')

第三步.

输出查询结果。
这里给大家看一下要查询的数据表(number)的内容:

第一种方式

row = cour.fetchone()
while row:
    print('%s,%s,%s' % (row[0],row[1],row[2]))
    row = cour.fetchone()

输出结果：

13522663258,nub/13522663258.html,_blank
18716872350,nub/18716872350.html,_blank
15023533998,nub/15023533998.html,_blank
15136797185,nub/15136797185.html,_blank
13988208612,nub/13988208612.html,_blank
15637987958,nub/15637987958.html,_blank
13101457722,nub/13101457722.html,_blank
13024837779,nub/13024837779.html,_blank
13419716513,nub/13419716513.html,_blank
······

第二种方式

row = cour.fetchall()
print(row)

输出结果：

[('13522663258', 'nub/13522663258.html', '_blank'), ('18716872350', 'nub/18716872350.html', '_blank'), ('15023533998', 'nub/15023533998.html', '_blank'), ('15136797185', 'nub/15136797185.html', '_blank'), ('13988208612', 'nub/13988208612.html', '_blank'), ('15637987958', 'nub/15637987958.html', '_blank'), ('13101457722', 'nub/13101457722.html', '_blank'), ('13024837779', 'nub/13024837779.html', '_blank'), ('13419716513', 'nub/13419716513.html', '_blank'), ('13419719513', 'nub/13419719513.html', '_blank'), ('15903107257', 'nub/15903107257.html', '_blank'), ('15987734078', 'nub/15987734078.html', '_blank'), ('13994996100', 'nub/13994996100.html', '_blank'), ('13951098420', 'nub/13951098420.html', '_blank'), ('13951095420', 'nub/13951095420.html', '_blank'), ('13664147043', 'nub/13664147043.html', '_blank'), ('15180058434', 'nub/15180058434.html', '_blank'), ('13578662673', 'nub/13578662673.html', '_blank'), ('15180056434', 'nub/15180056434.html', '_blank'), ('15916948627', 'nub/15916948627.html', '_blank'), ('15876269489', 'nub/15876269489.html', '_blank'), ('13902231296', 'nub/13902231296.html', '_blank'), ('13561520488', 'nub/13561520488.html', '_blank'), ('15367287394', 'nub/15367287394.html', '_blank'), ('15986769022', 'nub/15986769022.html', '_blank'), ('13922531005', 'nub/13922531005.html', '_blank'), ('13329102544', 'nub/13329102544.html', '_blank'), ('15855999621', 'nub/15855999621.html', '_blank'), ('18711474647', 'nub/18711474647.html', '_blank'), ('15252200555', 'nub/15252200555.html', '_blank'), ('13590523683', 'nub/13590523683.html', '_blank'), ('13037083630', 'nub/13037083630.html', '_blank'), ('13500003263', 'nub/13500003263.html', '_blank'), ('13878125227', 'nub/13878125227.html', '_blank'), ('13980001174', 'nub/13980001174.html', '_blank'), ('18781123577', 'nub/18781123577.html', '_blank'), ('18228020937', 'nub/18228020937.html', '_blank'), ('18626150901', 'nub/18626150901.html', '_blank'), ('13892166609', 'nub/13892166609.html', '_blank'), ('18909112988', 'nub/18909112988.html', '_blank'), ('13664669009', 'nub/13664669009.html', '_blank'), ('15111331181', 'nub/15111331181.html', '_blank'), ('15326870529', 'nub/15326870529.html', '_blank'), ('18702957672', 'nub/18702957672.html', '_blank'), ('13232512495', 'nub/13232512495.html', '_blank'), ('18933590464', 'nub/18933590464.html', '_blank'), ('15898485020', 'nub/15898485020.html', '_blank'), ('15648199554', 'nub/15648199554.html', '_blank'), ('13007316627', 'nub/13007316627.html', '_blank'), ('18623005632', 'nub/18623005632.html', '_blank')]

fetchall()与fetchone()的区别

从上图中我们可以看到两种不同的输出方式，在输出命令的格式上还是有很大差别的，那么这两者到底有什么区别呢？我们通过执行不同的命令来查看一下。

还是以这个数据表为例

内容

cour.execute('SELECT * FROM number')
row = cour.fetchall()
print(row)  

[('13522663258', 'nub/13522663258.html', '_blank'), ('18716872350', 'nub/18716872350.html', '_blank'), ('15023533998', 'nub/15023533998.html', '_blank'), ('15136797185', 'nub/15136797185.html', '_blank'), ('13988208612', 'nub/13988208612.html', '_blank'), ('15637987958', 'nub/15637987958.html', '_blank'), ('13101457722', 'nub/13101457722.html', '_blank'), ('13024837779', 'nub/13024837779.html', '_blank'), ('13419716513', 'nub/13419716513.html', '_blank'), ('13419719513', 'nub/13419719513.html', '_blank'), ('15903107257', 'nub/15903107257.html', '_blank'), ('15987734078', 'nub/15987734078.html', '_blank'), ('13994996100', 'nub/13994996100.html', '_blank'), ('13951098420', 'nub/13951098420.html', '_blank'), ('13951095420', 'nub/13951095420.html', '_blank'), ('13664147043', 'nub/13664147043.html', '_blank'), ('15180058434', 'nub/15180058434.html', '_blank'), ('13578662673', 'nub/13578662673.html', '_blank'), ('15180056434', 'nub/15180056434.html', '_blank'), ('15916948627', 'nub/15916948627.html', '_blank'), ('15876269489', 'nub/15876269489.html', '_blank'), ('13902231296', 'nub/13902231296.html', '_blank'), ('13561520488', 'nub/13561520488.html', '_blank'), ('15367287394', 'nub/15367287394.html', '_blank'), ('15986769022', 'nub/15986769022.html', '_blank'), ('13922531005', 'nub/13922531005.html', '_blank'), ('13329102544', 'nub/13329102544.html', '_blank'), ('15855999621', 'nub/15855999621.html', '_blank'), ('18711474647', 'nub/18711474647.html', '_blank'), ('15252200555', 'nub/15252200555.html', '_blank'), ('13590523683', 'nub/13590523683.html', '_blank'), ('13037083630', 'nub/13037083630.html', '_blank'), ('13500003263', 'nub/13500003263.html', '_blank'), ('13878125227', 'nub/13878125227.html', '_blank'), ('13980001174', 'nub/13980001174.html', '_blank'), ('18781123577', 'nub/18781123577.html', '_blank'), ('18228020937', 'nub/18228020937.html', '_blank'), ('18626150901', 'nub/18626150901.html', '_blank'), ('13892166609', 'nub/13892166609.html', '_blank'), ('18909112988', 'nub/18909112988.html', '_blank'), ('13664669009', 'nub/13664669009.html', '_blank'), ('15111331181', 'nub/15111331181.html', '_blank'), ('15326870529', 'nub/15326870529.html', '_blank'), ('18702957672', 'nub/18702957672.html', '_blank'), ('13232512495', 'nub/13232512495.html', '_blank'), ('18933590464', 'nub/18933590464.html', '_blank'), ('15898485020', 'nub/15898485020.html', '_blank'), ('15648199554', 'nub/15648199554.html', '_blank'), ('13007316627', 'nub/13007316627.html', '_blank'), ('18623005632', 'nub/18623005632.html', '_blank')]

cour.execute('SELECT * FROM number')
row = cour.fetchone()
print(row)

('13522663258', 'nub/13522663258.html', '_blank')

我们可以看到，fetchone()返回的只是一行的数据，而fetchall()返回的是全部的数据。

类型

cour.execute('SELECT * FROM number')
row = cour.fetchone()
print(type(row))

<class 'tuple'>

cour.execute('SELECT * FROM number')
row = cour.fetchall()
print(type(row))

<class 'list'>

cour.execute('SELECT * FROM number')
row = cour.fetchall()
print(type(row[0]))

<class 'tuple'>

我们可以看到，fetchone()返回的结果是元组类型，而fetchall()返回的结果是列表类型，并且列表中的每一项都是元组类型。

查看数据方式

如果要查看所有数据的话，使用fetchone()就要用循环的方式实现，而使用fetchall()就可以通过直接输出的方式实现。

cour.execute('SELECT * FROM number')
row = cour.fetchone()
while row:
    print(row)
    row = cour.fetchone()

('13522663258', 'nub/13522663258.html', '_blank')
('18716872350', 'nub/18716872350.html', '_blank')
('15023533998', 'nub/15023533998.html', '_blank')
('15136797185', 'nub/15136797185.html', '_blank')
('13988208612', 'nub/13988208612.html', '_blank')
('15637987958', 'nub/15637987958.html', '_blank')
······

row = cour.fetchall()
print(row)

[('13522663258', 'nub/13522663258.html', '_blank'), ('18716872350', 'nub/18716872350.html', '_blank'), ('15023533998', 'nub/15023533998.html', '_blank'), ('15136797185', 'nub/15136797185.html', '_blank'), ('13988208612', 'nub/13988208612.html', '_blank'), ('15637987958', 'nub/15637987958.html', '_blank'), ('13101457722', 'nub/13101457722.html', '_blank'), ('13024837779', 'nub/13024837779.html', '_blank'), ('13419716513', 'nub/13419716513.html', '_blank'), ('13419719513', 'nub/13419719513.html', '_blank'), ('15903107257', 'nub/15903107257.html', '_blank'), ('15987734078', 'nub/15987734078.html', '_blank'), ('13994996100', 'nub/13994996100.html', '_blank'), ('13951098420', 'nub/13951098420.html', '_blank'), ('13951095420', 'nub/13951095420.html', '_blank'), ('13664147043', 'nub/13664147043.html', '_blank'), ('15180058434', 'nub/15180058434.html', '_blank'), ('13578662673', 'nub/13578662673.html', '_blank'), ('15180056434', 'nub/15180056434.html', '_blank'), ('15916948627', 'nub/15916948627.html', '_blank'), ('15876269489', 'nub/15876269489.html', '_blank'), ('13902231296', 'nub/13902231296.html', '_blank'), ('13561520488', 'nub/13561520488.html', '_blank'), ('15367287394', 'nub/15367287394.html', '_blank'), ('15986769022', 'nub/15986769022.html', '_blank'), ('13922531005', 'nub/13922531005.html', '_blank'), ('13329102544', 'nub/13329102544.html', '_blank'), ('15855999621', 'nub/15855999621.html', '_blank'), ('18711474647', 'nub/18711474647.html', '_blank'), ('15252200555', 'nub/15252200555.html', '_blank'), ('13590523683', 'nub/13590523683.html', '_blank'), ('13037083630', 'nub/13037083630.html', '_blank'), ('13500003263', 'nub/13500003263.html', '_blank'), ('13878125227', 'nub/13878125227.html', '_blank'), ('13980001174', 'nub/13980001174.html', '_blank'), ('18781123577', 'nub/18781123577.html', '_blank'), ('18228020937', 'nub/18228020937.html', '_blank'), ('18626150901', 'nub/18626150901.html', '_blank'), ('13892166609', 'nub/13892166609.html', '_blank'), ('18909112988', 'nub/18909112988.html', '_blank'), ('13664669009', 'nub/13664669009.html', '_blank'), ('15111331181', 'nub/15111331181.html', '_blank'), ('15326870529', 'nub/15326870529.html', '_blank'), ('18702957672', 'nub/18702957672.html', '_blank'), ('13232512495', 'nub/13232512495.html', '_blank'), ('18933590464', 'nub/18933590464.html', '_blank'), ('15898485020', 'nub/15898485020.html', '_blank'), ('15648199554', 'nub/15648199554.html', '_blank'), ('13007316627', 'nub/13007316627.html', '_blank'), ('18623005632', 'nub/18623005632.html', '_blank')]

fetchone()每次只返回查询结果的一行数据，从第一行开始，每使用一次fetchone()就依次向下遍历一行，直到遍历结果为空，而fetchall()分别将查询结果的每一行数据添加到不同的元组中，再把所有元组添加到一个列表中。

fetchone()和fetchall()在外观上最大的区别就是one和all，one是一的意思，所以fetchone()每次只返回一行数据，all是所有的意思，所以fetchall()返回所有数据。这样是不是好记一点呢(😏)