flask_sqlalchemy使用设置UTF8无效

Question

运行环境
click==6.7
Flask==0.12.2
Flask-MySQLdb==0.2.0
Flask-SQLAlchemy==2.3.2
itsdangerous==0.24
Jinja2==2.10
MarkupSafe==1.0
mysqlclient==1.3.12
PyMySQL==0.8.0
SQLAlchemy==1.2.4
Werkzeug==0.14.1

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import pymysql

from datetime import datetime

# http://www.pythondoc.com/flask-sqlalchemy/config.html
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql://root:root@localhost:3306/movies?charset=utf8"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
app.config["SQLALCHEMY_NATIVE_UNICODE"] = True
db = SQLAlchemy(app)


# 用户
class User(db.Model):
    __tablename__ = "user"
    # id
    id = db.Column(db.Integer, primary_key=True)
    # 姓名
    name = db.Column(db.String(100), unique=True)
    # 密码
    pwd = db.Column(db.String(100))
    # 邮箱
    email = db.Column(db.String(100), unique=True)
    # 电话
    phone = db.Column(db.String(11), unique=True)
    # 简介
    info = db.Column(db.Text)
    # 头像
    face = db.Column(db.String(255), unique=True)
    # 注册时间
    addtime = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    # 唯一标识符
    uuid = db.Column(db.String(255), unique=True)
    # 关联 外键 第一个是类名
    userlogs = db.relationship("Userlog", backref='user')  # 日志关联
    comments = db.relationship("Comment", backref='user')  # 评论关联
    moviecols = db.relationship("Moviecol", backref='user')  # 收藏关联

    def __str__(self):
        return "<User %r>" % self.name


# 用户日志
class Userlog(db.Model):
    __tablename__ = "userlog"
    # id
    id = db.Column(db.Integer, primary_key=True)
    # 所属用户
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"))
    # ip
    ip = db.Column(db.String(100))
    # 登陆时间
    addtime = db.Column(db.DateTime, index=True, default=datetime.utcnow)

    def __str__(self):
        return "<Userlog %r>" % self.id


# 标签
class Tag(db.Model):
    __tablename__ = "tag"
    # id
    id = db.Column(db.Integer, primary_key=True)
    # 名称
    name = db.Column(db.String(100), unique=True)
    # 添加时间
    addtime = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    # 外键
    movies = db.relationship("Movie", backref='tag')

    def __str__(self):
        return "<Tag %r>" % self.name


# 电影
class Movie(db.Model):
    __tablename__ = "movie"
    # id
    id = db.Column(db.Integer, primary_key=True)
    # 标题
    title = db.Column(db.String(255), unique=True)
    # 播放地址
    url = db.Column(db.String(255), unique=True)
    # 简介
    info = db.Column(db.Text)
    # 图标
    logo = db.Column(db.String(255), unique=True)
    # 级别
    star = db.Column(db.SmallInteger)
    # 播放量
    playnum = db.Column(db.BigInteger)
    # 评论量
    commentnum = db.Column(db.BigInteger)
    # 所属标签
    tag_id = db.Column(db.Integer, db.ForeignKey("tag.id"))
    # 上映地区
    area = db.Column(db.String(255))
    # 上映时间
    release_time = db.Column(db.Date)
    # 时长
    length = db.Column(db.String(100))
    # 添加时间
    addtime = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    comments = db.relationship("Comment", backref='movie')  # 评论关联
    moviecols = db.relationship("Moviecol", backref='movie')  # 收藏关联

    def __str__(self):
        return "<Movie %r>" % self.title


# 预告
class Preview(db.Model):
    __tablename__ = "preview"
    # id
    id = db.Column(db.Integer, primary_key=True)
    # 标题
    title = db.Column(db.String(255), unique=True)
    # 图标
    logo = db.Column(db.String(255), unique=True)
    # 添加时间
    addtime = db.Column(db.DateTime, index=True, default=datetime.utcnow)

    def __str__(self):
        return "<Preview %r>" % self.title


