Discuss / Python / 打卡,学到了,原来SQL语句里可以直接筛选啊,我还傻傻的在想怎么提取所有数据后在做筛选排序

打卡,学到了,原来SQL语句里可以直接筛选啊,我还傻傻的在想怎么提取所有数据后在做筛选排序

Topic source

import os,sqlite3

db_file=os.path.join(os.path.dirname(__file__),'test.db')
if os.path.isfile(db_file):
os.remove(db_file)
#初始数据
con=sqlite3.connect(db_file)
cur=con.cursor()
cur.execute('create table user(id varchar(20) primary key,name varchar(20),score int)')
cur.execute(r"insert into user values ('A-001','Adam',95)")
cur.execute(r"insert into user values ('A-002','Bart',62)")
cur.execute(r"insert into user values ('A-003','Lisa',78)")
cur.close()
con.commit()
con.close()

def get_score_in(low,high):
#返回指定分数区间的名字,按分数从低到高排列
'''首先连接到数据库,然后查询语句,拿到结果集'''
con=sqlite3.connect(db_file)
cur=con.cursor()
cur.execute('select name from user where score between ? and ? order by score',(low,high))
values=cur.fetchall()
result=[]
for i in values:
result.append(i[0])
return result

OTO丶文强

#2 Created at ... [Delete] [Delete and Lock User]

我就是傻傻的把数据提出来筛选的,一看评论原来在SQL里筛选这么简单

Jinson100

#3 Created at ... [Delete] [Delete and Lock User]

请问是cur.execute('select name from user where score between ? and ? order by score',(low,high))这句起到筛选和排序的作用吗,如果我想将分数从高到低排列的话又该怎么办?


  • 1

Reply