一,安装

pip3  install  sqlalchemy   安装sqlalchemy
pip3  install  pymysql      安装连接数据库的包
安装数据库

二, 简介

object-relation mapping（ORM）技术，把关系数据库的表结构映射到对象上

三, 创建数据表

 1 # 1,导入官宣模型
 2 from sqlalchemy.ext.declarative import declarative_base
 3 # 导入数据类型
 4 from sqlalchemy import Column, Integer, String
 5 # 导入连接数据库的包
 6 from sqlalchemy import create_engine
 7 
 8 # 2,实例化官宣模型
 9 Base = declarative_base()
10 
11 
12 # 3,创建object
13 # 当前这个object继承了Base也就是说代表object继承了ORM模型
14 class User(Base):  # 相当于Django Models中的Model
15     # (1),为table创建名字(数据库表名)
16     __tablename__ = "user"
17     # (2),创建字段
18     # id字段是数字类型,是主键,自增长
19     id = Column(Integer,primary_key=True,autoincrement=True)
20     # name字段是字符串类型(最大长度32),设置索引(按照该字段排序
21     name = Column(String(32),index=True)
22 
23 
24 # 4,创建数据库引擎
25 engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/sqlalchemy?charset=utf8")
26 # 5,Base自动检索所有继承Base的ORM对象,并且创建所有的数据表
27 Base.metadata.create_all(engine)

运行程序,然后就在数据库中的sqlalchemy库中创建好了一张名为user的表

 

四 , 增删改查操作

通过上面的代码和操作,我们已经通过sqlalchemy创建好了一张user表,接下来看一下怎么对表进行简单的增删改查操作

数据库中 增 改 删 都是操作,也就是说执行以上三种操作的时候一定要commit

(1) 增加数据

 1 # SQL中:
 2     # insert 添加数据
 3     # insert One  添加一条数据
 4     # insert into user(name) values("liang")
 5 # ORM中:
 6 # 1,导入之前做好的ORM对象User
 7 from onetable.create_table import User
 8 # 导入sqlalchemy.orm中的sessionmaker
 9 from sqlalchemy.orm import sessionmaker
10 # 导入之前创建好的create_engine
11 from onetable.create_table import engine
12 
13 # 2,使用User模型创建一条数据
14 user1 = User(name="liang")
15 
16 # 3, 写入数据库
17 # 创建sessionmaker 会话对象,将数据库引擎engine交给sessionmaker
18 Session = sessionmaker(engine)
19 # 打开会话对象Session
20 db_session = Session()
21 # 在db_session绘画中添加一条UserORM模型创建的数据
22 db_session.add(user1)
23 # 使用db_session会话提交 ,指的是将db_session中的所有指令一次性提交
24 db_session.commit()
25 # 关闭会话
26 db_session.close()
添加一条数据

在这个过程中遇到一个小问题,就是导入自己写的包的时候飘红,但是程序运行没问题,原因是目录设置的问题,然后将上一级目录带上from onetable.create_table import User 就没有问题了

 1 # SQL中:
 2     # insert 添加数据
 3     # insert One  添加一条数据
 4     # insert into user(name) values("liang")
 5 # ORM中:
 6 # 1,导入之前做好的ORM对象User
 7 from onetable.create_table import User
 8 # 导入sqlalchemy.orm中的sessionmaker
 9 from sqlalchemy.orm import sessionmaker
10 # 导入之前创建好的create_engine
11 from onetable.create_table import engine
12 
13 # 2,使用User模型创建数据
14 user2 = User(name="ding")
15 user3 = User(name="doudou")
16 
17 # 3, 写入数据库
18 # 创建sessionmaker 会话对象,将数据库引擎engine交给sessionmaker
19 Session = sessionmaker(engine)
20 # 打开会话对象Session
21 db_session = Session()
22 # 在db_session绘画中添加一条UserORM模型创建的数据
23 db_session.add(user2)
24 db_session.add(user3)
25 # 使用db_session会话提交 ,指的是将db_session中的所有指令一次性提交
26 db_session.commit()
27 # 关闭会话
28 db_session.close()
添加多条数据(第一种)
 1 # SQL中:
 2     # insert 添加数据
 3     # insert One  添加一条数据
 4     # insert into user(name) values("liang")
 5 # ORM中:
 6 # 1,导入之前做好的ORM对象User
 7 from onetable.create_table import User
 8 # 导入sqlalchemy.orm中的sessionmaker
 9 from sqlalchemy.orm import sessionmaker
