Unit 2.4 - 2.5 Hacks
Libraries
Database and SQLAlchemy
In this blog, I will explore the use of programs with data, specifically focusing on databases. The SQLite Database will be used to demonstrate how programs can be used with data. Debugging will also be used to examine the objects created in the code.
The College Board discusses several ideas related to
- the use of filter systems for finding information and patterns.
- data management through classification and storage in tables
- programming, including the iterative and interactive processing of information
- gaining insights and knowledge from digitally represented information
Other topics covered in this post include
- PBL
- databases
- iterative programming Iterative programming refers to the repetition of a sequence of instructions until a specific end result is achieved.
- OOP: programming model that organizes software design around objects, rather than functions and logic.
- SQL: language used for programming, managing, and structuring data.
Creation of Flask and SQLAlchemy Objects
- I've seen the creation of Flask and SQLAlchemy objects in different programming scenarios. These objects are necessary in web development frameworks like Flask.
Flask App Object
-
The Flask app object is an important component of the Flask framework. It represents the application and provides different methods and attributes for managing and configuring applications. It enables developers to define routes and register blueprints, which are groups of routes that can be used to organize the functionality within the application's.
-
I have personally seen the use of Flask app objects in creating APIs during trimesters 1 and 2 like the StockAPI. We used these objects to register new blueprints, which helped us create our API that connected the front-end and back-end of our application.
SQLAlchemy DB Object
- We also used the SQLAlchemy DB object to create our former database, mine being the stock database. When we assigned the SQLAlchemy object to the database object, we registered our database, enabling the creation of the SQLite data table and the database that contained all the data necessary. The SQLAlchemy DB object is a powerful tool for working with databases, providing a high-level interface for interacting with databases and facilitating the creation of complex database-driven applications.
"""
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)
import datetime
import json
from datetime import datetime
from sqlalchemy.exc import IntegrityError
class Banlist(db.Model):
__tablename__ = 'banlist'
# added this after getting an error that the table had already been defined
__table_args__ = {'extend_existing': True}
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), unique=True, nullable=False)
pastfive = db.Column(db.String(255), unique=False, nullable=False)
reason = db.Column(db.String(255), unique=False, nullable=False)
def __init__(self, name, pastfive, reason):
self.name = name
self.pastfive = pastfive
self.reason = reason
@property
def banlist_info(self):
return self.read()
def create(self):
try:
db.session.add(self)
db.session.commit()
return self
except IntegrityError:
db.session.remove()
return None
def read(self):
return {
"id": self.id,
"name": self.name,
"pastfive": self.pastfive,
"reason": self.reason,
}
def update(self, name="", pastfive="", reason="", ):
if len(name) > 0:
self.name = name
if len(pastfive) > 0:
self.pastfive = pastfive
if len(reason) > 0:
self.reason = reason
db.session.commit()
return self
def delete(self):
db.session.delete(self)
db.session.commit()
return None
"""Database Creation and Testing """
# Builds working data for testing
def initBanlists():
with app.app_context():
"""Create database and tables"""
db.create_all()
"""Tester data for table"""
p1 = Banlist(name='ZACH LAVINE', pastfive='SHITE', reason='Sold me on 5 picks so far, them most inconsistent mf I have ever met.')
p2 = Banlist(name='DONOVAN MITCHELL', pastfive='DECENT', reason='Too inconsistent, performs highly some days and terrible on others')
# locals include age, name, date of birth, user id, etc.
# debugging allows you to go in to see if there is any problems with your code (ex. missing an attribute like user id)
# once you have your users, your sqlite.db file is created
banlists = [p1, p2]
"""Builds sample user/note(s) data"""
for banlist in banlists:
try:
'''add user to table'''
object = banlist.create()
print(f"Created new uid {object.name}")
except: # error raised if object nit created
'''fails with bad or duplicate data'''
print(f"Records exist uid {banlist.name}, or error.")
initBanlists()
def find_by_name(name):
with app.app_context():
game = Banlist.query.filter_by(name=name).first()
return game
#check_credentials("indi", "123qwerty")
def create():
# optimize user time to see if uid exists
name = input("Enter player name:")
banlist = find_by_name(name)
try:
print("Found\n", banlist.read())
return
except:
pass # keep going
# request value that ensure creating valid object
# pastfive = ("Terrible")
# reason = ("Sold all my picks")
pastfive = (input("How has he performed in the pastfive? (good, shite, amazing, etc.)"))
reason = (input("Enter your reasoning"))
# delete comments and update the code
# Initialize User object before date
banlist = Banlist(name=name,
pastfive=pastfive,
reason=reason,
)
# write object to database
with app.app_context():
try:
object = banlist.create()
print("Created\n", object.read())
except: # error raised if object not created
print("Unknown error name {name}")
create()
def read():
with app.app_context():
table = Banlist.query.all()
json_ready = [banlist.read() for banlist in table]
return json_ready
read()
def update():
# find the banlist to update
name = input("Enter the name of the Banlist to update: ")
banlist = find_by_name(name)
if not banlist:
print("player not found")
return
# print the current values
print("Current values:")
print(banlist.read())
# prompt the user for new values, allowing them to skip
new_name = input(f"Enter a new player [{banlist.name}]: ")
if new_name:
banlist.name = new_name
new_pastfive = input(f"Enter an updated pastfive game performances for this specific player")
if new_pastfive:
banlist.pastfive = new_pastfive
new_reason = input(f"Enter your reasoning for adding this player to the Banlist")
if new_reason:
banlist.pastfive = new_reason
# update the banlist in the database
with app.app_context():
try:
updated_banlist = banlist.update()
print("Updated values:")
print(updated_banlist.read())
except:
print("Error updating banlist")
update()
def delete():
name = input("Enter the name of the player to delete:")
banlist = find_by_name(name)
try:
print("Found\n", banlist.read())
except:
print(f"banlist with name {name} not found")
return
# confirm deletion
confirm = input("Are you sure you want to delete this player? (y/n)")
if confirm.lower() == 'y':
with app.app_context():
try:
banlist.delete()
print(f"{name} taken off the banlist")
except: # error raised if object not deleted
print(f"Unknown error deleting player named {name}")
else:
print(f"Deletion of {name} cancelled")
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 = Banlist.query.all()
json_ready = [banlist.read() for banlist in table]
return json_ready
elif selection.lower() == "u":
update()
elif selection.lower() == "d":
delete()
else:
selection = input("Please enter a valid letter --> c: Create, r: Read, u: Update, D: delete")
crudmenu()
Reading Users table in Sqlite.db
Uses SQL SELECT statement to read data
What is a connection object? After you google it, what do you think it does?
I think that the connection object connects the user to the db
Same for cursor object?
allows user to go to individual rows
Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
There are many attributes in the debugger menu some things that we can see is the data per user
Is "results" an object? How do you know?
Result objects are wrapper objects that indicate whether or not the API call was a success