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?
A database schema defines what the categories is in a column.
- What is the purpose of identity Column in SQL database?
An identity column is used to know more about the data and a way to easly access a database.
- What is the purpose of a primary key in SQL database?
The purpose of a primary key in SQL database is to easily identify an object. Id is a good primary key because an id is unique.
- What are the Data Types in SQL table?
Almost any type of data type can work (lists, structures, dictionaries, etc.)
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('userspeople')").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 is a uniques session for a database. I think it is used to record pieces of data in its own database.
- Same for cursor object?
A cursor object is used to set different objects in rows or under categories of a table.
- Look at connect object and cursor object in VSCode debugger. What attributes are in the object?
The attributes of the objects inform the reader of the status of a database and the objects and properties inside of it.
- Is "results" an object? How do you know?
Results is an object because it ends up with data and displaying it.
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 userspeople').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
- Compore create() in both SQL lessons. What is better or worse in the two implementations?
Both versions do the same thing but this example might be better because it is better at dealing with errors with its try/except statement. This results in the database having clearer data.
- Explain purpose of SQL INSERT. Is this the same as User init?
The purpose of SQL INSERT is to add new data items into a database. This is very similar to init because both involve assigning new variables.
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 userspeople (_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 is used to tell the user that their password was not good enough and would result in them easily getting hacked by an online attacker.
- Explain try/except, when would except occur?
Try / Except: Try/expect first tries to do something in the try section but if that section of code results in an error, the expect section is ran.
- What code seems to be repeated in each of these examples to point, why is it repeated?
The code repeated on the dot are some of the last two lines, cursor.close() and conn.close(). These are used to close the server session because the action is now complete and the session has no more use because the code can open it again later if needed.</p>
</div>
</div>
</div>
Uses a delete function to remove a user based on a user input of the id. DELETE is a dangerous operation because it can permanently remove data that could have been useful and/or needed. "f" is used to format the following in the print statement into a string. {uid} is used to display the string of the uid that was inserted. CRUD and Schema interactions from one location by running menu. Observe input at the top of VSCode, observe output underneath code cell. The menu repeats to constantly show the programmer that all the operations are running. If they are not, the code with stop running and will tell the user to run all the functions written before it. This menu can be formated to make it work as a list because it will still use if,elif,else statements to determine what to make of the characters in the list. Procedural abstaraction is not present in this implementation because none of the functions used in the code set parameters and the actions inside of the functions only use if,elif,else statements when dealing with fixed or previously set up variables/values. In 2.4a or 2.4b lecture Data abstraction is present in the creation of these tables. The data can be shown using debugging. Reference... sqlite documentationimport 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
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
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
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
database = 'sqlite:///sqlite.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = database
app.config['SECRET_KEY'] = 'SECRET_KEY'
db = SQLAlchemy()
db.init_app(app)
import json
from sqlalchemy.exc import IntegrityError
class food(db.Model):
__tablename__ = 'fooditems'
id = db.Column(db.Integer, primary_key=True)
_name = db.Column(db.String(255), unique=True, nullable=False)
_points = db.Column(db.String(255), unique=False, nullable=False)
_image = db.Column(db.String(255), unique=True, nullable=False)
def __init__(self, name, points, image):
self._name = name
self._points = points
self._image = image
@property
def name(self):
return self._name
@name.setter
def name(self, name):
self._name = name
@property
def points(self):
return self._points
@points.setter
def points(self, points):
self._points = points
@property
def image(self):
return self._image
@image.setter
def image(self, image):
self._image = image
def __str__(self):
return json.dumps(self.read())
def create(self):
try:
db.session.add(self)
db.session.commit()
return self
except IntegrityError:
db.session.remove()
return None
def read(self):
return {
"id": self.id,
"name" : self.name,
"points" : self.points,
"image" : self.image
}
def update(self, name="", points="", image=""):
"""only updates values with length"""
if len(name) > 0:
self.name = name
if len(points) > 0:
self.restaurant = points
if len(image) > 0:
self.protein = image
db.session.commit()
return self
def delete(self):
db.session.delete(self)
db.session.commit()
return None
def initfooditem():
with app.app_context():
db.create_all()
u1 = food(name='Burger', points='10', image='https://png.pngtree.com/png-vector/20190130/ourlarge/pngtree-cute-minimalist-creative-cartoon-hamburger-png-image_611163.jpg')
u2 = food(name= 'Fries', points='20', image='https://thumbs.dreamstime.com/b/french-fries-cartoon-clipart-red-paper-box-carton-121897301.jpg')
fooditems = [u1, u2]
for h in fooditems:
try:
h.create()
except IntegrityError:
db.session.remove()
print(f"Records exist, duplicate, or error: {food.id}")
initfooditem()
def read():
with app.app_context():
table = food.query.all()
json_ready = [food.read() for food in table]
return json_ready
read()
def locateitem(name):
with app.app_context():
item = food.query.filter_by(name=name).first()
return item
def create():
name = input("Enter name of item")
item = locateitem(name)
try:
print(user.read())
return
except:
pass
points = input("Enter points value")
image = input("Enter link of image")
user = food(name=name,
points=points,
image=image
)
with app.app_context():
try:
newitem = user.create()
print(newitem.read())
except:
print("Unknown error uid {uid}")
create()
def locateitem(name):
with app.app_context():
item = food.query.filter_by(name=name).first()
return item
def update():
name = input("Enter name of item")
item = locateitem(name)
try:
print(item.read())
pass
except:
return("Unknown Name")
points = input("Enter new points")
image = input("Enter link of image")
with app.app_context():
try:
updateditem = item.update(name, points, image)
print(updateditem.read())
except: # error raised if object not created
print("Unknown error uid {uid}")
update()
def locateitem(name):
with app.app_context():
item = food.query.filter_by(name=name).first()
return item
def delete():
name = input("Enter name of item")
item = locateitem(name)
try:
print(item.read())
pass
except:
return("Unknown Name")
with app.app_context():
try:
item.delete()
print("item has been deleted")
except:
print("Unknown error uid {uid}")
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:
return
else:
print("Please enter c, r, u, or d")
menu()
try:
menu()
except:
print("Perform Jupyter 'Run All' prior to starting menu")