Verify Database Changes with sqlite3 CLI - Python SQLite Part 2 , #35

Verify Database Changes with sqlite3 CLI - Python SQLite Part 2

In this tutorial, you will create a simple shopping product database in Python using SQLite with full CRUD (Create, Read, Update, Delete) functionality. Each database modification is verified by querying the database directly through the sqlite3 command-line interface, ensuring changes are correctly applied.

Code

import sqlite3
import sys

DB_NAME = 'shopping.db'

def connect_db():
  return sqlite3.connect(DB_NAME)

def create_table():
  with connect_db() as conn:
    conn.execute('''
      CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        price REAL NOT NULL
      )
    ''')

def insert_product(name, price):
  with connect_db() as conn:
    conn.execute('INSERT INTO products (name, price) VALUES (?, ?)', (name, price))
    conn.commit()

def read_products():
  with connect_db() as conn:
    cursor = conn.execute('SELECT id, name, price FROM products')
    return cursor.fetchall()

def update_product(product_id, new_price):
  with connect_db() as conn:
    conn.execute('UPDATE products SET price = ? WHERE id = ?', (new_price, product_id))
    conn.commit()

def delete_product(product_id):
  with connect_db() as conn:
    conn.execute('DELETE FROM products WHERE id = ?', (product_id,))
    conn.commit()

def main():
  create_table()
  if len(sys.argv) < 2:
    print("Usage: python shopping.py [create|read|update|delete] [args...]")
    return

  action = sys.argv[1].lower()

  if action == 'create' and len(sys.argv) == 4:
    name = sys.argv[2]
    price = float(sys.argv[3])
    insert_product(name, price)
    print("Inserted product:")
    print(read_products()[-1])  # Show last inserted product

  elif action == 'read':
    products = read_products()
    for p in products:
      print(p)

  elif action == 'update' and len(sys.argv) == 4:
    product_id = int(sys.argv[2])
    new_price = float(sys.argv[3])
    print("Before update:")
    print([p for p in read_products() if p[0] == product_id])
    update_product(product_id, new_price)
    print("After update:")
    print([p for p in read_products() if p[0] == product_id])

  elif action == 'delete' and len(sys.argv) == 3:
    product_id = int(sys.argv[2])
    print("Before delete:")
    print([p for p in read_products() if p[0] == product_id])
    delete_product(product_id)
    print("After delete:")
    print([p for p in read_products() if p[0] == product_id])

  else:
    print("Invalid command or arguments")

if __name__ == '__main__':
  main()

Key Points

• Use parameter placeholders (?) in SQL statements to safely insert, update, and delete data.
• Dispatch CRUD actions from the command line using sys.argv for flexible interaction.
• Verify each database change by querying the SQLite file directly with the sqlite3 CLI or within Python.
• Create tables with IF NOT EXISTS to avoid errors when running the script multiple times.
• Commit changes explicitly to ensure data is saved after INSERT, UPDATE, and DELETE operations.