Database and SQLAlchemy

In this blog we will explore using programs with data, focused on Databases. We will use SQLite Database to learn more about using Programs with Data. Use Debugging through these examples to examine Objects created in Code.

  • College Board talks about ideas like

    • Program Usage. "iterative and interactive way when processing information"
    • Managing Data. "classifying data are part of the process in using programs", "data files in a Table"
    • Insight "insight and knowledge can be obtained from ... digitally represented information"
    • Filter systems. 'tools for finding information and recognizing patterns"
    • Application. "the preserve has two databases", "an employee wants to count the number of book"

  • PBL, Databases, Iterative/OOP

    • Iterative. Refers to a sequence of instructions or code being repeated until a specific end result is achieved
    • OOP. A computer programming model that organizes software design around data, or objects, rather than functions and logic
    • SQL. Structured Query Language, abbreviated as SQL, is a language used in programming, managing, and structuring data

Questions and Answers

Imports and Flask Objects

Defines and key object creations

  • Comment on where you have observed these working? Provide a defintion of purpose.
    1. Flask app object

The Flask app object is a core component of the Flask web application framework. It represents a Flask application instance and is responsible for handling incoming HTTP requests, routing those requests to the appropriate view functions, and returning HTTP responses to the client. In other words, the Flask app object is the entry point of a Flask application, where the application is configured and initialized. The Flask app object is working when it is successfully created and running, which means that the Flask application is up and running, and ready to receive and process incoming requests from clients.

  1. SQLAlchemy db object SQLAlchemy is an Object-Relational Mapping (ORM) library that provides a high-level interface for interacting with relational databases using Python code. The SQLAlchemy db object is an instance of the SQLAlchemy class, which represents a database connection and provides a high-level interface for executing database operations such as querying, inserting, updating, and deleting data. The SQLAlchemy db object is working when it is successfully connected to the target database, which means that the database connection is established, and the ORM is ready to interact with the database by executing SQL queries and mapping database tables to Python objects.

Model Definition

Define columns, initialization, and CRUD methods for users table in sqlite.db

  1. Comment on these items in the class, purpose and defintion.
  • class User

The provided code defines a class called User that represents a user object and provides a CRUD (Create, Read, Update, Delete) interface for managing users stored in a SQLite database. The purpose of the User class is to create a model that can be used to interact with user data in the database.

  • db.Model inheritance

The db.Model inheritance indicates that the class is a SQLAlchemy ORM model that will be used to interact with the database. The tablename class attribute specifies the name of the table in the database.

  • init method

