SqlAlchemy学习笔记

前言

　　SqlAlchemy 是在python中最有名的ORM(Objet Relational Mapping)框架，主要的任务是把关系数据库的表结构映射到python中的额对象。

　　以前python通过原生的sql访问操作数据库，那种方式很繁琐，且没有安全感，现在通过ORM这个壳来访问操作数据库，这个壳具有python的style，壳里面包裹着sql。这样就大大方便了python使用者。

　　Alembic是SQLAlchemy数据库迁移工具，由SQLAlchemy作者所创建，是对后台SQLAIchemy数据库执行迁移的事实上的标准工具。换句话说，Alembic可以对SQLAIchemy数据库进行版本控制(类似于git)。

创建模型类

 1 from sqlalchemy import create_engine,Column,Integer,String
 2 from sqlalchemy.ext.declarative import declarative_base
 3 
 4 # HOSTNAME = '127.0.0.1'
 5 # PORT = '3306'
 6 # DATABASE = 'first_sqlalchemy'
 7 # USERNAME = 'ROOT'
 8 # PASSWORD = '123456'
 9 
10 engine = create_engine("mysql+pymysql://root:123456@127.0.0.1/first_sqlalchemy?charset=utf8")
11 BO = declarative_base(engine)
12 # 1、用declarative_base创建一个ORM基类；
13 # 2、创建一个ORM模型类，继承自sqlalchemy提供的基类；类中创建属性，跟表中的字段一一映射，
14 # 这些属性必须是sqlalchemy给我们提供好的数据类型
15 class Person(BO):
16     __tablename__ = 'Person'
17     id = Column(Integer,primary_key=True,autoincrement=True)
18     name = Column(String(50))
19     age = Column(Integer)
20 # 3、将建好的ORM模型类映射到数据库中；
21 BO.metadata.create_all()

增删改查

 1 #encoding: utf-8
 2 
 3 from sqlalchemy import create_engine,Column,Integer,String
 4 from sqlalchemy.ext.declarative import declarative_base
 5 from sqlalchemy.orm import sessionmaker
 6 
 7 HOSTNAME = '127.0.0.1'
 8 PORT = '3306'
 9 DATABASE = 'first_sqlalchemy'
10 USERNAME = 'root'
11 PASSWORD = '123456'
12 
13 # dialect+driver://username:password@host:port/database
14 DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
15 engine = create_engine(DB_URI)
16 Base = declarative_base(engine)
17 session = sessionmaker(engine)()
18 
19 class Person(Base):
20     __tablename__ = 'person'
21     id = Column(Integer,primary_key=True,autoincrement=True)
22     name = Column(String(50))
23     age = Column(Integer)
24     country = Column(String(50))
25     def __str__(self):
26         return "<Person(name:%s,age:%s,country:%s)>" % (self.name,self.age,self.country)
27 
28 
29 Base.metadata.drop_all()
30 Base.metadata.create_all()
31 
32 # 增
33 def add_data():
34     p1 = Person(name='刘亦菲',age=22,country='America')
35     p2 = Person(name='李沁',age=20,country='China')
36     p3 = Person(name='高圆圆',age=23,country='China')
37     session.add_all([p1,p2,p3]) # 添加多条数据
38     session.commit() # 提交
39 
40 # 查
41 def search_data():
42     # all_person = session.query(Person).all()  # 使用all方法获取结果集中的所有数据
43     # for p in all_person:
44     #     print(p)
45     # all_person = session.query(Person).filter_by(name='wy').all() #过滤器，条件查询
46     # for x in all_person:
47     #     print(x)
48     # all_person = session.query(Person).filter(Person.name=='wy').all()
49     # for x in all_person:
50     #     print(x)
51     person = session.query(Person).first() # 使用first方法获取结果集中的第一条数据
52     print(person)
53 # 改
54 
55 def update_data():
56     person = session.query(Person).first()
57     person.name = 'wangyi'
58     session.commit()
59 
60 # 删
61 def delete_data():
62     person = session.query(Person).first()
63     session.delete(person)
64     session.commit()
65 
66 if __name__ == '__main__':
67 
68     # add_data()
69     search_data()
70     # update_data()
71     # delete_data()

条件查询

 1 #encoding: utf-8
 2 from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text,func,and_,or_
 3 from sqlalchemy.dialects.mysql import LONGTEXT
 4 from sqlalchemy.ext.declarative import declarative_base
 5 from sqlalchemy.orm import sessionmaker
 6 # 在Python3中才有这个enum模块，在python2中没有
 7 import enum
 8 from datetime import datetime
 9 import random
