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

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

方式一：raw_sql

									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)

方式二：orm（既flask-SQLAlchemy）

									# 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)

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

原文链接：http://www.cnblogs.com/hhh5460/p/6557350.html

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

相关文章

热门资讯