使用 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
显示数据,前提是player表中有数据