import pymysql
from flask import Flask, render_template, flash, request, redirect, url_for
from Book_Info.Book import book
from flask_sqlalchemy import SQLAlchemy
from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField
from wtforms.validators import DataRequired
pymysql.install_as_MySQLdb()
app = Flask(__name__)
app.secret_key = 'sad'
# 连接 自己的mysql
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root@localhost:3306/flask_book'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.register_blueprint(book)
db = SQLAlchemy(app)
class Author(db.Model):
__tablename__ = 'authors'
id = db.Column(db.Integer, primary_key=True)
a_name = db.Column(db.String(20))
book = db.relationship('Book', backref='author')
def __repr__(self):
return '<Autor: %s>' % self.a_name
@app.route('/')
def index():
author = Author.query.all()
return Response(author)
if __name__ == '__main__':
# 删除所有的表
# db.drop_all()
# 创建表
# db.create_all()
app.run(debug=True)
from flask_sqlalchemy import SQLAlchemy
from flask import Flask
import pymysql
pymysql.install_as_MySQLdb()
app = Flask(__name__)
# 配置数据库的地址mysql://root@localhost:3306/
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root@localhost:3306/falsk_sqlalchemy'
# 跟踪我们数据库的动态， 不建议开启
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
# 数据库模型， 需要继承db.Model
# 就是和DJANGO 继承model.Model 一样，不过Django 都是帮你封装好的
class Teacher(db.Model):
''':return 定义表名'''
__tablename__ = 'teacher'
'''创建字段'''
id = db.Column(db.Integer, primary_key=True)
t_name = db.Column(db.String(10))
''' 表示和user模型发生了关联'''
student = db.relationship('Student', backref='teacher')
def __repr__(self):
return '<Teacher: %s %s>' % (self.id, self.t_name)
class Student(db.Model):
__tablename__ = 'student'
id = db.Column(db.Integer, primary_key=True)
s_name = db.Column(db.String(10))
'''__tablename__ .id 创建外键'''
t_id = db.Column(db.Integer, db.ForeignKey('teacher.id'))
def __repr__(self):
return '<Teacher: %s %s %s>' % (self.id, self.s_name, self.t_id)
@app.route('/')
def index():
return '123'
if __name__ == '__main__':
# 删除表
db.drop_all()
# # 创建表
db.create_all()
app.run(debug=True)
In [3]: from app_flask_sqlalchemy import *
# 增
In [4]: teacher = Teacher(t_name='tom')
In [5]: teacher
Out[5]: <Teacher (transient 2347722206656)>
In [6]: db.session.add(teacher)
# 注意增必须要提交commit()
In [7]: db.session.commit()
In [8]: student = Student(s_name='小沫', t_id=1)
In [9]: db.session.add(student)
In [10]: db.session.commit()
# 改
In [11]: student.s_name = 'xiaomo'
# 改不需要add 直接commit() 就可以了
In [12]: db.session.commit()
# 删除 delete
In [13]: db.session.delete(student)
# 需要commit()
In [14]: db.session.commit()
# 查询所有的老师
Teacher.query.all()
# 查询有多少老师
Teacher.query.count()
# 查询第一条数据
Teacher.query.first()
# 查询id 为 4 的数据
Teacher.query.get(4)
# 第二种写法
Teacher.query.filter_by(id=4).first()
# 第三种写法
Teacher.query.filter(Teacher.id==1).first()
# filter 和 filer_by
filter 功能更加强大， 能查询更多的条件
from sqlalchemy import create_engine
from sqlalchemy import Column, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import sqlite3
'''
flask 使用SQLAlchemy
- SQLAlchemy 就是关系对象映射(orm) 说简单的一点就是将你的sql语句转换为orm语句。
- SQLAlchemy 简单来说就是表和表之间的关系（一对一， 一对多， 多对多）映射到表中，然后我们的框架只需要在你的对象层操作就可以了
- 相对sql 而言 orm 的语法更为简单，灵活，开发效率高，有的时候也会使用sql， 就比如在查询复杂的场景，使用sql语句
'''
'''
安装 flask flask-sqlalchemy 的扩展包
pip install flask-sqlalchemy
SQLAlchemy 通过engine(引擎) 去选择数据
'''
DB_FILES = 'db/database.sqlite3'
engine = create_engine('sqlite:///' + DB_FILES, echo=True)
# 创建模型基类
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
name = Column('username', String(100), primary_key=True)
password = Column('password', String(100))
email = Column('email', String(100))
if __name__ == '__main__':
Base.metadata.create_all(engine)
def find_all():
Session = sessionmaker(bind=engine)
session = Session()
list = session.query(User).all()
list2 = []
for user in list:
dic = {}
dic['username'] = user.username
dic['password'] = user.password
dic['eamil'] = user.eamil
list2.append(dic)
session.close()
# 创建数据库连接对象
conn = sqlite3.connect(DB_FILES)
try:
cursor = conn.cursor()
sql = 'SELECT username, password, email FROM users'
cursor.execute(sql)
# 提前游标数据
result_set = cursor.fetchall()
for row in result_set:
dic = {}
dic['username'] = row[0]
dic['password'] = row[1]
dic['eamil'] = row[2]
list.append(dic)
print('数据库查询成功')
except:
print('数据库查询失败')
finally:
conn.close()
return list2
flask sqlalchemy基本介绍
