Quartz 5

08 SQLite details

4 min read

SQLite details

SQLite details

Create or Open Database
  • If no path included: SQlite file is assumed to be in the same directory as the Python file
  • If path does not exist: empty file created with the given filename
  • close() method of the connection object ⇒ closes file properly, but does not save any modifications to data
  • Using INSERT, UPDATE, or DELETE implicitly opens a transaction
  • MUST call .commit() to save changes

E.g.

import sqlite3
connection = sqlite3.connect("library.db") # create a Connection object
connection.execute(“CREATE TABLE Book ” +
“(ID INTEGER PRIMARY KEY, Title TEXT)“)
connection.execute(“INSERT INTO Book(ID, Title) ” +
“VALUES(0, ‘Example Book’)“)
connection.commit()
connection.close()

.rollback() discards/undoes all changes made since the transaction was opened / since the last call to commit()

E.g. 1st and 2nd statements rolled back ⇒ no effect on database;
3rd statement committed ⇒ has effect on database

import sqlite3
connection = sqlite3.connect("library.db")
connection.execute(“INSERT INTO Book(ID, Title) ” +
“VALUES(1, ‘Rollback Book’)“)
connection.execute(“INSERT INTO Book(ID, Title) ” +
“VALUES(2, ‘Also Rollback Book’)“)
connection.rollback()
connection.execute(“INSERT INTO Book(ID, Title) ” +
“VALUES(3, ‘Committed Book’)“)
connection.commit()
connection.close()

Parameter substitution to safely include data provided by user

  • (X): connection.execute(“DELETE FROM Book WHERE ID = ” + book_id)
  • Prevents SQL injection: user enters “1 OR 1”
  • Use “?” as a placeholder for data provided by user
  • Then provide a 2nd argument to execute() that is a tuple of values that will replace the placeholders ⇒ Ensures the provided values are escaped properly and cannot be misrepresented as SQL
  • Follows same order in which placeholders appear in the SQL

E.g. to safely ask for ID of book and delete the corresponding row from the database

import sqlite3
connection = sqlite3.connect("library.db")
# Insert some rows first so we have something to delete
connection.execute(“INSERT INTO Book(ID, Title) ” +
“VALUES(4, ‘Extra Book’)“)
connection.execute(“INSERT INTO Book(ID, Title) ” +
“VALUES(5, ‘Also Extra Book’)“)
connection.commit()
# Ask for ID and delete the corresponding row
book_id = input("Enter Book ID to delete: ")
connection.execute(“DELETE FROM Book WHERE ID = ?”, (book_id,))
connection.commit()
connection.close()
SELECT command in SQL → In Python, must access selected rows using a cursor object that is returned by the execute() method
  • Cursor object goes through selected rows, 1 by 1, using either a for-in loop or the fetchone() method, each iteration returns a tuple of columns in the current row
for-in loopfetchone() method
import sqlite3
connection = sqlite3.connect(“library.db”)
cursor = connection.execute(“SELECT ID, Title FROM Book”)
for row in cursor:
# Title is 2nd item in tuple
print(row[1])		row = cursor.fetchone()
while row is not None:
print(row[1])
row = cursor.fetchone()
connection.close()
  • Each call to fetchone() advances the cursor to the next row
  • Calling fetchone() repeatedly will iterate through the selected rows until the cursor reaches the end and returns None
  • Returns a tuple of values from the next row of the query result or None if there are no more values
  • fetchall() method fetches all rows at once and keeps them in a list of tuples, with each tuple containing the selected column for each row
  • Calls fetchone() repeatedly until it returns None, and returns a list of the non-None results
fetchall() method
import sqlite3
connection = sqlite3.connect(“library.db”)
cursor = connection.execute(“SELECT ID, Title FROM Book”)
rows = cursor.fetchall()
for row in rows:
print(row[1])
connection.close()
  • Retrieve each row as a dict mapping column names to field values
  • Set the connection object’s row_factory attribute to the built-in sqlite3.Row class
  • ⇒ Can change ordering of columns in SELECT statement without having to modify the code for extracting individual column values
.row_factory method
import sqlite3
connection = sqlite3.connect(“library.db”)
connection.row_factory = sqlite3.Row
cursor = connection.execute(“SELECT ID, Title FROM Book”)
for row in cursor:
print(row[“Title”]) # row is now a dictionary
connection.close()

E.g. let user insert data into Book table

import sqlite3
connection = sqlite3.connect(“library.db”)
while True:
try:
book_id = int(input(“Enter Book ID: ”))
except ValueError:
print(“Not a valid ID”)
title = input(“Enter Title: ”)
try:
connection.execute(“INSERT INTO Book(ID, TItle) ” + “VALUES(?, ?)”, (book_id, title))
connection.commit()
except sqlite3.DatabaseError:
print(“Database error (e.g. duplicate ID)”)
continue
print(“Insertion successful!”)
if input(“Quit (Y/N)? ”).upper() == “Y”:
break
connection.close()

Q:
A:
(note method to check if an input does not exist)
import sqlite3
while True:
connection = sqlite3.connect(“loans.db”)
# get Borrower ID
borrower_id = int(input(“Enter Borrower ID: ”))
cursor = connection.execute(“SELECT COUNT(*) FROM Borrower ” + “WHERE ID = ?”, (borrower_id,))
if cursor.fetchone()[0] == 0:
name = input(“Enter Borrower Name: ”)
connection.execute(“INSERT INTO Borrower(ID, Name) ” + “Values (?, ?)”, (borrower_id, name))
# get Book ID
book_id = int(input(“Enter Book ID: ”))
cursor = connection.execute(“SELECT COUNT(*) FROM Book ” + “WHERE ID = ?”, (book_id,))
if cursor.fetchone()[0] == 0:
title = input(“Enter Book Title: ”)
connection.execute(“INSERT INTO Book(ID, Title) ” + “Values(?,?)”, (book_id, title))
# insert loan
cursor = connection.execute(“SELECT COUNT(*) FROM Loan ” + “WHERE BookID = ?”, (book_id,))
if cursor.fetchone()[0] == 0:
connection.execute(“INSERT INTO LOAN(BorrowerID, ” + “BookID) VALUES (?, ?)”, (borrower_id, book_id))
connection.commit()
else:
print(“Error: book is already on loan”)<br>connection.rollback()
connection.close()
if input(“Quit (Y/N)? ”).upper() == “Y”:
break
Note: no space between COUNT & (*) !!

… (pg 14)

Graph View

Backlinks