import sqlite3
from flask import Flask, request, jsonify

app = Flask(__name__)

DATABASE_URI = ":memory:"


# 创建表格、插入数据
@app.before_first_request
def create_db():
    # 连接
    conn = sqlite3.connect(DATABASE_URI)
    c = conn.cursor()

    # 创建表
    c.execute('''DROP TABLE IF EXISTS user''')
    c.execute('''CREATE TABLE user (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT)''')

    # 数据
    # 格式：用户名,邮箱
    purchases = [('admin', 'admin@example.com'),
                 ('guest1', 'guest1@example.com'),
                 ('guest2', 'guest2@example.com'),
                 ('guest3', 'guest3@example.com'),
                 ('guest4', 'guest4@example.com')]

    # 插入数据
    c.executemany('INSERT INTO user(name, email) VALUES (?,?)', purchases)

    # 提交！！！
    conn.commit()

    # 关闭
    conn.close()



def get_db():
    db = sqlite3.connect(DATABASE_URI)
    db.row_factory = sqlite3.Row
    return db


def query_db(query, args=(), one=False):
    db = get_db()
    cur = db.execute(query, args)
    db.commit()
    rv = cur.fetchall()
    db.close()
    return (rv[0] if rv else None) if one else rv


@app.route("/user")
def users():
    res = query_db("SELECT * FROM user WHERE id <= ?", args=(6,))
    return "<br>".join(["{0}: {1}".format(user[1], user[2]) for user in res])


@app.route("/user/<int:id>")
def user(name):
    res = query_db("SELECT * FROM user WHERE id=?", args=(id,)) #不妨设定：第一次只返回6个数据
    return jsonify(id = res[1],
                   name = res[2], 
                   email = res[3]) # 返回json格式


if __name__ == "__main__":
    app.run(debug=True)

# flask_sqlalchemy.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite://'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)


# 定义ORM
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

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

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

# 创建表格、插入数据
@app.before_first_request
def create_db():
    # Recreate database each time for demo
    #db.drop_all()
    db.create_all()
    
    admin = User('admin', 'admin@example.com')
    db.session.add(admin)

    guestes = [User('guest1', 'guest1@example.com'),
               User('guest2', 'guest2@example.com'),
               User('guest3', 'guest3@example.com'),
               User('guest4', 'guest4@example.com')]
    db.session.add_all(guestes)
    db.session.commit()
    
    
# 查询
@app.route('/user')
def users():
    users = User.query.all()
    return "<br>".join(["{0}: {1}".format(user.name, user.email) for user in users])
    
# 查询
@app.route('/user/<int:id>')
def user(id):
    user = User.query.filter_by(id=id).one()
    return "{0}: {1}".format(user.name, user.email)
    
# 运行
if __name__ == '__main__':
    app.run('127.0.0.1', 5000)