10 HOSTNAME = '127.0.0.1'
11 PORT = '3306'
12 DATABASE = 'first_sqlalchemy'
13 USERNAME = 'root'
14 PASSWORD = '123456'
15 # dialect+driver://username:password@host:port/database
16 DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
17 engine = create_engine(DB_URI)
18 Base = declarative_base(engine)
19 session = sessionmaker(engine)()
20 
21 class Article(Base):
22     __tablename__ = 'article'
23     id = Column(Integer,primary_key=True,autoincrement=True)
24     title = Column(String(50),nullable=False)
25     price = Column(Float,nullable=False)
26     content = Column(Text)
27 
28     def __repr__(self):
29         return "<Article(title:%s)>" % self.title
30 
31 # session.query(Article).filter(Article.id == 1)
32 # session.query(Article).filter_by(id = 1)
33 
34 # 1. equal
35 # article = session.query(Article).filter(Article.title == "title0").first()
36 # print(article)
37 
38 # 2. not equal
39 # articles = session.query(Article).filter(Article.title != 'title0').all()
40 # print(articles)
41 
42 # 3. like & ilike（不区分大小写）
43 # articles = session.query(Article).filter(Article.title.ilike('title%')).all()
44 # print(articles)
45 
46 # 4. in：
47 # for xxx in xxx
48 # def _in()
49 # articles = session.query(Article).filter(Article.title.in_(['title1','title2'])).all()
50 # print(articles)
51 
52 # not in
53 # articles = session.query(Article).filter(~Article.title.in_(['title1','title2'])).all()
54 # print(articles)
55 # articles = session.query(Article).filter(Article.title.notin_(['title1','title2'])).all()
56 # print(articles)
57 
58 # is null
59 # articles = session.query(Article).filter(Article.content==None).all()
60 # print(articles)
61 
62 # is not null
63 # articles = session.query(Article).filter(Article.content!=None).all()
64 # print(articles)
65 
66 # and
67 # articles = session.query(Article).filter(Article.title=='abc',Article.content=='abc').all()
68 # print(articles)
69 
70 # or
71 articles = session.query(Article).filter(or_(Article.title=='abc',Article.content=='abc'))
72 print(articles)
73 
74 # 以上不写all()会直接打印底层原生的sql语句

外键

外键构建和四种约束

 1 #encoding: utf-8
 2 from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text,func,and_,or_,ForeignKey
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy.orm import sessionmaker
 5 
 6 HOSTNAME = '127.0.0.1'
 7 PORT = '3306'
 8 DATABASE = 'first_sqlalchemy'
 9 USERNAME = 'root'
10 PASSWORD = '123456'
11 
12 # dialect+driver://username:password@host:port/database
13 DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
14 
15 engine = create_engine(DB_URI)
16 Base = declarative_base(engine)
17 session = sessionmaker(engine)()
18 
19 class User(Base):
20     __tablename__ = 'user'
21     id = Column(Integer,primary_key=True,autoincrement=True)
22     username = Column(String(50),nullable=False)
23 
24 class Article(Base):
25     __tablename__ = 'article'
26     id = Column(Integer,primary_key=True,autoincrement=True)
27     title = Column(String(50),nullable=False)
28     content = Column(Text,nullable=False)
29 
30     uid = Column(Integer,ForeignKey('user.id', ondelete="CASCADE"))  # 这里uid的数据类型要和user表中的id的数据类型一致
31 """
32 外键约束:
33 1、RESTRICT：默认ondelete=RESTRICT,此时删除主表的被关联数据，会报错，如：
34 ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint
35 fails (`first_sqlalchemy`.`article`, CONSTRAINT `article_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`))
36 2、NO ACTION：在mysql中等同于RESTRICT；
37 3、CASCADE：级联删除
38 4、SET NULL:主表数据删除后副表置空。此时从表的外键nullable不能为False。
39 """
40 
41 Base.metadata.drop_all()
42 Base.metadata.create_all()
43 
44 user1 = User(username='刘亦菲')
45 user2 = User(username='杨幂')
46 session.add_all([user1,user2])
47 session.commit()
48 
49 article1 = Article(title='hello world',content='这是内容',uid=1)
50 session.add(article1)
51 session.commit()

ORM层外键关联

实现迅速在另一个表中找到相关联的数据：

 1 #encoding: utf-8
 2 from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text,func,and_,or_,ForeignKey
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy.orm import sessionmaker,relationship
 5 
 6 HOSTNAME = '127.0.0.1'
 7 PORT = '3306'
 8 DATABASE = 'first_sqlalchemy'
 9 USERNAME = 'root'
