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 dictates how data is organized, such as with tables, fields, JSON, etc.
- What is the purpose of identity Column in SQL database?
- The identity column helps identify different profiles in the database. It is unique to every profile, so it makes it able to distinguish users in the data.
- What is the purpose of a primary key in SQL database?
- The purpose of the primary key is to make the data unique. This allows each profile in the data to be distinguishable from each other.
- What are the Data Types in SQL table?
- Exact numbers, approximate numbers, date and time, character strings, unicode character strings, binary strings, 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('students')").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 each SQLite database. I think it is responsible for connecting to the database so it can create cursor objects and transaction control.
- Same for cursor object?
- Yes, cursor object allows the use of commands, and also fetches the data.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- conn.cursor() has attributes of special variables, function variables, class variables, in_transaction, isolation_level, row_factory, and total_changes. For the objects, all of the attributes say "none."
- Is "results" an object? How do you know?
- "Results can be an object because it prints out the entire database. I know this because in the debugger is has the [()] thing with all the rows and data in 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 students').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?
- Imperative:Has less code and is easier to work with than OOP. However, it is unable to check for garbage data such as in gradDate.- OOP: Has slightly more code and is somewhat harder to work with(less functions used). However, it is able to check for garbage data.
- Explain purpose of SQL INSERT. Is this the same as User init?
- SQL INSERT inserts the profile into the table(sqlite.db) with the parameters entered. It is the same as init.
import sqlite3
def create():
name = input("Enter your name:")
college = input("Enter your college:")
gpa = input("Enter your GPA:")
sat = input("Enter your SAT score:")
act = input("Enter your ACT score:")
gradDate = input("Enter your date of graduation('MM-DD-YYYY'):")
# 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 students (_name, _college, _gpa, _sat, _act, _gradDate) VALUES (?, ?, ?, ?, ?, ?)", (name, college, gpa, sat, act, gradDate))
# Commit the changes to the database
conn.commit()
print(f"A new user record {name} 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?
- If the new password inputted is less than 2 characters, the code detects it as a hack and a new password is automatically created for you.
- Explain try/except, when would except occur?
- Try attempts to execute the code under it. If there is an error with executing the code, except occurs and executes the below code. This is useful because you can write code that continues to run even after hitting a problem or error.
- What code seems to be repeated in each of these examples to point, why is it repeated?
- The code conn.cursor, cursor.execute, conn.commit, and cursor/conn.close is repeated. It is repeated because it constantly has to set up a cursor object and connection object. It has to be executed in order to do any actions, and must close at the end so that the code can end.
import sqlite3
def update():
name = input("Enter your name:")
college = input("Enter your college:")
gpa = input("Enter your GPA:")
sat = input("Enter your SAT score:")
act = input("Enter your ACT score:")
# 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 students SET _college = ?, _gpa = ?, _sat = ?, _act = ? WHERE _name = ?", (college, gpa, sat, act, name))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No name {name} was not found in the table")
else:
conn.commit()
print(f"Profile with name {name} has been updated")
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?
- It is dangerous because once you delete an object, the object is gone forever.
- In the print statemements, what is the "f" and what does {uid} do?
- 'f' makes the variables in the {var} available to use. Without 'f', {var} would not work. {uid} is the string that is fetched from the database, it displays the uid being deleted.
import sqlite3
def delete():
id = 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 students WHERE id = ?", (id,))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No id {id} was not found in the table")
else:
# The uid was found in the table and the row was deleted
print(f"The row with id {id} 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 the code never runs into an error.
- Could you refactor this menu? Make it work with a List?
- Yes. it could be CRUD = [c,r,u,d]
def menu():
cruds = ["c","r","u","d","s"]
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
if operation.lower() == cruds[0]:
create()
elif operation.lower() == cruds[1]:
read()
elif operation.lower() == cruds[2]:
update()
elif operation.lower() == cruds[3]:
delete()
elif operation.lower() == cruds[4]:
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. In menu, the function is called for CRUDS. Functions of connect, cursor, etc. are also being called.
- In 2.4a or 2.4b lecture
- Do you see data abstraction? Complement this with Debugging example.
- Yes. SQLite database is used. dictionaries are used. They are defined with parameters in init and are shown in columns.
- Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
Reference... sqlite documentation
