Quartz 5

07 SQL & SQLite Summary

5 min read

SQL & SQLite Summary

SQL & SQLite Summary

SQL StatementSQLite in Python
Create or Open DatabaseNAimport sqlite3
connection = sqlite3.connect(<filename.db>)
connection.close()
Create TableCREATE TABLE table_name(
column1_name COLUMN1_TYPE COLUMN1_CONSTRAINTS,
column2_name COLUMN2_TYPE COLUMN2_CONSTRAINTS,
# E.g. Date INTEGER,
…
# MUST BE AFTER EVERYTH. Definite column for these too on top.
PRIMARY KEY (column1_name, column2_name, …),
FOREIGN KEY (column_name)** REFERENCES table_name(column_name)
);
*Column constraints: NOT NULL, PRIMARY KEY
AUTOINCREMENT (only applies to primary key)
INTEGER, TEXT, NUMERIC, BLOB		connection.execute(<SQL statement>)
connection.commit()
*take effect immediately, CANNOT rollback
Drop TableDROP TABLE [IF EXISTS] table_name;
Insert (New) DataINSERT INTO table_name(column1_name, column2_name, ...)
VALUES(column1_value, column2_value, …);
*Must insert data for columns with constraint ‘NOT NULL’		connection.execute(<SQL statement / query>)
E.g. connection.execute(“INSERT INTO Book(ID, TItle) ” + “VALUES(?, ?)”, (book_id, title))
connection.rollback()
connection.commit()
*execute() opens a transaction such that modifications to the data are not saved until the commit() method is called
Update (Existing) DataUPDATE table_name SET
column1_name = column1_expression,
column2_name = column2_expression,
…
WHERE where_expression;
*|| for string concatenation		WHERE column1_name > ? AND column2_name < ?, (column1_value, column2_value)
*Enclosing User Input in SQL Safely
FOREIGN KEY statement should be last line of query
query = ‘’’
‘’’
Delete DataDELETE FROM table_name
WHERE where_expression;
*whole row of data is deleted, but table is NOT deleted from database
Retrieve Data from ONE tableSELECT column1_name, column2_name, ...
FROM table_name
WHERE where_expression
ORDER BY order_expression ASC/DESC;
*Default in ascending order, even without ASC;
*Logical operators: AND, OR, NOT, IS, IS NOT
*Comparison operators: =, !=, <>, <=, >=
*Arithmetic operators: +, -, *, /, %		for loop:
cursor = connection.execute(<SQL statement>)
for row in cursor:
# row is a tuple of the column values
fetchone:
cursor = connection.execute(<SQL statement>)
row = cursor.fetchone()
while row is not None:
# row is a tuple of the column values
row = cursor.fetchone()
Calculation on Data from ONE table
(count/max/min/sum)		SELECT
COUNT(*)/MAX(column1_name)/MIN(column2_name)/SUM(column3_name),
…
FROM table_name;
fetchall:
cursor = connection.execute(<SQL statement>)
rows = cursor.fetchall()
for row in rows:
# row is a tuple of the column values
Retrieve Data from TWO tables*Match table1 column3 with table2 column4
*Join method depends on cases without data in table1 column3
Inner Join (exclude such cases)
SELECT table1_name.column1_name, table2_name.column2_name, ...
FROM table1_name, table2_name
WHERE table1_name.column3_name = table2_name.column4_name;
SELECT table1_name.column1_name, table2_name.column2_name, ...
FROM table1_name
INNER JOIN table2_name
ON table1\_name.column3\_name = table2\_name.column4\_name**;
Left Outer Join (include such cases)
SELECT table1_name.column1_name, table2_name.column2_name, ...
FROM table1_name
LEFT OUTER JOIN table2_name
ON table1\_name.column3\_name = table2\_name.column4\_name**;		row factory:
connection.row_factory = sqlite3.Row
cursor = connection.execute(<SQL statement>)
for row in cursor:
# row is a dictionary with key of column
# name and value of column value
cursor.close()
db.close()
*NA for join tables

SQL template

  • Note: No need UNIQUE or NULL; only if qn asks then need AUTOINCREMENT
.sql
# IMPTTTTTTTTT (present ans like this)
Create database ServiceLog.db
CREATE TABLE "Log" (
”LogID” INTEGER,
“Sender” TEXT,
“AccessDate” TEXT,
“Status” INTEGER,
“AppType” TEXT,
PRIMARY KEY(“LogID” AUTOINCREMENT)
);
in sqlite3: PRIMARY KEY AUTOINCREMENT must come together in first line

SQLite template