10 PASSWORD = '123456'
11 
12 # dialect+driver://username:password@host:port/database
13 DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
14 
15 engine = create_engine(DB_URI)
16 Base = declarative_base(engine)
17 session = sessionmaker(engine)()
18 
19 class User(Base):
20     __tablename__ = 'user'
21     id = Column(Integer,primary_key=True,autoincrement=True)
22     username = Column(String(50),nullable=False)
23     # articles = relationship("Article")
24     def __repr__(self):
25         return "<user{id:%s,username:%s}>"%(self.id,self.username)
26 
27 class Article(Base):
28     __tablename__ = 'article'
29     id = Column(Integer,primary_key=True,autoincrement=True)
30     title = Column(String(50),nullable=False)
31     content = Column(Text,nullable=False)
32     uid = Column(Integer,ForeignKey('user.id'))
33     author = relationship("User",backref="articles")   # 这两个可以通过backref反转合二为一
34     def __repr__(self):
35         return "<article{id:%s,title:%s,content:%s,uid:%s}>"%(self.id,self.title,self.content,self.uid)
36 
37 Base.metadata.drop_all()
38 Base.metadata.create_all()
39 
40 user1 = User(username='刘亦菲')
41 user2 = User(username='杨幂')
42 session.add_all([user1,user2])
43 session.commit()
44 
45 article1 = Article(title='文章1',content='内容1',uid=1)
46 article2 = Article(title='文章2',content='内容2',uid=2)
47 article3 = Article(title='文章3',content='内容3',uid=2)
48 session.add_all([article1,article2,article3])
49 session.commit()
50 
51 # 对于都有数据的两张表user和article，正常情况下，要拿到第一篇article对应的username，通过如下方式：
52 # article = session.query(Article).first()
53 # uid = article.uid
54 # user = session.query(User).get(uid)
55 # print(user)
56 
57 
58 # 现在，通过ORM层外键实现：在Article模型中添加 author = relationship("User")
59 article = session.query(Article).filter(Article.title=="文章3").first()
60 print(article.author.username) # 杨幂
61 
62 
63 # 一个用户可以写多篇文章，一对多的关系，那么怎么拿到一个用户的所有文章的标题呢？同样，在User中添加 articles = relationship("Article")
64 
65 user = session.query(User).filter(User.username=="杨幂").first()
66 for i in range(len(user.articles)):
67     print(user.articles[i].title)

这两个relationship可以通过backref反转合二为一： author = relationship("User",backref="articles")

ORM层面添加关联数据

 1 #encoding: utf-8
 2 from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text,func,and_,or_,ForeignKey
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy.orm import sessionmaker,relationship
 5 
 6 HOSTNAME = '127.0.0.1'
 7 PORT = '3306'
 8 DATABASE = 'first_sqlalchemy'
 9 USERNAME = 'root'
10 PASSWORD = '123456'
11 
12 # dialect+driver://username:password@host:port/database
13 DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
14 
15 engine = create_engine(DB_URI)
16 Base = declarative_base(engine)
17 session = sessionmaker(engine)()
18 
19 class User(Base):
20     __tablename__ = 'user'
21     id = Column(Integer,primary_key=True,autoincrement=True)
22     username = Column(String(50),nullable=False)
23     # articles = relationship("Article")
24     def __repr__(self):
25         return "<user{id:%s,username:%s}>"%(self.id,self.username)
26 
27 class Article(Base):
28     __tablename__ = 'article'
29     id = Column(Integer,primary_key=True,autoincrement=True)
30     title = Column(String(50),nullable=False)
31     content = Column(Text,nullable=False)
32     uid = Column(Integer,ForeignKey('user.id'))
33     author = relationship("User",backref="articles")
34     def __repr__(self):
35         return "<article{id:%s,title:%s,content:%s,uid:%s}>"%(self.id,self.title,self.content,self.uid)
36 
37 Base.metadata.drop_all()
38 Base.metadata.create_all()
39 #
40 user1 = User(username='刘亦菲')
41 user2 = User(username='杨幂')
42 session.add_all([user1,user2])
43 session.commit()
44 
45 article1 = Article(title='文章1',content='内容1',uid=1)
46 article2 = Article(title='文章2',content='内容2',uid=2)
47 article3 = Article(title='文章3',content='内容3',uid=2)
48 session.add_all([article1,article2,article3])
49 session.commit()
50 
51 
52 # ORM层面给用户添加文章：
53 article4 = Article(title='文章4',content='内容4')
54 article5 = Article(title='文章5',content='内容5')
55 user = User(username='高圆圆') # 顺着id直接新增，可以重名
56 user.articles.append(article4)
57 user.articles.append(article5)
58 session.add(user)
59 session.commit()
60 
61 #ORM层面给文章指定作者
62 article6 = Article(title='文章6',content='内容6')
63 user = User(username='李沁')
64 article6.author = user
65 session.add(article6)
66 session.commit()

