Unit 2.4b (Using Programs with Data, SQL) Notes
Using Programs with Data is focused on SQL and database actions. Part B focuses on learning SQL commands, connections, and curses using an Imperative programming style,
- Database Programming is Program with Data
- Menu Interface to CRUD operations
- Hacks
Database Programming is Program with Data
The Tri 2 Final Project is an example of a Program with Data.
Prepare to use SQLite in common Imperative Technique
- Explore SQLite Connect object to establish database connection- Explore SQLite Cursor Object to fetch data from a table within a database
Schema of Users table in Sqlite.db
Uses PRAGMA statement to read schema.
Describe Schema, here is resource Resource
- What is a database schema?
A database schema is essentially a column defined by certian information in a database.
- What is the purpose of identity Column in SQL database?
It is to locate which row of a data table a certain entry is found.
- What is the purpose of a primary key in SQL database?
The primary key is the main way to access a certain piece of information in a database. Generally, unlike the identity column, a primary key should never change for a piece of data.
- What are the Data Types in SQL table?
Integer, float, string, Boolean, images; really any data type or data structure (list, dictionary, class).
import sqlite3
database = 'instance/sqlite.db' # this is location of database
def schema():
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
# Fetch results of Schema
results = cursor.execute("PRAGMA table_info('users')").fetchall()
# Print the results
for row in results:
print(row)
# Close the database connection
conn.close()
schema()
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?
From what I understand, a connection object is basically an object that is used to connect to a database so that you can interact with it with various other functions such as the cursor object below. To use it, the argument is a string with the path to the database.
- Same for cursor object?
The cursor object can select data and execute code within the database after connecting. It essentially allows you to do what you would otherwise do with sqlite3 in the terminal.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
There are many special variables, function variables, and class variables in both. The unique attributes that I'm seeing are in_transaction:False, isolation_level: '', row_factory: None, and total_changes: 0 in the conn object and arraysize, lastrowid, row_factory, and rowcount.
- Is "results" an object? How do you know?
It is an object because we can see that it has attributes, which are exclusive to objects.
import sqlite3
def read():
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
# Execute a SELECT statement to retrieve data from a table
results = cursor.execute('SELECT * FROM users').fetchall()
# Print the results
if len(results) == 0:
print("Table is empty")
else:
for row in results:
print(row)
# Close the cursor and connection objects
cursor.close()
conn.close()
read()
Create a new User in table in Sqlite.db
Uses SQL INSERT to add row
- Compare create() in both SQL lessons. What is better or worse in the two implementations?
The create() function in the first lesson is, in my opinion, simpler and easier to use because it works within the SQLite User data directly. It helps me, at least, to pull the User attributes and data and interact with them in Python. However, this create() function can be because it's a bit easier to use on a smaller scale without reliance on objects.
- Explain purpose of SQL INSERT. Is this the same as User
__init__?
SQL INSERT is very different from __init__ because, while __init__ initializes the User object and creates a set of attributes that the user can work with, SQL INSERT acts like a create function by adding in a new User object with the given attributes.
import sqlite3
def create():
name = input("Enter your name:")
uid = input("Enter your user id:")
password = input("Enter your password")
dob = input("Enter your date of birth 'YYYY-MM-DD'")
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to insert data into a table
cursor.execute("INSERT INTO users (_name, _uid, _password, _dob) VALUES (?, ?, ?, ?)", (name, uid, password, dob))
# Commit the changes to the database
conn.commit()
print(f"A new user record {uid} has been created")
except sqlite3.Error as error:
print("Error while executing the INSERT:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
#create()
Updating a User in table in Sqlite.db
Uses SQL UPDATE to modify password
- What does the hacked part do?
The "hacked" part activates if the new password the person inputted is less than 2 characters. This is kind of a mean way to say that the password the person put in is not safe, and therefore is invalid.
- Explain
try/except. When wouldexceptoccur?
Using try allows you to make code run up until an error occurs, at which point the except condition is activated. This allows programs to provide failsafes for errors or invalid user interactions.
- What code seems to be repeated in each of these examples and why is it repeated?
Creating the connection and cursor objects is done in every block because, at the end of each, the connection and cursor are both closed. In order to use them again, they need to be re-activated.
import sqlite3
def update():
uid = input("Enter user id to update")
password = input("Enter updated password")
if len(password) < 2:
message = "hacked"
password = 'gothackednewpassword123'
else:
message = "successfully updated"
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to update data in a table
cursor.execute("UPDATE users SET _password = ? WHERE _uid = ?", (password, uid))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No uid {uid} was not found in the table")
else:
print(f"The row with user id {uid} the password has been {message}")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the UPDATE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
#update()
Delete a User in table in Sqlite.db
Uses a delete function to remove a user based on a user input of the id.
- Is DELETE a dangerous operation? Why?
DELETE can be a dangerous operation when left to user inputs. User error could lead to the deletion of important information that is difficult to retrieve.
- What is the "f" and {uid} do?
The "f" prepares a print statement to read {uid} as the value of the variable uid rather than the literal text "{uid}".
import sqlite3
def delete():
uid = input("Enter user id to delete")
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
cursor.execute("DELETE FROM users WHERE _uid = ?", (uid,))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No uid {uid} was not found in the table")
else:
# The uid was found in the table and the row was deleted
print(f"The row with uid {uid} was successfully deleted")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the DELETE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
#delete()
def menu():
operation = input("Enter: (C)reate, (R)ead, (U)pdate, (D)elete or (S)chema")
if operation.lower() == 'c':
create()
elif operation.lower() == 'r':
read()
elif operation.lower() == 'u':
update()
elif operation.lower() == 'd':
delete()
elif operation.lower() == 's':
schema()
elif len(operation) == 0: # Escape Key
return
else:
print("Please enter c, r, u, or d")
menu() # recursion, repeat menu
try:
menu() # start menu
except:
print("Perform Jupyter 'Run All' prior to starting menu")
Hacks
My Own Database
I decided to use the object oriented form of database because I prefer the orderliness that it affords me.
Features
Because I've been working with Gamemaker recently to try to make an RPG game (mainly for fun, but also to try applying what I'm learning in different ways), I decided to make a database with various (fake) video game bosses, with attributes for their stats, weaknesses, descriptions, etc.
To add a twist on the easier databases we've been working with recently, I decided to make it like a user forum. Each character in the database has room for users to add their strategies on how to beat it.
On top of that, to leave a forum comment, I decided to make it based on the user database from 2.4a. You'll have to connect to one of the users in the original database using your username and password. This connection uses the other type we worked on today.
Initializing and Creating the Database
The big code block below initializes and creates the database.
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
# Setup of key Flask object (app)
app = Flask(__name__)
# Setup SQLAlchemy object and properties for the database (db)
database = 'sqlite:///vgenemy.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)
""" database dependencies to support sqlite examples """
import datetime
from datetime import datetime
import json
from sqlalchemy.exc import IntegrityError
class Comment(db.Model):
__tablename__ = 'comments'
# Define the Notes schema
id = db.Column(db.Integer, primary_key=True)
note = db.Column(db.String(255), unique=False, nullable=False)
userID = db.Column(db.String(255), unique=False, nullable=False)
monsterID = db.Column(db.Integer, db.ForeignKey('enemies.id'))
# Constructor of a Notes object, initializes of instance variables within object
def __init__(self, uid, note):
self.userID = uid
self.note = note
# Returns a string representation of the Notes object, similar to java toString()
# returns string
def __repr__(self):
return "Notes(" + str(self.id) + "," + self.note + "," + str(self.userID) + ")"
# CRUD create, adds a new record to the Notes table
# returns the object added or None in case of an error
def create(self):
try:
# creates a Notes object from Notes(db.Model) class, passes initializers
db.session.add(self) # add prepares to persist person object to Notes table
db.session.commit() # SqlAlchemy "unit of work pattern" requires a manual commit
return self
except IntegrityError:
db.session.remove()
return None
# CRUD read, returns dictionary representation of Notes object
# returns dictionary
def read(self):
return {
"id": self.id,
"userID": self.userID,
"note": self.note,
"monsterID": self.monsterID
}
class Enemy(db.Model):
__tablename__ = 'enemies' # 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)
_desc = db.Column(db.String(255), unique=True, nullable=False)
_att = db.Column(db.Integer, unique=False, nullable=False)
_mag = db.Column(db.Integer, unique=False, nullable=False)
_dfs = db.Column(db.Integer, unique=False, nullable=False)
_spd = db.Column(db.Integer, unique=False, nullable=False)
_comments = db.relationship('Comment', backref='enemies', lazy=True)
# constructor of a User object, initializes the instance variables within object (self)
def __init__(self, name, desc, att, mag, dfs, spd):
self._name = name # variables with self prefix become part of the object,
self._desc = desc
self._att = att
self._mag = mag
self._dfs = dfs
self._spd = spd
@property
def name(self):
return self._name
@name.setter
def name(self, name):
self._name = name
@property
def desc(self):
return self._desc
@desc.setter
def desc(self, desc):
self._desc = desc
@property
def att(self):
return self._att
@att.setter
def att(self, att):
self._att = att
@property
def mag(self):
return self._mag
@mag.setter
def mag(self, mag):
self._mag = mag
@property
def dfs(self):
return self._dfs
@dfs.setter
def dfs(self, dfs):
self._dfs = dfs
@property
def spd(self):
return self._spd
@spd.setter
def spd(self, spd):
self._spd = spd
# 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 Enemy(db.Model) class, passes initializers
db.session.add(self) # add prepares to persist person object to enemies 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,
"desc": self.desc,
"att": self.att,
"mag": self.mag,
"dfs": self.dfs,
"spd": self.spd,
}
# CRUD update: updates all values
# returns self
def update(self, name="", desc="", att=1, mag=1, dfs=1, spd=1):
"""only updates values with length"""
if len(name) > 0:
self.name = name
if len(desc) > 0:
self.desc = desc
self.att = att
self.mag = mag
self.dfs = dfs
self.spd = spd
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
"""Database Creation and Testing """
# Builds working data for testing
def initEnemies():
with app.app_context():
"""Create database and tables"""
db.create_all()
"""Tester data for table"""
e1 = Enemy(name='Forest Guardian', desc='A lumbering green beast with sharp horns.', att=85, mag=70, dfs=80, spd=50)
e2 = Enemy(name='Sea Guardian', desc='A strange, unpredictable fish with long whiskers.', att=60, mag=100, dfs=85, spd=80)
e3 = Enemy(name='Terra Guardian', desc='A keen, sharp-clawed mole with a drill tail.', att=110, mag=80, dfs=60, spd=100)
e4 = Enemy(name='Molten Guardian', desc='A giant lava monster with metal accessories.', att=85, mag=115, dfs=85, spd=90)
e5 = Enemy(name='Sky Guardian', desc='A valiant, flying knight bird with armor.', att=100, mag=100, dfs=100, spd=100)
enemies = [e1, e2, e3, e4, e5]
"""Builds sample user/note(s) data"""
for enemy in enemies:
try:
comment = "Share your strategies here!"
enemy._comments.append(Comment("Admin", comment))
'''add user to table'''
object = enemy.create()
print(f'Created new enemy "{object.name}".')
except: # error raised if object nit created
'''fails with bad or duplicate data'''
print(f"Error creating {enemy.name} data.")
initEnemies()
Enemy Read Function
A read function is nice to have for future use in the commenting code. This is the code corresponding to the enemy database, however; it will not have full CRUD code because the list of monsters is simply there for reference. I made it a corresponding parent database for simplicity.
def e_read():
with app.app_context():
table = Enemy.query.all()
json_ready = [enemy.read() for enemy in table] # each user adds user.read() to list
return json_ready
The code above successfully creates all five of the template enemies.
Code for Adding Comments (Create)
Now that the enemies have been made in the database, I created a function below that lets the user comment on a specific monster. The parameters correspond to code in the verify function found later.
def make_comment(userID):
with app.app_context():
id_inp = input("Please input the number corresponding to the enemy you would like to comment on.")
try:
id_sel = int(id_inp)
except:
print("Please input an integer corresponding to an enemy.")
make_comment(userID)
enemy = Enemy.query.filter_by(id=id_sel).first()
if enemy:
print("Enemy found. Please enter a comment.")
c_enter = input("Please discuss your thoughts on/strategies for this enemy.")
print("Your comment will look like this:")
print(f'{userID}: {c_enter}')
print("Is this alright?")
consent = input('Enter "y" for YES and "n" for NO.').lower()
if consent == "y":
# Connect to the database file
conn = sqlite3.connect('instance/vgenemy.db')
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to insert data into a table
cursor.execute("INSERT INTO comments (note, userID, monsterID) VALUES (?, ?, ?)", (c_enter, userID, id_sel))
# Commit the changes to the database
conn.commit()
except sqlite3.Error as error:
print("Error while executing the INSERT:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
print("Comment posted successfully!")
else:
make_comment(userID)
else:
print("Enemy not found.")
make_comment(userID)
import sqlite3
udb = 'instance/sqlite.db' # this is location of database
def verify():
# Connect to the database file
conn = sqlite3.connect(udb)
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
# get user input
uid = input("Enter your user ID: ")
password = input("Enter your password: ")
# Execute a SELECT statement to retrieve data from the table
cursor.execute("SELECT * FROM users WHERE _uid = ? AND _password = ?", (uid, password))
# fetch the first matching row
user = cursor.fetchone()
#closing cursor
cursor.close()
conn.close()
#checking if user with matching data exists
if user:
print(f"Welcome, {user[1]}!")
uid = user[2]
for enemy in e_read():
print(f'{enemy["id"]}. {enemy["name"]}')
make_comment(uid)
else:
print(f'User with ID "{uid}" and password "{password}" not found.')
return
verify()
from IPython.display import clear_output
def forum():
for enemy in e_read():
print(f'{enemy["id"]}. {enemy["name"]}')
id_inp = input("Please input the number corresponding to the enemy you would like to comment on.")
try:
id_sel = int(id_inp)
except:
print("Please input an integer corresponding to an enemy.")
forum()
with app.app_context():
enemy = Enemy.query.filter_by(id=id_sel).first()
if enemy:
table = Comment.query.all()
comments = [comment.read() for comment in table]
print("\n" + enemy._name + "\n")
print('Base Stats:')
print(f'Attack: {enemy._att} | Magic: {enemy._mag} | Defense: {enemy._dfs} | Speed: {enemy._spd}\n')
print('Comments:')
for comment in comments:
if comment["monsterID"] == id_sel:
print(f'{comment["userID"]}: {comment["note"]}')
else:
print("Enemy not found. Make sure to input a corresponding integer.")
forum()
Finishing the Comments CRUD
I already made the make_comment function that essentially acts like a create function. The grading standards got changed to include the necessity to make a menu with create, read, update and delete, so I decided to go through and make the three remaining functions for the comments.
Read
I don't have a formal read function for comments, so I made it below.
import sqlite3
def c_read():
with app.app_context():
table = Comment.query.all()
json_ready = [comment.read() for comment in table] # each user adds user.read() to list
return json_ready
c_read()
cdb = 'instance/vgenemy.db'
def c_update(userID):
# Printing all of the user's comments
i = 0
u_comments = []
for comment in c_read():
if comment['userID'] == userID:
i += 1
print(str(i) + f'. {comment["userID"]}: {comment["note"]}')
u_comments.append(comment)
e_comment = input("Which comment would you like to edit?")
try:
c_sel = int(e_comment) - 1 # using user input as index
chosen_c = u_comments[c_sel]
pass
except:
print("Invalid input. Enter an integer corresponding to a listed comment.")
c_update(userID)
new_note = input("What would you like your comment to say instead?")
print("Your comment will look like this:")
print(f'{userID}: {new_note}')
print("Is this alright?")
consent = input('Enter "y" for YES and "n" for NO.').lower()
if consent == "y":
# Connect to the database file
conn = sqlite3.connect(cdb)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to update data in a table
cursor.execute("UPDATE comments SET note = ? WHERE id = ?", (new_note, chosen_c["id"]))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No ID {chosen_c['id']} was not found in the table.")
else:
print("Your comment has been updated successfully!")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the UPDATE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
else:
return
I left a sample of it running with my username above. It works by first printing out all of the comments created by the logged-in user. The user then selects a comment to be edited based on the number associated with it. That number corresponds to the index of one of the listed comments, which the user can then change by inputting a new comment. That new comment replaces the old one with the execute function.
Delete
The delete code borrows a lot from the update code, so most of how that one worked applies to this one. The main difference is the execute code.
def c_delete(userID):
# Printing all of the user's comments
i = 0
u_comments = []
for comment in c_read():
if comment['userID'] == userID:
i += 1
print(str(i) + f'. {comment["userID"]}: {comment["note"]}')
u_comments.append(comment)
d_comment = input("Which comment would you like to delete?")
try:
c_sel = int(d_comment) - 1 # using user input as index
chosen_c = u_comments[c_sel]
pass
except:
print("Invalid input. Enter an integer corresponding to a listed comment.")
update(userID)
print("Your comment will be permanently deleted. Is that alright?")
consent = input('Enter "y" for YES and "n" for NO.').lower()
if consent == "y":
# Connect to the database file
conn = sqlite3.connect(cdb)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to update data in a table
cursor.execute("DELETE FROM comments WHERE id = ?", (chosen_c['id'],))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No ID {chosen_c['id']} was not found in the table.")
else:
print("Your comment has been deleted successfully!")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the UPDATE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
else:
return
Menu with CRUD Procedures
Now that all of the CRUD features are done, I decided to make a menu that takes advantage of the user log-in capabilities I created before. The user starts by logging in (based on the information provided to the users database). After that, the user can make comments, read other comments, or update and delete their own comments.
Because of the forum-like nature of this menu system, the c_read function created above is not used in the menu directly. Instead, the forum function, which also reads comment data, is placed in the menu instead.
udb = 'instance/sqlite.db'
def login():
# Connect to the database file
conn = sqlite3.connect(udb)
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
# get user input
uid = input("Enter your user ID: ")
password = input("Enter your password: ")
# Execute a SELECT statement to retrieve data from the table
cursor.execute("SELECT * FROM users WHERE _uid = ? AND _password = ?", (uid, password))
# fetch the first matching row
user = cursor.fetchone()
#closing cursor
cursor.close()
conn.close()
#checking if user with matching data exists
if user:
print(f"Welcome, {user[1]}!")
uid = user[2]
c_menu(uid)
else:
print("User not found. Make sure that your user ID and password are inputted correctly.")
login()
# Even COOLER menu to run other cells from one control point
def c_menu(userID):
operation = input("Enter: (C)reate, (R)ead, (U)pdate or (D)elete")
if operation.lower() == 'c':
for enemy in e_read():
print(f'{enemy["id"]}. {enemy["name"]}')
make_comment(userID)
elif operation.lower() == 'r':
forum()
elif operation.lower() == 'u':
c_update(userID)
elif operation.lower() == 'd':
c_delete(userID)
elif len(operation) == 0: # Escape Key
return
else:
print("Please enter c, r, u, or d")
c_menu(userID) # repeat the menu
try:
login() # log in to your account
except:
print("Bye-bye!")
Reflection
I had never worked with parent-child objects before this, nor had I understood how to use ForeignKey attributes before this, but I worked on it for a very long time and eventually still got it to work.
Doing this made me realize that sometimes, the procedural, non-object-oriented method we learned in class today is just easier. The classes were a lot to juggle, and I found myself really struggling to jump between the two different methods.
At the same time, I was doing that to demonstrate knowledge. If I didn't have to do any of that, I could have more easily used two separate sqlite tables together.
Procedural Abstraction Question
We were tasked to answer where procedural abstraction can be seen in our new databases. In mine, it can very easily be seen all throughout the process, starting with the Comment and Enemy class functions and ending with the forum function. In each case, I created new functions to prevent other functions from becoming too bulky or difficult to understand.
For example, I made sure that the primary focus of verify was to log the user in before making a comment. Then, I had it transition into the make_comment function when it was time for that step in the process.
I also used it for e_read so that I could grab the up-to-date enemy table data in an easy-to-manipulate JSON format whenever I needed to in other functions, including make_comment and forum. I think this was some pretty valuable foresight.
The CRUD all works with named procedures to make the menu more accessible.