The init method is the constructor for the User class. It takes four parameters:name, uid, password, and dob. The name parameter is the name of the user, uid is a unique identifier for the user, password is the user's password, and dob is the user's date of birth. If the password or dob parameters are not provided, they will be set to default values.

  • "@property, @.setter"</li> </ul>

    The @property and @.setter decorators are used to define getter and setter methods for the class properties. These allow the class properties to be accessed and modified like object attributes.</p>

    • create, read, update, delete methods

    The create, read, update, and delete methods are the CRUD methods for managing user data in the database. create method adds a new user to the database, read method retrieves user data from the database, update method updates user data in the database, and delete method removes user data from the database.

    Hacks

    • Add this Blog to you own Blogging site. In the Blog add notes and observations on each code cell.
    • Change blog to your own database.
    • Add additional CRUD
      • Add Update functionality to this blog.
      • Add Delete functionality to this blog.

    Initial Data

    Uses SQLALchemy db.create_all() to initialize rows into sqlite.db

    • Comment on how these work?
    1. Create All Tables from db Object

    For db.create_all(), the method creates all the tables defined in the SQLAlchemy models into the database. This method should be called once when initializing a new database. If the tables already exist in the database, it will not recreate them.

    1. User Object Constructors

    The user objects are created using the User constructor. The constructor takes in parameters like name, uid, password, and dob (date of birth). This is a convenient way to create user objects with the necessary attributes.

    1. Try / Except

    The try-except block is used to catch exceptions that may be raised when creating new objects. In this specific code, it catches the IntegrityError which occurs when the object to be created violates a unique constraint. This block ensures that if an error is raised, it will be caught, and a message will be printed indicating that the object already exists in the database.

    Check for given Credentials in users table in sqlite.db

    Use of ORM Query object and custom methods to identify user to credentials uid and password

    • Comment on purpose of following
    1. User.query.filter_by

    "User.query.filter_by" is used to query the database and retrieve a specific record based on the provided filter criteria. In this case, filter_by is used to filter the records based on the _uid column, which is equivalent to filtering records based on the uid attribute of the User class.

    1. user.password

    The "user.password" is used to retrieve the password associated with a user object. This is possible because the User class has a password attribute that corresponds to the password column in the database table.

    Create a new User in table in Sqlite.db

    Uses SQLALchemy and custom user.create() method to add row.

    • Comment on purpose of following
    1. user.find_by_uid() and try/except

    The purpose of user.find_by_uid() and try/except is to check if a user with the given uid already exists in the database. If a user with that uid is found, the function returns the existing user, otherwise it proceeds to create a new user.

    1. user = User(...)

    user = User(...) creates a new User object with the provided arguments for name, uid, and password.

    1. user.dob and try/except

    user.dob is used to store the date of birth of the new user. It is initially set to None and will be updated if a valid date is provided by the user.

    1. user.create() and try/except

    user.create() is a custom method that is used to add a new row to the users table in the database. It takes the User object and writes it to the database using SQLAlchemy's db.session.add() and db.session.commit() methods. The try/except block is used to catch any errors that may occur during the creation of the new user object and inform the user of the error.

    Reading users table in sqlite.db

    Uses SQLALchemy query.all method to read data

    • Comment on purpose of following
    1. User.query.all

    User.query.all() is a SQLAlchemy method that returns a list of all the User objects in the database. It's used to retrieve all the rows in a table.

    1. json_ready assignment, google List Comprehension

    json_ready is a list of dictionaries that's created by iterating over the list of User objects (table) and calling the read() method on each object. The json_ready list comprehension uses the user.read() method to get a dictionary of each user's attributes, which can be easily converted to JSON format. The json_ready list is then returned by the read() function.

    My Hacks

    Lesson Notes

    • db.Model:Inheritance - capable to make the class utilize database attributes and methods (getters and setters) - Init method instantiate an object from user class
    • Setters allow attributes to be changed
    • Getters enable the retrieval of a given object
    </div> </div> </div> 
    """
These imports define the key objects
"""

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

"""
These object and definitions are used throughout the Jupyter Notebook.
"""

# Setup of key Flask object (app)
app = Flask(__name__)
# Setup SQLAlchemy object and properties for the database (db)
database = 'sqlite:///cars.db'  # path and filename of database
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = database
app.config['SECRET_KEY'] = 'SECRET_KEY'
db = SQLAlchemy()


# This belongs in place where it runs once per project
db.init_app(app)

    from sqlalchemy import Column, Integer, Text, String, Boolean
from sqlalchemy.exc import IntegrityError

