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.
-
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
"""
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
- 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 ''' 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 User(db.Model): __tablename__ = 'users' # table name is plural, class name is singular # Define the User schema with "vars" from object id = db.Column(db.Integer, primary_key=True) _name = db.Column(db.String(255), unique=False, nullable=False) _uid = db.Column(db.String(255), unique=True, nullable=False) _password = db.Column(db.String(255), unique=False, nullable=False) _dob = db.Column(db.Date) # constructor of a User object, initializes the instance variables within object (self) def __init__(self, name, uid, password="123qwerty", dob=datetime.today()): self._name = name # variables with self prefix become part of the object, self._uid = uid self.set_password(password) if isinstance(dob, str): # not a date type dob = date=datetime.today() self._dob = dob # 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 email from object @property def uid(self): return self._uid # a setter function, allows name to be updated after initial object creation @uid.setter def uid(self, uid): self._uid = uid # check if uid parameter matches user id in object, return boolean def is_uid(self, uid): return self._uid == uid @property def password(self): return self._password[0:10] + "..." # because of security only show 1st characters # update password, this is conventional setter def set_password(self, password): """Create a hashed password.""" self._password = generate_password_hash(password, method='sha256') # check password parameter versus stored/encrypted password def is_password(self, password): """Check against hashed password.""" result = check_password_hash(self._password, password) return result # dob property is returned as string, to avoid unfriendly outcomes @property def dob(self): dob_string = self._dob.strftime('%m-%d-%Y') return dob_string # dob should be have verification for type date @dob.setter def dob(self, dob): if isinstance(dob, str): # not a date type dob = date=datetime.today() self._dob = dob @property def age(self): today = datetime.today() return today.year - self._dob.year - ((today.month, today.day) < (self._dob.month, self._dob.day)) # 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, "name": self.name, "uid": self.uid, "dob": self.dob, "age": self.age, } # CRUD update: updates user name, password, phone # returns self def update(self, name="", uid="", password=""): """only updates values with length""" if len(name) > 0: self.name = name if len(uid) > 0: self.uid = uid if len(password) > 0: self.set_password(password) db.session.commit() return self # CRUD delete: remove self # None def delete(self): db.session.delete(self) db.session.commit() return None
Note
classs User: What's the purpose?
The purpose of the class User is to provide a model for managing actions in the 'users' table of a database using SQLAlchemy Object Relational Mapping. The class defines the schema for the 'users' table and has methods to perform CRUD (Create, Read, Update, and Delete) operations on the table. The class represents the data that we want to store in the 'users' table, and it is built on top of the db.Model layer, which is an inner layer of the SQLAlchemy ORM. The User class has instance variables such as id, name, uid, password, and dob that represent the columns in the 'users' table. It also has methods to get and set the values of these variables, as well as methods to verify and update passwords. The class provides methods for creating a new record in the 'users' table, reading an existing record from the table, updating an existing record, and deleting a record from the table. Additionally, the class has methods to convert the object to a human-readable string and to a dictionary that can be used for an API response.
db.model inheritance
In this code, User class inherits from db.Model class, which means that User class is a subclass of db.Model class.
db.Model is a base class provided by the SQLAlchemy ORM (Object Relational Mapping) library. It provides a set of common functionality to be used by classes that are mapped to database tables. When a class inherits from db.Model, it gains the ability to interact with the database through the SQLAlchemy ORM, such as the ability to define
init method: What is it?
The init method is the constructor method for the User class. It is called when a new User object is created and initializes the object's instance variables (_name, _uid, _password, and _dob) with the provided arguments (or default values if none are provided). It also calls the set_password method to hash the provided password.
What does property and setter do?
- @property is a decorator that defines a method as a "getter" for a class attribute, which allows the attribute to be accessed like a regular attribute but with some additional functionality. When a @property method is called, it returns the value of the attribute it is associated with.
- In Python, setters are created using the @
.setter decorator. When a setter method is called, it updates the value of the attribute it is associated with.</li> </ul> Additional methods?
</div> </div> </div>"""Database Creation and Testing """ # Builds working data for testing def initUsers(): with app.app_context(): """Create database and tables""" db.create_all() """Tester data for table""" u1 = User(name='Thomas Edison', uid='toby', password='123toby', dob=datetime(1847, 2, 11)) u2 = User(name='Nikola Tesla', uid='niko', password='123niko') u3 = User(name='Alexander Graham Bell', uid='lex', password='123lex') u4 = User(name='Eli Whitney', uid='whit', password='123whit') u5 = User(name='Indiana Jones', uid='indi', dob=datetime(1920, 10, 21)) u6 = User(name='Marion Ravenwood', uid='raven', dob=datetime(1921, 10, 21)) users = [u1, u2, u3, u4, u5, u6] """Builds sample user/note(s) data""" for user in users: try: '''add user to table''' object = user.create() print(f"Created new uid {object.uid}") except: # error raised if object nit created '''fails with bad or duplicate data''' print(f"Records exist uid {user.uid}, or error.") initUsers()
def find_by_uid(uid): with app.app_context(): user = User.query.filter_by(_uid=uid).first() return user # returns user object # Check credentials by finding user and verify password def check_credentials(uid, password): # query email and return user record user = find_by_uid(uid) if user == None: return False if (user.is_password(password)): return True return False #check_credentials("indi", "123qwerty")
Note
What is the User.query.filter_by?
My Answer
User.query.filter_by() is a method provided by the SQLAlchemy library, which is an Object-Relational Mapping (ORM) library for Python. It is used to query the database and extract records from a specific table.
ChatGPT Elaborated
In the given code, User.query.filter_by(_uid=uid) is used to extract a single user from the database table User, where _uid matches the provided uid. The filter_by() method filters the records of a table based on the given conditions, and first() method returns the first record from the filtered result set.
So, user variable will hold the record of the User table whose _uid field matches the uid parameter provided to the function. If there is no matching record, None will be returned.
What is the user.password?
user.password refers to an attribute of the User class that stores the user's password in a hashed format. The is_password() method is then used to verify if the password entered by the user matches the hashed password stored in the database.
def create(): # optimize user time to see if uid exists uid = input("Enter your user id:") user = find_by_uid(uid) try: print("Found\n", user.read()) return except: pass # keep going # request value that ensure creating valid object name = input("Enter your name:") password = input("Enter your password") # Initialize User object before date user = User(name=name, uid=uid, password=password ) # create user.dob, fail with today as dob dob = input("Enter your date of birth 'YYYY-MM-DD'") try: user.dob = datetime.strptime(dob, '%Y-%m-%d').date() except ValueError: user.dob = datetime.today() print(f"Invalid date {dob} require YYYY-mm-dd, date defaulted to {user.dbo}") # write object to database with app.app_context(): try: object = user.create() print("Created\n", object.read()) except: # error raised if object not created print("Unknown error uid {uid}") create()
Note
user=User(...)
ChatGPT
In the given code segment, user = User(name=name, uid=uid, password=password) is used to create a new instance of the User class with the provided values for name, uid, and password. This instance is then assigned to the user variable.
The purpose of this line of code is to create a User object that represents a new user that will be added to the database. By initializing a new instance of the User class with the provided values, we can create a new user with the required information.
Once the User object is created, it is further modified in the subsequent code block by adding the user's date of birth (dob) to the object. Finally, the object is written to the database using the user.create() method.
Therefore, the purpose of setting user = User(...) is to create a new User object that can be further processed before it is added to the database.
user.dob
Code allows users to input their own date of birth in the order of year, month, to day. If users enter it in incorrectly, the code will input an error so if you tried to enter in a date like 2032 in 2022 the error would occur and set your dob back to default.
user.create()
Allows you to add in your info which makes it so you can create a new user using user.create().
# SQLAlchemy extracts all users from database, turns each user into JSON def read(): with app.app_context(): table = User.query.all() json_ready = [user.read() for user in table] # each user adds user.read() to list return json_ready read()
Note
User.query.all
User.query.all() retrieves all the users from the database, and the read() function then reads each user's data and returns it as a list of JSON objects.
json_ready assignment
json_ready is a list of JSON objects, where each JSON object represents a single user's data from the User table in the database. The list of JSON objects is then returned by the read() function when it is called.
</div>How would I add update functionality to this blog?
I can change the findbyuid from returning none to adding a create() function where you can then update yourself into the user databse. I would have to do this by using an if statement that will run as if user is None: and then put in user input for name, uid, dob, and password. I will also have to add the error checking for dob by using the command used for error checking in the previous code cell. The else portion of the elif statement will be that if the user is in the database, they can update it. You can do this by asking the user if they want to update their user with a yes or no answer and then if no stop running and if they do, the user will be asked what they want to update and then they can pick from name, uid, dob, and password. Once they update it, it will ask if they want to update or exit and so on so forth, it will repeat until the user is satisfied.
Add Delete functionality to this blog?
Like what I was just talking about you can also search for your user like the above and if it's found it will also ask if you want to delete your user. This can be used through a two factor authentication such as asking "Do you want to delete" then ask "Are you sure" so the user can be 100% sure since we discussed why deleting in SQLAlchemy is dangerous.