flask数据库基础demo

SQLAlchemy一对一的创建及一些操作

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import os
# 获取绝对路径
BASE_DIR = os.path.abspath(os.path.dirname(__file__))
# wk/fk.sqlite
# 1.配置数据库连接
class Config(object):
    # 连接数据库
    SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(BASE_DIR, 'fk.sqlite')
    # 设置sqlalchemy 自动更新
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    SQLALCHEMY_COMMIT_ON_TEARDOWN = True
# 2. 读取Config 对象
app.config,from_object(Config)
# 3. 创建 对象
db = SQLAlchemy(app)
# 创建模型类
class Student(db.Model):
	__tablename__='tb_student'
	id = db.Column(db.Integer, primary_key= True, autoincrement= True)
	name = db.Column(db.String(32), nullable= False, unique= True)
	age = db.Column(db.Integer, default = 18)
	
	def __repr__(self):
		return '<obj name:{}>'.format(self.name)

if __name__ == '__main__':
	# 方便测试，每次开启都会更新数据库
	db.drop_all() # 删除表格
	db.create_all() # 创建表格
	
	std1 = Student(name='zs', age=18)
	std2 = Student(name='ls', age=17)
	std3 = Student(name='zss', age=20)
	std4 = Student(name='lss', age=19)
	
	db.session.add_all([std1,std2,std3,std4])
	db.session.commit()
	
# 查询：
# all所有
reselt1 = Student.query.all()
# get具体某一对象必须唯一标识
reselt2 = Student.query.get()
reselt3 = Student.query.first()# 第一个对象
# filter_by() 过滤，可以写多个条件 
reselt4 = Student.query.filter_by(name='zs',age=17).all
# filter()过滤 类名.属性名==值
reselt5 = Student.query.filter(Student.name == 'zs').all()

from sqlalchemy import or_, not_

reselt6 = Student.query.filter(or_(Student.name == 'zs',Student.age == 19)).all()# 或
reselt7 = Student.query.filter(not_(Student.age == 20)).all()# 取反
reselt8 = Student.query.order_by(-Student.age).all()# 升序排序 '-'降序
reselt9 = Student.query.offset(1).all() # 偏移
reselt10 = Student.query.limit(2).all() # 限定
reselt11 = Student.query.filter(Student.name.like('%s%')).all()
# 原生sql
reselt = db.session.execute("select * from tb_student")
for ret in reselt:
	print(ret.id,ret.name,ret.age)

 # 默认开启事务，增删改操作，需提交
# 改1
student_obj = Student.query.get(1)
student_obj.age = 40
db.session.add(student_obj)
db.session.commit()
# 改2
Student.query.filter_by(id=1).update({'age':30})
db.session.commit()
# 删除
student_obj = Student.query.get(3)
db.session.delete(student_obj)
db.session.commit()

一对多及一些基础操作

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import os
# 获取绝对路径
BASE_DIR = os.path.abspath(os.path.dirname(__file__))
# wk/fk.sqlite
# 1.配置数据库连接
class Config(object):
    # 连接数据库
    SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(BASE_DIR, 'fk.sqlite')
    # 设置sqlalchemy 自动更新
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    SQLALCHEMY_COMMIT_ON_TEARDOWN = True
# 2. 读取Config 对象
app.config,from_object(Config)
# 3. 创建 对象
db = SQLAlchemy(app)

class Course(db.Model):
	__tablename__='tb_course'
	id = db.Column(db.Integer, primary_key=True, autoincrement=True)
	name = db.Column(db.String(32), nullable=False, unique=True)
	student = db.relationship('Student', backref='course')
	def __repr__(self):
		return '<obj name:{}>'.format(self.name)

class Student(db.Model):
	__tablename__='tb_student'
	id = db.Column(db.Integer,primary_key=True, autoincrement=True)
	name = db.Column(db.String(32), nullable=False, unique=True)
	age = db.Column(db.Integer, default=18)
	# 外键
	course_id = db.Column(db.Integer, db.ForeigmKey('tb_course.id'))
	def __repr__(self):
		return '<obj name:{}>'.format(self.name)
		
if __name__=='__main__'
	db.drop_all()
	db.create_all()
	
	course1 = Course(name='三年一班')
	course2 = Course(name='三年二班')
	course3 = Course(name='三年三班')
	
	stu1 = Student(name='周杰伦', age=40, course_id=course2.id)
	stu2 = Student(name='周杰棍', age=50, course_id=course1.id)
	stu3 = Student(name='双杰伦', age=30, course_id=course2.id)
	stu4 = Student(name='周杰', age=66, course_id=course3.id)
	db.session.add_all([stu1,stu2,stu3,stu4])
	db.session.commit()
	
# 查询：周杰伦哪个班
student_obj = Student.query.filter(Student.name == '周杰伦').first()
ret = student_obj.course.name
print(ret)
	# 法二
course_obj = Course.query.get(1)
print(course_obj.name)

# 删除周杰 关联的记录被置空
std_obj = Student.query.filter(Student.name == '周杰').first()
db.session.delete(std_obj)
db.session.commit()