python 数据库查询返回list或tuple实例
作者:chdeWang 时间:2024-01-25 01:28:45
MySQLdb默认查询结果都是返回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))
...
来源:https://blog.csdn.net/weixin_38383877/article/details/82379373
标签:python,数据库,查询,list,tuple
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
MySQL Dump/Restore
2010-10-14 13:49:00
TensorFlow中关于tf.app.flags命令行参数解析模块
2021-10-17 03:40:40
![](https://img.aspxhome.com/file/2023/6/101146_0s.png)
python 使用fileinput读取文件
2021-09-07 23:48:43
![](https://img.aspxhome.com/file/2023/4/115084_0s.jpg)
Python实现获取汉字偏旁部首的方法示例【测试可用】
2022-10-26 16:42:16
![](https://img.aspxhome.com/file/2023/5/76775_0s.png)
python中安装模块包版本冲突问题的解决
2021-07-23 11:04:29
培养色感的一些经验分享
2013-11-10 03:47:03
![](https://img.aspxhome.com/file/UploadPic/20071/200713110530379s.gif)
基于Python制作一个桌面宠物
2022-06-12 11:41:22
![](https://img.aspxhome.com/file/2023/2/100942_0s.jpg)
fckeditor 修改记录添加行距功能插件
2022-04-20 23:05:23
如何使div在任何分辩率的情况下居中
2007-08-13 09:10:00
sql IDENTITY_INSERT对标识列的作用和使用
2024-01-20 16:05:04
![](https://img.aspxhome.com/file/2023/6/84356_0s.png)
Python基于正则表达式实现计算器功能
2021-08-17 13:00:02
微信小程序上传图片功能(附后端代码)
2023-07-24 04:21:40
![](https://img.aspxhome.com/file/2023/9/55969_0s.jpg)
mysql query browser中文乱码的解决方法
2024-01-17 14:44:51
![](https://img.aspxhome.com/file/2023/1/126311_0s.gif)
完整java开发中JDBC连接数据库代码和步骤
2024-01-21 19:27:23
SQL Server 移动系统数据库
2024-01-15 11:35:54
![](https://img.aspxhome.com/file/2023/0/102070_0s.png)
11个案例讲透Python函数参数小结
2023-08-01 13:38:10
截字符串 去除HTML标记
2023-07-29 17:01:08
浅谈Python数学建模之固定费用问题
2022-09-08 10:26:41
![](https://img.aspxhome.com/file/2023/6/135326_0s.png)
Display SQL Server Login Mode
2024-04-18 10:55:50
PHP扩展Swoole实现实时异步任务队列示例
2023-11-10 05:11:22