10 # 导入之前创建好的create_engine
11 from onetable.create_table import engine
12 
13 # 2,使用User模型创建数据
14 user_list = [
15     User(name="111"),
16     User(name="222"),
17     User(name="333"),
18 ]
19 # 3, 写入数据库
20 # 创建sessionmaker 会话对象,将数据库引擎engine交给sessionmaker
21 Session = sessionmaker(engine)
22 # 打开会话对象Session
23 db_session = Session()
24 # 在db_session绘画中添加一条UserORM模型创建的数据
25 db_session.add_all(user_list)
26 # 使用db_session会话提交 ,指的是将db_session中的所有指令一次性提交
27 db_session.commit()
28 # 关闭会话
29 db_session.close()
添加多条数据(第二种)

 

(2) 查询数据

 1 # ORM操作查询数据
 2 # 有了之前insert增加数据的经验,那么查询之前的准备工作也是类似的
 3 from one_table.create_table import User, engine
 4 from sqlalchemy.orm import sessionmaker
 5 
 6 Session = sessionmaker(engine)
 7 db_session = Session()
 8 
 9 # SQL中:
10     # select * from user 查询user表中的所有数据
11 # sqlalchemy中:
12 # 使用db_session会话执行User表query(User)取出全部数据
13 user_all_list = db_session.query(User).all()
14 print(user_all_list)
15 # [<one_table.create_table.User object at 0x000001722FC683C8>, <one_table.create_table.User object at 0x000001722FC68518>, <one_table.create_table.User object at 0x000001722FC684A8>, <one_table.create_table.User object at 0x000001722FC685F8>, <one_table.create_table.User object at 0x000001722FC68588>, <one_table.create_table.User object at 0x000001722FC686D8>]
16 # 查询结果如上,是一个列表,然后我们就可以循环取数据了,然后.出属性
17 for i in user_all_list:
18     print(i.id,i.name)
19 # 4 111
20 # 5 222
21 # 6 333
22 # 2 ding
23 # 3 doudou
24 # 1 liang
25 
26 # 关闭对话
27 db_session.close()
查询所有数据
 1 # ORM操作查询数据
 2 # 有了之前insert增加数据的经验,那么查询之前的准备工作也是类似的
 3 from one_table.create_table import User, engine
 4 from sqlalchemy.orm import sessionmaker
 5 
 6 Session = sessionmaker(engine)
 7 db_session = Session()
 8 
 9 user1 = db_session.query(User).filter(User.id>=3)
10 user2 = db_session.query(User).filter(User.id>=3).first()
11 print(user1)
12 # SELECT user.id AS user_id, user.name AS user_name 
13 # FROM user 
14 # WHERE user.id >= %(id_1)s
15 for i in user1:
16     print(i.id,i.name)
17     # 3 doudou
18     # 4 111
19     # 5 222
20     # 6 333
21 print(user2.id,user2.name)
22 # 3 doudou
23 db_session.close()
查询一条数据

 

(3) 修改数据

 1 from one_table.create_table import User, engine
 2 from sqlalchemy.orm import sessionmaker
 3 
 4 Session = sessionmaker(engine)
 5 db_session = Session()
 6 
 7 # SQL中:
 8     # UPDATE user SET name="haha" WHERE id=2 更新一条数据
 9 # sqlalchemy中:
