SQLAlchemyORM框架详解SQLAlchemy ORM 框架详解
⼀、安装
pip install sqlalchemy==1.4.7
pip install pymysql # 连接mysql数据库所需库
⼆、连接数据库
from sqlalchemy import create_engine
engine = create_engine(f"mysql+pymysql://{数据库账号}:{密码}@{数据库地址}:3306/{数据库}", echo=True) print(engine)
------打印结果------
Engine(mysql+pymysql://root:***@127.0.0.1:3306/scrapy)
三、创建会话通道
import sessionmaker
maker = sessionmaker(bind=engine)
session = maker()
print(session)
------打印结果----------------
&session.Session object at 0x03BB4400>
四、关闭会话通道
session.close_all()
五、创建数据表模型
创建models模块，存放模型对象
创建表的模型对象，要继承declarative_base对象
__tablename__：数据库中表的名称
字段要与数据库中字段对应
from sqlalchemy import Column, Integer, String
declarative import declarative_base
Base = declarative_base()
class User(Base):
__tablename__ ='users'
id= Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(20))
fullname = Column(String(32))
password = Column(String(32))
def__repr__(self):# 当查询的时候，返回模型结果数据时调⽤
return f"<User(name='{self.name}', fullname='{self.fullname}', password='{self.password}')>"
六、⾃动⽣成数据表（在没有表的情况下）
七、数据库操作
1、添加
mysql下载jar包1）add: 单条添加
# 实例化模型类
ed_user = User(name="desire", fullname="asdfasdf", password="123123")
# 添加
session.add(ed_user)
# 提交
sessionmit()
====> sql
INSERT INTO users (name, fullname, password)VALUES(%(name)s,%(fullname)s,%(password)s)
[generated in0.00054s] {'name': 'desire','fullname': 'asdfasdf','password': '123123'}
2）add_all: 批量添加（内部还是使⽤的add）
# 列表每个元素为模型类实例
session.add_all([
User(name='wendy', fullname='Wendy Williams', password='windy'),
User(name='mary', fullname='Mary Contrary', password='mary'),
User(name='fred', fullname='Fred Flintstone', password='freddy')])
sessionmit()
==> sql
INSERT INTO users (name, fullname, password)VALUES(%(name)s,%(fullname)s,%(password)s)
[cached since 0.005862s ago] {'name': 'wendy','fullname': 'Wendy Williams','password': 'windy'}
INSERT INTO users (name, fullname, password)VALUES(%(name)s,%(fullname)s,%(password)s)
[cached since 0.01483s ago] {'name': 'mary','fullname': 'Mary Contrary','password': 'mary'}
INSERT INTO users (name, fullname, password)VALUES(%(name)s,%(fullname)s,%(password)s)
[cached since 0.01808s ago] {'name': 'fred','fullname': 'Fred Flintstone','password': 'freddy'}
2、修改
1）⽅式⼀
根据ID查询出来数据实体类
然后直接修改数据实体类中的数据
进⾏commit提交，
user = (User,8)
user.password ="123456"
sessionmit()
==> sql
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users
WHERE users.id =%(pk_1)s
-- [generated in 0.00105s] {'pk_1': 8}
UPDATE users SET password=%(password)s WHERE users.id =%(users_id)s
-- [generated in 0.00042s] {'password': '123456', 'users_id': 8}
2）⽅式⼆
通过链式调⽤进⾏更新操作
update参数为字典形式，字典的key要跟列名对应
更新成功返回1，更新失败返回0
session.query(User).filter(User.id==8).update({"password":"654321"})
sessionmit()
==> sql
UPDATE users SET password=%(password)s WHERE users.id =%(id_1)s
-- [generated in 0.00195s] {'password': '654321', 'id_1': 8}
3、查询
1）查询所有数据
通过query进⾏查询，使⽤all()查询所有数据，返回数据为列表嵌套模型类
可以通过循环遍历获取
可以通过【类名.属性名】获取指定的数据
users = session.query(User).all()
for user in users:
print(user, user.name)
==> sql
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users
==> 打印结果
<User(name='desire', fullname='asdfasdf', password='123123')> desire
<User(name='wendy', fullname='Wendy Williams', password='windy')> wendy
<User(name='mary', fullname='Mary Contrary', password='mary')> mary
<User(name='fred', fullname='Fred Flintstone', password='freddy')> fred
2）查询第⼀条数据
使⽤first查询第⼀条数据
返回的是模型类，可以通过【类名.属性名】获取指定的数据
user = session.query(User).first()
print(user, user.name)
==> sql
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users
LIMIT%(param_1)s
-- [generated in 0.00076s] {'param_1': 1}
==> 打印结果
<User(name='desire', fullname='asdfasdf', password='123123')> desire
3）查询指定列数据
在query中添加多个参数，参数可以为模型类，也可以为列名，返回的数据为列表嵌套元组
通过循环遍历获取
同样可以使⽤【类名.属性名】获取指定的数据
users = session.query(User.name, User.fullname).all()
for user in users:
print(user, user.name,user.fullname)
==> sql
SELECT users.name AS users_name, users.fullname AS users_fullname
FROM users
==> 打印结果
('desire', 'asdfasdf') desire asdfasdf
('wendy', 'Wendy Williams') wendy Wendy Williams
('mary', 'Mary Contrary') mary Mary Contrary
('fred', 'Fred Flintstone') fred Fred Flintstone
4）条件查询
1. filter条件查询
使⽤filter进⾏条件查询，查询条件为【User.fullname==Wendy Williams】
使⽤filter指定列为条件时，需要使⽤【类名.属性名】当做条件
users = session.query(User).filter(User.fullname =="Wendy Williams").all()
for user in users:
print(user)
==> sql
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users
WHERE users.fullname =%(fullname_1)s
-- [generated in 0.00043s] {'fullname_1': 'Wendy Williams'}
==> 打印结果
<User(name='wendy', fullname='Wendy Williams', password='windy')>
2. filter_by条件查询
使⽤filter_by进⾏条件查询，可以把模型类属性当做filter_by参数进⾏条件查询【fullname=“Wendy Williams”】可以简化代码复杂度
users = session.query(User).filter_by(fullname="Wendy Williams").all()
for user in users:
print(user)
==> sql
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users
WHERE users.fullname =%(fullname_1)s
-- [generated in 0.00053s] {'fullname_1': 'Wendy Williams'}
==> 打印结果
<User(name='wendy', fullname='Wendy Williams', password='windy')>
3.多条件查询
可以使⽤链式调⽤，添加多个filter/filter_by
也可以在filter/filter_by添加多个参数进⾏多条件查询
users = session.query(User).filter_by(fullname="Wendy Williams", name="wendy").all()
# users = session.query(User).filter_by(fullname="Wendy Williams").filter_by(name="wendy").all()
# users = session.query(User).filter(User.fullname=="Wendy Williams", User.name=="wendy").all()
for user in users:
print(user)
==> sql
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users
WHERE users.fullname =%(fullname_1)s AND users.name =%(name_1)s
-- [generated in 0.00053s] {'fullname_1': 'Wendy Williams', 'name_1': 'wendy'}
==> 打印结果
<User(name='wendy', fullname='Wendy Williams', password='windy')>
5）模糊查询
like模糊查询，不区分⼤⼩写，但是在其他后端区分⼤⼩写（暂时没遇到过）
ilike模糊查询，对于保证不区分⼤⼩写的⽐较，推荐使⽤这个
users = session.query(User).filter(User.fullname.like('%F%')).all()
# users = session.query(User).filter(User.fullname.ilike('%F%')).all()
for user in users:
print(user)
==> sql
-- like>>
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users
WHERE users.fullname LIKE%(fullname_1)s
-- [generated in 0.00051s] {'fullname_1': '%F%'}
-- ilike>>
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users
WHERE lower(users.fullname)LIKE lower(%(fullname_1)s)
-- [generated in 0.00061s] {'fullname_1': '%F%'}
==> 打印结果
<User(name='desire', fullname='asdfasdf', password='123123')>
<User(name='fred', fullname='Fred Flintstone', password='freddy')>
6）IN查询
1. IN查询
查询多个id的数据时，可以使⽤IN查询
可以使⽤【类名.属性名.in_()】进⾏IN查询
IN查询参数为列表
返回值为列表
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论