有勇气的牛排博客
☰

python SQLAlchemy数据库操作

有勇气的牛排 208 Python 2023-05-18 20:28:11

文章目录

哈喽，大家好，我是有勇气的牛排（全网同名）🐮🐮🐮

有问题的小伙伴欢迎在文末评论，点赞、收藏是对我最大的支持！！！。

1 增

Customer为表名
image.png

try:
	username = '123456'
	customerInfo = Customer(username=username)
	db.add(customerInfo)            # 添加数据
	db.commit()                     # 提交即保存到数据库
except Exception as e:
	db.rollback()
	print(e)

2 删

# 删除
@route('/professionDeleteType/<num>')
def professionDeleteType(db,num):
    print(num)

    try:
        print('--------删除--------------')
        print(num)
        TypeNameInfo = db.query(Profession).filter(Profession.pid==int(num)).delete()
        db.commit()  # 提交即保存到数据库
        # db.close()  # 关闭会话
    except Exception as e:
        print(e)

    return redirect('/profession')

3 改

Users表

image.png

try:
	username = db.query(Users).filter_by(username=username).first()
	username.nickname = Hnickname
	username.img_url = Himg_url
	db.commit()	
except:
	db.rollback()
	print(e)
	return json.dumps({'data': 0, 'info': '内部错误，请刷新！！！'})

4 基础查询

4.1 基础查询汇总

# select * from users;
result = db.query(Users).all()
# select uid, username from users;
result = db.query(Users.uid, Users.username).all()
# select * from users where uid=17 and username='666';
result = db.query(Users).filter_by(uid=17, username='666').all()
# select * from users where uid>=16 or username='666';
result = db.query(Users).filter(or_(
	Users.uid >= 16,
	Users.username == '666'
)).all()

4.2 限制查询

# 查询前3行
result = db.query(Users).limit(3).all()
# select * from users limit 3,5;
result = db.query(Users).limit(3).offset(3).all()

limit：查询多少条
offset：偏移量 -> 3 表示从第4条开始

4.3 计数

select count(*) from users where ...

filter：== >= < > != != in not

count = db.query(Users).filter(Users.uid > 16).count()
print(count)

4.4 去重

select distinct(openid) from users;

result = db.query(Users.openid).distinct(Users.openid).all()
print(result)
for row in result:
    print(row.openid)

4.5 排序

select * from users order by uid desc;

result = db.query(Users).order_by(Users.uid.desc()).all()

4.6 模糊查询

select * from users where username like '%929%';

result = db.query(Users).filter(Users.username.like('%929%')).all()

4.7 分组查询

select * from users group by isAdmin;

result = db.query(Users).group_by(Users.openid).all()
result = db.query(Users).group_by(Users.openid).having(Users.uid > 2).all()

4.8 聚合函数

min、max、avg、sum

select sum(u_dealNum) from users;

result = db.query(func.sum(Users.u_dealNum)).first()
print(result)

5 多表查询

5.1 内连接

查询发表过文章的 用户信息+文章信息

select a_title from users, article where users.uid=article.uid and article.a_id=1;
select a_title from article inner join users on users.uid=article.uid where article.a_id=1;

# 多表查询时，返回的结果集不再是单纯的[Model, Model]数据结构，而是每张表的结果有独立的对象来维护
result = db.query(Article, Users).join(Users, Article.uid == Users.uid).filter(Article.a_id == 1).all()
print(result)
for article, users in result:
	print(article.uid, article.a_title, users.username)

result = db.query(Article, Users.username).join(Users, Article.uid == Users.uid).filter(Article.a_id == 1).all()
print(result)
for article, username in result:
    print(article.uid, article.a_title, username)

5.2 左外连接

查询所有用户 写的文章阅读量

select nickname,a_title,sum(a_pageviews) as total from users left join article on users.uid=article.uid group by users.uid having total>0;

result = db.query(Users.uid, Users.nickname, func.sum(Article.a_pageviews)).outerjoin(Article, Users.uid == Article.uid).group_by(Users.uid).all()

5.3 复杂查询

and和or混用: username like 'charles' or uid>1 and nickname='导演'

result = db.query(Users).filter(
        or_(Users.username.like('charles'), and_(Users.uid > 1, Users.nickname == "导演"))).all()
result = db.query(Users).filter(Users.username.like('charles'), or_(Users.uid > 1, Users.nickname == "导演")).all()
print(result)
for row in result:
    print(row.uid, row.username)

5.4 三表连接

result = db.query(Article, Users).join(Users, Article.uid == Users.uid) \
        .join(Label, Article.l_id == Label.l_id).all()

5.5 利用SQLAlchemy执行原生SQL

result = db.execute("select * from users").fetchall()
print(result)

6 原生语句查询 返回json格式

try:
	sql = ''
    Info = db.execute(sql)
    print(Info)
except Exception as e:
    db.rollback()
    print(e)

results = []
for i in searchs:

    bid = i['bid']                  # 索引
    isbn = i['isbn']                # isbn
    print(bid)
    print(isbn)
    
    result = {
        "bid": bid,
        "isbn": isbn,
    }
    print(result)
    results.append(result)

data = {
    "status": '1',
    "results": results
}
return json.dumps(data)

3 统计数量

from sqlalchemy import func

older_num = db.query(func.count(Order_history.seller)).filter_by(seller=uname).scalar()

Sqlchemy查询结果转json
MySQL 查询统计 日期查询

留言

专栏
文章
加入群聊
程序员兼职接单交流
编程交流