Python uses the flask framework to operate sqlite3 in two ways: flasksqlite3

Python uses the flask framework to operate sqlite3 in two ways: flasksqlite3

This article describes two methods for using the flask framework to operate sqlite3 in Python. We will share this with you for your reference. The details are as follows:

Method 1: raw_ SQL

Import sqlite3from flask import Flask, request, jsonifyapp = Flask (_ name _) DATABASE_URI = ": memory:" # create a table and insert data @ app. before_first_requestdef create_db (): # connect to conn = sqlite3.connect (DATABASE_URI) c = conn. cursor () # CREATE a TABLE c.exe cute ('''drop table if exists user ''') c.exe cute (''' create table user (id integer primary key autoincrement, name TEXT, email TEXT) ''') # data # format: user name, email purchases = [('admin ',' Admin@example.com '), ('guest1', 'guest1 @ example.com'), ('guest2', 'guest2 @ example.com '), ('guest3', 'guest3 @ example.com '), ('guest4', 'guest4 @ example.com ')] # INSERT data c.exe cute.pdf ('insert INTO user (name, email) VALUES (?,?) ', Purchases) # submit !!! Conn. commit () # disable 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.exe cute (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,) # You can set it to: return only 6 data for the first time. return jsonify (id = res [1], name = res [2], email = res [3]) # Return the json format of if _ name _ = "_ main _": app. run (debug = True)

Method 2: 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) # define 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 # create a table and insert data @ app. before_first_requestdef 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 () # query @ app. route ('/user') def users (): users = user. query. all () return "<br> ". join (["{0 }:{ 1 }". format (user. name, user. email) for user in users]) # query @ app. route ('/user/<int: id>') def user (id): user = User. query. filter_by (id = id ). one () return "{0 }:{ 1 }". format (user. name, user. email) # Run if _ name _ = '_ main _': app. run ('2017. 0.0.1 ', 5000)