Flask 之 SQLAlchemy 基础入门

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