class Car(db.Model):
    __tablename__ = "cars"
    __table_args__ = {'extend_existing': True}
    id = db.Column(db.Integer, primary_key=True)
    _make = db.Column(db.String(255), nullable=False, unique = False)
    _model = db.Column(db.String(255), nullable=False, unique = False)
    _price = db.Column(db.Integer, nullable=False, unique = False)
    _year = db.Column(db.Integer, nullable=False, unique = False)
    _desc = db.Column(db.String(255), nullable=False, unique = True)
    _engine = db.Column(db.String(255), nullable=False, unique = False)
    _body_style = db.Column(db.String(255), nullable=False, unique = False)
    _owner = db.Column(db.String(255), nullable=False, unique = False)
    
    def __init__(self, make, model, price, year, desc, body_style, engine, owner):
        # Adding instance attributes
        self._make = make
        self._model = model
        self._price = price
        self._year = year
        self._desc = desc
        self._body_style = body_style
        self._engine = engine
        self._owner = owner
        
    # Add getters and setters for make, model, price, year
    @property
    def make(self):
        return self._make
    
    @make.setter
    def make(self, make):
        self._make = make

    @property
    def model(self):
        return self._model
    
    @model.setter
    def model(self, model):
        self._model = model

    @property
    def price(self):
        return self._price
    
    @price.setter
    def price(self, price):
        self._price = price

    @property
    def year(self):
        return self._year
    
    @year.setter
    def year(self, year):
        self._year = year
    
    @property
    def desc(self):
        return self._desc
    
    @desc.setter
    def desc(self, desc):
        self._desc = desc

    @property
    def body_style(self):
        return self._body_style
    
    @body_style.setter
    def body_style(self, body_style):
        self._body_style = body_style

    @property
    def engine(self):
        return self._engine
    
    @engine.setter
    def engine(self, engine):
        self._engine = engine

    @property
    def owner(self):
        return self._owner

    @owner.setter
    def owner(self, owner):
        self._owner = owner
    

    def dictionary(self):
        dict = {
            "make" : self.make,
            "model" : self.model,
            "price" : self.price,
            "year" : self.year,
            "desc" : self.desc,
            "body_style" : self.body_style,
            "engine" : self.engine,
            "owner" : self.owner
        }
        return dict 

    def __str__(self):
        return json.dumps(self.dictionary)

    def create(self):
        try:
            # creates a Car object from Car(db.Model) class, passes initializers
            db.session.add(self)  # add prepares to persist person object to Users table
            db.session.commit()  # SqlAlchemy "unit of work pattern" requires a manual commit
            return self
        except IntegrityError:
            db.session.remove()
            return None

    # CRUD read converts self to dictionary
    # returns dictionary
    def read(self):
        return {
            "id" : self.id,
            "make" : self.make,
            "model" : self.model,
            "price" : self.price,
            "year" : self.year,
            "desc" : self.desc,
            "body_style" : self.body_style,
            "engine" : self.engine,
            "owner" : self.owner        
        }

    # CRUD update: updates attributes
    # returns self
    def update(self, make="", model="", price="", year="", desc="", body_style="", engine="", owner=""):
        """only updates values with length"""
        if len(make) > 0:
            self.make = make
        if len(model) > 0:
            self.model = model
        if price > 0:
            self.price(price)
        if year > 0:
            self.year(year)
        if desc >= 0:
            self.desc(desc)
        if len(body_style) > 0:
            self.body_style(body_style)
        if len(engine) > 0:
            self.engine(engine)
        if len(owner) > 0:
            self.owner(owner)
        db.session.commit()
        return self  
    
    # CRUD delete: remove self
    # None
    def delete(self):
        db.session.delete(self)
        db.session.commit()
        return None

# Function to initialize the Cars
def initCars():
    with app.app_context():
        """Create database and tables"""
        # db.init_app(app)
        db.create_all()

        """Data for table"""
        car1 = Car(make="BMW", model="2 Series", price=34000, year=2021, desc="The BMW 2 series is a stylish sports coupe that drives just as well as it looks.", body_style="coupe", engine="2.0L four-cylinder", owner="John Doe")

        cars = [car1]

        """Builds sample user/note(s) data"""
        for car in cars:
            try:
                car.create()
            except IntegrityError:
                '''fails with bad or duplicate data'''
                db.session.remove()
                print(f"Records exist, duplicate car, or error: {car.id}")

initCars()

    Create a new car 

    import sqlite3

database = 'instance/cars.db'

