SQLAlchemy与Flask-SQLAlchemy

在web应用里使用原生的SQL语句操作数据库主要存在下面两类问题：

1.手动编写SQL语句比较乏味，而且视图函数中加入太多的SQL语句会降低代码的易读性。另外还容易出现安全问题，比如SQL注入。
2.在部署时切换到不通的DBMS，我们需要使用不同的Python接口库，这让DBMS的切换变的不太容易。

下面利用SQLAlchemy对MySQL进行简单的CRUD操作

# -*- coding: utf-8 -*-
"""利用sqlalchemy对mysql进行简单的增删改查"""

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, Integer, String, DateTime, Boolean

engine = create_engine('mysql+pymysql://root:root@localhost:3306/news?charset=utf8')  # 连接news数据库（news库需提前创建好）
Session = sessionmaker(bind=engine)
Base = declarative_base()


class News(Base):
    """
    在python终端执行以下命令手动创建表
    from sqlalchemy_test import News, engine
    News.metadata.create_all(engine)
    """
    # 表名
    __tablename__ = 'news'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(200), nullable=False)
    content = Column(String(2000), nullable=False, default='新闻内容')
    types = Column(String(20), nullable=False)
    image = Column(String(300), default="")
    author = Column(String(20), default="")
    view_count = Column(Integer, default=0)
    created_at = Column(DateTime)
    is_valid = Column(Boolean, default=True)


class MysqlOrmTest(object):
    def __init__(self):
        self.session = Session()

    def add_one(self):
        # 添加一条数据
        news_obj = News(
            title='标题',
            types='体育',
            content="新闻内容"
        )
        self.session.add(news_obj)
        self.session.commit()
        return news_obj

    def add_more(self):
        """添加多条数据"""
        self.session.add_all([
            News(title='标题2', types='推荐', content="新闻内容"),
            News(title='标题3', types='百家', content="新闻内容"),
            News(title='标题4', types='军事', content="新闻内容"),
            News(title='标题5', types='推荐', content="新闻内容")
        ])
        self.session.commit()

    def get_more(self):
        """获取多条数据"""
        return self.session.query(News).filter_by(is_valid=1)

    def get_one(self):
        """获取一条数据"""
        return self.session.query(News).get(8)   # 获取id为8的数据

    def update_data(self):
        """"数据更新 """
        obj = self.session.query(News).get(4)
        obj.is_valid = 0
        self.session.add(obj)
        self.session.commit()
        return obj.is_valid

    def delete_data(self):
        """删除数据"""
        # 获取要删除的数据
        data = self.session.query(News).get(8)
        self.session.delete(data)
        self.session.commit()


def main():
    obj = MysqlOrmTest()
    # 增加一条数据
    result = obj.add_one()
    # 增加多条数据
    obj.add_more()
    # 获取一条数据
    result = obj.get_one()
    # 获取多条数据
    result = obj.get_more()
    for item in result:
        print(item.title)
    print(obj.update_data())
    # 删除数据
    obj.delete_data()


if __name__ == "__main__":
    main()

Flask-SQLAlchemy是对SQLAlchemy又进行了一层封装

# -*- coding: utf-8 -*-
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:root@localhost:3306/note?charset=utf8'
db = SQLAlchemy(app)
class Note(db.Model):
    """
     在python终端执行以下命令手动创建表
     from flask_sqlalchemy_test import db
     db.create_all()
    """
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    body = db.Column(db.Text)

    def __repr__(self):
        return '<Note %r>' % self.body

Create

note1 = Note(body="这是第1条笔记")
note2 = Note(body="这是第2条笔记")
db.session.add(note1)
db.session.add(note2)
db.session.commit()

除了依次调用add()方法添加多个记录，也可以使用add_all()一次添加包含所有记录对象的列表

Read

一般来说，一个完整的查询遵循<模型类名>.query.<过滤方法>.<查询方法>的模式
all()： 返回所有的记录

Note.query.all()

first()： 返回第一条记录

note1 = Note.query.first() 
print(note1.id)

count()： 返回记录的数量

Note.query.count()

get()： 指定主键值（id字段）的记录

note2 = Note.query.get(2)   # 读取表中id为2的数据
print(note2.body)

filter()： 它使用指定的规则来过滤记录

Note.query.filter(Note.body=='SHAVE').first()    #找出body字段值为SHAVE的记录

LIKE

filter(Note.body.like('%foo%'))

filter(Note.body.in_(['foo','bar','baz']))

NOT IN

filter(~Note.body.in_(['foo','bar','baz']))

AND
1.使用and_()

from sqlalchemy import and_
filter(and_(Note.body == 'foo',Note.title == 'foobar'))

2.或在filter()中加入多个表达式，使用逗号分隔

filter(Note.body == 'foo', Note.title == 'foobar')

3.或叠加调用多个filter() / filter_by()方法

filter(Note.body == 'foo').filter(Note.title == 'foobar')

OR 使用or_()

from sqlalchemy import or_
filter(or_(Note.body == 'foo', Note.body == 'bar'))

filter_by()：在filter_by方法中，你可以使用关键字表达式来指定过滤规则。

Note.query.filter(body=='SHAVE').first()

更多的查询方法和过滤方法可参考(http://docs.sqlalchemy.org/en/lates/orm/query.html)

Update

更新一条记录非常简单，直接赋值给模型类的字段属性就可以改变字段值，然后调用commit()方法提交会话即可。

note = Note.query.get(3)
note.body = "hello world"
db.session.commit()

只有要插入新的记录或将现有的记录添加到会话中时才需要add()方法，单纯要更新现有的记录时只需要直接为属性赋值，然后提交会话。

Delete

note = Note.query.get(2)
db.session.delete(note)
db.session.commit()

删除记录和添加记录很相似，最后都需调用commit()方法提交需改

SQLAlchemy与Flask-SQLAlchemy