python3使用flask-sqlalchemy操作mysql

   由于 MySQL-python 不支持 Python 3（MySQL-3.23 through 5.5 and Python-2.4 through 2.7 are currently supported），python3下使用flask-sqlalchemy时，安装的命令是：

pip install pymysql
pip install flask-sqlalchemy

  相关代码如下：

# config.py

SECRET_KEY = 'flaskMysql'
SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://user:password@host:port/dbname'
SQLALCHEMY_TRACK_MODIFICATIONS = True
DEBUG = True 

# _init_.py

from flask import Flask, render_template, jsonify
from db_mysql import db

app = Flask(__name__) # 新建app对象
app.config.from_object('config') # 加载配置信息，其中有数据库的配置信息，包含在SQLALCHEMY_DATABASE_URI中


# 初始化db,并创建models中定义的表格
with app.app_context(): # 添加这一句，否则会报数据库找不到application和context错误
    db.init_app(app) # 初始化db
    db.create_all() # 创建所有未创建的table

 

#db_mysql.py

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, nullable=False, primary_key=True, autoincrement=True)
    username = db.Column(db.String(32), nullable=False, unique=True, server_default='', index=True)
    role_id = db.Column(db.Integer, nullable=False, server_default='0')

    def __init__(self,username,role_id):
        self.username = username
        self.role_id = role_id

    def __repr__(self):
        return '<User %r,Role id %r>' %(self.username,self.role_id)

class Role(db.Model):
    __tablename__ = 'roles'
    id = db.Column(db.Integer, nullable=False, primary_key=True, autoincrement=True)
    name = db.Column(db.String(16), nullable=False, server_default='', unique=True)

    def __init__(self,name):
        self.name = name

    def __repr__(self):
        return '<Role %r>' % self.name

数据库关联操作：

#按用户名摸糊查询
User.query.join(Role).filter(Users.username.like('%xx%'))
#select xxx from User inner join Role on user.role_id=role.id where user.username like '%xx%'

#左外联接(left join)
User.query.outerjoin(Role).filter(Users.username.like('%xx%'))
#select xxx from User left outer join Role on user.role_id=role.id where user.username like '%xx%'

#以上是已经设置好外键,它自动找到关联的字段.也可以自己指定:
User.query.join(Role,User.role_id==Role.id).filter(User.username.like('%xx%'))
#select xxx from User inner join User on user.role_id=role.id where user.username like '%xx%'