python3 原生操作sqlite3 代码实例demo

基于flask项目演示

sqlite3 数据库文件为market.db，放在static 目录下，app.py源码如下:

import os
import sqlite3
from flask import g
from flask import Flask, render_template, jsonify, request

app = Flask(__name__)

DATABASE = os.path.join(os.path.abspath('static'), 'market.db')


def connect_db():
    return sqlite3.connect(DATABASE)


@app.before_request
def before_request():
    g.db = connect_db()


@app.teardown_request
def teardown_request(exception):
    if hasattr(g, 'db'):
        g.db.close()


def query_db(query, args=(), one=False):
    cur = g.db.execute(query, args)
    rv = [dict((cur.description[idx][0], value)
               for idx, value in enumerate(row)) for row in cur.fetchall()]
    return (rv[0] if rv else None) if one else rv


@app.route('/query_br')
def query_br():
    name = request.args.get('name')
    if name is not None and len(name)>0:
     user = query_db('select * from brands where name = ?', [name])
    else:
        user = query_db('select * from brands  ')
    return jsonify(user)

@app.route('/query_in')
def query_in():
    name = request.args.get('name')
    if name is not None and len(name)>0:
     user = query_db('select * from inventory where name = ?', [name])
    else:
        user = query_db('select * from inventory  ')
    return jsonify(user)

@app.route('/query_me')
def query_me():
    name = request.args.get('name')
    if name is not None and len(name)>0:
     user = query_db('select * from membership where name = ?', [name])
    else:
        user = query_db('select * from membership  ')
    return jsonify(user)

@app.route('/query_pr')
def query_pr():
    name = request.args.get('name')
    if name is not None and len(name)>0:
     user = query_db('select * from procurement where product_id = ?', [name])
    else:
        user = query_db('select * from procurement  ')
    return jsonify(user)

@app.route('/query_sa')
def query_sa():
    name = request.args.get('name')
    if name is not None and len(name)>0:
     user = query_db('select * from sales where product_id = ?', [name])
    else:
        user = query_db('select * from sales  ')
    return jsonify(user)

@app.route('/query_st')
def query_st():
    name = request.args.get('name')
    if name is not None and len(name)>0:
     user = query_db('select * from staffs where name = ?', [name])
    else:
        user = query_db('select * from staffs  ')
    return jsonify(user)

@app.route('/')
def index():
    return render_template("index.html", )


@app.route('/brands')
def brands():
    return render_template("brands.html", )

@app.route('/inventory')
def inventory():
    return render_template("inventory.html", )

@app.route('/membership')
def membership():
    return render_template("membership.html", )

@app.route('/procurement')
def procurement():
    return render_template("procurement.html", )

@app.route('/sales')
def sales():
    return render_template("sales.html", )

@app.route('/staffs')
def staffs():
    return render_template("staffs.html", )
# brands
# inventory
# membership
# procurement
# sales
# staffs

if __name__ == '__main__':
    app.run()