数据库SQLAlchemy
[TOC]
SQLAlchemy介绍
- flask_sqlalchemy是一套ORM框架。
- ORM（Object Relationship Mapping）：模型关系映射,把关系数据库的表结构映射到对象上
- ORM优点：
- 隐藏数据访问细节，提高开发效率
- 构造数据结构变得简单
- ORM缺点：
- 降低执行效率，增加学习成本
- 无法进行复杂的查询
配置连接与初始化
- 在config.py文件添加配置信息
# 数据库连接遵循这个语句
# dialect+driver://username:password@host:port/database
DIALECT = 'mysql'
DRIVER = 'pymysql'
USERNAME = 'Jyang'
PASSWORD = 'yj19930621+-*/'
HOST = '127.0.0.1'
PORT = '3306'
DATABASE = 'jsql'
SQLALCHEMY_DATABASE_URI = "{}+{}://{}:{}@{}:{}/{}?charset=utf8".format(
DIALECT, DRIVER, USERNAME, PASSWORD, HOST, PORT, DATABASE)
SQLALCHEMY_TRACK_MODIFICATIONS = False # 不提示警告
- 导入用flask_sqlalchemy中的SQLAlchemy进行初始化，并引入配置文件
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import config
app = Flask(__name__)
app.config.from_object(config) #引入配置文件
db = SQLAlchemy(app) #实例化
db.create_all() #将模型创建到数据库中(只创建，后续有变化不会修改，如字段有变动需要再数据库把表删掉)
实例
config.py
# encoding: utf-8
DEBUG = True
DIALECT = 'mysql'
DRIVER = 'pymysql'
USERNAME = 'Jyang'
PASSWORD = 'yj19930621+-*/'
HOST = '127.0.0.1'
PORT = '3306'
DATABASE = 'jsql'
SQLALCHEMY_DATABASE_URI = "{}+{}://{}:{}@{}:{}/{}?charset=utf8".format(
DIALECT, DRIVER, USERNAME, PASSWORD, HOST, PORT, DATABASE)
SQLALCHEMY_TRACK_MODIFICATIONS = False
main.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import config
app = Flask(__name__) #引入配置文件
app.config.from_object(config)
db = SQLAlchemy(app)
class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key= True, autoincrement = True)
username = db.Column(db.String(100), nullable = False)
# 创建映射表
class Article(db.Model): #模型必须继承db.Model
#db.Column映射到表中字段，primary_key主键，autoincrement自增,nullable可为空(默认为True)
__tablename__ = 'article'
id = db.Column(db.Integer, primary_key= True, autoincrement = True)
title = db.Column(db.String(100),nullable = False)
content = db.Column(db.Text, nullable = False)
author_id = db.Column(db.Integer,db.ForeignKey('user.id'))#关联user表的id字段
#反向引用，'User'为模型名字
author = db.relationship('User',backref=db.backref('article'))
db.create_all()
@app.route('/')
def hello_world():
return "hello world"
if __name__ == '__main__':
app.run()
