介绍
这篇文章主要为大家展示了python如何实现数据库查询返回列表或元组,内容简而易懂,希望大家可以学习一下,学习完之后肯定会有收获的,下面让小编带大家一起来看看吧。
MySQLdb默认查询结果都是返回元组,输出时候不是很方便,必须按照0,1这样读取,无意中在网上找到简单的修改方法,就是传递一个游标。DictCursor就行。
默认程序:
进口MySQLdb db=MySQLdb.connect(主机=& # 39;localhost # 39;,用户=& # 39;根# 39;,passwd=& # 39; 123456 & # 39;, db=& # 39;测试# 39;) 坏蛋=db.cursor () cur.execute(& # 39;从用户选择* # 39;) rs=cur.fetchall () 打印rs #返回类似如下 # ((1000 l, 0 l) (2000 l, 0 l) (3000 l, 0 l)
修改后:
进口MySQLdb 进口MySQLdb.cursors db=MySQLdb.connect(主机=& # 39;localhost # 39;,用户=& # 39;根# 39;,passwd=& # 39; 123456 & # 39;, db=& # 39;测试# 39;, cursorclass=MySQLdb.cursors.DictCursor) 坏蛋=db.cursor () cur.execute(& # 39;从用户选择* # 39;) rs=cur.fetchall () 打印rs #返回类似如下 #({& # 39;年龄# 39;:0 l & # 39; num # 39;: 1000 l},{& # 39;年龄# 39;:0 l & # 39; num # 39;: 2000 l},{& # 39;年龄# 39;:0 l & # 39; num # 39;: 3000 l})
或者也可以用下面替换连接和光标部分
db=MySQLdb.connect(主机=& # 39;localhost # 39;,用户=& # 39;根# 39;,passwd=& # 39; 123456 & # 39;, db=& # 39;测试# 39;)
引用>
cur=db.cursor (cursorclass=MySQLdb.cursors.DictCursor)我的实践:
输出为元组类型:
进口pymysql db=pymysql.connect (“localhost",“root",“123456”、“filestore") 光标=db.cursor () sql=& # 39; select * from tablelist id> % & # 39;% 4 #查询方法一 cursor.execute (sql) 结果=cursor.fetchall () 打印(& # 39;结果# 39;,结果) sql2=& # 39; select * from tablelist id> % & # 39; 值=(& # 39;4 & # 39;)#此处为元组类型 #查询方法二 cursor.execute (sql2值) result2=cursor.fetchall () 打印(& # 39;result2& # 39;, result2) id_list=[] tablename_list=[] tabletime_lsit=[] cursor.execute (& # 39; select * from tablelist id> % & # 39;, [4]) result3=cursor.fetchall () 打印(& # 39;类型(result3) & # 39;,类型(result3)) #对((& # 39;engineeringdata20180901& # 39;, & # 39; 1535731200 & # 39;),)类型数据的提取 因为我在范围(len (result3)): id_list.append (result3[我][0]) tablename_list.append (result3[我][1]) tabletime_lsit.append (result3[我][2]) 打印(id_list) 打印(tabletime_lsit) 打印(tablename_list) cursor.close () db.close () #输出结果: 结果((& # 39;engineeringdata20180901& # 39;, & # 39; 1535731200 & # 39;),(618年,& # 39;engineeringdata20180904& # 39;, & # 39; 1535990400 & # 39;)) result2 ((& # 39; engineeringdata20180901& # 39;, & # 39; 1535731200 & # 39;),(618年,& # 39;engineeringdata20180904& # 39;, & # 39; 1535990400 & # 39;)) 类型(result3) & lt;类& # 39;元组# 39;比; [618] [& # 39;1535731200 & # 39;& # 39;1535990400 & # 39;】 [& # 39;engineeringdata20180901& # 39; & # 39; engineeringdata20180904& # 39;]输出为列表类型:
list_id=[] list_tablename=[] list_tabletime=[]=get_list列表(& # 39;select * from tablelist id> % & # 39;, [4]) 打印(& # 39;清单:& # 39;,列表) #对[{& # 39;id # 39;: 6日& # 39;表# 39;:& # 39;engineeringdata20180901& # 39;, & # 39; tabletime& # 39;: & # 39; 1535731200 & # 39;},]类型数据的提取 因为我在范围(len(列表): print(列表[我]) list_id.append(列出[我][& # 39;id # 39;]) list_tablename.append(列出[我][& # 39;表# 39;]) list_tabletime.append(列出[我][& # 39;tabletime& # 39;]) 打印(& # 39;list_id: & # 39;, list_id) 打印(& # 39;list_tabletime: & # 39;, list_tabletime) 打印(& # 39;list_tablename: & # 39;, list_tablename) #输出结果为: 列表:[{& # 39;id # 39;: 6日& # 39;表# 39;:& # 39;engineeringdata20180901& # 39;, & # 39; tabletime& # 39;: & # 39; 1535731200 & # 39;}, {& # 39; id # 39;: 618 & # 39;表# 39;:& # 39;engineeringdata20180904& # 39;, & # 39; tabletime& # 39;: & # 39; 1535990400 & # 39;}] {& # 39;id # 39;: 6日& # 39;表# 39;:& # 39;engineeringdata20180901& # 39;, & # 39; tabletime& # 39;: & # 39; 1535731200 & # 39;} {& # 39;id # 39;: 618 & # 39;表# 39;:& # 39;engineeringdata20180904& # 39;, & # 39; tabletime& # 39;: & # 39; 1535990400 & # 39;} list_id (618): list_tabletime: [& # 39; 1535731200 & # 39; & # 39; 1535990400 & # 39;】 list_tablename: [& # 39; engineeringdata20180901& # 39; & # 39; engineeringdata20180904& # 39;]python如何实现数据库查询返回列表或元组