def create():
    make = input("Enter the make of the car:")
    model = input("Enter the model:")
    price = input("Enter the price:")
    year = input("Enter the year:")
    desc = input("Enter a description of the car:")
    body_style = input("Enter the body style of the car:")
    engine = input("Enter the engine type:")
    owner = input("Who's the owner of the car?")
        
    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()

    try:
        # Execute an SQL command to insert data into a table
        cursor.execute("INSERT INTO cars (_make, _model, _price, _year, _desc, _body_style, _engine, _owner) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", (make, model, price, year, desc, body_style, engine, owner))
        
        # Commit the changes to the database
        conn.commit()
        print(f"A new car has been added")
                
    except sqlite3.Error as error:
        print("Error while executing the INSERT:", error)


    # Close the cursor and connection objects
    cursor.close()
    conn.close()
    
create()

    A new car has been added

    Read existing records 

    def read():
    with app.app_context():
        table = Car.query.all()
    json_ready = [car.read() for car in table] # "List Comprehensions", for each user add user.read() to list
    return json_ready

read()

    [{'id': 1,
  'make': 'BMW',
  'model': '2 Series',
  'price': 34000,
  'year': 2021,
  'desc': 'The BMW 2 series is a stylish sports coupe that drives just as well as it looks.',
  'body_style': 'coupe',
  'engine': '2.0L four-cylinder',
  'owner': 'John Doe'},
 {'id': 3,
  'make': 'Toyota',
  'model': 'Sienna',
  'price': 40000,
  'year': 2019,
  'desc': 'Reliable Van',
  'body_style': 'Van',
  'engine': 'Van',
  'owner': 'Johnny Doe'},
 {'id': 5,
  'make': 'Hyundai',
  'model': 'Elantra N',
  'price': 48000,
  'year': 2022,
  'desc': 'Elantra N!!!',
  'body_style': 'Sedan',
  'engine': 'V6',
  'owner': 'Wayne'},
 {'id': 7,
  'make': 'Hyundai',
  'model': 'Veloster N',
  'price': 48000,
  'year': 2019,
  'desc': 'New Description!',
  'body_style': 'Coupe',
  'engine': 'V6',
  'owner': 'Jan'},
 {'id': 8,
  'make': 'Lucid',
  'model': 'Air',
  'price': 72000,
  'year': 2023,
  'desc': 'The all new, all electric, Lucid Air!',
  'body_style': 'Sedan',
  'engine': 'Electric',
  'owner': 'Lucid'},
 {'id': 9,
  'make': 'Kia',
  'model': 'Stinger',
  'price': 42000,
  'year': 2021,
  'desc': 'Kia Stinger, sports sedan!',
  'body_style': 'Sedna',
  'engine': 'V6',
  'owner': 'Sting'}]

    Update an existing record 

    import sqlite3

database = 'instance/cars.db'

# Take the inputs for each attribute
def update():
    id = input("Enter the id of the car:")
    make = input("Enter the make of the car:")
    model = input("Enter the model:")
    price = input("Enter the price:")
    year = input("Enter the year:")
    desc = input("Enter a description of the car:")
    body_style = input("Enter the body style of the car:")
    engine = input("Enter the engine type:")
    owner = input("Who's the owner of the car?")

    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()

    try:
        # Execute an SQL command to update data in a table
        # Trying to add dob right now...
        cursor.execute("UPDATE cars SET _make = ?, _model = ?, _price = ?, _year = ?, _desc = ?,  _body_style = ?,  _engine = ?,  _owner = ?  WHERE id = ?", (make, model, price, year, desc, body_style, engine, owner, id))
        if cursor.rowcount == 0:
            # Car ID not found
            print(f"No id was found in the table")
        
        # Commit new data
        else: 
            print(f"Car with the id {id} has been updated")
            conn.commit()

    except sqlite3.Error as error:
        print("Error while updating the record:", error)
    
    # Close the cursor and connection objects
    cursor.close()
    conn.close()
    
