SQLALchemy学习笔记（mysql+python环境）

一、数据库连接

dialect+driver://username:password@host:port/database

固定连接格式：DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format

from sqlalchemy import create_engine

HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'complaint'
USERNAME = 'root'
PASSWORD = 'xinzhi'

# dialect+driver://username:password@host:port/database
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)

判断是否连接成功

conn = engine.connect()
result = conn.execute('select 1')
print(result.fetchone())

二、ORM介绍

 ORM：Object Relationship Mapping。大白话：对象模型与数据库表的映射

   

1. 用`declarative_base`根据`engine`创建一个ORM基类。
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine(DB_URI)
Base = declarative_base(engine)
2. 用这个`Base`类作为基类来写自己的ORM类。要定义`__tablename__`类属性，来指定这个模型映射到数据库中的表名。
class Person(Base):
__tablename__ = 'person'
3. 创建属性来映射到表中的字段，所有需要映射到表中的属性都应该为Column类型：
class Person(Base):
__tablename__ = 'person'
 在这个ORM模型中创建一些属性，来跟表中的字段进行一一映射。这些属性必须是sqlalchemy给我们提供好的数据类型。
id = Column(Integer,primary_key=True,autoincrement=True)
name = Column(String(50))
age = Column(Integer)
4. 使用`Base.metadata.create_all()`来将模型映射到数据库中。
5. 一旦使用`Base.metadata.create_all()`将模型映射到数据库中后，即使改变了模型的字段，也不会重新映射了。

# 1. 创建一个ORM模型，这个ORM模型必须继承自sqlalchemy给我们提供好的基类
class Person(Base):
    __tablename__ = 'person'
    # 2. 在这个ORM模型中创建一些属性，来跟表中的字段进行一一映射。这些属性必须是sqlalchemy给我们提供好的数据类型。
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(50))
    age = Column(Integer)
    country = Column(String(50))
# 3. 将创建好的ORM模型，映射到数据库中。
Base.metadata.create_all()

 

三、session增删改查

1. 构建session对象：所有和数据库的ORM操作都必须通过一个叫做`session`的会话对象来实现，通过以下代码来获取会话对象：
from sqlalchemy.orm import sessionmaker

engine = create_engine(DB_URI)
session = sessionmaker(engine)()

2. 增加对象：
* 创建对象，也即创建一条数据：
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()
3. 查找对象：
# 查找某个模型对应的那个表中所有的数据：
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()
4. 修改对象

首先从数据库中查找对象，然后将这条数据修改为你想要的数据，最后做commit操作就可以修改数据了。
person = session.query(Person).first()
person.name = 'ketang'
session.commit()
5. 删除对象

将需要删除的数据从数据库中查找出来，然后使用`session.delete`方法将这条数据从session中删除，最后做commit操作就可以了。
person = session.query(Person).first()
session.delete(person)
session.commit()

# dialect+driver://username:password@host:port/database
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)
session = sessionmaker(engine)()
class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(50))
    age = Column(Integer)
    country = Column(String(50))
    def __str__(self):
        return "<Person(name:%s,age:%s,country:%s)>" % (self.name,self.age,self.country)
# session：会话
# 增
def add_data():
    p1 = Person(name='zhiliao1',age=19,country='china')
    p2 = Person(name='zhiliao2',age=20,country='china')
    session.add_all([p1,p2])
    session.commit()
# 查
def search_data():
    all_person = session.query(Person).all()
    for p in all_person:
        print(p)
    all_person = session.query(Person).filter_by(name='zhiliao').all()
    for x in all_person:
        print(x)
    all_person = session.query(Person).filter(Person.name=='zhiliao').all()
    for x in all_person:
        print(x)
    person = session.query(Person).first()
    print(person)
# 改
def update_data():
    person = session.query(Person).first()
    person.name = 'ketang'
    session.commit()
# 删
def delete_data():
    person = session.query(Person).first()
    session.delete(person)
    session.commit()
if __name__ == '__main__':
    # add_data()
    # search_data()
    # update_data()
    delete_data()

