Unit 2.4a Using Programs with Data, SQLAlchemy
Using Programs with Data is focused on SQL and database actions. Part A focuses on SQLAlchemy and an OOP programming style,
2.4 A Notes
College Board talks about ideas like:
-
Program Usage: This refers to the way in which programs are used to process information in an iterative and interactive manner.
-
Managing Data: This involves classifying data as part of the process of using programs, and organizing data files in a table.
-
Insight: The College Board suggests that insight and knowledge can be obtained from digitally represented information.
-
Filter Systems: This refers to the tools used to find information and recognize patterns within data.
-
Application: The College Board gives an example of an employee wanting to count the number of books in two databases that the preserve has.
PBL, Databases, Iterative/OOP:
-
Iterative: Refers to a process in which a sequence of instructions or code is repeated until a specific end result is achieved.
-
OOP: An approach to computer programming in which software design is based on objects or data, rather than functions and logic.
-
SQL: Structured Query Language, abbreviated as SQL, is a language used in programming, managing, and structuring data, often used in managing databases.
Imports and Flask Objects
Defines and key object creations
-
Comment on where you have observed these working? Provide a defintion of purpose.
-
Flask app object: I have observed the Flask app object working in various web development projects. Flask is a popular web framework for building web applications with Python. The Flask app object is the central point of the application, and is used to configure the application and define the application routes. Its purpose is to create and configure the Flask application, including any extensions or third-party libraries, and handle the routing of incoming requests.
-
SQLAlchemy db object:
I have also observed the SQLAlchemy db object in various Python-based web development projects. SQLAlchemy is a popular Object Relational Mapper (ORM) for working with databases in Python. The db object is used to represent the database connection, and is used to interact with the database, including creating tables, querying data, and performing transactions. Its purpose is to provide a high-level, object-oriented interface for working with databases, and to abstract away the details of the underlying database engine, allowing developers to focus on their application logic rather than low-level database operations.
"""
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:///sqlite.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)
Model Definition
Define columns, initialization, and CRUD methods for users table in sqlite.db
-
class User: The class User is a Python class that represents a user entity in an application. It is used in many web applications to store user-related information, such as their name, email, and password. Its purpose is to provide a blueprint for creating user objects that can be stored and retrieved from a database.
-
db.Model inheritance:
The db.Model inheritance refers to the inheritance of the db.Model class from the SQLAlchemy library. In Python-based web applications, this inheritance is commonly used to define database models for different entities, such as users, posts, and comments. The purpose of inheriting from the db.Model class is to provide a set of predefined methods and attributes that enable interaction with the underlying database. By using this inheritance, developers can create more efficient and streamlined code for database operations.
- init method:
The init method is a special method in Python classes that is called when an object of the class is created. In database models, this method is commonly used to initialize the attributes of an object with default or user-defined values. The purpose of the init method is to provide a way to create instances of the class with specific attributes, making it easier to work with the class in other parts of the application.
- @property, @
.setter:</strong></li> </ul> These are Python decorators that are commonly used in database models. The @property decorator is used to define a getter method for a class attribute, while the @
.setter decorator is used to define a setter method for a specific column in the database. The purpose of these decorators is to provide a more Pythonic way of working with class attributes and database columns, and to enable additional functionality, such as validation or formatting, when getting or setting attributes.</p> - create, read, update, delete methods:
These are commonly used methods in database models that represent the basic operations that can be performed on a database entity. The create method is used to create a new entity in the database, the read method is used to retrieve one or more entities from the database, the update method is used to update an existing entity in the database, and the delete method is used to remove an entity from the database. The purpose of these methods is to provide a way to interact with the database in a standard way that is easy to understand and maintain. By using these methods, developers can create more efficient and streamlined code for database operations.
</div> </div> </div>""" database dependencies to support sqlite examples """ import datetime from datetime import datetime import json from sqlalchemy.exc import IntegrityError from werkzeug.security import generate_password_hash, check_password_hash ''' Tutorial: https://www.sqlalchemy.org/library.html#tutorials, try to get into a Python shell and follow along ''' # Define the User class to manage actions in the 'users' table # -- Object Relational Mapping (ORM) is the key concept of SQLAlchemy # -- a.) db.Model is like an inner layer of the onion in ORM # -- b.) User represents data we want to store, something that is built on db.Model # -- c.) SQLAlchemy ORM is layer on top of SQLAlchemy Core, then SQLAlchemy engine, SQL # defining the template the class of Users, class definition template, helps us create objects which are of the type User, template for future object class Restaurant_B(db.Model): __tablename__ = 'Restaurant_business' # table name is plural, class name is singular # Define the User schema with "vars" from object id = db.Column(db.Integer, primary_key=True) _Restaurant = db.Column(db.String(255), unique=True, nullable=False) _Sales = db.Column(db.String(255), unique=False, nullable=False) _Avg = db.Column(db.String(255), unique=False, nullable=False) _City = db.Column(db.String(255), unique=False, nullable=False) _State = db.Column(db.String(255), unique=False, nullable=False) _Meals = db.Column(db.String, unique=False) # constructor of a User object, initializes the instance variables within object (self) def __init__(self, Restaurant, Sales, Avg, City, State, Meals): self._Restaurant = Restaurant # variables with self prefix become part of the object, self._Sales = Sales self._Avg = Avg self._City = City self._State = State self._Meals = Meals # a name getter method, extracts name from object @property def Restaurant(self): return self._Restaurant # a setter function, allows name to be updated after initial object creation @Restaurant.setter def Restaurant(self, Restaurant): self._Restaurant = Restaurant @property def Sales(self): return self._Sales # a setter function, allows name to be updated after initial object creation @Sales.setter def Sales(self, Sales): self._Sales = Sales @property def Avg(self): return self._Avg # a setter function, allows name to be updated after initial object creation @Avg.setter def Avg(self, Avg): self._Avg = Avg @property def City(self): return self._City # a setter function, allows name to be updated after initial object creation @City.setter def City(self, City): self._City = City @property def State(self): return self._State # a setter function, allows name to be updated after initial object creation @State.setter def State(self, State): self._State = State @property def Meals(self): return self._Meals @Meals.setter def Meals(self, Meals): self._Meals = Meals # output content using str(object) in human readable form, uses getter # output content using json dumps, this is ready for API response def __str__(self): return json.dumps(self.read()) # CRUD create/add a new record to the table # returns self or None on error def create(self): try: # creates a person object from User(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, "Restaurant" : self.Restaurant, "Sales" : self.Sales, "Avg" : self.Avg, "City" : self.City, "State": self.State, "Meals": self.Meals } # CRUD update: updates user name, password, phone # returns self def update(self, Restaurant, Sales, Avg, City, State, Meals): """only updates values with length""" if len(Restaurant) > 0: self.Restaurant = Restaurant if len(Sales) > 0: self.Sales = Sales if len(Avg) > 0: self.Avg = Avg if len(City) > 0: self.City = City if len(State) > 0: self.State = State if len(Meals) > 0: self.Meals = Meals db.session.add(self) db.session.commit() return self # CRUD delete: remove self # None def delete(self): db.session.delete(self) db.session.commit() return None
Initial Data
Uses SQLALchemy db.create_all() to initialize rows into sqlite.db
-
Create All Tables from db Object: Creating all tables from the db object involves using SQLAlchemy's create_all method to generate the necessary database tables based on the defined models. This method scans the application for all models that inherit from db.Model and creates the corresponding tables in the database. The purpose of this method is to automate the process of creating database tables, rather than manually creating them in the database. This method can be used during the application's initialization to ensure that all the required tables exist before the application starts serving requests.
-
User Object Constructors:
In Python, object constructors are special methods that are called when an object is created. User object constructors are used to initialize user objects with default or user-defined values. They are used to set initial attributes for the user object, such as the user's name, email, and password. The purpose of the user object constructors is to provide a way to create user objects with specific attributes that can be used throughout the application.
- Try / Except:
The try/except statement in Python is used to handle exceptions or errors that may occur during the execution of code. The try block contains the code that may raise an exception, while the except block contains the code that should be executed if an exception occurs. The purpose of the try/except statement is to gracefully handle exceptions, preventing the application from crashing due to unhandled errors. This is particularly useful in web applications, where users may interact with the application in unpredictable ways, and errors can occur at any time. By using try/except statements, developers can catch and handle errors, providing a better user experience and preventing security vulnerabilities.
"""Database Creation and Testing """ # Builds working data for testing def initRestaurant_business(): with app.app_context(): """Create database and tables""" db.create_all() """Tester data for table""" R1 = Restaurant_B(Restaurant='Komodo', Sales='41,000,000', Avg='115', City='Miami', State='Fla.', Meals='285,000') R2 = Restaurant_B(Restaurant='The Boathouse Orlando', Sales='39,863,628', Avg='44', City='Fla.', State='25', Meals='921,785') R3 = Restaurant_B(Restaurant='Swan', Sales='31,000,000', Avg='85', City='Miami',State='Fla.', Meals='185,000') R4 = Restaurant_B(Restaurant='Maple & Ash (Chicago)', Sales='30,286,684', Avg='106', City='Chicago', State='Ill.', Meals='285,714') R5 = Restaurant_B(Restaurant='Mila', Sales='27,350,000', Avg='134', City='Miami Beach', State='Fla.', Meals='203,990') R6 = Restaurant_B(Restaurant='Alinea', Sales='27,072,500', Avg='650', City='Chicago', State='Ill.', Meals='41,650') R7 = Restaurant_B(Restaurant='CTop of the World', Sales='25,672,308', Avg='133', City='Las Vegas', State='Nev.', Meals='218,586') R8 = Restaurant_B(Restaurant='Shooters Waterfront', Sales='25,025,370', Avg='68', City='Fort Lauderdale', State='Fla.', Meals='419,972') R9 = Restaurant_B(Restaurant='Prime 112', Sales='24,750,000', Avg='155', City='Miami Beach', State='Fla.', Meals='210,000') R10 = Restaurant_B(Restaurant='Paddlefish', Sales='23,795,000', Avg='52', City='Orlando', State='Fla.', Meals='485,000') R11 = Restaurant_B(Restaurant='Capa', Sales='23,547,000', Avg='121', City='Orlando', State='Fla.', Meals='197,000') R12 = Restaurant_B(Restaurant='Maple & Ash (Scottsdale)', Sales='23,487,122', Avg='95', City='Scottsdale', State='Ariz.', Meals='247,232') R13 = Restaurant_B(Restaurant='Commanders Palace', Sales='23,184,000', Avg='89', City='New Orleans', State='La.', Meals='264,000' ) R14 = Restaurant_B(Restaurant='Taste of Texas', Sales='23,180,522', Avg='64', City='Houston', State='Texas', Meals='356,894') R15 = Restaurant_B(Restaurant='Joes Seafood, Prime Steak & Stone Crab', Sales='22,477,000', Avg='94', City='Washington', State='N/A', Meals='255,000') Rest = [R1, R2, R3, R4, R5, R6, R7, R8, R9, R10, R11, R12, R13, R14, R15] for R in Rest: try: object = R.create() print(f"Added data for {object.Restaurant}") except IntegrityError: '''fails with bad or duplicate data''' print(f"Records exist, duplicate email, or error: {R.Restaurant}") initRestaurant_business()
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
-
User.query.filter_by: User.query.filter_by is a method in SQLAlchemy that is used to retrieve data from the database based on specific filter conditions. In this case, it is used to retrieve a specific user object from the database based on the value of one or more attributes, such as the user's email or ID. The purpose of this method is to provide a convenient way to retrieve data from the database that meets specific criteria. By using filter_by, developers can write more efficient code that retrieves only the data that is needed, rather than retrieving all data and filtering it in the application code.
-
user.password:
user.password refers to the password attribute of a User object. This attribute is commonly used to store a user's password in a secure and encrypted format, such as using a hash function. The purpose of this attribute is to store the user's password securely, ensuring that it is not visible or easily accessible to anyone, including the application developers or database administrators. By storing passwords securely, applications can protect user accounts from unauthorized access and prevent security breaches.
def find_by_Restaurant(Restaurant): with app.app_context(): # ORM allows us to do query methods on our data # only returns the match to the first match to the database restaurant_B = Restaurant_B.query.filter_by(_Restaurant=Restaurant).first() return restaurant_B # returns user object
Create a new User in table in Sqlite.db
Uses SQLALchemy and custom user.create() method to add row.
-
user.find_by_uid() and try/except: user.find_by_uid() is a method that is used to retrieve a user object from the database based on the user's ID. The purpose of this method is to provide a convenient way to retrieve user objects based on a unique identifier, such as an ID or email address. The try/except block is used to handle any errors that may occur during the execution of the method, such as if the user does not exist in the database. By using try/except, developers can catch and handle errors gracefully, providing a better user experience and preventing security vulnerabilities.
-
user = User(...):
user = User(...) is a statement that creates a new user object with the specified attributes, such as the user's name, email, and password. The purpose of this statement is to create a new user object that can be saved to the database or used in the application code.
- user.dob and try/except:
user.dob refers to the date of birth attribute of a User object. The try/except block is used to handle any errors that may occur during the execution of the code that accesses this attribute, such as if the attribute is not set or is invalid. By using try/except, developers can catch and handle errors gracefully, providing a better user experience and preventing security vulnerabilities.
- user.create() and try/except:
user.create() is a method that is used to save a new user object to the database. The try/except block is used to handle any errors that may occur during the execution of the method, such as if the user object is invalid or if there is an error connecting to the database. By using try/except, developers can catch and handle errors gracefully, providing a better user experience and preventing security vulnerabilities.
def create(): # optimize user time to see if uid exists Restaurant = input("Enter a Restaurant name:") Dinner = find_by_Restaurant(Restaurant) try: print("Found\n", Dinner.read()) return except: pass # keep going # request value that ensure creating valid object Sales = input("Enter the total sales:") Avg = input("Enter their average check:") City = input("Enter the City:") State = input("Enter the state:") Meals = input("Enter the total meals served:") # Initialize User object before date restaurant_B = Restaurant_B(Restaurant=Restaurant, Sales=Sales, Avg=Avg, City=City, State=State, Meals=Meals ) # write object to database with app.app_context(): try: object = restaurant_B.create() print("Created\n", object.read()) except: # error raised if object not created print("Unknown error uid {Restaurant}") create()
Reading users table in sqlite.db
Uses SQLALchemy query.all method to read data
-
User.query.all: User.query.all is a method that is used to retrieve all User objects from the database. The purpose of this method is to provide a convenient way to retrieve all User objects for use in the application code. The returned objects can be used for further processing, such as displaying them to the user or manipulating them in some way.
-
json_ready assignment, google List Comprehension:
json_ready is a variable that is assigned the result of a list comprehension, which is a concise way to create a list in Python. The purpose of this variable is to create a list of dictionaries that can be easily converted to JSON format for use in a web application. List comprehension is used to create a list of dictionaries based on the User objects retrieved from the database, with each dictionary representing a User object and its attributes. The resulting list of dictionaries can be easily serialized to JSON format and sent to the client-side of a web application.
def read(): with app.app_context(): table = Restaurant_B.query.all() json_ready = [restaurant_B.read() for restaurant_B in table] # "List Comprehensions", for each user add user.read() to list return json_ready read()
def updateq(): Restaurant = input("Enter the name of the Restaurant to update: ") Dinner = find_by_Restaurant(Restaurant) # Request new values for the QB attributes Sales = input("Enter their Total Sales : ") Avg = input("Enter their Average Checks : ") City = input("Enter their City season: ") State = input("Enter Their state: ") Meals = input("Enter their total Meals: ") if Dinner is not None: with app.app_context(): Dinner.update(Restaurant=Restaurant, Sales=Sales, Avg=Avg, City=City, State=State, Meals=Meals) print("Updated Restaurant", Restaurant) else: print("error") updateq()
def delete(): # optimize user time to see if uid exists Restaurant = input("Enter a Restaurant name:") Dinner = find_by_Restaurant(Restaurant) try: pass except: Restaurant = input("Try again, that was not a valid Restaurant:") with app.app_context(): try: object = Dinner.delete() print("Deleted\n", Dinner) except: # error raised if object not created print("Unknown error uid {Restaurant}") delete()
def crudmenu(): selection = input("Enter a letter to select your option --> c: Create, r: Read, u: Update, d: delete") if selection.lower() == "c": create() elif selection.lower() == "r": with app.app_context(): table = Restaurant_B.query.all() json_ready = [restaurant_B.read() for restaurant_B in table] return json_ready elif selection.lower() == "u": updateq() elif selection.lower() == "d": delete() else: selection = input("Please enter a valid letter --> c: Create, r: Read, u: Update, D: delete") crudmenu()
</div>The update() function takes an id parameter to identify which user to update, and a data parameter containing the updated user data. It first retrieves the user from the database using the filter_by() method and the provided id. If the user exists, it then calls the update() method on the user object, passing in the data parameter. The update() method is a custom method you would need to define on your User model that updates the user's attributes based on the provided data. Finally, the function commits the changes to the database and returns the updated user data in JSON format.
The delete() function is similar to the update() function, but instead of updating the user, it deletes the user from the database. If the user exists, it calls the delete() method on the user object, commits the changes, and returns the deleted user data in JSON format. If the user does not exist, it returns None.
Note that you would also need to define the update() and delete() methods on your User model in order to use these functions.