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?
- A database schema is a blueprint or structure that defines how data is organized and stored in a database. It includes tables, columns, data types, relationships, constraints, and other attributes that define the database's structure and behavior.
- What is the purpose of identity Column in SQL database?
- An identity column is a column in a SQL database table that generates a unique value for each new row inserted into the table automatically. It is often used as a primary key or a unique identifier for the table. The purpose of an identity column is to ensure that each row in the table has a unique identifier without requiring the application to generate or manage it explicitly.
- What is the purpose of a primary key in SQL database?
- What are the Data Types in SQL table?
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?
- Same for cursor object?
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- Is "results" an object? How do you know?
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()
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()
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()
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 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.
"""
database = 'sqlite:///sqlite.db' # path and filename of database
# 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)
""" database dependencies to support sqliteDB examples """
from random import randrange
from datetime import date
import os, base64
import json
import sqlite3
# from __init__ import app, db
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 Python shell and follow along '''
# Define the Post class to manage actions in 'posts' table, with a relationship to 'users' table
class Post(db.Model):
__tablename__ = 'forumPosts'
# Define the Notes schema
id = db.Column(db.Integer, primary_key=True)
postTitle = db.Column(db.String(255), unique=True, nullable=False)
post = db.Column(db.String(255), unique=False, nullable=False)
# Define a relationship in Notes Schema to userID who originates the note, many-to-one (many notes to one user)
# Constructor of a Notes object, initializes of instance variables within object
def __init__(self, postTitle, post):
self.postTitle = postTitle
self.post = post
# Returns a string representation of the Notes object, similar to java toString()
# returns string
def __repr__(self):
return "Posts(" + str(self.id) + "," + self.postTitle + "," + self.post + "," + ")"
# 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,
"postTitle": self.postTitle,
"post": self.post
}
# CRUD update, updates a record in the Notes table
def update(self, postTitle="", post=""):
"""only updates values with length"""
if len(postTitle) > 0:
self.postTitle = postTitle
if len(post) > 0:
self.post = post
db.session.commit()
return self
# CRUD delete: remove self
# None
def delete(self):
db.session.delete(self)
db.session.commit()
return None
# 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
# Builds working data for testing
def initPost():
with app.app_context():
"""Create database and tables"""
# db.init_app(app)
db.create_all()
#! """Tester data for table"""
# p1 = Post(postTitle='First post', post='This is my first post')
# p2 = Post(postTitle='Second post', post='This is my second post')
# p3 = Post(postTitle='Third post', post='This is my third post')
# posts = [p1, p2, p3]
# #! """Builds sample user/note(s) data"""
# for post in posts:
# try:
# '''add a few 1 to 4 notes per user'''
# post.create()
# except IntegrityError:
# '''fails with bad or duplicate data'''
# db.session.remove(posts)
# return None
def initSports():
"""Create database and tables"""
with app.app_context():
# db.init_app(app)
db.create_all()
initSports()