Flask – Using SQLite

Connect to DB

from flask import g
# g is global
import sqlite3

# Create function to connect to DB :

def connect_db():
    sql = sqlite3.connect('data.db')
    sql.row_factory = sqlite3.Row
    return sql

def get_db():
    if not hasattr(g, 'sqlite_db'):
        g.sqlite_db = connect_db()
    return g.sqlite_db

@app.teardown_appcontext
def close_db(error):
    if hasattr(g, 'sqlite_db'):
        g.sqlite_db.close()

# FETCH ALL DATA
db = get_db()
query = db.execute('SELECT * FROM users')
data = query.fetchall()

# FETCH ONE
db = get_db()
query = db.execute('SELECT * FROM users WHERE id = ?', [id])
data = query.fetchone()
print(data)

# INSERT DATA
db = get_db()
db.execute('INSERT INTO users(name, email, password) VALUES (?, ?, ?)', [name, email, password])
db.commit()

# UPDATE DATA
db = get_db()
db.execute('UPDATE users SET name = ? WHERE id = ?', [name, id])
db.commit()

# DELETE DATA
db = get_db()
db.execute('DELETE FROM users WHERE id = ?', [id])
db.commit()


Database Helper

app.py :

from flask import Flask, g
from database import get_db

app = Flask(__name__)

@app.teardown_appcontext
def close_db(error):
    if hasattr(g, 'sqlite_db'):
        g.sqlite_db.close()

...
db = get_db()
...

database.py :

from flask import g
import sqlite3

def connect_db():
    sql = sqlite3.connect('')
    sql.row_factory = sqlite3.Row
    return sql

def get_db():
    if not hasattr(g, 'sqlite_db'):
        g.sqlite_db = connect_db()
    return g.sqlite_db

Create table from schema.sql

schema.sql :

create table users (
    id integer primary key autoincrement,
    name text not null,
    email text not null,
    password text not null
);

Running :

sqlite3 db_name.db < schema.sql

Leave a Reply

Your email address will not be published. Required fields are marked *