I added the remaining CRUD statements in Part 2. In this post, I’ll DRY up the code used for executing and committing the SQL statements.
Create a new function that accepts the database path and the SQL statement. Create a single unit of work by pulling in the connection and cursor creation and closure, statement execution, and results. As this evolves into an app or a library, I would rather open the connection, do some work, and close the connection rather than worry about leaving a connection open by accident.
def query_db(db_path, sql):
# Open and connection and create a cursor
conn = sqlite3.connect(db_path)
cur = conn.cursor()
# Execute the query and fetch the results
cur = conn.execute(sql)
conn.commit() # If there are no open transactions, it is a no-op
res = cur.fetchall() # If there are no records, returns an empty list []
# Close the cursor and connection
cur.close()
conn.close()
return res
Not all of the SQL statements executed from Python need the conn.commit() code. Per the documentation[1], the commit() method is a “no-op” and does nothing when there are no open transactions. Additionally, the documentation[2][3] indicates that fectchone() returns None[2] and fetchall() returns an empty list [][3] if there are no records to fetch. Use fetchall() to cover all bases for now. In the future, I may add some conditional statements to change what is returned from the function. For now, it is clean, and that is good for me.
Now execute the single function after each SQL statement and print the result if desired.
# Read a record by id
sql = "SELECT ROWID, name, comments FROM institutions WHERE ROWID = 1"
res = query_db(db_path, sql)
print(res)
Here is the module.
institution_03.py
import sqlite3
import sys
from pathlib import Path
def query_db(db_path, sql):
# Open and connection and create a cursor
conn = sqlite3.connect(db_path)
cur = conn.cursor()
# Execute the query and fetch the results
cur = conn.execute(sql)
conn.commit() # If there are no open transactions, it is a no-op
res = cur.fetchall() # If there are no records, returns an empty list []
# Close the cursor and connection
cur.close()
conn.close()
return res
def main():
db_path = Path(".") / "home.db"
# Create the table
sql = "CREATE TABLE IF NOT EXISTS institutions (name, comments)"
res = query_db(db_path, sql)
# Create one record
sql = """INSERT INTO institutions (name, comments)
VALUES ('ABC Bank', 'A regional bank')"""
res = query_db(db_path, sql)
# Create many records
sql = """INSERT INTO institutions (name, comments)
VALUES ('Chase', 'International'), ('Discover', 'National')"""
res = query_db(db_path, sql)
# Read a record by id
sql = "SELECT ROWID, name, comments FROM institutions WHERE ROWID = 1"
res = query_db(db_path, sql)
print(res)
# Read all records
sql = "SELECT ROWID, name, comments FROM institutions"
res = query_db(db_path, sql)
print(res)
# Update a record by id
sql = "UPDATE institutions SET name = 'My Bank' WHERE ROWID = 1"
res = query_db(db_path, sql)
# Update all records
sql = "UPDATE institutions SET comments = 'Comments are stupid'"
res = query_db(db_path, sql)
# Delete one record from the table by id
sql = "DELETE FROM institutions WHERE ROWID = 1"
res = query_db(db_path, sql)
# Delete all records from the table
sql = "DELETE FROM institutions"
res = query_db(db_path, sql)
# Delete the table
sql = "DROP TABLE IF EXISTS institutions"
res = query_db(db_path, sql)
if __name__ == "__main__":
sys.exit(main())
My journey with Python and SQLite
References:
- sqlite3 — DB-API 2.0 interface for SQLite databases, Python.org Documentation
- sqlite3 — DB-API 2.0 interface for SQLite databases | Cursor.fetchone, Python.org Documentation
- sqlite3 — DB-API 2.0 interface for SQLite databases | Cursor.fetchall, Python.org Documentation
