创建flask一对一关系表
from flask import Flask,render_template
from flask_sqlalchemy import SQLAlchemy
import os
app = Flask(__name__)
db = SQLAlchemy(app)
#配置-项目的根路径
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
# 连接mysql,要按照pymysql ; root:123456@127.0.0.1 用户名:密码@
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@127.0.0.1/onetoone'
# 显示mysql语句
# app.config['SQLALCHEMY_ECHO'] = True
class Parent(db.Model):
id = db.Column(db.Integer, primary_key=True,autoincrement=True)
name = db.Column(db.String(29))
child = db.relationship("Child", backref='parent', uselist=False)
# uselist=False 来约束, 因为在一对多中通过 查询得到的是一个列表,
# 所以, uselist=False禁用列表, 使我们只能查找到一个, 就构成了一对一关系
class Child(db.Model):
id = db.Column(db.Integer, primary_key=True,autoincrement=True)
name = db.Column(db.String(29))
parent_id = db.Column(db.Integer, db.ForeignKey('parent.id'),unique=True)
def func():
# db.drop_all() #清空数据库
db.create_all() #导入数据到数据库
def add1():
parent = Parent(name='mama')
db.session.add(parent)
db.session.commit()
child = Child(name='child',parent_id=parent.id)
db.session.add(child)
db.session.commit()
def update1():
a = Child.query.filter_by(name='child').first()
a.name = 'lala'
db.session.commit()
if __name__ == '__main__':
# func()
# add1()
update1()
一对多
# 一个人有 多个地址
class Person(db.Model):
__tablename__='my_person'
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
name = db.Column(db.String(80),unique=True)
addresses = db.relationship('Address',backref='person',lazy='dynamic',cascade='all, delete-orphan')
class Address(db.Model):
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
address = db.Column(db.String(69))
person_id = db.Column(db.Integer,db.ForeignKey('my_person.id'))
#删除表
def init():
# db.drop_all()
db.create_all()
# 添加
def add1():
a = Person(name='yes')
db.session.add(a)
db.session.commit()
addr1 = Address(address='广州',person_id=a.id)
addr2 = Address(address='深圳',person_id=a.id)
db.session.add(addr1)
db.session.add(addr2)
db.session.commit()
#修改
def update1():
b = Person.query.get(2)
b.name='panpan23'
db.session.commit()
# 删除
def delete1():
# 级联删除只要主键被删除,外键也会被删除
p = Person.query.get(1)
db.session.delete(p)
db.session.commit()
#查询
def select():
person = Person.query.filter_by(name='panpan').first()
print(person.name)
print(person.id)
adrs = Address.query.filter_by(person_id=person.id)
for adr in adrs:
print(adr.address)
if __name__ == '__main__':
# init()
# add1()
update1()
# delete1()
# select()
多对多
tec_stu = db.Table('tec_stu',
db.Column('student_id', db.Integer, db.ForeignKey('student.id')),
db.Column('teacher_id', db.Integer, db.ForeignKey('teacher.id')),
)
class Teacher(db.Model):
id = db.Column(db.Integer, primary_key=True,autoincrement=True)
name = db.Column(db.String(10))
students = db.relationship('Student', secondary=tec_stu,backref=db.backref('teachers', lazy='dynamic'),cascade='all')
class Student(db.Model):
id = db.Column(db.Integer, primary_key=True,autoincrement=True)
name = db.Column(db.String(29))
def init():
# db.drop_all()
db.create_all()
def add1():
# 先添加学生
stu = Student(name='aa')
stu2 = Student(name='bb')
db.session.add(stu)
db.session.commit()
# 筛选老师
tec = Teacher.query.filter_by(name='sdfr').first()
# tec = Teacher(name='sdfr')
db.session.add(tec) # 添加老师
# 给老师添加学生
tec.students.append(stu)
tec.students.append(stu2)
# 之后再提交
db.session.commit()
def delete1():
# 删除指定老师和所有的学生,级联情况下
q = Teacher.query.get(1)
db.session.delete(q)
db.session.commit()
# 删除某个学生,外键表对应的信息也会删除
# s = Student.query.filter_by(name='aa').first()
# db.session.delete(s)
# db.session.commit()
def update():
d = Teacher.query.filter_by(name='sdfr').first()
d.name = 'lala'
db.session.commit()
if __name__ == '__main__':
# init()
# add1()
# delete1()
update()
复杂关系表
# **一个用户有多篇文章,文章与文章类型多对多**
class User(db.Model):
__tablename__ = 'blog_user'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(100), nullable=False, unique=True)
password = db.Column(db.String(100), nullable=False)
articles = db.relationship('Article', backref='user', lazy='dynamic', cascade='all,delete-orphan')
blog_articletype_article = db.Table(
'blog_articletype_article',
db.Column('id', db.Integer, primary_key=True),
db.Column('articletype_id', db.Integer, db.ForeignKey('blog_articletype.id')),
db.Column('article_id', db.Integer, db.ForeignKey('blog_article.id'))
)
class ArticleType(db.Model):
__tablename__ = 'blog_articletype'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(100), nullable=False, unique=True)
articles = db.relationship(
'Article',
secondary=blog_articletype_article,
backref=db.backref('articletypes', lazy='dynamic')
)
class Article(db.Model):
__tablename__ = 'blog_article'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(100), nullable=False, unique=True)
content = db.Column(db.Text, nullable=False)
publishdate = db.Column(db.DateTime, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('blog_user.id'))
# db.create_all()
@app.route(rule='/',methods=['get'])
def index():
return render_template('home.html')
@app.route(rule='/register/',methods=['get'])
def register_get():
return render_template('register.html')
@app.route(rule='/register/',methods=['POST'])
def register_post():
name = request.form.get('name')
password = request.form.get('pwd')
if not password or not name :
return '请输入用户名和密码'
user = User(name=name,password=password)
db.session.add(user)
db.session.commit()
return '注册成功'
@app.route(rule='/login/',methods=['get'])
def login_get():
return render_template('login.html')
@app.route(rule='/login/',methods=['POST'])
def login_post():
name = request.form.get('name')
password = request.form.get('pwd')
if not password or not name :
return '请输入用户名和密码'
user = User.query.filter(User.name==name,User.password==password).first()
if user:
return '登录成功'
else:
return '用户名不存在或密码错误'
if __name__ == '__main__':
app.run(host='127.0.0.1',port=8888,debug=True)