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
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?
- <span style="color:blue"> A database schema is a blueprint or a plan that outlines the logical and physical structure of a database. It defines how the data is organized, stored, and accessed within the database. The schema defines the tables, columns, data types, relationships between tables, constraints, and indexes in the database. </span>
-
What is the purpose of identity Column in SQL database?
- An identity column in SQL database is a column that automatically generates unique numeric values for each row in a table. The purpose of an identity column is to provide a unique and sequential identifier for each record in the table, which can be used as a primary key or a foreign key in other tables.
-
What is the purpose of a primary key in SQL database?
- A primary key in SQL database ensures uniqueness and data integrity, and enables referencing of records in other tables.
-
What are the Data Types in SQL table?
- SQL tables support numeric and character string data types, including INT, BIGINT, DECIMAL, FLOAT, CHAR, VARCHAR, and TEXT.
import sqlite3
database = 'files/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('scores')").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?
- A connection object represents a connection to a database, used to authenticate the user and execute queries or commands against the database.
-
Same for cursor object?
- A cursor object is an object in programming used to traverse and manipulate the results of a database query, typically one row at a time.
-
Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- Special variables, function variables, class variables, in transaction, isolation level, row factory, and total changes are some of the properties included in the conn object. Special variables, function variables, arraysize, connection, description, lastrowid, row factory, and rowcount are some of the properties that make up the cursor object.
-
Is "results" an object? How do you know?
- Yes it is an object because it is cursor.execute(), which is an object.
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 scores').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 2.4a uses SQLite database to input user data to create the new users. The create function of 2.4b uses SQLAlchemy to create the new users. 2.4a function can be better used for more simple projects where as 2.4b function can be used for more complex projects.
- Explain purpose of SQL INSERT. Is this the same as User init?
- SQL INSERT inserts new records. __init__ also creates new records.
import sqlite3
def create():
name = input("Enter your name:")
uid = input("Enter your user id:")
# 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 scores (_name, _uid) VALUES (?, ?)", (name, uid))
# 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?
- Verifies that the new password is at least two characters long. The code assumes that they were hacked if it is not. - Explain try/except, when would except occur?
- Try/except is like an if else statement. If the try doesn't work, the except occurs.
-
What code seems to be repeated in each of these examples to point, why is it repeated?
- Try/except is repeated. This is repeated because there are many different places where checking to ensure everything is inputted correctly is necessary.
import sqlite3
def update():
name = input("Enter user id to update")
# 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 scores SET _uid = ? WHERE _name = ?", (name))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No uid {name} was not found in the table")
else:
print(f"The row with user id {name} the password has been updated")
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 is a dangerous function because the records can not be recovered
- What is the "f" and {uid} do?
- "f" and {uid} is an f string, where uid is printed in a string
import sqlite3
def delete():
name = input("Enter user 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 scores WHERE _name = ?", (name,))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No uid {name} 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 {name} 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?
- The menu repeats because it is recursive.
- Could you refactor this menu? Make it work with a List?
- Yes, you can make it a list with crud methods
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")
Hacks
- Add this Blog to you own Blogging site. In the Blog add notes and observations on each code cell.
- Create a new Table or do something new, sqlite documentation
- In implementation in previous bullet, do you see procedural abstraction?
"""
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:///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)
""" 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 Score(db.Model):
__tablename__ = 'scores' # 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=False, nullable=False)
# constructor of a User object, initializes the instance variables within object (self)
def __init__(self, name, uid):
self._name = name # variables with self prefix become part of the object,
self._uid = uid
# 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')
# 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.add(self)
db.session.commit()
return self
# CRUD delete: remove self
# None
def delete(self):
db.session.delete(self)
db.session.commit()
return None
from random import randrange
def initScores():
with app.app_context():
"""Create database and tables"""
db.init_app(app)
db.create_all()
"""Tester data for table"""
u1 = Score(name='Grace', uid='8')
u2 = Score(name='Claire', uid='6')
u3 = Score(name='Claire', uid='3')
u4 = Score(name='Annika', uid='9')
u5 = Score(name='Mortensen', uid='4')
users = [u1, u2, u3, u4, u5]
"""Builds sample user/note(s) data"""
for user in users:
try:
'''add a few 1 to 4 notes per user'''
for num in range(randrange(1, 4)):
note = "#### " + user.name + " note " + str(num) + ". \n Generated by test data."
'''add user/post data to table'''
user.create()
except IntegrityError:
'''fails with bad or duplicate data'''
db.session.remove()
print(f"Records exist, duplicate email, or error: {user.uid}")
# initScores()
New Table
