python 数据库查询返回list或tuple实例
MySQLdb默认查询结果都是返回tuple，输出时候不是很方便，必须按照0，1这样读取，无意中在网上找到简单的修改方法，就是传递一个cursors.DictCursor就行。
默认程序：
importMySQLdb
db=MySQLdb.connect(host='localhost',user='root',passwd='123456',db='test')
cur=db.cursor()
cur.execute('select*fromuser')
rs=cur.fetchall()
printrs
#返回类似如下
#((1000L,0L),(2000L,0L),(3000L,0L))
修改后：
importMySQLdb
importMySQLdb.cursors
db=MySQLdb.connect(host='localhost',user='root',passwd='123456',db='test',
cursorclass=MySQLdb.cursors.DictCursor)
cur=db.cursor()
cur.execute('select*fromuser')
rs=cur.fetchall()
printrs
#返回类似如下
#({'age':0L,'num':1000L},{'age':0L,'num':2000L},{'age':0L,'num':3000L})
或者也可以用下面替换connect和cursor部分
db=MySQLdb.connect(host='localhost',user='root',passwd='123456',db='test')
cur=db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
我的实践：
输出为元组类型：
importpymysql
db=pymysql.connect("localhost","root","123456","filestore")
cursor=db.cursor()
sql='select*fromtablelistwhereid>%s'%4
#查询方法一
cursor.execute(sql)
result=cursor.fetchall()
print('result',result)
sql2='select*fromtablelistwhereid>%s'
values=('4')#此处为元组类型
#查询方法二
cursor.execute(sql2,values)
result2=cursor.fetchall()
print('result2',result2)
id_list=[]
tablename_list=[]
tabletime_lsit=[]
cursor.execute('select*fromtablelistwhereid>%s',[4,])
result3=cursor.fetchall()
print('type(result3)',type(result3))
#对((6,'engineeringdata20180901','1535731200'),)类型数据的提取
foriinrange(len(result3)):
id_list.append(result3[i][0])
tablename_list.append(result3[i][1])
tabletime_lsit.append(result3[i][2])
print(id_list)
print(tabletime_lsit)
print(tablename_list)
cursor.close()
db.close()
#输出结果：
result((6,'engineeringdata20180901','1535731200'),(618,'engineeringdata20180904','1535990400'))
result2((6,'engineeringdata20180901','1535731200'),(618,'engineeringdata20180904','1535990400'))
type(result3)
[6,618]
['1535731200','1535990400']
['engineeringdata20180901','engineeringdata20180904']
输出为list类型：
list_id=[]
list_tablename=[]
list_tabletime=[]
list=get_list('select*fromtablelistwhereid>%s',[4])
print('list:',list)
#对[{'id':6,'tablename':'engineeringdata20180901','tabletime':'1535731200'},]类型数据的提取
foriinrange(len(list)):
print(list[i])
list_id.append(list[i]['id'])
list_tablename.append(list[i]['tablename'])
list_tabletime.append(list[i]['tabletime'])
print('list_id:',list_id)
print('list_tabletime:',list_tabletime)
print('list_tablename:',list_tablename)
#输出结果为：
list:[{'id':6,'tablename':'engineeringdata20180901','tabletime':'1535731200'},{'id':618,'tablename':'engineeringdata20180904','tabletime':'1535990400'}]
{'id':6,'tablename':'engineeringdata20180901','tabletime':'1535731200'}
{'id':618,'tablename':'engineeringdata20180904','tabletime':'1535990400'}
list_id:[6,618]
list_tabletime:['1535731200','1535990400']
list_tablename:['engineeringdata20180901','engineeringdata20180904']
补充知识：python下将pymysql返回的元组数据转换为列表
我就废话不多说了，大家还是直接看代码吧！
fromitertoolsimportchain ... sql="selectelemsfromtable" cursor.execute(sql) elems=cursor.fetchall() resultlist=list(chain.from_iterable(elems)) ...
以上这篇python数据库查询返回list或tuple实例就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。