update()

    Car with the id 3 has been updated

    Check the update

    Read to check if the record has truly been updated

    read()

    [{'id': 1,
  'make': 'BMW',
  'model': '2 Series',
  'price': 34000,
  'year': 2021,
  'desc': 'The BMW 2 series is a stylish sports coupe that drives just as well as it looks.',
  'body_style': 'coupe',
  'engine': '2.0L four-cylinder',
  'owner': 'John Doe'},
 {'id': 3,
  'make': 'Toyota',
  'model': 'Sienna',
  'price': 50000,
  'year': 2023,
  'desc': 'New Description! Sienna!',
  'body_style': 'Van',
  'engine': 'V6',
  'owner': 'SiennaDoe'},
 {'id': 5,
  'make': 'Hyundai',
  'model': 'Elantra N',
  'price': 48000,
  'year': 2022,
  'desc': 'Elantra N!!!',
  'body_style': 'Sedan',
  'engine': 'V6',
  'owner': 'Wayne'},
 {'id': 7,
  'make': 'Hyundai',
  'model': 'Veloster N',
  'price': 48000,
  'year': 2019,
  'desc': 'New Description!',
  'body_style': 'Coupe',
  'engine': 'V6',
  'owner': 'Jan'},
 {'id': 8,
  'make': 'Lucid',
  'model': 'Air',
  'price': 72000,
  'year': 2023,
  'desc': 'The all new, all electric, Lucid Air!',
  'body_style': 'Sedan',
  'engine': 'Electric',
  'owner': 'Lucid'},
 {'id': 9,
  'make': 'Kia',
  'model': 'Stinger',
  'price': 42000,
  'year': 2021,
  'desc': 'Kia Stinger, sports sedan!',
  'body_style': 'Sedna',
  'engine': 'V6',
  'owner': 'Sting'},
 {'id': 10,
  'make': '',
  'model': '',
  'price': '',
  'year': '',
  'desc': '',
  'body_style': '',
  'engine': '',
  'owner': ''}]

    Delete an existing record 

    import sqlite3

def delete():

    id = input("Select the ID of the car you wish to remove: ")
    
    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()

    # Validate the input
    try:
        id = int(id)
    except ValueError:
        print("Invalid input: ID must be an integer.")
        return

    try:
        # Use a parameterized query to prevent SQL injection
        c = conn.cursor()
        c.execute("DELETE FROM cars WHERE id = ?", (id,))
        conn.commit()
        print(f"Row with ID {id} has been deleted.")
    except sqlite3.Error as e:
        print(f"Error deleting row with ID {id}: {e}")
    finally:
        # Close the connection
        cursor.close
        conn.close()

delete()

    Row with ID 5 has been deleted.

    Run a read to check if the record has truly been deleted 

    read()

    [{'id': 1,
  'make': 'BMW',
  'model': '2 Series',
  'price': 34000,
  'year': 2021,
  'desc': 'The BMW 2 series is a stylish sports coupe that drives just as well as it looks.',
  'body_style': 'coupe',
  'engine': '2.0L four-cylinder',
  'owner': 'John Doe'},
 {'id': 3,
  'make': 'Toyota',
  'model': 'Sienna',
  'price': 50000,
  'year': 2023,
  'desc': 'New Description! Sienna!',
  'body_style': 'Van',
  'engine': 'V6',
  'owner': 'SiennaDoe'},
 {'id': 7,
  'make': 'Hyundai',
  'model': 'Veloster N',
  'price': 48000,
  'year': 2019,
  'desc': 'New Description!',
  'body_style': 'Coupe',
  'engine': 'V6',
  'owner': 'Jan'},
 {'id': 8,
  'make': 'Lucid',
  'model': 'Air',
  'price': 72000,
  'year': 2023,
  'desc': 'The all new, all electric, Lucid Air!',
  'body_style': 'Sedan',
  'engine': 'Electric',
  'owner': 'Lucid'},
 {'id': 9,
  'make': 'Kia',
  'model': 'Stinger',
  'price': 42000,
  'year': 2021,
  'desc': 'Kia Stinger, sports sedan!',
  'body_style': 'Sedna',
  'engine': 'V6',
  'owner': 'Sting'},
 {'id': 10,
  'make': '',
  'model': '',
  'price': '',
  'year': '',
  'desc': '',
  'body_style': '',
  'engine': '',
  'owner': ''}]
    </div>