# Create database if not exist
import sqlite3
connection = sqlite3.connect('TRIP.db')
# For debugging
connection.execute(“DROP TABLE IF EXISTS Flight”)
sql1 = '''
CREATE TABLE Flight(
FlightNo INTEGER,
Date TEXT,
UnitPrice REAL,
Phone TEXT
…,
PRIMARY KEY(FlightNo),
FOREIGN KEY (CustomerNo) REFERENCES Customer(CustomerNo)
);
'''
connection.execute(sql1)
…
connection.commit()
connection.close()
connection = sqlite3.connect("TRIP.db")
f1 = open("FLIGHT.txt", "r")
for line in f1:
line = line.strip()
FlightNo, DepartCity, ArrivalCity, DepartTime, ArrivalTime = line.split(’,‘)
sql1 = '''
INSERT INTO Flight(FlightNo, DepartCity, ArrivalCity, DepartTime, ArrivalTime)
VALUES (?,?,?,?,?);
'''
connection.execute(sql1, (FlightNo, DepartCity, ArrivalCity, DepartTime, ArrivalTime))
f1.close()
…
# AT THE END
connection.commit()
connection.close()
connection.row_factory = sqlite3.Row
cursor = connection.execute(sql2)
print(f"FlightNo\tDepartCity\tArrivalCity\t\tDepartTime\tArrivalTime")
for row in cursor:
print(f"{row[0]:<16}{row[1]:<16}{row[2]:<24}{row[3]:<16}{row[4]}")

SQL+Webapp template

  • Commit and close database before returning rendered template
  • LEARN RADIO
.py
import flask, sqlite3, os
from flask import render_template, request, url_for
app = flask.Flask(__name__)
@app.route('/')
def home():
connection = sqlite3.connect(r'C:\Users\Andrea\H2 Computing\Past Year Papers\2025 HCI Prelims\Salon2.db')
… (sql stuff)
cursor = connection.execute(...)
results = cursor.fetchall()
return render_template('task4_4.html', results=results)
OR
results = []
for row in cursor:
results.append(row)
connection.close()
return render_template('infor.html', results=results, date=date)
if __name__ == "__main__":
app.run(debug=True)
@app.route('/', methods = [‘GET’, 'POST'])
def index():
if request.method == 'GET':
return render_template('form.html')
else:
if 'date' in request.form:
…
@app.route('/form')
def form():
return render_template('form.html')
@app.route('/infor', methods=['POST'])
def index():
if 'date' in request.form:
date = request.form['date']
… (sql stuff)
results = []
for row in cursor:
results.append(row)
connection.close()
return render_template('infor.html', results=results, date=date)
# RMB!
else:
return "No form data found!"
<!— home.html to display the result —>
<!DOCTYPE html>
<html>
<head>
<title>Stylists’ sales record</title>
</head>
<body>
<!— don’t use <p> use h1 —>
<p>Stylists’ sales record</p>
<!— # ans key: <h1>Stylist Revenue</h1> —>
<table border>
{% if results | length > 0 %}
<tr><th>Name</th><th>Completed appointments</th><th>Total revenue</th></tr>
{% for item in results %}
<tr>
<td>{{ item[0] }}</td><td>{{ item[1] }}</td><td>{{ $‘%0.2f’% item[2] }}</td>
</tr>
{% endfor %}
{% else %}
<tr><td colspan = “2”>No logs<td></td></tr>
{% endif %}
</table>
</body>
</html>
<!— form.html to display the form —>
<!DOCTYPE html>
<html>
<head>
<title>Form</title>
</head>
<body>
<form method=“post” action=”{{ url_for(‘index’) }}”>
<p>Input the date in the form of DDMMYYYY: <input name=“date”></p>
<h3><i>For example, enter 01072023 for 2023 July 1</i></h3>
<p><input type=“submit”></p>
</form>
</body>
</html>
<!— infor.html to display the result —>
<!DOCTYPE html>
<html>
<head>
<title>Ticket Information</title>
</head>
<body>
<!— # USE h1 NOT p —>
<h1>Ticket Information for {{date}}</h1>
<!— # INCLUDE —>
<table border=‘1px black’>
<tr>
<th>Name</th>
<th>Depart City</th>
<th>Arrival City</th>
<th>Seat</th>
</tr>
{% if results | length > 0 %}
{% for item in results %}
<tr>
<td>{{ item[0] }}</td>
<td>{{ item[1] }}</td>
<td>{{ item[2] }}</td>
<td>{{ item[3] }}</td>
</tr>
{% endfor %}
{% endif %}
</table>
</body>
</html>

Menu

<!DOCTYPE html>
<html>
<head>
<title>Menu</title>
<link rel=“stylesheet” type=“text/css”
href="{{ url_for('static', filename='styles.css') }}">
</head>
<body>
<p>Menu</p>
<p><a href=”{{ url_for(‘task4_2’) }}“>Student health records</a></p>
<p><a href=”{{ url_for(‘task4_3’) }}“>Health record statistics</a></p>
</body>
</html>
# for each of the other .html before the end of body:
<p><a href=”{{ url_for(‘task4_1’) }}“>Back to Menu</a></p>

Other details

{% if item[3] is none %} # note small letter n
<td>NULL</td>
{% else %}
<td>{{ item[3] }}</td>
{% endif %}
# for $.2dp (NOTE placement of ‘’)
${{‘%0.2f**’**% item[2] }}

Graph View

Backlinks