Unit 2.4b Using Programs with Data, SQL
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
Each Tri 2 Final Project should be 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?
- the columns of a database aka the information that populates the datatable.
- What is the purpose of identity Column in SQL database?
- the purpose is to have unique rows of data and to be able to differentiate different users.
- What is the purpose of a primary key in SQL database?
- the primary key has to be unique because it is used as an identifier for different people. For example, name would not be a good primary key because two people could have the same name.
- What are the Data Types in SQL table?
- strings, integers, boolean, images
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?
- Each open SQLite database is represented by a Connection object, which is created using sqlite3.connect() . Their main purpose is creating Cursor objects, and Transaction control.
- Same for cursor object?
- simplifies the code for the user. an instance using which you can invoke methods that execute SQLite statements, fetch data from the result sets of the queries.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- the cursor object contains attributes like special variables, function variables, and class variables
- conn object contains attributes like special variables and function variables
- Is "results" an object? How do you know?
- results is an object because it has attributes
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?
- out of the two create(), i think the sqlite3 method using conn and cur is better. It is able to accomplish the same thing with less code. It is less complicated and just as efficient.
- Explain purpose of SQL INSERT. Is this the same as User init?
- SQL INSERT is a command used to add new records or rows of data to a database table
- User init is a method used in object-oriented programming languages like Python. The init method is used to initialize the properties or attributes of an object when it is created.
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?
- It serves like a warning message. When the length of my password is less than 2, the message will appear.
- Explain try/except, when would except occur?
- except would occur if there is a sqlite3 error
- What code seems to be repeated in each of these examples to point, why is it repeated?
cursor = conn.cursor()is repeated in each example. Most likely because this line of code is necessary to connect to the database
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?
- When you perform a delete operation on a SQLite database, you are permanently removing data from the database. If you accidentally delete the wrong data or forget to include a WHERE clause in your delete statement, you can end up losing valuable data.
- In the print statements, what is the "f" and what does {uid} do?
- {uid} gets the uid of the user in question
- f is the syntax for formatted string literals. Inside this string, you can write a Python expression between { } characters that can refer to variables or literal values.
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()
Menu Interface to CRUD operations
CRUD and Schema interactions from one location by running menu. Observe input at the top of VSCode, observe output underneath code cell.
- Why does the menu repeat?
- a recursion is happening. As the function continues, it calls itself, thus the menu is always repeating
def menu():
operation = input("Enter: (C)reate (R)ead (U)pdate or (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")
- Could you refactor this menu? Make it work with a List?
- yes its better to refactor this menu since recursion can lead to stack overflow.
def menu():
db_funcs = [
('c', create),
('r', read),
('u', update),
('d', delete),
('s', schema)
]
while True:
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
if len(operation)==0: # Escape Key
return
found = False
for func in db_funcs:
if (func[0]) == operation.lower():
found = True
func[1]()
if not found:
print("Please enter c, r, u, or d")
try:
menu() # start menu
except:
print("Perform Jupyter 'Run All' prior to starting menu")
Hacks
- In this implementation, do you see procedural abstraction?
- yes. For example, there are functions for create, read, delete, and update. All of which make the overall code simpler. This can be seen in the menu function where all the functions for CRUD are called. Instead of having long lines of code under each elif statement, the create/read/delete/etc functions can just be called.
- In 2.4a or 2.4b lecture
- Do you see data abstraction? Complement this with Debugging example.
- yes. For example, the class User is an example of data abstraction. it initializes multiple objects with several parameters. It would be very hard to save properties of a user into a database if they were not all comprised into one object. Thus, in this sense, the data is abstracted
- Do you see data abstraction? Complement this with Debugging example.
Reference... sqlite documentation
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:///instance/sqlite.db' # path and filename of database
#database = 'sqlite:///files/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 json
from sqlalchemy.exc import IntegrityError
class College(db.Model):
__tablename__ = 'colleges' # table name is plural, class name is singular
# Define the User schema with "vars" from object
id = db.Column(db.Integer, unique=True, primary_key=True)
_uid = db.Column(db.String(255), unique=True, nullable=False)
_name = db.Column(db.String(255), unique=False, nullable=False)
_rank = db.Column(db.Integer, unique=False, nullable=False)
_acceptanceRate = db.Column(db.Integer, unique=False, nullable=False)
# constructor of a User object, initializes the instance variables within object (self)
def __init__(self, uid, name, rank, acceptanceRate):
self._name = name
self._rank = rank
self._acceptanceRate = acceptanceRate
self._uid = uid
# a getter method, extracts uid from object
@property
def uid(self):
return self._uid
# a setter function, allows uid to be updated after initial object creation
@uid.setter
def uid(self, uid):
self._uid = uid
# gets the name the college
@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
# gets the rank of the college
@property
def rank(self):
return self._rank
# a setter function, allows rank to be updated after initial object creation
@rank.setter
def rank(self, rank):
self._rank = rank
# a acceptanceRate getter
@property
def acceptanceRate(self):
return self._acceptanceRate
# a setter function to set the college's acceptanceRate
@acceptanceRate.setter
def acceptanceRate(self, acceptanceRate):
self._acceptanceRate = acceptanceRate
# 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,
"rank" : self.rank,
"acceptanceRate" : self.acceptanceRate,
"uid": self.uid
}
def update(self, name="", rank="", acceptanceRate="", uid=""):
"""only updates values with length"""
if len(name) > 0:
self.name = name
if len(rank) > 0:
self.rank = rank
if len(acceptanceRate) > 0:
self.acceptanceRate = acceptanceRate
if len(uid) > 0:
self.uid = uid
db.session.add(self) # performs update when id exists\n",
db.session.commit()
return self
def delete(self):
db.session.delete(self)
db.session.commit()
return None
def initCollege():
with app.app_context():
"""Create database and tables"""
db.create_all()
"""Tester data for table"""
c1 = College(name='Princeton', rank='1', acceptanceRate='4.4', uid='princeton')
c2 = College(name='MIT', rank='2', acceptanceRate='4', uid='mit')
c3 = College(name='Harvard', rank='3', acceptanceRate='4', uid='harvard')
c4 = College(name='Stanford', rank='4', acceptanceRate='3.9', uid='stanford')
c5 = College(name='Yale', rank='5', acceptanceRate='5.3', uid='yale')
c6 = College(name='U of Chicago', rank='6', acceptanceRate='6.5', uid='uofchicago')
c7 = College(name='Johns Hopkins', rank='7', acceptanceRate='7.5', uid='johnshopkins')
c8 = College(name='UPENN', rank='8', acceptanceRate='5.9', uid='upenn')
c9 = College(name='CalTech', rank='9', acceptanceRate='3.9', uid='caltech')
c10 = College(name='Duke', rank='10', acceptanceRate='6', uid='duke')
c11 = College(name='Northwestern', rank='11', acceptanceRate='7', uid='northwestern')
c12 = College(name='Dartmouth', rank='12', acceptanceRate='6.2', uid='dartmouth')
c13 = College(name='Brown', rank='13', acceptanceRate='5.5', uid='brown')
c14 = College(name='Vanderbilt', rank='14', acceptanceRate='7.1', uid='vanderbilt')
c15 = College(name='Rice', rank='15', acceptanceRate='9.5', uid='rice')
colleges = [c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15]
"""Builds sample user/note(s) data"""
for college in colleges:
try:
'''add user to table'''
object = college.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 {college.uid}, or error.")
initCollege()
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()