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.
Focus on objects and objects as being data
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
# see the info on the configs because the data is translated, app is assigned the attributes... called setters
"""
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.
"""
# app and db were used throughout the year
# 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'
# made a db and got it started
db = SQLAlchemy()
# break point mean wan to examine the things above which have already happened
# 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
- class User purpose
- db.Model inheritance
- init method
- @property, @
.setter</li> - additional methods
</ul> </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 class Car(db.Model): __tablename__ = 'cars' id = db.Column(db.Integer, primary_key=True) _license = db.Column(db.String(255), unique=True, nullable=False) _maker = db.Column(db.String(255), unique=False, nullable=False) _model = db.Column(db.String(255), unique=False, nullable=False) _price = db.Column(db.String(255), unique=False, nullable=False) _color = db.Column(db.String(255), unique=False, nullable=False) def __init__(self, license, maker, model, price, color): self._license = license self._maker = maker self._model = model self._price = price self._color = color # license @property def license(self): return self._license @license.setter def license(self, license): self._license = license def is_license(self, license): return self._license == license # maker @property def maker(self): return self._maker @maker.setter def maker(self, maker): self._maker = maker # model @property def model(self): return self._model @model.setter def model(self, model): self._model = model # price @property def price(self): return self._price @price.setter def price(self, price): self._price = price # color @property def color(self): return self._color @color.setter def color(self, color): self._color = color def __str__(self): return json.dumps(self.read()) # new table 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 # returns dictionary def read(self): return { "id": self.id, "license": self.license, "maker": self.maker, "model": self.model, "price": self.price, "color": self.color } # CRUD update: updates user name, password, phone # returns self def update(self, license="", maker="", model="", price="", color=""): """only updates values with length""" if len(license) > 0: self.license = license if len(maker) > 0: self.maker = maker if len(model) > 0: self.model = model if len(price) > 0: self.price = price if len(color) > 0: self.set_phone(color) db.session.commit() return self # CRUD delete: remove self # None def delete(self): db.session.delete(self) db.session.commit() return None
def initCars(): with app.app_context(): """Create database and tables""" db.create_all() """Tester data for table""" c1 = Car(license='274920', maker='Toyota', model='SUV', price='111000', color='red') c2 = Car(license='285039', maker='Tesla', model='Crossover', price='334000', color='grey') c3 = Car(license='682100', maker='Subaru', model='Convertible', price='450000', color='white') c4 = Car(license='472940', maker='Lexus', model='Sedan', price='270000', color='blue') c5 = Car(license='819408', maker='Tesla', model='Minivan', price='150000', color='orange') c6 = Car(license='159502', maker='Honda', model='Hatchback', price='603000', color='black') cars = [c1, c2, c3, c4, c5, c6] for car in cars: try: object = car.create() print(f"New Data For {object.license}") except IntegrityError: # error raised if object nit created '''fails with bad or duplicate data''' print(f"Records exist duplicate {car.license}.") initCars()
def find_by_license(license): with app.app_context(): car = Car.query.filter_by(_license=license).first() return car # returns user object # Check credentials by finding user and verify password def check_credentials(license, maker): # query email and return user record car = find_by_license(license) if car == None: return False if (car.is_maker(maker)): return True return False check_credentials("274920", "Toyota")
def create(): # optimize user time to see if uid exists license = input("Enter your license plate:") car = find_by_license(license) try: print("Found\n", car.read()) return except: pass # keep going # request value that ensure creating valid object maker = input("Enter the maker of your car:") model = input("Enter the model of your car:") price = input("Enter the price of your car:") color = input("Enter color of your car:") # Initialize User object before date car = Car(license=license, maker=maker, model=model, price=price, color=color ) # write object to database with app.app_context(): try: object = car.create() print("Created\n", object.read()) except: # error raised if object not created print("Unknown error uid {license}") create()
# SQLAlchemy extracts all users from database, turns each user into JSON def read(): with app.app_context(): table = Car.query.all() json_ready = [car.read() for car in table] # each user adds user.read() to list return json_ready read()
</div>