flask+sqlalchemy/flask_sqlalchem

Flask项目连接数据库postgresql，比较常用以下两种方式
Sqlalchemy和flask_sqlalchemy
前提：

# 运行以下命令安装需要的包
pip install Flask
pip install Flask-SQLAlchemy
pip install SQLAlchemy

SQLAlchemy：

SQLAlchemy是一个关系型数据库框架，它提供了高层的 ORM 和底层的原生数据库的操作，让开发者不用直接和 SQL 语句打交道，而是通过 Python 对象来操作数据库，在舍弃一些性能开销的同时，换来的是开发效率的较大提升。一句话：就是对数据库的抽象！

Flask-SQLAlchemy：

Flask-SQLAlchemy是一个简化了 SQLAlchemy 操作的flask扩展，是SQLAlchemy的具体实现，封装了对数据库的基本操作。举例：如果说动物园是SQLAlchemy，那Flask-SQLAlchemy只是其中的一只。
下面分别以这两种方式写两个小的项目

1.SQLAlchemy操作数据库

# 使用SQLAlchemy进行数据库连接


from flask import Flask

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import Column, Integer, String, Float, Boolean, Text, ForeignKey

app = Flask(__name__)
Base = declarative_base()

engine = create_engine('postgresql://postgres:admin@127.0.0.1:5432/flask_test', echo=True)
Session = sessionmaker(bind=engine)
session = Session()


class Author(Base):
    __tablename__ = 'author'
    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(String(10))

    def save(self):
        session.add(self)
        session.commit()

    def __str__(self):
        return self.name


class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer, autoincrement=True, primary_key=True)
    book_name = Column(String(11))
    author_id = Column(Integer, ForeignKey('author.id', ondelete='CASCADE'))

    def save(self):
        session.add(self)
        session.commit()

    def __str__(self):
        return self.book_name


if __name__ == "__main__":
   Base.metadata.drop_all(engine)
   Base.metadata.create_all(engine)
   author = Author(name='老舍')
   author.save()
   book = Book(book_name="骆驼祥子",author_id=author.id)
   book.save()
   print(author,book)

运行结果：

2020-04-30 17:09:12,664 INFO sqlalchemy.engine.base.Engine select version()
2020-04-30 17:09:12,664 INFO sqlalchemy.engine.base.Engine {}
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine select current_schema()
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine {}
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine {}
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine {}
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine {}
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine {'name': 'author'}
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine {'name': 'book'}
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine {'name': 'author'}
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine {'name': 'book'}
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE author (
    id SERIAL NOT NULL, 
    name VARCHAR(10), 
    PRIMARY KEY (id)
)


2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine {}
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine COMMIT
2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE book (
    id SERIAL NOT NULL, 
    book_name VARCHAR(11), 
    author_id INTEGER, 
    PRIMARY KEY (id), 
    FOREIGN KEY(author_id) REFERENCES author (id) ON DELETE CASCADE
)


2020-04-30 17:09:12,680 INFO sqlalchemy.engine.base.Engine {}
2020-04-30 17:09:12,696 INFO sqlalchemy.engine.base.Engine COMMIT
2020-04-30 17:09:12,696 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-04-30 17:09:12,696 INFO sqlalchemy.engine.base.Engine INSERT INTO author (name) VALUES (%(name)s) RETURNING author.id
2020-04-30 17:09:12,696 INFO sqlalchemy.engine.base.Engine {'name': '老舍'}
2020-04-30 17:09:12,696 INFO sqlalchemy.engine.base.Engine COMMIT
2020-04-30 17:09:12,696 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-04-30 17:09:12,696 INFO sqlalchemy.engine.base.Engine SELECT author.id AS author_id, author.name AS author_name 
FROM author 
WHERE author.id = %(param_1)s
2020-04-30 17:09:12,696 INFO sqlalchemy.engine.base.Engine {'param_1': 1}
2020-04-30 17:09:12,696 INFO sqlalchemy.engine.base.Engine INSERT INTO book (book_name, author_id) VALUES (%(book_name)s, %(author_id)s) RETURNING book.id
2020-04-30 17:09:12,696 INFO sqlalchemy.engine.base.Engine {'book_name': '骆驼祥子', 'author_id': 1}
2020-04-30 17:09:12,696 INFO sqlalchemy.engine.base.Engine COMMIT
2020-04-30 17:09:12,696 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-04-30 17:09:12,696 INFO sqlalchemy.engine.base.Engine SELECT author.id AS author_id, author.name AS author_name 
FROM author 
WHERE author.id = %(param_1)s
2020-04-30 17:09:12,696 INFO sqlalchemy.engine.base.Engine {'param_1': 1}
老舍 2020-04-30 17:09:12,696 INFO sqlalchemy.engine.base.Engine SELECT book.id AS book_id, book.book_name AS book_book_name, book.author_id AS book_author_id 
FROM book 
WHERE book.id = %(param_1)s
2020-04-30 17:09:12,696 INFO sqlalchemy.engine.base.Engine {'param_1': 1}
骆驼祥子

2.flask_sqlalchemy操作数据库

# 使用flask_sqlalchemy进行数据库连接
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# 配置数据库信息
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres:admin@localhost:5432/flask_test'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# 生成数据库访问对象
db = SQLAlchemy(app)
db.init_app(app)

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer,autoincrement=True,primary_key=True)
    name = db.Column(db.String(10))
    activate = db.Boolean(db.Boolean)
    heigh = db.Column(db.Float)

    def save(self):
        db.session.add(self)
        db.session.commit()
    def __str__(self):
        return self.name

class Phone(db.Model):
    __tablename__ = 'phone'
    id = db.Column(db.Integer,autoincrement=True,primary_key=True)
    content = db.Column(db.String(11))
    user_id = db.Column(db.Integer,db.ForeignKey('user.id',ondelete='CASCADE'))


    def save(self):
        db.session.add(self)
        db.session.commit()

    def __str__(self):
        return self.content


if __name__=="__main__":
    #  对表的操作 创建：# 删除：
    db.drop_all()
    db.create_all()
    user1 = User(name='小明',activate=True,heigh=1.5)
    user1.save()
    phone = Phone(content='12345678901',user_id=user1.id)
    phone.save()
    print(user1,phone)

运行结果：

image.png