四、常用数据类型

1. Integer：整形，映射到数据库中是int类型。
2. Float：浮点类型，映射到数据库中是float类型。他占据的32位。
3. Double：双精度浮点类型，映射到数据库中是double类型，占据64位。
4. String：可变字符类型，映射到数据库中是varchar类型.
5. Boolean：布尔类型，映射到数据库中的是tinyint类型。
6. DECIMAL：定点类型。是专门为了解决浮点类型精度丢失的问题的。在存储钱相关的字段的时候建议大家都使用这个数据类型。并且这个类型使用的时候需要传递两个参数，第一个参数是用来标记这个字段总能能存储多少个数字，第二个参数表示小数点后有多少位。
7. 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)
```
8. Date：存储时间，只能存储年月日。映射到数据库中是date类型。在Python代码中，可以使用`datetime.date`来指定。示例代码如下：
```python
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))
```
9. DateTime：存储时间，可以存储年月日时分秒毫秒等。映射到数据库中也是datetime类型。在Python代码中，可以使用`datetime.datetime`来指定。示例代码如下：
```python
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))
```
10. Time：存储时间，可以存储时分秒。映射到数据库中也是time类型。在Python代码中，可以使用`datetime.time`来至此那个。示例代码如下：
```python
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))
```
11. Text：存储长字符串。一般可以存储6W多个字符。如果超出了这个范围，可以使用LONGTEXT类型。映射到数据库中就是text类型。
12. LONGTEXT：长文本类型，映射到数据库中是longtext类型。

from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,Date,DateTime,Time,String,Text
import enum

class TagEnum(enum.Enum):
    python = "python"
    flask = "flask"
    django = "django"

class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer,primary_key=True,autoincrement=True)
     price = Column(Float)
     is_delete = Column(Boolean)
    price = Column(DECIMAL(10,4)) #100000.0001
     tag = Column(Enum(TagEnum))
     create_time = Column(Date)
     create_time = Column(DateTime)
     create_time = Column(Time)
     title = Column(String(50))
    content = Column(Text)
    content = Column(LONGTEXT)

# alembic
# flask-migrate
Base.metadata.drop_all()
Base.metadata.create_all()

from datetime import date
from datetime import datetime
from datetime import time

article = Article(price=100000.99999)
session.add(article)
session.commit()

 五、Column常用参数

1. primary_key：设置某个字段为主键。
2. autoincrement：设置这个字段为自动增长的。
3. default：设置某个字段的默认值。在发表时间这些字段上面经常用。
4. nullable：指定某个字段是否为空。默认值是True，就是可以为空。
5. unique：指定某个字段的值是否唯一。默认是False。
6. onupdate：在数据更新的时候会调用这个参数指定的值或者函数。在第一次插入这条数据的时候，不会用onupdate的值，只会使用default的值。常用的就是`update_time`（每次更新数据的时候都要更新的值）。
7. name：指定ORM模型中某个属性映射到表中的字段名。如果不指定，那么会使用这个属性的名字来作为字段名。如果指定了，就会使用指定的这个值作为参数。这个参数也可以当作位置参数，在第1个参数来指定。
title = Column(String(50),name='title',nullable=False)
title = Column('my_title',String(50),nullable=False)

class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer,primary_key=True,autoincrement=True)
    create_time = Column(DateTime,default=datetime.now)
    read_count = Column(Integer,default=11)
    title = Column(String(50),name='my_title',nullable=False)
    telephone = Column(String(11),unique=True)
    update_time = Column(DateTime,onupdate=datetime.now,default=datetime.now)

Base.metadata.drop_all()
Base.metadata.create_all()
article1 = Article(title='abc')
session.add(article1)
session.commit()

六、query可用参数

1. 模型对象。指定查找这个模型中所有的对象。
2. 模型中的属性。可以指定只查找某个模型的其中几个属性。
3. 聚合函数。
* func.count：统计行的数量。
* func.avg：求平均值。
* func.max：求最大值。
* func.min：求最小值。
* func.sum：求和。
`func`上，其实没有任何聚合函数。但是因为他底层做了一些魔术，只要mysql中有的聚合函数，都可以通过func调用。

class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    price = Column(Float,nullable=False)
    def __repr__(self):
        return "<Article(title:%s)>" % self.title
# Base.metadata.drop_all()
# Base.metadata.create_all()
for x in range(6):
    article = Article(title='title%s'%x,price=random.randint(50,100))
    session.add(article)
session.commit()

# 模型对象
articles = session.query(Article).all()
print(articles)

# 模型中的属性
articles = session.query(Article.title,Article.price).all()
print(articles)#返回元祖[(title,price),()...]

# 聚合函数
result = session.query(func.count(Article.id)).first()#一共有多少个id，也有就统计一共多少行
result = session.query(func.avg(Article.price)).first()#表中价格平均，以下类似
result = session.query(func.max(Article.price)).first()
result = session.query(func.min(Article.price)).first()
result = session.query(func.sum(Article.price)).first()
print(result)
print(func.sum(Article.price))
# select sum(price) from article

七、filter过滤条件

过滤是数据提取的一个很重要的功能，以下对一些常用的过滤条件进行解释，并且这些过滤条件都是只能通过filter方法实现的：
1. equals：
article = session.query(Article).filter(Article.title == "title0").first()
2. not equals:
query.filter(User.name != 'ed')
2. like：
query.filter(User.name.like('%ed%'))

3. 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%'))))

4. not in：
query.filter(~User.name.in_(['ed','wendy','jack']))
5. is null：
query.filter(User.name==None)
query.filter(User.name.is_(None))

6. is not null:
query.filter(User.name != None)
# 或者是query.filter(User.name.isnot(None))

7. and：
from sqlalchemy import and_
query.filter(and_(User.name=='ed',User.fullname=='Ed Jones'))

query.filter(User.name=='ed',User.fullname=='Ed Jones)'# 或者是传递多个参数

query.filter(User.name=='ed').filter(User.fullname=='Ed Jones')# 或者是通过多次filter操作

8. or：
from sqlalchemy import or_ query.filter(or_(User.name=='ed',User.name=='wendy'))

如果想要查看orm底层转换的sql语句，可以在filter方法后面不要再执行任何方法直接打印就可以看到了。比如：
articles = session.query(Article).filter(or_(Article.title=='abc',Article.content=='abc'))
print(articles)

八、ForeignKey外键

使用SQLAlchemy创建外键非常简单。在从表中增加一个字段，指定这个字段外键的是哪个表的哪个字段就可以了。从表中外键的字段，必须和父表的主键字段类型保持一致。

# 父表 / 从表
# user/article

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"))

user = User(username='xinzhi')
session.add(user)
session.commit()

article = Article(title='abc',content='123',uid=1)
session.add(article)
session.commit()

外键约束有以下几项：
1. RESTRICT：父表数据被删除，会阻止删除。默认就是这一项。
2. NO ACTION：在MySQL中，同RESTRICT。
3. CASCADE：级联删除。
4. SET NULL：父表数据被删除，子表数据会设置为NULL。

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"),ondelete='RESTRICT')#父id禁止删除，不填则默认
    uid = Column(Integer, ForeignKey("user.id"), ondelete='SET NULL')#允许父表删除，如子表后nullable=False则报错
    uid = Column(Integer, ForeignKey("user.id"), ondelete='CASCADE')#父表删除，子表跟着删除

 

九、ORM一对多模型

mysql级别的外键，还不够ORM，必须拿到一个表的外键，然后通过这个外键再去另外一张表中查找，这样太麻烦了。SQLAlchemy提供了一个`relationship`，这个类可以定义属性，以后在访问相关联的表的时候就直接可以通过属性访问的方式就可以访问得到了。

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)
    #子表中可以通过`backref`来指定反向访问的属性名称，父表关联可省略。
    # articles = relationship("Article")

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",uselist=False)

article = session.query(Article).first()

user = session.query(User).first()
print(user.articles)

 父表中添加子表数据

article1=Article(title='title1',content='123')
article2=Article(title='title2',content='456')
user.articles.append(article1)
user.articles.append(article2)
session.add(user)
session.commit()

子表中加父表对象

user=User(username='xinzhi')
article1=Article(title='title1',content='123')
article1.author=user
session.add(article1)
session.commit

十、ORM一对一模型

在sqlalchemy中，如果想要将两个模型映射成一对一的关系，那么应该在父模型中，指定引用的时候，要传递一个`uselist=False`这个参数进去。就是告诉父模型，以后引用这个从模型的时候，不再是一个列表了，而是一个对象了。

from sqlalchemy.orm import sessionmaker,relationship,backref

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)

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添加一个extend，非列表类型，则不能通过user.extend.append去增加多个
    user = relationship("User",backref=backref("extend",uselist=False))

十一、ORM多对多模型

1. 多对多的关系需要通过一张中间表来绑定他们之间的关系。
2. 先把两个需要做多对多的模型定义出来
3. 使用Table定义一个中间表，中间表一般就是包含两个模型的外键字段就可以了，并且让他们两个来作为一个“复合主键”。
4. 在两个需要做多对多的模型中随便选择一个模型，定义一个relationship属性，来绑定三者之间的关系，在使用relationship的时候，需要传入一个secondary=中间表。

from sqlalchemy import Table

class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    #两个类中其中一个填写关系即可
    # tags = relationship("Tag",backref="articles",secondary=article_tag)

class Tag(Base):
    __tablename__ = 'tag'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False)
    articles = relationship("Article",backref="tags",secondary=article_tag)

article_tag = Table(
    "article_tag",
    Base.metadata,
    Column("article_id",Integer,ForeignKey("article.id"),primary_key=True),
    Column("tag_id",Integer,ForeignKey("tag.id"),primary_key=True)
)
article1 = Article(title="article1")
article2 = Article(title="article2")
tag1 = Tag(name='tag1')
tag2 = Tag(name='tag2')

article1.tags.append(tag1)
article1.tags.append(tag2)
article2.tags.append(tag1)
article2.tags.append(tag2)

session.add(article1)
session.add(article2)
session.commit()

article = session.query(Article).first()
print(article.tags)
tag = session.query(Tag).first()
print(tag.articles)

十二、ORM层面删除数据注意事项

ORM层面删除数据，会无视mysql级别的外键约束。直接会将对应的数据删除，然后将从表中的那个外键设置为NULL。如果想要避免这种行为，应该将从表中的外键的`nullable=False`。
在SQLAlchemy，只要将一个数据添加到session中，和他相关联的数据都可以一起存入到数据库中了。这些是怎么设置的呢？其实是通过relationship的时候，有一个关键字参数cascade可以设置这些属性：
1. save-update：默认选项。在添加一条数据的时候，会把其他和他相关联的数据都添加到数据库中。这种行为就是save-update属性影响的。
2. delete：表示当删除某一个模型中的数据的时候，是否也删掉使用relationship和他关联的数据。
3. delete-orphan：表示当对一个ORM对象解除了父表中的关联对象的时候，自己便会被删除掉。当然如果父表中的数据被删除，自己也会被删除。这个选项只能用在一对多上，不能用在多对多以及多对一上。并且还需要在子模型中的relationship中，增加一个single_parent=True的参数。
4. merge：默认选项。当在使用session.merge，合并一个对象的时候，会将使用了relationship相关联的对象也进行merge操作。
5. expunge：移除操作的时候，会将相关联的对象也进行移除。这个操作只是从session中移除，并不会真正的从数据库中删除。
6. all：是对save-update, merge, refresh-expire, expunge, delete几种的缩写。

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)
    uid = Column(Integer,ForeignKey("user.id"),nullable=False)#nullable=False如果不填写，可以session.delete(user)通过删掉父表，此数据为null

    author = relationship("User",backref='articles')

user = User(username='zhiliao')
article = Article(title='hello world')
article.author = user
session.add(article)
session.commit()

user = session.query(User).first()
session.delete(user)#主键设置nullable=False，执行报错
session.commit()