添加结果：

这有个疑问，为什么上面代码56行是append，而64行是赋值？

这是因为建立外键关联后，默认两张表是一对多的关系，主1从多。具体如下：

一对一

将用户的不常用的静态字段如毕业学校，手机号，住址等放到单独的表里，外键关联选择一对一：

这里一对一的关键：

user = relationship("User",backref=backref("school",uselist=False))

 1 #encoding: utf-8
 2 from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text,func,and_,or_,ForeignKey
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy.orm import sessionmaker,relationship,backref
 5 
 6 HOSTNAME = '127.0.0.1'
 7 PORT = '3306'
 8 DATABASE = 'first_sqlalchemy'
 9 USERNAME = 'root'
10 PASSWORD = '123456'
11 
12 # dialect+driver://username:password@host:port/database
13 DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
14 
15 engine = create_engine(DB_URI)
16 Base = declarative_base(engine)
17 session = sessionmaker(engine)()
18 
19 class User(Base):
20     __tablename__ = 'user'
21     id = Column(Integer,primary_key=True,autoincrement=True)
22     username = Column(String(50),nullable=False)
23     # articles = relationship("Article")
24     def __repr__(self):
25         return "<user{id:%s,username:%s}>"%(self.id,self.username)
26 
27 class Uschool(Base):
28     '''新增毕业学校表'''
29     __tablename__ = 'uschool'
30     id = Column(Integer, primary_key=True, autoincrement=True)
31     school = Column(String(50),nullable=False)
32     uid = Column(Integer,ForeignKey("user.id"))
33 
34     user = relationship("User",backref=backref("school",uselist=False)) # 要导入backref
35     # uselist=False 就表明主从表是一对一的关系
36 
37 class Article(Base):
38     __tablename__ = 'article'
39     id = Column(Integer,primary_key=True,autoincrement=True)
40     title = Column(String(50),nullable=False)
41     content = Column(Text,nullable=False)
42     uid = Column(Integer,ForeignKey('user.id'))
43     author = relationship("User",backref="articles")
44     def __repr__(self):
45         return "<article{id:%s,title:%s,content:%s,uid:%s}>"%(self.id,self.title,self.content,self.uid)
46 
47 Base.metadata.drop_all()
48 Base.metadata.create_all()
49 
50 user1 = User(username='刘亦菲')
51 user2 = User(username='杨幂')
52 session.add_all([user1,user2])
53 session.commit()
54 
55 article1 = Article(title='文章1',content='内容1',uid=1)
56 article2 = Article(title='文章2',content='内容2',uid=2)
57 article3 = Article(title='文章3',content='内容3',uid=2)
58 session.add_all([article1,article2,article3])
59 session.commit()
60 
61 # ORM层面给用户添加文章：
62 article4 = Article(title='文章4',content='内容4')
63 article5 = Article(title='文章5',content='内容5')
64 user = User(username='高圆圆')
65 print(type(user.articles)) # <class 'sqlalchemy.orm.collections.InstrumentedList'>
66 # 默认一对多，user.articles是列表类型
67 user.articles.append(article4)
68 user.articles.append(article5)
69 for i in user.articles:
70     print(i)
71     # < article{id: None, title: 文章4, content: 内容4, uid: None} >
72     # < article{id: None, title: 文章5, content: 内容5, uid: None} >
73 
74 # user.articles = article4 #报错 TypeError: Incompatible collection type: Article is not list-like
75 session.add(user)
76 session.commit()
77 #ORM层面给文章指定作者
78 article6 = Article(title='文章6',content='内容6')
79 user = User(username='李沁')
80 print(type(article6.author)) # <class 'NoneType'>
81 article6.author = user
82 session.add(article6)
83 session.commit()
84 
85 #这里一对一是假设一个用户只能对应唯一的学校，或许手机号的例子更合适
86 uschool01 = Uschool(school="清华")
87 uschool02 = Uschool(school="北大")
88 user1 = User(username="余承东")
89 user2 = User(username="李彦宏")
90 user1.school = uschool01
91 user2.school = uschool02
92 session.add(uschool01)
93 session.add(uschool02)
94 session.commit(

ORM层面删除数据

1 user = session.query(User).first()
2 session.delete(user)
3 session.commit()

将user表的第一个用户刘亦菲删除了，但是article表的对应刘亦菲的uid变为NULL。

正常情况下载sql层面上是不能删除有外键约束的表数据的，但是在ORM层面上会无视约束，会先把uid置为NULL，然后再去删除主表数据。

此时要想数据不被ORM删除，只需将从表的uid字段置为nullable=False。非空之后ORM也删不了了。

当然，如果只删除从表那就没主表什么事了。但如果想删除从表的同时，将主表也一并删除，怎么实现？

在relationship（）中有个参数cascade，可以实现主从表一并删除。

relationship中的cascade

在SqlAlchemy中，只要将一个数据添加到sessioon中，和他相关联的数据都可以一起存入到数据库中了。这些是怎么设置的呢？其实是通过relationship的中的一个关键字cascade实现的。如：

1、save-update：默认选项。在添加一条数据的时候会将其他相关联的数据添加进去；

2、delete：删除某个模型中数据的时候，也会删除relationship中关联模型的数据；

3、delete_orphan：写在主表的relationship中，当副表的uid置空时，副表的数据会被删除。只能用在一对多。还需要在副表的relationship中添加single_parent=True参数。

4、merge：默认选项。当在使用session.merge，合并一个对象的时候，会将使用了relationship相关联的对象也进行merge操作。

5、expunge：移除操作的时候，会将相关联的对象也进行移除。这个操作只是从session中移除，并不会真正的从数据库中删除。

6、all：是对save-update, merge, refresh-expire, expunge, delete几种的缩写。

排序

对字段进行正序或倒序排序，有三种方式去实现：

 1 #encoding: utf-8
 2 from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text,func,and_,or_,ForeignKey
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy.orm import sessionmaker,relationship,backref
 5 from datetime import datetime
 6 HOSTNAME = '127.0.0.1'
 7 PORT = '3306'
 8 DATABASE = 'first_sqlalchemy'
 9 USERNAME = 'root'
10 PASSWORD = '123456'
11 
12 # dialect+driver://username:password@host:port/database
13 DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
14 
15 engine = create_engine(DB_URI)
16 Base = declarative_base(engine)
17 session = sessionmaker(engine)()
18 
19 class User(Base):
20     __tablename__ = 'user'
21     id = Column(Integer,primary_key=True,autoincrement=True)
22     username = Column(String(50),nullable=False)
23 
24 class Article(Base):
25     __tablename__ = 'article'
26     id = Column(Integer,primary_key=True,autoincrement=True)
27     title = Column(String(50),nullable=False)
28     create_time = Column(DateTime,nullable=False,default=datetime.now)
29     uid = Column(Integer,ForeignKey('user.id'))
30     
31     author = relationship("User",backref=backref("articles",order_by=create_time.desc()))
32 
33     # __mapper_args__ = {
34     #     "order_by": create_time
35     #     # "order_by": create_time.desc()
36     # }
37 
38     def __repr__(self):
39         return "<article{id:%s,title:%s,create_time:%s}>"%(self.id,self.title,self.create_time)
40 # Base.metadata.drop_all()
41 # Base.metadata.create_all()
42 # article1 = Article(title="文章1")
43 # article2 = Article(title="文章2")
44 # article3 = Article(title="文章3")
45 # session.add_all([article3,])
46 # session.commit()
47 
48 # 1、方法一
49 # 正序
50 # article = session.query(Article).order_by('create_time').all()
51 article = session.query(Article).order_by(Article.create_time).all()
52 # 倒序
53 article = session.query(Article).order_by(Article.create_time.desc()).all()
54 
55 # 方法二：直接在模型中通过__mapper_args__指定某个字段的排序方式
56 # 方法三：直接在relationship()中指定排序方式。
57 print(article)

Limit、offset和slice切片

 1 #encoding: utf-8
 2 from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text,func,and_,or_,ForeignKey
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy.orm import sessionmaker,relationship,backref
 5 from datetime import datetime
 6 HOSTNAME = '127.0.0.1'
 7 PORT = '3306'
 8 DATABASE = 'first_sqlalchemy'
 9 USERNAME = 'root'
10 PASSWORD = '123456'
11 
12 # dialect+driver://username:password@host:port/database
13 DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
14 
15 engine = create_engine(DB_URI)
16 Base = declarative_base(engine)
17 session = sessionmaker(engine)()
18 
19 
20 class Article1(Base):
21     __tablename__ = 'article'
22     id = Column(Integer,primary_key=True,autoincrement=True)
23     title = Column(String(50),nullable=False)
24     create_time = Column(DateTime,nullable=False,default=datetime.now)
25 
26 
27     def __repr__(self):
28         return "<article{id:%s,title:%s,create_time:%s}>"%(self.id,self.title,self.create_time)
29 # Base.metadata.drop_all()
30 # Base.metadata.create_all()
31 
32 def add_data():
33     import time
34     for i in range(1,11):
35         article = Article1(title = "文章%s"%i)
36         session.add(article)
37         time.sleep(1)
38         session.commit()
39 # add_data()
40 # 倒序取第7，第6两篇文章
41 articles1 = session.query(Article1).order_by(Article1.create_time.desc()).offset(3).limit(2).all()
42 # 切片取第3到第5篇文章,注意从0开始
43 articles2 = session.query(Article1).slice(2,5).all()
44 # 与上面等价
45 articles3 = session.query(Article1)[2:5]
46 
47 # print(articles1)
48 # [<article{id:7,title:文章7,create_time:2020-04-14 01:28:22}>, <article{id:6,title:文章6,create_time:2020-04-14 01:28:21}>]

懒加载

在一对多，或者多对多的时候，如果想要获取多的这一部分的数据的时候，往往能通过一个属性就可以全部获取了。比如有一个作者，想要或者这个作者的所有文章，那么可以通过user.articles就可以获取所有的。但有时候我们不想获取所有的数据，比如只想获取这个作者今天发表的文章，那么这时候我们可以给relationship传递一个lazy='dynamic'，以后通过user.articles获取到的就不是一个列表，而是一个AppenderQuery对象了。这样就可以对这个对象再进行一层过滤和排序等操作。
通过`lazy='dynamic'，获取出来的多的那一部分的数据，就是一个’AppenderQuery’对象了。这种对象既可以添加新数据，也可以跟'Query'一样，可以再进行一层过滤。
总而言之：如果你在获取数据的时候，想要对多的那一边的数据再进行一层过滤，那么这时候就可以考虑使用 lazy='dynamic'。
lazy可用的选项：
1. 'select'：这个是默认选项。还是拿'user.articles'的例子来讲。如果你没有访问'user.articles'这个属性，那么sqlalchemy就不会从数据库中查找文章。一旦你访问了这个属性，那么sqlalchemy就会立马从数据库中查找所有的文章，并把查找出来的数据组装成一个列表返回。这也是懒加载。
2. 'dynamic'：就是在访问'user.articles'的时候返回回来的不是一个列表，而是'AppenderQuery'对象。

注意：lazy要写在一对多的情况中"多"的那个relationship()里。

 1 #encoding: utf-8
 2 from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text,func,and_,or_,ForeignKey,Table
 3 from sqlalchemy.dialects.mysql import LONGTEXT
 4 from sqlalchemy.ext.declarative import declarative_base
 5 from sqlalchemy.orm import sessionmaker,relationship,backref
 6 from datetime import datetime
 7 import time
 8 import random
 9 HOSTNAME = '127.0.0.1'
10 PORT = '3306'
11 DATABASE = 'first_sqlalchemy'
12 USERNAME = 'root'
13 PASSWORD = '123456'
14 # dialect+driver://username:password@host:port/database
15 DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
16 engine = create_engine(DB_URI)
17 Base = declarative_base(engine)
18 session = sessionmaker(engine)()
19 
20 
21 class User(Base):
22     __tablename__ = 'user1'
23     id = Column(Integer, primary_key=True, autoincrement=True)
24     username = Column(String(50),nullable=False)
25 
26 
27 class Article(Base):
28     __tablename__ = 'article1'
29     id = Column(Integer, primary_key=True, autoincrement=True)
30     title = Column(String(50), nullable=False)
31     create_time = Column(DateTime,nullable=False,default=datetime.now)
32     uid = Column(Integer,ForeignKey("user1.id"))
33 
34     author = relationship("User",backref=backref("articles",lazy="dynamic"))
35 
36     def __repr__(self):
37         return "<Article(title:%s)>" % self.title
38 
39 
40 
41 def add_data():
42     Base.metadata.drop_all()
43     Base.metadata.create_all()
44     user = User(username='wangyi')
45     for i in range(1,21):
46         article = Article(title="文章%s" %i)
47         article.author = user
48         session.add(article)
49         time.sleep(1)
50         session.commit()
51 # add_data()
52 
53 user = session.query(User).first()
54 # user.articles是一个Query对象。
55 print(user.articles.filter(Article.id>10).order_by(Article.create_time.desc()).limit(2).all())
56 # 可以继续追加数据进去
57 article =Article(title='文章21')
58 user.articles.append(article)
59 session.commit()

article1表：

查询扩展

group_by,having

 1 #encoding: utf-8
 2 from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text,func,and_,or_,ForeignKey,Table
 3 from sqlalchemy.dialects.mysql import LONGTEXT
 4 from sqlalchemy.ext.declarative import declarative_base
 5 from sqlalchemy.orm import sessionmaker,relationship,backref
 6 # 在Python3中才有这个enum模块，在python2中没有
 7 import enum
 8 from datetime import datetime
 9 import random
10 HOSTNAME = '127.0.0.1'
11 PORT = '3306'
12 DATABASE = 'first_sqlalchemy'
13 USERNAME = 'root'
14 PASSWORD = '123456'
15 # dialect+driver://username:password@host:port/database
16 DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
17 engine = create_engine(DB_URI)
18 Base = declarative_base(engine)
19 session = sessionmaker(engine)()
20 
21 class User(Base):
22     __tablename__ = 'user2'
23     id = Column(Integer,primary_key=True,autoincrement=True)
24     username = Column(String(50),nullable=False)
25     age = Column(Integer,default=0)
26     gender = Column(Enum("male","female","secret"),default="male")
27 
28 def add_data():
29     Base.metadata.drop_all()
30     Base.metadata.create_all()
31 
32     user1 = User(username='刘德华',age=18,gender='female')
33     user2 = User(username='洪金宝',age=17,gender='female')
34     user3 = User(username="成龙",age=19,gender='female')
35     user4 = User(username="周润发",age=22,gender='female')
36     user5 = User(username="甄子丹",age=15,gender='female')
37     user6 = User(username="张曼玉",age=16,gender='male')
38     user7 = User(username="王祖贤",age=17,gender='male')
39 
40     session.add_all([user1,user2,user3,user4,user5,user6,user7])
41     session.commit()
42 
43 # add_data()
44 
45 # 每个年龄的人数
46 result = session.query(User.age,func.count(User.id)).group_by(User.age).having(User.age<18).all()
47 print(result)

join

假如有两张表：

查询文章数最多的前两个用户的姓名、文章标题和具体文章数：

 1 #encoding: utf-8
 2 from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text,func,and_,or_,ForeignKey
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy.orm import sessionmaker,relationship,backref
 5 from datetime import datetime
 6 HOSTNAME = '127.0.0.1'
 7 PORT = '3306'
 8 DATABASE = 'first_sqlalchemy'
 9 USERNAME = 'root'
10 PASSWORD = '123456'
11 
12 # dialect+driver://username:password@host:port/database
13 DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
14 
15 engine = create_engine(DB_URI)
16 Base = declarative_base(engine)
17 session = sessionmaker(engine)()
18 
19 class User(Base):
20     __tablename__ = 'user3'
21     id = Column(Integer,primary_key=True,autoincrement=True)
22     username = Column(String(50),nullable=False)
23 
24 class Article(Base):
25     __tablename__ = 'article3'
26     id = Column(Integer,primary_key=True,autoincrement=True)
27     title = Column(String(50),nullable=False)
28     uid = Column(Integer,ForeignKey('user3.id'))
29     author = relationship("User",backref = "articles")
30 
31     def __repr__(self):
32         return "<article{id:%s,title:%s}>"%(self.id,self.title)
33 # Base.metadata.drop_all()
34 # Base.metadata.create_all()
35 # user1 = User(username='wangyi')
36 # user2 = User(username='wenwen')
37 # user3 = User(username='yanyan')
38 # user4 = User(username='xian')
39 # article1 = Article(title="文章1")
40 # article2 = Article(title="文章2")
41 # article3 = Article(title="文章3")
42 # article4 = Article(title="文章4")
43 # article5 = Article(title="文章5")
44 # article6 = Article(title="文章6")
45 # user1.articles.append(article1)
46 # user1.articles.append(article2)
47 # user2.articles.append(article3)
48 # user2.articles.append(article4)
49 # user2.articles.append(article5)
50 # user3.articles.append(article6)
51 #
52 #
53 # session.add(user1)
54 # session.add(user2)
55 # session.add(user3)
56 # session.add(user4)
57 # session.commit()
58 
59 # 查询文章数最多的前两个用户的姓名和文章标题：
60 result = session.query(User.username,func.group_concat(Article.title),func.count(Article.title)).outerjoin(Article,User.id==Article.uid).group_by(User.username).order_by(func.count(Article.title).desc()).limit(2).all()
61 print(result)
62 
63 """
64 sql语句：
65 SELECT user3.username AS user3_username, group_concat(article3.title) AS group_concat_1, count(article3.title) AS count_1 
66 FROM user3 LEFT OUTER JOIN article3 ON user3.id = article3.uid GROUP BY user3.username ORDER BY count(article3.title) DESC 
67  LIMIT %(param_1)s
68 """

子查询subquery

子查询可以让多个查询变成一个查询，只要查找一次数据库，性能相对来讲更加高效一点。不用写多个sql语句就可以实现一些复杂的查询。那么在sqlalchemy中，要实现一个子查询，应该使用以下几个步骤：
1. 将子查询按照传统的方式写好查询代码，然后在`query`对象后面执行`subquery`方法，将这个查询变成一个子查询。
2. 在子查询中，将以后需要用到的字段通过`label`方法，取个别名。
3. 在父查询中，如果想要使用子查询的字段，那么可以通过子查询的返回值上的`c`属性拿到。

 1 #encoding: utf-8
 2 from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text,func,and_,or_,ForeignKey,Table
 3 from sqlalchemy.dialects.mysql import LONGTEXT
 4 from sqlalchemy.ext.declarative import declarative_base
 5 from sqlalchemy.orm import sessionmaker,relationship,backref
 6 # 在Python3中才有这个enum模块，在python2中没有
 7 import enum
 8 from datetime import datetime
 9 import random
10 HOSTNAME = '127.0.0.1'
11 PORT = '3306'
12 DATABASE = 'first_sqlalchemy'
13 USERNAME = 'root'
14 PASSWORD = '123456'
15 # dialect+driver://username:password@host:port/database
16 DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
17 
18 engine = create_engine(DB_URI)
19 Base = declarative_base(engine)
20 session = sessionmaker(engine)()
21 
22 class User(Base):
23     __tablename__ = 'user4'
24     id = Column(Integer,primary_key=True,autoincrement=True)
25     username = Column(String(50),nullable=False)
26     city = Column(String(50),nullable=False)
27     age =  Column(Integer,default=0)
28 
29     def __repr__(self):
30         return "<User(username: %s)>" % self.username
31 
32 
33 Base.metadata.drop_all()
34 Base.metadata.create_all()
35 
36 user1 = User(username='李A',city="长沙",age=18)
37 user2 = User(username='王B',city="长沙",age=18)
38 user3 = User(username='赵C',city="北京",age=18)
39 user4 = User(username='张D',city="长沙",age=20)
40 
41 session.add_all([user1,user2,user3,user4])
42 session.commit()
43 
44 # 婚恋
45 # 寻找和李A这个人在同一个城市，并且是同年龄的人
46 # user = session.query(User).filter(User.username=='李A').first()
47 # users = session.query(User).filter(User.city==user.city,User.age==user.age).all()
48 # print(users)
49 
50 stmt = session.query(User.city.label("city"),User.age.label("age")).filter(User.username=='李A').subquery()
51 result = session.query(User).filter(User.city==stmt.c.city,User.age==stmt.c.age).all()
52 print(result)

flask_sqlalchemy

flask_sqlalchemy是对SqlAlchemy的封装，使得其更适合于flask框架的应用。

 1 from flask import Flask
 2 from flask_sqlalchemy import SQLAlchemy
 3 app = Flask(__name__)
 4 HOSTNAME = '127.0.0.1'
 5 PORT = '3306'
 6 DATABASE = 'flask_sqlalchemy_demo'
 7 USERNAME = 'root'
 8 PASSWORD = '123456'
 9 # dialect+driver://username:password@host:port/database
10 DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8mb4".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
11 app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI
12 app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
13 db = SQLAlchemy(app)
14 
15 class User(db.Model):
16     __tablename__ = 'user'
17     # 这个表名不写则默认使用模型类的小写名作为表名，驼峰则用_分开，如 UserModel——> user_model
18     id = db.Column(db.Integer,primary_key=True,autoincrement=True)
19     username = db.Column(db.String(50),nullable=False)
20     def __repr__(self):
21         return "<user(username:%s)>"%self.username
22 
23 class Article(db.Model):
24     __tablename__ = 'article'
25     id = db.Column(db.Integer,primary_key=True,autoincrement=True)
26     title = db.Column(db.String(50),nullable=False)
27     uid = db.Column(db.Integer,db.ForeignKey('user.id'))
28     author = db.relationship("User",backref = "articles")
29 
30 # db.drop_all()
31 # db.create_all()
32 def add_data():
33 
34     user1 = User(username="wangyi")
35     user2 = User(username="wenwen")
36     article1 = Article(title="文章1")
37     article2 = Article(title="文章2")
38     article1.author = user1
39     article2.author = user2
40     db.session.add(article1)
41     db.session.add(article2)
42     db.session.commit()
43 # add_data()
44 
45 # 单表查询
46 # users = User.query.order_by(User.id.desc()).all()
47 # print(users)
48 
49 # 改
50 # user = User.query.filter(User.username=='wenwen').first()
51 # user.username = "yanyan"
52 # db.session.commit()
53 
54 # 删
55 # user = User.query.filter(User.username=='wenwen').first()
56 # db.session.delete(user)
57 # db.session.commit()
58 
59 @app.route('/')
60 def hello_world():
61     return 'Hello World!'
62 
63 
64 if __name__ == '__main__':
65     app.run()

posted @ 2020-04-12 23:51 天青色wy 阅读(767) 评论(0) 编辑收藏举报

刷新页面返回顶部

天青色wy

print("Hello World")