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? Defines the way data is organized and also shows how it is accessed and changed by user inputs
- What is the purpose of identity Column in SQL database? Primary key to establish relationships between tables
- What is the purpose of a primary key in SQL database? Makes sure no two rows are the same
- What are the Data Types in SQL table? Some data types are integer, real, and text. They all have different requirements
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? Provides multiples methods which can be used to connect to a database, execute SQL statements, or manage a transaction
- Same for cursor object? The cursor object provides methods to execute SQL statements, fetch rows, and navigate the result set.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object? The connection object has attributes for the name of the connected database, the user used to connect to the database, the host or IP address of the database server, and the port number used to connect to the database server. The cursor object has attributes for the list of tuples that describe the columns in the current result set and the number of rows affected by the last SQL statement.
- Is "results" an object? How do you know? Yes there are attributes for 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 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. I added notes.
-
In this implementation, do you see procedural abstraction? Yes calculate_bill() gives an answer based off the input parameters.
-
In 2.4a or 2.4b lecture
-
Do you see data abstraction? Complement this with Debugging example. When debugging the menu and stepping into one of the hw_functions, it shows the code for them. It abstracts code.
-
Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
-
Reference... sqlite documentation
import sqlite3
class ArrivalTable:
def __init__(self):
self.connection = sqlite3.connect('database.db')
self.cursor = self.connection.cursor()
def create_table(self):
self.cursor.execute('''CREATE TABLE IF NOT EXISTS Arrivals
(name TEXT, house_number INT, arrival_time TEXT)''')
self.connection.commit()
def insert_data(self, name, house_number, arrival_time):
self.cursor.execute('''INSERT INTO Arrivals(name, house_number, arrival_time)
VALUES(?, ?, ?)''', (name, house_number, arrival_time))
self.connection.commit()
def close_connection(self):
self.connection.close()
# Usage example
arrival_table = ArrivalTable()
arrival_table.create_table()
name = input("Enter your name: ")
house_number = int(input("Enter your house number: "))
arrival_time = input("Enter your arrival time: ")
arrival_table.insert_data(name, house_number, arrival_time)
arrival_table.close_connection()
How the code works
The code uses the sqlite3 module to connect to a database file called database.db. It also defines a class called ArrivalTable which has methods for creating a table, inserting data into the table, and closing the connection to the database. Finally, the code creates an instance of the ArrivalTable class, prompts the user to input their information, and inserts it into the "Arrivals" table in the database.