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?
- What is the purpose of identity Column in SQL database?
- 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() # This is an object, it basically has the same basic functionality of an object in OOP programming
# 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))
# Inserts data for name, uid, password, and date of birth, '(?, ?, ?, ?)' is the amount of rows/items there are
# 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()
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.
- In this implementation, do you see procedural abstraction?
- Yes, different procedures are called to run the program.
- In 2.4a or 2.4b lecture
- Do you see data abstraction? Complement this with Debugging example.
- Yes, there is data abstraction when displaying the database, it only shows the file name and not the information in the file, when updating.
- Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
- Do you see data abstraction? Complement this with Debugging example.
Reference... sqlite documentation
import sqlite3
from sqlite3 import Error
import datetime
def create_connection(db_file):
""" create a database connection to the SQLite database
specified by db_file
:param db_file: database file
:return: Connection object or None
"""
conn = None
try:
conn = sqlite3.connect(db_file)
return conn
except Error as e:
print(e)
return conn
def create_table(conn, create_table_sql):
""" create a table from the create_table_sql statement
:param conn: Connection object
:param create_table_sql: a CREATE TABLE statement
:return:
"""
try:
c = conn.cursor()
c.execute(create_table_sql)
except Error as e:
print(e)
def main():
database = 'pythonsqlite.db'
# sends command to sqlite, tells sqlite to create a table if one does not already exist
sql_create_people_table = """ CREATE TABLE IF NOT EXISTS people (
socSecNum integer PRIMARY KEY,
name text NOT NULL,
birth_date text,
age integer
); """
# create a database connection
conn = create_connection(database)
# create tables
if conn is not None:
# create projects table
create_table(conn, sql_create_people_table)
else:
print("Error! cannot create the database connection.")
current_datetime=datetime.datetime.now()
ssn = int(current_datetime.strftime("%f%M%S")) # Uses datetime to randomly generate a social security num
names = ['Mayoi Ayase', 'Yuzuru Fushimi'] # Names that are added into the table
with conn:
# create a new project
for i in range(len(names)): # loops data adding
n = ssn + i
person1 = (str(n), names[i], '2004-06-06', '18') # ands the date of birth and age
person_id = create_person(conn, person1)
print('Created person ', person_id)
#person2 = ('7841956', 'Yuzuru Fushimi', '2004-08-18', '18')
#person_id = create_person(conn, person2)
# create person
#create_person(conn, person1)
# create_person(conn, person2)
def create_person(conn, person): # creates a new person
"""
Create a new person into the people table
:param conn:
:param people:
:return: person id
"""
sql = ''' INSERT INTO people(socSecNum, name, birth_date, age)
VALUES(?,?,?,?) '''
cur = conn.cursor() # connects to cursor
cur.execute(sql, person)
conn.commit()
return cur.lastrowid # puts data into the table
if __name__ == '__main__':
main()
Answers, Notes, etc.
- What is a connection object? After you google it, what do you think it does?
- Connection objects are unique sessions with a datasource. These make it so that each action is a unique instance.
- Same for cursor object?
- The object used to make a connection between the code and the SQLite database, and send commands to it.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- The objects are used to read, update, delete, and create other items.
-
Is "results" an object? How do you know?
- Results has data in it, and different types/attributes.
-
Compare create() in both SQL lessons. What is better or worse in the two implementations?
- Object-oriented programming is better, because it takes less code, and is therefore more efficient.
- Explain purpose of SQL INSERT. Is this the same as User init?
- SQL insert inserts new data into the database.
- What does the hacked part do?
- The 'hacked' part of the procedure changes the password, if 2 characters or under, to 'hacked'.
- Explain try/except, when would except occur?
- Try/Except are much like 'if/else', except they prompt the code to take alternative actions if the specifications are not met, unlike elif statements.
- What code seems to be repeated in each of these examples to point, why is it repeated?
- All of the code seems to repeat 'connect', 'cursor', and 'results', because each function is necessary for each code block to run different functions.
- Is DELETE a dangerous operation? Why?
- Deleting data makes it unrecoverable, and it cannot be retrieved again.
- What is the "f" and {uid} do?
- UID is the user ID, which allows for the user to be identified in the program.
- Why does the menu repeat?
- The menu repeats because it links each code cell to each other to create options for the user.
- Could you refactor this menu? Make it work with a List?
- Yes, the code can be modified so that each command is part of a list, and it can switch each word to initiate the code sequences if they are part of that list.