SQLAlchemy创建,crud,聚合函数,过滤条件,外键约束,relationship
1.sqlalchemy的创建
先创建引擎create_engine,再创建基类Base:
from sqlalchemy.ext.declarative import declarative_base,Column
from sqlalchemy import create_engine
DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
engine = create_engine(DB_URI)
Base = declarative_base(engine)
class Person(Base):
__tablename__ = 'person'
# 2. 在这个ORM模型中创建一些属性,来跟表中的字段进行一一映射。这些属性必须是sqlalchemy给我们提供好的数据类型。
id = Column(Integer,primary_key=True,autoincrement=True)
name = Column(String(50))
age = Column(Integer)
使用Base.metadata.create_all()
来将模型映射到数据库中。
**注意:**一旦使用Base.metadata.create_all()
将模型映射到数据库中后,即使改变了模型的字段,也不会重新映射了。
2.crud
用session做数据的增删改查操作:
构建session对象:所有和数据库的ORM操作都必须通过一个叫做session
的会话对象来实现,通过以下代码来获取会话对象:
*add_data:
```python
from sqlalchemy.orm import sessionmaker
engine = create_engine(DB_URI)
session = sessionmaker(engine)()
添加对象:
* 创建对象,也即创建一条数据:
p = Person(name='zhiliao',age=18,country='china')
* 将这个对象添加到`session`会话对象中:
session.add(p)
* 将session中的对象做commit操作(提交):
session.commit()
* 一次性添加多条数据:
p1 = Person(name='zhiliao1',age=19,country='china')
p2 = Person(name='zhiliao2',age=20,country='china')
session.add_all([p1,p2])
session.commit()
```
*seach_data:
# 查找某个模型对应的那个表中所有的数据:
all_person = session.query(Person).all()
# 使用filter_by来做条件查询
all_person = session.query(Person).filter_by(name='zhiliao').all()
# 使用filter来做条件查询
all_person = session.query(Person).filter(Person.name=='zhiliao').all()
# 使用get方法查找数据,get方法是根据id来查找的,只会返回一条数据或者None
person = session.query(Person).get(primary_key)
# 使用first方法获取结果集中的第一条数据
person = session.query(Person).first()
*update_data:
#修改对象:首先从数据库中查找对象,然后将这条数据修改为你想要的数据,最后做commit操作就可以修改数据了
person = session.query(Person).first()
person.name = 'ketang'
session.commit()
*delete_data:
#删除对象:将需要删除的数据从数据库中查找出来,然后使用`session.delete`方法将这条数据从session中删除,最后做commit操作就可以了
person = session.query(Person).first()
session.delete(person)
session.commit()
SQLAlchemy常用数据类型:
-
Integer:整形,映射到数据库中是int类型。
-
Float:浮点类型,映射到数据库中是float类型。他占据的32位。
-
Double:双精度浮点类型,映射到数据库中是double类型,占据64位。
-
String:可变字符类型,映射到数据库中是varchar类型.
-
Boolean:布尔类型,映射到数据库中的是tinyint类型。
-
DECIMAL:定点类型。是专门为了解决浮点类型精度丢失的问题的。在存储钱相关的字段的时候建议大家都使用这个数据类型。并且这个类型使用的时候需要传递两个参数,第一个参数是用来标记这个字段总能能存储多少个数字,第二个参数表示小数点后有多少位。
-
Enum:枚举类型。指定某个字段只能是枚举中指定的几个值,不能为其他值。在ORM模型中,使用Enum来作为枚举,示例代码如下:
```python class Article(Base): __tablename__ = 'article' id = Column(Integer,primary_key=True,autoincrement=True) tag = Column(Enum("python",'flask','django')) ```
在Python3中,已经内置了enum这个枚举的模块,我们也可以使用这个模块去定义相关的字段。示例代码如下:
```python class TagEnum(enum.Enum): python = "python" flask = "flask" django = "django" ```
class Article(Base): __tablename__ = 'article' id = Column(Integer,primary_key=True,autoincrement=True) tag = Column(Enum(TagEnum)) article = Article(tag=TagEnum.flask)
-
Date:存储时间,只能存储年月日。映射到数据库中是date类型。在Python代码中,可以使用
datetime.date
来指定。示例代码如下:class Article(Base): __tablename__ = 'article' id = Column(Integer,primary_key=True,autoincrement=True) create_time = Column(Date) article = Article(create_time=date(2017,10,10))
-
DateTime:存储时间,可以存储年月日时分秒毫秒等。映射到数据库中也是datetime类型。在Python代码中,可以使用
datetime.datetime
来指定。示例代码如下:class Article(Base): __tablename__ = 'article' id = Column(Integer,primary_key=True,autoincrement=True) create_time = Column(DateTime) article = Article(create_time=datetime(2011,11,11,11,11,11))
-
Time:存储时间,可以存储时分秒。映射到数据库中也是time类型。在Python代码中,可以使用
datetime.time
来至此那个。示例代码如下:class Article(Base): __tablename__ = 'article' id = Column(Integer,primary_key=True,autoincrement=True) create_time = Column(Time) article = Article(create_time=time(hour=11,minute=11,second=11))
-
Text:存储长字符串。一般可以存储6W多个字符。如果超出了这个范围,可以使用LONGTEXT类型。映射到数据库中就是text类型。
-
LONGTEXT:长文本类型,映射到数据库中是longtext类型。
-
Column常用参数:
-
primary_key:设置某个字段为主键。
-
autoincrement:设置这个字段为自动增长的。
-
default:设置某个字段的默认值。在发表时间这些字段上面经常用。
-
nullable:指定某个字段是否为空。默认值是True,就是可以为空。
-
unique:指定某个字段的值是否唯一。默认是False。
-
onupdate:在数据更新的时候会调用这个参数指定的值或者函数。在第一次插入这条数据的时候,不会用onupdate的值,只会使用default的值。常用的就是
update_time
(每次更新数据的时候都要更新的值)。 -
name:指定ORM模型中某个属性映射到表中的字段名。如果不指定,那么会使用这个属性的名字来作为字段名。如果指定了,就会使用指定的这个值作为参数。这个参数也可以当作位置参数,在第1个参数来指定。
title = Column(String(50),name='title',nullable=False) title = Column('my_title',String(50),nullable=False)
3.聚合函数
query可用参数:
- 模型对象。指定查找这个模型中所有的对象。
- 模型中的属性。可以指定只查找某个模型的其中几个属性。
- 聚合函数。
- func.count:统计行的数量。
- func.avg:求平均值。
- func.max:求最大值。
- func.min:求最小值。
- func.sum:求和。
func
上,其实没有任何聚合函数。但是因为他底层做了一些魔术,只要mysql中有的聚合函数,都可以通过func调用。
4.过滤条件
filter过滤条件:
过滤是数据提取的一个很重要的功能,以下对一些常用的过滤条件进行解释,并且这些过滤条件都是只能通过filter方法实现的:
-
equals:
article = session.query(Article).filter(Article.title == "title0").first() print(article)
-
not equals:
query.filter(User.name != 'ed')
-
like:
query.filter(User.name.like('%ed%'))
-
in:
query.filter(User.name.in_(['ed','wendy','jack'])) # 同时,in也可以作用于一个Query query.filter(User.name.in_(session.query(User.name).filter(User.name.like('%ed%'))))
-
not in:
query.filter(~User.name.in_(['ed','wendy','jack']))
-
is null:
query.filter(User.name==None) # 或者是 query.filter(User.name.is_(None))
-
is not null:
query.filter(User.name != None) # 或者是 query.filter(User.name.isnot(None))
-
and:
from sqlalchemy import and_ query.filter(and_(User.name=='ed',User.fullname=='Ed Jones')) # 或者是传递多个参数 query.filter(User.name=='ed',User.fullname=='Ed Jones') # 或者是通过多次filter操作 query.filter(User.name=='ed').filter(User.fullname=='Ed Jones')
-
or:
from sqlalchemy import or_ query.filter(or_(User.name=='ed',User.name=='wendy'))
如果想要查看orm底层转换的sql语句,可以在filter方法后面不要再执行任何方法直接打印就可以看到了。比如:
```python
articles = session.query(Article).filter(or_(Article.title=='abc',Article.content=='abc'))
print(articles)
```
5.外键约束
外键:
使用SQLAlchemy创建外键非常简单。在从表中增加一个字段,指定这个字段外键的是哪个表的哪个字段就可以了。从表中外键的字段,必须和父表的主键字段类型保持一致。
示例代码如下:
class User(Base):
__tablename__ = 'user'
id = Column(Integer,primary_key=True,autoincrement=True)
username = Column(String(50),nullable=False)
class Article(Base):
__tablename__ = 'article'
id = Column(Integer,primary_key=True,autoincrement=True)
title = Column(String(50),nullable=False)
content = Column(Text,nullable=False)
uid = Column(Integer,ForeignKey("user.id"))
外键约束有以下几项:
1. RESTRICT:父表数据被删除,会阻止删除。默认就是这一项。
2. NO ACTION:在MySQL中,同RESTRICT。
3. CASCADE:级联删除。
4. SET NULL:父表数据被删除,子表数据会设置为NULL。
第一种:RESTRICT(默认就是这种。当父表数据被删除,从表会拒绝删除)
语法:uid = Column(Integer , ForeignKey("user.id" ,ondelete="RESTRICT"))
第二种:NO ACTIION(同RESTRICT 一样)
语法: uid = Column(Integer , ForeignKey("user.id" ,ondelete="NO ACTION"))
第三种:CASCADE (父表数据删除、从表数据也会跟着删除)
语法:uid = Column(Integer , ForeignKey("user.id" ,ondelete="CASCADE"))
第四种: SET NULL (父表数据删除,从表外键字段设为NULL)
语法:uid = Column(Integer , ForeignKey("user.id" ,ondelete="SET NULL"))
注意: 如果uid字段设置了 nullable=False , 再设置 ondelete = "SET NULL",pycharm运行程序则会报错
6.relationship
ORM关系以及一对多:
mysql级别的外键,还不够ORM,必须拿到一个表的外键,然后通过这个外键再去另外一张表中查找,这样太麻烦了。SQLAlchemy提供了一个relationship
,这个类可以定义属性,以后在访问相关联的表的时候就直接可以通过属性访问的方式就可以访问得到了。示例代码:
class User(Base):
__tablename__ = 'user'
id = Column(Integer,primary_key=True,autoincrement=True)
username = Column(String(50),nullable=False)
# articles = relationship("Article")
def __repr__(self):
return "<User(username:%s)>" % self.username
class Article(Base):
__tablename__ = 'article'
id = Column(Integer,primary_key=True,autoincrement=True)
title = Column(String(50),nullable=False)
content = Column(Text,nullable=False)
uid = Column(Integer,ForeignKey("user.id"))
author = relationship("User",backref="articles")
另外,可以通过backref
来指定反向访问的属性名称。articles是有多个。他们之间的关系是一个一对多的关系
一对一的关系:
在sqlalchemy中,如果想要将两个模型映射成一对一的关系,那么应该在父模型中,指定引用的时候,要传递一个uselist=False
这个参数进去。就是告诉父模型,以后引用这个从模型的时候,不再是一个列表了,而是一个对象了。示例代码如下:
class User(Base):
__tablename__ = 'user'
id = Column(Integer,primary_key=True,autoincrement=True)
username = Column(String(50),nullable=False)
extend = relationship("UserExtend",uselist=False)
def __repr__(self):
return "<User(username:%s)>" % self.username
class UserExtend(Base):
__tablename__ = 'user_extend'
id = Column(Integer, primary_key=True, autoincrement=True)
school = Column(String(50))
uid = Column(Integer,ForeignKey("user.id"))
user = relationship("User",backref="extend")
当然,也可以借助sqlalchemy.orm.backref
来简化代码:
class User(Base):
__tablename__ = 'user'
id = Column(Integer,primary_key=True,autoincrement=True)
username = Column(String(50),nullable=False)
# extend = relationship("UserExtend",uselist=False)
def __repr__(self):
return "<User(username:%s)>" % self.username
class UserExtend(Base):
__tablename__ = 'user_extend'
id = Column(Integer, primary_key=True, autoincrement=True)
school = Column(String(50))
uid = Column(Integer,ForeignKey("user.id"))
user = relationship("User",backref=backref("extend",uselist=False))
多对多的关系:
- 多对多的关系需要通过一张中间表来绑定他们之间的关系。
- 先把两个需要做多对多的模型定义出来
- 使用Table定义一个中间表,中间表一般就是包含两个模型的外键字段就可以了,并且让他们两个来作为一个“复合主键”。
- 在两个需要做多对多的模型中随便选择一个模型,定义一个relationship属性,来绑定三者之间的关系,在使用relationship的时候,需要传入一个secondary=中间表。