10     # 使用db_session执行User表query(User)筛选User.id=2的数据filter(User.id==2)
11     # 将name字段的值改为haha  update({"name":"haha"})
12 res = db_session.query(User).filter(User.id==2).update({"name":"haha"})
13 print(res)  # res是当前语句所更新的行数
14 
15 # 提交
16 db_session.commit()
17 db_session.close()
修改一条数据
 1 from one_table.create_table import User, engine
 2 from sqlalchemy.orm import sessionmaker
 3 
 4 Session = sessionmaker(engine)
 5 db_session = Session()
 6 
 7 # SQL中:
 8     # UPDATE user SET name="haha" WHERE id=2 更新一条数据
 9 # sqlalchemy中:
10     # 使用db_session执行User表query(User)筛选User.id=2的数据filter(User.id==2)
11     # 将name字段的值改为haha  update({"name":"haha"})
12 res = db_session.query(User).filter(User.id>=3).update({"name":"haha"})
13 print(res)  # res是当前语句所更新的行数
14 
15 # 提交
16 db_session.commit()
17 db_session.close()
修改多条数据

 

(4)删除数据

 1 from one_table.create_table import User, engine
 2 from sqlalchemy.orm import sessionmaker
 3 
 4 Session = sessionmaker(engine)
 5 db_session = Session()
 6 
 7 # SQL中
 8     # DELETE FROM `user` WHERE id=2
 9 # sqlalchemy中
10 res = db_session.query(User).filter(User.id==2).delete()
11 print(res)
12 
13 db_session.commit()
14 db_session.close()
删除数据

 

