CRUD

This database stores planes and their stats, it implements CRUD using imperative programming

Create

Importing the necessary modules and create function:

import sqlite3

database = 'files/planes.db'


def create_design(designation, price, max_speed, designer):
    conn = sqlite3.connect(database)
    c = conn.cursor()
    
    try:
        # Execute an SQL command to insert data into a table
        c.execute("INSERT INTO planes (designation, price, max_speed, designer) VALUES (?, ?, ?, ?)",
              (designation, price, max_speed, designer))
                
        # Commit the changes to the database
        conn.commit()
        print(f"A new user record {designation} has been created with the data:")
        print((designation, price, max_speed, designer))
                
    except sqlite3.Error as error:
        print("Error while executing the INSERT:", error)

    c.close()
    conn.close()
    
    
create_design("U-2", 120000000, 475, "Lockheed")

A new user record U-2 has been created with the data:
('U-2', 120000000, 475, 'Lockheed')

This function takes four arguments (designation, price, max_speed, and designer) and inserts them into the "designs" table as a new record.

Read

def read_design(id):
    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL queries
    c = conn.cursor()
    
    # Execute a SELECT statement to retrieve data from a table
    results = c.execute('SELECT * FROM planes').fetchall()

    # Print the results
    if len(results) == 0:
        print("Table is empty")
    else:
        for row in results:
            if row[0] == id:
                print(row)

    # Close the cursor and connection objects
    c.close()
    conn.close()
    
read_design(1)

(1, 'SR-71', 34000000, 2200, 'Lockheed')

This function takes a design ID as an argument and returns the record from the "designs" table that matches that ID.

Update

def update_design(design_id, designation=None, price=None, max_speed=None, designer=None):
    conn = sqlite3.connect(database)
    c = conn.cursor()
    update_dict = {}
    
    if designation is not None:
        update_dict["designation"] = designation
    if price is not None:
        update_dict["price"] = price
    if max_speed is not None:
        update_dict["max_speed"] = max_speed
    if designer is not None:
        update_dict["designer"] = designer
        
    update_str = ", ".join([f"{col}=? " for col in update_dict.keys()])
    c.execute(f"UPDATE planes SET {update_str} WHERE id=?", (*update_dict.values(), design_id))
    conn.commit()
    print(read_design(design_id))
    
    c.close()
    conn.close()
    
update_design(2, None, None, 480, None)

(2, 'U-2', 120000000, 480, 'Lockheed')
None

This function takes a design ID and any combination of the other four arguments as keyword arguments. It updates the record in the "designs" table that matches the ID with the new values provided.

Delete

def delete_design(design_id):
    conn = sqlite3.connect(database)
    c = conn.cursor()
    
    print(read_design(design_id))
    c.execute("DELETE FROM planes WHERE id=?", (design_id,))
    
    conn.commit()
    conn.close()
    
delete_design(2)

(2, 'U-2', 120000000, 480, 'Lockheed')
None

This function takes a design ID as an argument and deletes the record from the "designs" table that matches that ID.