Python使用flask框架操作sqlite3的两种方式

Python  /  管理员 发布于 7年前   355

本文实例讲述了Python使用flask框架操作sqlite3的两种方式。分享给大家供大家参考，具体如下：

方式一：raw_sql

import sqlite3from flask import Flask, request, jsonifyapp = Flask(__name__)DATABASE_URI = ":memory:"# 创建表格、插入数据@app.before_first_requestdef 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', '[email protected]'),         ('guest1', '[email protected]'),         ('guest2', '[email protected]'),         ('guest3', '[email protected]'),         ('guest4', '[email protected]')]  # 插入数据  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 dbdef 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 [email protected]("/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)

方式二：orm（既flask-SQLAlchemy）

# flask_sqlalchemy.pyfrom flask import Flaskfrom flask_sqlalchemy import SQLAlchemyapp = Flask(__name__)app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite://'app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = Truedb = SQLAlchemy(app)# 定义ORMclass 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_requestdef create_db():  # Recreate database each time for demo  #db.drop_all()  db.create_all()  admin = User('admin', '[email protected]')  db.session.add(admin)  guestes = [User('guest1', '[email protected]'),        User('guest2', '[email protected]'),        User('guest3', '[email protected]'),        User('guest4', '[email protected]')]  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)

更多关于Python相关内容感兴趣的读者可查看本站专题：《Python操作SQLite数据库技巧总结》、《Python常见数据库操作技巧汇总》、《Python数据结构与算法教程》、《Python函数使用技巧总结》、《Python字符串操作技巧汇总》、《Python入门与进阶经典教程》及《Python文件与目录操作技巧汇总》

希望本文所述对大家Python程序设计有所帮助。