(5)高级查询

  1   1 # 高级版查询操作,厉害了哦
  2   2 #老规矩
  3   3 from my_create_table import User,engine
  4   4 from sqlalchemy.orm import sessionmaker
  5   5 
  6   6 Session = sessionmaker(engine)
  7   7 db_session = Session()
  8   8 
  9   9 # 查询数据表操作
 10  10 # and or
 11  11 from sqlalchemy.sql import and_ , or_
 12  12 ret = db_session.query(User).filter(and_(User.id > 3, User.name == 'liang')).all()
 13  13 ret = db_session.query(User).filter(or_(User.id < 2, User.name == 'liang')).all()
 14  14 
 15  15 # 查询所有数据
 16  16 r1 = db_session.query(User).all()
 17  17 
 18  18 # 查询数据 指定查询数据列 加入别名
 19  19 r2 = db_session.query(User.name.label('username'), User.id).first()
 20  20 print(r2.id,r2.username) 
 21  21 
 22  22 # 表达式筛选条件
 23  23 r3 = db_session.query(User).filter(User.name == "liang").all()
 24  24 
 25  25 # 原生SQL筛选条件
 26  26 r4 = db_session.query(User).filter_by(name='liang').all()
 27  27 r5 = db_session.query(User).filter_by(name='liang').first()
 28  28 
 29  29 # 字符串匹配方式筛选条件 并使用 order_by进行排序
 30  30 r6 = db_session.query(User).filter(text("id<:value and name=:name")).params(value=224, name='liang').order_by(User.id).all()
 31  31 
 32  32 #原生SQL查询
 33  33 r7 = db_session.query(User).from_statement(text("SELECT * FROM User where name=:name")).params(name='liang').all()
 34  34 
 35  35 # 筛选查询列
 36  36 # query的时候我们不在使用User ORM对象,而是使用User.name来对内容进行选取
 37  37 user_list = db_session.query(User.name).all()
 38  38 print(user_list)
 39  39 for row in user_list:
 40  40     print(row.name)
 41  41 
 42  42 # 别名映射  name as nick
 43  43 user_list = db_session.query(User.name.label("nick")).all()
 44  44 print(user_list)
 45  45 for row in user_list:
 46  46     print(row.nick) # 这里要写别名了
 47  47 
 48  48 # 筛选条件格式
 49  49 user_list = db_session.query(User).filter(User.name == "liang").all()
 50  50 user_list = db_session.query(User).filter(User.name == "liang").first()
 51  51 user_list = db_session.query(User).filter_by(name="liang").first()
 52  52 for row in user_list:
 53  53     print(row.nick)
 54  54 
 55  55 # 复杂查询
 56  56 from sqlalchemy.sql import text
 57  57 user_list = db_session.query(User).filter(text("id<:value and name=:name")).params(value=3,name="liang")
 58  58 
 59  59 # 查询语句
 60  60 from sqlalchemy.sql import text
 61  61 user_list = db_session.query(User).filter(text("select * from User id<:value and name=:name")).params(value=3,name="liang")
 62  62 
 63  63 # 排序 :
 64  64 user_list = db_session.query(User).order_by(User.id).all()
 65  65 user_list = db_session.query(User).order_by(User.id.desc()).all()
 66  66 for row in user_list:
 67  67     print(row.name,row.id)
 68  68 
 69  69 #其他查询条件
 70  70 """
 71  71 ret = session.query(User).filter_by(name='liang').all()
 72  72 ret = session.query(User).filter(User.id > 1, User.name == 'liang').all()
 73  73 ret = session.query(User).filter(User.id.between(1, 3), User.name == 'liang').all() # between 大于1小于3的
 74  74 ret = session.query(User).filter(User.id.in_([1,3,4])).all() # in_([1,3,4]) 只查询id等于1,3,4的
 75  75 ret = session.query(User).filter(~User.id.in_([1,3,4])).all() # ~xxxx.in_([1,3,4]) 查询不等于1,3,4的
 76  76 ret = session.query(User).filter(User.id.in_(session.query(User.id).filter_by(name='liang'))).all() 子查询
 77  77 from sqlalchemy import and_, or_
 78  78 ret = session.query(User).filter(and_(User.id > 3, User.name == 'liang')).all()
 79  79 ret = session.query(User).filter(or_(User.id < 2, User.name == 'liang')).all()
 80  80 ret = session.query(User).filter(
 81  81     or_(
 82  82         User.id < 2,
 83  83         and_(User.name == 'eric', User.id > 3),
 84  84         User.extra != ""
 85  85     )).all()
 86  86 # select * from User where id<2 or (name="eric" and id>3) or extra != "" 
 87  87 
 88  88 # 通配符
 89  89 ret = db_session.query(User).filter(User.name.like('e%')).all()
 90  90 ret = db_session.query(User).filter(~User.name.like('e%')).all()
 91  91 
 92  92 # 限制
 93  93 ret = db_session.query(User)[1:2]
 94  94 
 95  95 # 排序
 96  96 ret = db_session.query(User).order_by(User.name.desc()).all()
 97  97 ret = db_session.query(User).order_by(User.name.desc(), User.id.asc()).all()
 98  98 
 99  99 # 分组
100 100 from sqlalchemy.sql import func
101 101 
102 102 ret = db_session.query(User).group_by(User.extra).all()
103 103 ret = db_session.query(
104 104     func.max(User.id),
105 105     func.sum(User.id),
106 106     func.min(User.id)).group_by(User.name).all()
107 107 
108 108 ret = db_session.query(
109 109     func.max(User.id),
110 110     func.sum(User.id),
111 111     func.min(User.id)).group_by(User.name).having(func.min(User.id) >2).all()
112 112 """
113 113 
114 114 # 关闭连接
115 115 db_session.close()
高级查询

 

(6)高级版修改数据操作

 1 1 #高级版更新操作
 2  2 from my_create_table import User,engine
 3  3 from sqlalchemy.orm import sessionmaker
 4  4 
 5  5 Session = sessionmaker(engine)
 6  6 db_session = Session()
 7  7 
 8  8 #直接修改
 9  9 db_session.query(User).filter(User.id > 0).update({"name" : "099"})
10 10 
11 11 #在原有值基础上添加 - 1
12 12 db_session.query(User).filter(User.id > 0).update({User.name: User.name + "099"}, synchronize_session=False)
13 13 
14 14 #在原有值基础上添加 - 2
15 15 db_session.query(User).filter(User.id > 0).update({"age": User.age + 1}, synchronize_session="evaluate")
16 16 db_session.commit()
高级版修改数据