# 评论
class Comment(db.Model):
    __tablename__ = "comment"
    # id
    id = db.Column(db.Integer, primary_key=True)
    # 评论
    content = db.Column(db.Text)
    # 所属电影
    movie_id = db.Column(db.Integer, db.ForeignKey("movie.id"))
    # 所属用户
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"))
    # 添加时间
    addtime = db.Column(db.DateTime, index=True, default=datetime.utcnow)

    def __str__(self):
        return "<Comment %r>" % self.id


# 收藏电影
class Moviecol(db.Model):
    __tablename__ = "moviecol"
    # id
    id = db.Column(db.Integer, primary_key=True)
    # 所属电影
    movie_id = db.Column(db.Integer, db.ForeignKey("movie.id"))
    # 所属用户
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"))
    # 添加时间
    addtime = db.Column(db.DateTime, index=True, default=datetime.utcnow)

    def __str__(self):
        return "<Moviecol %r>" % self.id


# 权限
class Auth(db.Model):
    __tablename__ = "auth"
    # id
    id = db.Column(db.Integer, primary_key=True)
    # 名称
    name = db.Column(db.String(255), unique=True)
    # 地址
    url = db.Column(db.String(255), unique=True)
    # 添加时间
    addtime = db.Column(db.DateTime, index=True, default=datetime.utcnow)

    def __str__(self):
        return "<Auth %r>" % self.name


# 角色
class Role(db.Model):
    __tablename__ = "role"
    # id
    id = db.Column(db.Integer, primary_key=True)
    # 名称
    name = db.Column(db.String(255), unique=True)
    # 权限列表
    auths = db.Column(db.String(255))
    # 添加时间
    addtime = db.Column(db.DateTime, index=True, default=datetime.utcnow)

    def __str__(self):
        return "<Role %r>" % self.name


# 管理员
class Admin(db.Model):
    __tablename__ = "admin"
    # id
    id = db.Column(db.Integer, primary_key=True)
    # 管理员账号
    name = db.Column(db.String(100), unique=True)
    # 管理员密码
    pwd = db.Column(db.String(100))
    # 是否超级管理员 0是
    is_super= db.Column(db.SmallInteger)
    # 所属角色
    role_id = db.Column(db.Integer, db.ForeignKey("role.id"))
    # 添加时间
    addtime = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    # 外键
    adminlogs = db.relationship("Adminlog",backref="admin")
    oplogs = db.relationship("Oplog",backref="admin")

    def __str__(self):
        return "<Admin %r>" % self.name


# 登陆日志
class Adminlog(db.Model):
    __tablename__ = "adminlog"
    # id
    id = db.Column(db.Integer, primary_key=True)
    # 所属用户
    admin_id = db.Column(db.Integer, db.ForeignKey("admin.id"))
    # ip
    ip = db.Column(db.String(100))
    # 登陆时间
    addtime = db.Column(db.DateTime, index=True, default=datetime.utcnow)

    def __str__(self):
        return "<Adminlog %r>" % self.id

# 操作日志
class Oplog(db.Model):
    __tablename__ = "oplog"
    # id
    id = db.Column(db.Integer, primary_key=True)
    # 所属用户
    admin_id = db.Column(db.Integer, db.ForeignKey("admin.id"))
    # ip
    ip = db.Column(db.String(100))
    # 操作原因
    reson = db.Column(db.String(255))
    # 登陆时间
    addtime = db.Column(db.DateTime, index=True, default=datetime.utcnow)

    def __str__(self):
        return "<Oplog %r>" % self.id

if __name__ == '__main__':
    db.create_all()
    # role = Role(
    #     name=u"超级管理员",
    #     auths=""
    # )
    # db.session.add(role)
    # db.session.configure()

报错内容

 Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 480")
  cursor.execute(statement, parameters)

SAWarning: At least one scoped session is already present.  configure() can not affect sessions that have already been created.
  warn('At least one scoped session is already present. '

Answer 1

flask_sqlalchemy设置utf8(charset=utf8)

app.config["SQLALCHEMY_DATABASE_URI"] = "mysql://用户名:密码@localhost:3306/movies?charset=utf8"

创建数据库时,设置默认编码utf8

create database movie default character set utf8;