连接数据库
需要事先安装pymysql和flask_sqlalchemy
安装方式
pip install pymysql
pip install flask_sqlalchemy
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import pymysql
app = Flask(__name__)
# 数据库的配置变量
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'hyachin_flask'
USERNAME = 'root'
PASSWORD = '123'
DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME,PASSWORD,
HOSTNAME,PORT,DATABASE)
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)
@app.route('/')
def hello_world(): # put application's code here
engine = db.get_engine()
with engine.connect() as conn:
result = conn.execute('select 1')
print(result.fetchone()) # 如果输出1,说明连接成功
return 'Hello World!'
if __name__ == '__main__':
app.run()
ORM映射与增删改查操作
建表
class Article(db.Model):
__tablename__ = 'article'
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
title = db.Column(db.String(200),nullable=False)
content = db.Column(db.Text,nullable=False)
db.create_all()
增删改查
@app.route('/article')
def article_view():
# 1.添加数据
# article = Article(title='钢铁是怎样炼成的',content='xxx')
# db.session.add(article)
# db.session.commit()
# 2.查询数据
# article = Article.query.filter_by(id=1)[0]
# print(article.title)
# 3.修改数据
# article = Article.query.filter_by(id=1)[0]
# article.content = 'yyy'
# db.session.commit()
# 4.删除数据
Article.query.filter_by(id=1).delete()
db.session.commit()
return "数据操作成功"
一对多关系实现
class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
username = db.Column(db.String(200),nullable=False)
class Article(db.Model):
__tablename__ = 'article'
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
title = db.Column(db.String(200),nullable=False)
content = db.Column(db.Text,nullable=False)
# 外键
author_id = db.Column(db.Integer,db.ForeignKey('user.id'))
# relationship:
# 第一个参数要和引用的模型的类名一致
# 第二个参数是反向引用。author访问他的文章时用的字段
author = db.relationship('User',backref='articles')
db.drop_all()
db.create_all()
# 访问以下目录时对数据库进行操作
@app.route('/otm')
def one_to_many():
article = Article(title='111',content='xxx')
user = User(username='hyachin')
article.author = user
db.session.add(article)
db.session.commit()
print(user.articles)
return 'one_to_many数据操作成功'
一对一关系实现
定义用户拓展模型,和用户之间的关系是一对一
class UserExtension(db.Model):
__tablename__ = 'user_extension'
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
school = db.Column(db.String(100))
user_id = db.Column(db.Integer,db.ForeignKey('user.id'))
# db.backref
# 1.在反向引用的时候,如果需要传递一些其他的参数,那么就需要用到这个函数,否则不需要使用,
# 只要在relationship的backref参数上,设置反向引用的名称就可以了。
# 2.useList=False:表示反向引用的时候,不是一个列表,而是一个对象
user = db.relationship('User',backref=db.backref('extension',uselist=False))
访问地址操作数据库
@app.route('/oto')
def one_to_one():
user = User(username='hyachin')
extension = UserExtension(school='清华大学')
user.extension = extension
db.session.add(user)
db.session.commit()
return "one_to_one数据操作成功"