python 数据库查询list tuple python 数据库查询返回list或tuple实例
chdeWang 人气:0MySQLdb默认查询结果都是返回tuple,输出时候不是很方便,必须按照0,1这样读取,无意中在网上找到简单的修改方法,就是传递一个cursors.DictCursor就行。
默认程序:
import MySQLdb db = MySQLdb.connect(host='localhost', user='root', passwd='123456', db='test') cur = db.cursor() cur.execute('select * from user') rs = cur.fetchall() print rs # 返回类似如下 # ((1000L, 0L), (2000L, 0L), (3000L, 0L))
修改后:
import MySQLdb import MySQLdb.cursors db = MySQLdb.connect(host='localhost', user='root', passwd='123456', db='test', cursorclass=MySQLdb.cursors.DictCursor) cur = db.cursor() cur.execute('select * from user') rs = cur.fetchall() print rs # 返回类似如下 # ({'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)
我的实践:
输出为元组类型:
import pymysql db = pymysql.connect("localhost", "root", "123456", "filestore") cursor = db.cursor() sql='select * from tablelist where id>%s' %4 #查询方法一 cursor.execute(sql) result=cursor.fetchall() print('result',result) sql2='select * from tablelist where id>%s' values=('4') # 此处为元组类型 #查询方法二 cursor.execute(sql2,values) result2=cursor.fetchall() print('result2',result2) id_list=[] tablename_list=[] tabletime_lsit=[] cursor.execute('select * from tablelist where id>%s',[4,]) result3=cursor.fetchall() print('type(result3)',type(result3)) #对((6, 'engineeringdata20180901', '1535731200'),)类型数据的提取 for i in range(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) <class 'tuple'> [6, 618] ['1535731200', '1535990400'] ['engineeringdata20180901', 'engineeringdata20180904']
输出为list类型:
list_id=[] list_tablename=[] list_tabletime=[] list=get_list('select * from tablelist where id>%s',[4]) print('list:',list) # 对[{'id': 6, 'tablename': 'engineeringdata20180901', 'tabletime': '1535731200'},]类型数据的提取 for i in range(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 返回的元组数据转换为列表
我就废话不多说了,大家还是直接看代码吧!
from itertools import chain ... sql="select elems from table" cursor.execute(sql) elems = cursor.fetchall() resultlist = list(chain.from_iterable(elems)) ...
以上这篇python 数据库查询返回list或tuple实例就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。
加载全部内容