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,
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
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
- We've seen these on our previous projects, like the scrum project and the create performance task
- We use to to store our backend database on, and use the postman mehtods to accomplish this
- Purpose:to configure, route, view functions,and extend the flask application 2. SQLAlchemy db object
- we've seen it in our sqlite.db file, which we use to store our databse on our project
- pupose: to store data and be able to manipulate it with CRUD
- Flask app object
"""
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
- Comment on these items in the class, purpose and defintion.
- class User
- a class which creates an object of thye user, storing different attibutes of said user
- some attributes include name, user id, password, and date of birth
- db.Model inheritance
- Inheritance is a pillar of object oriented programming
- the idea of inheritance is to use attributes and methods from another class to help build a different class
-
init method
- the init method is a constructor for a class, which initializes an object of the type of the class. This method can initialize each attribute with parameters given by the user.
-
@property,@<column>.setter- Property is an attribute of a class, like name, brand, author, and so on
- setters are methods which allow a user to change an attribute of a class after it have been initialized.
- create, read, update, delete methods
- Creates a new entry into a database
- Reads and returns the data for the user to see
- Updates an entry of the data
- Deletes an entry of the data
- class User
""" 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
class Country(db.Model):
__tablename__ = 'countries' # table name is plural, class name is singular
# Define the User schema with "vars" from object
_name = db.Column(db.String(255), unique=True, nullable=False, primary_key=True)
_language = db.Column(db.String(255), unique=False, nullable=False)
_population = db.Column(db.Integer, unique=False, nullable=False)
_area = db.Column(db.Integer, unique = False)
# constructor of a User object, initializes the instance variables within object (self)
def __init__(self, name, language, population, area):
self._name = name # variables with self prefix become part of the object,
self._language = language
self._area = area
self._population = population
# a name getter method, extracts name from object
@property
def name(self):
return self._name
# a setter function, allows name to be updated after initial object creation
@name.setter
def name(self, name):
self._name = name
# a getter method, extracts uid from object
@property
def language(self):
return self._language
# a setter function, allows uid to be updated after initial object creation
@language.setter
def language(self, language):
self._language = language
# a getter method, extracts uid from object
@property
def area(self):
return self._area
# a setter function, allows uid to be updated after initial object creation
@area.setter
def area(self, area):
self._area = area
# a getter method, extracts uid from object
@property
def population(self):
return self._population
# a setter function, allows uid to be updated after initial object creation
@population.setter
def population(self, population):
self._population = population
# age is calculated field, age is returned according to date of birth
@property
def populationdensity(self):
return self._population/self._area
# output content using str(object) is in human readable form
# 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):
app.app_context()
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 {
"name": self.name,
"language": self.language,
"area": self.area,
"population": self.population,
"populationdensity":self.populationdensity
}
# CRUD update: updates user name, password, phone
# returns self
def update(self, births, deaths, arrivals, departures):
"""only updates values with length"""
self._population += births-deaths+arrivals-departures
db.session.commit()
print("vj")
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
- Comment on how these work?
- Create All Tables from db Object
- uses db.create_all()
- User Object Constructors
- when a user object like u1 is created, the User() class automatically runs the init method, using the parameters to show the attributes
- Try / Except
- these statements check if an error would occur if a statement executes, and return something if it either executes or doesn't they are a good way to debug and
- Create All Tables from db Object
"""Database Creation and Testing """
# Builds working data for testing
def initCountries():
with app.app_context():
"""Create database and tables"""
db.create_all()
"""Tester data for table"""
u1 = Country(name='USA', language='english', population=3000000, area=78425426)
countries = [u1]
"""Builds sample user/note(s) data"""
for country in countries:
try:
'''add user to table'''
object = country.create()
print(f"Created new name {object.name}")
except: # error raised if object nit created
'''fails with bad or duplicate data'''
print(f"Records exist name {country.name}, or error.")
initCountries()
def find_by_uid(name):
with app.app_context():
country = Country.query.filter_by(_name=name).first()
return country # returns user object
# Check credentials by finding user and verify password
def check_credentials(name):
# query email and return user record
country = find_by_uid(name)
if country == None:
return False
return True
check_credentials("USA")
Create a new User in table in Sqlite.db
Uses SQLALchemy and custom user.create() method to add row.
- Comment on purpose of following:user.find_by_uid() and try/except:The purpose of using user.find_by_uid() and try/except is to handle exceptions that may occur when searching for a user with a specific unique identifier (UID). By wrapping the user.find_by_uid() method in a try/except block, any potential exceptions can be caught and handled in a controlled manner. For example, if the UID is not found in the database, the try/except block can be used to handle this error and take appropriate action, such as returning a specific error message or redirecting the user to a relevant page.
user = User(...): The purpose of this line is to create a new instance of the User class, which may include setting various attributes and properties of the user object. This can be used to create a new user in a database or represent a user in memory for further processing. The ... in this line may represent arguments passed to the User constructor, which could include information such as the user's name, email, password, and other relevant details.
user.dob and try/except: The purpose of using user.dob and try/except is to handle exceptions that may occur when accessing the user's date of birth (DOB) property. By wrapping this property access in a try/except block, any potential exceptions can be caught and handled in a controlled manner. For example, if the DOB is not set for the user, the try/except block can be used to handle this error and take appropriate action, such as returning a specific error message or setting a default value.
user.create() and try/except: The purpose of using user.create() and try/except is to handle exceptions that may occur when creating a new user in a database or other persistent storage. By wrapping this method call in a try/except block, any potential exceptions can be caught and handled in a controlled manner. For example, if the user object is missing required fields or there is an issue with the database connection, the try/except block can be used to handle this error and take appropriate action, such as rolling back any changes or returning a specific error message.
def create():
with app.app_context():
# optimize user time to see if uid exists
name = input("Enter country name:")
country = find_by_uid(name)
try:
print("Found\n", country.read())
return
except:
pass # keep going
# request value that ensure creating valid object
area = int(input("Enter the area:"))
population = int(input("Enter your population"))
language = input("enter the language:")
# Initialize User object before date
country = Country(name=name,
language = language,
population=population,
area = area
)
country.create()
# create user.dob, fail with today as dob
create()
Reading users table in sqlite.db
Uses SQLALchemy query.all method to read data
- Comment on purpose of following
User.query.all is likely a method call in a codebase that uses an Object Relational Mapping (ORM) framework to interact with a database. It is used to retrieve all records from the User table and return them as a list.
json_ready assignment and Google List Comprehension are likely code constructs used in a web application or API that accepts and returns data in JSON format. The purpose of json_ready could be to prepare data to be returned in a JSON response by converting it to a format that can be easily serialized to JSON, such as a dictionary or list of dictionaries. The purpose of a list comprehension, specifically a Google List Comprehension, could be to transform or filter a list of objects in a concise and readable way.
# SQLAlchemy extracts all users from database, turns each user into JSON
def update():
with app.app_context():
name = input("which one?")
country = find_by_uid(name)
births = int(input("Births"))
deaths = int(input("Deaths"))
arrivals = int(input("Arrivals"))
departures = int(input("Departures"))
country.update(births,deaths,arrivals,departures)
update()
# SQLAlchemy extracts all users from database, turns each user into JSON
def read():
with app.app_context():
table = Country.query.all()
json_ready = [country.read() for country in table] # "List Comprehensions", for each user add user.read() to list
return json_ready
read()
def delete():
with app.app_context():
name = input("which one?")
country = find_by_uid(name)
country.delete()
delete()