python - web框架 - Flask sqlalchemy Mysql

使用 Flask 的 flask-sqlalchemy 连接数据库

参考网址：

http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#unicode

https://pythonhosted.org/Flask-SQLAlchemy/quickstart.html

条件：

需要安装 sqlalchemy

$ pip install flask-sqlalchemy 

接下来代码

flask_exe_009_mysql.py

这个文件中的代码，主要作用是创建了一个数据库表的模型

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy import Table, Column, Integer, String, Date, Float
import config  

# DB class
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] =  config.DB_URI
db = SQLAlchemy(app)
 
# DB classess
class Player(db.Model):
    __tablename__ = 'player'
 
    playerid = db.Column('playerid', Integer, primary_key=True)
    username = db.Column('username', String(30), unique=True)
    email = db.Column('email', String(50), unique=True)
    password = db.Column('password', String(100), unique=False)
    avatarid = db.Column('avatarid', Integer,default=1)
    language = db.Column('language', Integer,default=1)
    regdate = db.Column('regdate', Date)
    activation_key = db.Column('activation_key', String(60))
    active = db.Column('active', Integer, default=0)
 
    def __init__(self, username=None, email=None,password=None):
        self.username = username
        self.email = email
        self.password = password
 
    def __repr__(self):
        return '<Player %s %s>' % (self.username, self.email)

if __name__ == '__main__':
    #create table from up model
    db.create_all()

其中

import config

config 对应的内容：

'''
Config imported by flask_exe_009_mysql.py 
'''
dbhost = '122.00.100.12:3388'
dbuser = 'test'
dbpass = 'test'
dbname = 'test'

DB_URI = 'mysql://' + dbuser + ':' + dbpass + '@' + dbhost + '/' +dbname

通过以下代码创建数据库表

if __name__ == '__main__':
    #create table from up model
    db.create_all()

页面视图

flask_exe_009_mysql_run.py

# Filename: mp.py
 
import datetime
import hashlib
import string,random 
 
# config file
import config
 
from flask import Flask, jsonify, request
 
## app and db is defined on flask_exe_009_mysql.py
from flask_exe_009_mysql import db, app, Player
 
# other misc functions
def get_user_id(username):
    p  = Player.query.filter_by(username=username).first()
    if p  is not None:
       return p
    return None
 
def get_userid_by_email(email):
    p  = Player.query.filter_by(email=email).first()
    if p  is not None:
       return p
    return None
 
def get_user_key(key):
    p  = Player.query.filter_by(activation_key=key).first()
    if p  is not None:
       return p
    return None
 
 
# url  routing
@app.errorhandler(404)
def page_not_found(error):
    return 'This page does not exist', 404
 
@app.after_request
def shutdown_session(response):
    db.session.remove()
    return response
 
@app.route('/')
def hello_world():
    return "Hello World!"
 
@app.route('/users/')
def list_players():
    players = Player.query.all()
    #app.logger.debug("Player length %s ", len(players))
    #a = ""
    d = {}
    for p in players:
       app.logger.debug(p.username)
       #a = a + " " + p.username
       a = p.username
    return "Hello Players: %s "  % (a)
 
  
if __name__ == '__main__':
    app.debug = True
    #app.run(host='0.0.0.0')
    app.run()

运行后：

 * Running on http://127.0.0.1:5000/
 * Restarting with reloader

访问URL

http://127.0.0.1:5000/users/ 

显示数据，前提是player表中有数据