Hello in this tutorial, we will explain the SQLite implementation in Python flask.
1. Introduction
SQLite is a software library that provides a relational database management system. It is lightweight in terms of setup, database administration, and required resources. It is self-contained, serverless, zero-configuration, transactional.
- Self-contained means that it require minimal support from the operating system or any external library
- Zero-configuration means that no external installation is required before using it
- Transactional means it is fully ACID-compliant i.e. all queries and changes are atomic, consistent, isolated, and durable
1.1 Setting up Python
If someone needs to go through the Python installation on Windows, please watch this link. You can download the Python from this link.
2. Python SQLite Tutorial
I am using JetBrains PyCharm as my preferred IDE. You’re free to choose the IDE of your choice. Fig. 1 represents the project structure for this tutorial.
The file named – songs.db will be generated dynamically during the application run.
2.1 Creating a requirements file
Add the below code to the requirements file. The file will be responsible to download and install the packages required for this tutorial.
requirements.txt
Faker==10.0.0 Flask==1.1.4
2.2 Creating the database config
Create the database configuration file. The file will be responsible for handling the SQLite database connection and interacting with the songs database.
db.py
import sqlite3
from sqlite3 import Error
DATABASE_NAME = "songs.db"
def get_db():
""" create a database connection to a SQLite database """
try:
conn = sqlite3.connect(DATABASE_NAME)
return conn
except Error as e:
print(e)
def create_table():
tables = [
"""
CREATE TABLE IF NOT EXISTS songs(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, singer TEXT NOT NULL)
"""
]
db = get_db()
cursor = db.cursor()
for table in tables:
cursor.execute(table)
2.3 Creating the controller class
Create the controller class responsible for interacting with the database. The controller class methods are responsible for performing the SQL CRUD operations.
controller.py
from db import get_db
def get_row_count():
return len(get_all())
def insert_song(name, singer):
db = get_db()
cursor = db.cursor()
sql = "INSERT INTO songs(name, singer) VALUES (?, ?)"
cursor.execute(sql, [name, singer])
db.commit()
return True
def get_by_id(key):
db = get_db()
cursor = db.cursor()
sql = "SELECT id, name, singer FROM songs WHERE id = ?"
cursor.execute(sql, [key])
return cursor.fetchone()
def get_all():
db = get_db()
cursor = db.cursor()
sql = "SELECT id, name, singer FROM songs"
cursor.execute(sql)
return cursor.fetchall()
def delete_by_id(key):
db = get_db()
cursor = db.cursor()
item = get_by_id(key)
if item is None:
return False
sql = "DELETE FROM songs WHERE id = ?"
cursor.execute(sql, [key])
db.commit()
return True
def update_by_id(key, name, singer):
db = get_db()
cursor = db.cursor()
item = get_by_id(key)
if item is None:
return False
sql = "UPDATE songs SET name = ?, singer = ? WHERE id = ?"
cursor.execute(sql, [name, singer, key])
db.commit()
return True
2.4 Creating the application class
Create the main class responsible for handling the incoming requests from the client and interact with the database to show the results.
main.py
from faker import Faker
from flask import Flask, jsonify, request
import controller
from db import create_table
app = Flask(__name__)
faker = Faker()
RESOURCE_NOT_FOUND = "RESOURCE_NOT_FOUND"
# http get endpoint= http://localhost:8000/song/all
@app.route("/song/all", methods=["GET"])
def get_all():
songs = controller.get_all()
items = []
for song in songs:
items.append({"id": song[0], "name": song[1], "singer": song[2]})
return jsonify({"songs": items})
# http get endpoint= http://localhost:8000/song/1
@app.route("/song/", methods=["GET"])
def get_by_id(key):
song = controller.get_by_id(key)
if song is None:
return jsonify({"msg": RESOURCE_NOT_FOUND})
return jsonify({"id": song[0], "name": song[1], "singer": song[2]})
# http delete endpoint= http://localhost:8000/song/1
@app.route("/song/", methods=["DELETE"])
def delete_by_id(key):
result = controller.delete_by_id(key)
if not result:
return jsonify({"msg": RESOURCE_NOT_FOUND})
return jsonify(result)
# http put endpoint= http://localhost:8000/song/1
@app.route("/song/", methods=["PUT"])
def update_by_id(key):
details = request.get_json()
name = details["name"]
singer = details["singer"]
result = controller.update_by_id(key, name, singer)
if not result:
return jsonify({"msg": RESOURCE_NOT_FOUND})
return jsonify(result)
if __name__ == '__main__':
create_table()
print("Table created")
if controller.get_row_count() == 0:
for x in range(1, 6):
controller.insert_song(faker.word(), faker.name())
print("Sample records inserted")
else:
print("Skipping insert")
app.run(host="localhost", port=8000, debug=False)
3. Run the application
Run the main.py python script once the code is completed and if everything goes well the application will be started on the port number – 8000 as shown in the below logs.
Application logs
Table created Skipping insert * Serving Flask app "main" (lazy loading) * Environment: production WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead. * Debug mode: off * Running on http://localhost:8000/ (Press CTRL+C to quit)
4. Demo
To play around with the application endpoints open up the postman tool and hit the endpoints.
Application endpoints
-- get all songs -- http get method http://localhost:8000/song/all -- get a song by id -- http get method http://localhost:8000/song/1 -- delete song by id -- http delete method http://localhost:8000/song/1 -- update song by id -- http put method http://localhost:8000/song/1
That is all for this tutorial and I hope the article served you with whatever you were looking for. Happy Learning and do not forget to share!
5. Summary
In this tutorial, we learned about SQLite implementation in a python application. You can download the source code of this tutorial from the Downloads section.
6. Download the Project
This was a tutorial on how to implement CRUD operations using SQLite in Python applications.
You can download the full source code of this example here: Python SQLite Tutorial
