a way to be - My journey with Python and SQLite

In Part 1, I set up the database and created a table and one record in that table. In this post, I’ll fill out the rest of the CRUD statements for single records and multiple or all records. I use the SQL WHERE clause for single record requests to select by ROWID.

I want to highlight that you use cur.fetchall() instead of cur.fetchone() to fetch all the records returned by a SELECT query.

    # Read all records
    sql = "SELECT ROWID, name, comments FROM institutions"
    cur = conn.execute(sql)
    res = cur.fetchall()  # <-- List of tuples
    print(res)

Furthermore, the returned result is a list of tuples.

[(1, 'ABC Bank', 'A regional bank'), (2, 'Chase', 'International'), (3, 'Discover', 'National')]

The rest of the statements are self-explanatory, reflecting the SQL syntax and pattern to execute, commit, and fetch.

Here is the updated module.

institutions_02.py

import sqlite3
import sys
from pathlib import Path


def main():
    db_path = Path(".") / "home.db"

    # Open and connection and create a cursor
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()

    # Create the table
    sql = "CREATE TABLE IF NOT EXISTS institutions (name, comments)"
    conn.execute(sql)

    # Create one record
    sql = """INSERT INTO institutions (name, comments)
            VALUES ('ABC Bank', 'A regional bank')"""
    conn.execute(sql)
    conn.commit()

    # Create many records
    sql = """INSERT INTO institutions (name, comments)
            VALUES ('Chase', 'International'), ('Discover', 'National')"""
    conn.execute(sql)
    conn.commit()

    # Read a record by id
    sql = "SELECT ROWID, name, comments FROM institutions WHERE ROWID = 1"
    cur = conn.execute(sql)
    res = cur.fetchone()  # <-- Tuple
    print(res)

    # Read all records
    sql = "SELECT ROWID, name, comments FROM institutions"
    cur = conn.execute(sql)
    res = cur.fetchall()  # <-- List of tuples
    print(res)

    # Update a record by id
    sql = "UPDATE institutions SET name = 'My Bank' WHERE ROWID = 1"
    cur = conn.execute(sql)
    conn.commit()
    print(res)

    # Update all records
    sql = "UPDATE institutions SET comments = 'Comments are stupid'"
    cur = conn.execute(sql)
    conn.commit()

    # Delete one record from the table by id
    sql = "DELETE FROM institutions WHERE ROWID = 1"
    conn.execute(sql)
    conn.commit()

    # Delete all records from the table
    sql = "DELETE FROM institutions"
    conn.execute(sql)
    conn.commit()

    # Delete the table
    sql = "DROP TABLE IF EXISTS institutions"
    conn.execute(sql)

    # Close the cursor and connection
    cur.close()
    conn.close()


if __name__ == "__main__":
    sys.exit(main())

My journey with Python and SQLite

My journey with Python and SQLite | Part 2

Sep 10, 2023