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 is the structure of a database described in a formal language supported by the database management system (DBMS). It is considered the “blueprint” of a database which describes how the data may relate to other tables or other data models. The schema defines how data is organized within a relational database, including logical constraints such as table names, fields, data types, and the relationships between these entities.
- What is the purpose of identity Column in SQL database?
An identity column in SQL is a column that is defined to be automatically generated by the database management system (DBMS) when a new record is inserted into the table. The purpose of an identity column is to provide a unique value for each row in the table. It is often used as a primary key for the table. The identity column is also known as an auto-increment column.
- What is the purpose of a primary key in SQL database?
A primary key in SQL is a column or a set of columns that uniquely identifies each row in a table. The purpose of a primary key is to ensure that each row in a table can be uniquely identified and to enforce the integrity of the data in the table. A primary key can be used to link tables together in a relational database.
- What are the Data Types in SQL table?
Numeric data types: such as int, float, and decimal. Character data types: such as char, varchar, and text. Date and time data types: such as date, time, and datetime. Binary data types: such as binary, varbinary, and image.
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()
Notes and Observations #1
-
Imports the sqlite3 module and defines a variable database that stores the path to an SQLite database file.
-
schema function is defined which establishes a connection to the database specified in database variable using
sqlite3.connect. Creates a cursor object usingconn.cursor()to execute SQL queries.-
Executes the SQL statement
"PRAGMA table_info('users')"which retrieves information about the users table. -
fetchall(): called on the cursor object to get all rows from the result set. -
for loop iterates through the result set and prints each row using the
print()function. -
Closes the database connection using
conn.close().
-
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 in SQL is an object that represents a connection to a database. It is used to establish a connection to a database and to manage the connection. The connection object is created by passing a connection string to the constructor of the object. The connection string contains information about the database to connect to, such as the server name, database name, and authentication credentials. Once the connection object is created, it can be used to execute SQL commands against the database.
- Same for cursor object?
A cursor object in SQL is an object that allows you to process rows individually or traverse over the rows. It is a database object that is used to retrieve and manipulate rows from a result set one at a time. Cursors are useful when you need to perform operations on each row of a result set rather than on the set as a whole.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
The conn object is an instance of the sqlite3.Connection class and represents a connection to an SQLite database. It has attributes like autocommit, in_transaction, isolation_level, row_factory, text_factory, and total_changes. The cursor object is an instance of the sqlite3.Cursor class, which is used to execute SQL statements and interact with the database. It has attributes like arraysize, connection, description, lastrowid, rowcount, and statement. In the given code, the cursor object is used to execute the SQL query "SELECT * FROM users" and retrieve all rows using the fetchall() method. It then checks if the result set is empty and prints each row if not. Finally, it closes the cursor and conn objects using the close() method.
- Is "results" an object? How do you know?
Results is an object. It is a list of tuples containing the rows returned by the SQL query executed using the cursor object. The fetchall() method returns a list of tuples, where each tuple represents a row in the result set. Each tuple contains values for each column in the table. In the code segment, results is assigned the value returned by the fetchall() method called on the cursor object after executing the SQL statement "SELECT * FROM users". The if statement checks if the length of results is 0, indicating an empty table, or if there are rows in the table, in which case each row is printed using a for loop.
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()
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? Explain purpose of SQL INSERT. Is this the same as User init?
The purpose of the SQL INSERT statement is to add a new row to the users table in the SQLite database, with values provided by the user. The INSERT statement is used to specify the values to be inserted into each column of the table.The statement INSERT INTO users (_name, _uid, _password, _dob) VALUES (?, ?, ?, ?) is a parameterized SQL statement that specifies the column names and the values to be inserted. The ? placeholders are used to specify the values to be inserted, which are provided as a tuple in the execute() method call.The INSERT statement is not the same as the init method used in classes to initialize object attributes. The init method is called when an object is created, whereas the INSERT statement is executed on the database to add a new row. In this code segment, the create function prompts the user to input values for the name, uid, password, and dob attributes of the new user record. The INSERT statement is then executed using these values, and if the operation is successful, a message is printed to indicate that a new user record has been created. If an error occurs while executing the INSERT statement, an error message is printed instead.
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()
Updating a User in table in Sqlite.db
Uses SQL UPDATE to modify password
- What does the hacked part do?
- Explain try/except, when would except occur?
- What code seems to be repeated in each of these examples to point, why is it repeated?
The hacked part in the update() function is a simple check for the length of the user-provided password. If the password is less than 2 characters long, the message variable is set to "hacked", and a default password "gothackednewpassword123" is used instead of the user-provided password. This is an example of a simple data validation technique to prevent weak passwords. The try/except block is used to handle any errors that occur while executing the SQL UPDATE statement. If an error occurs, the except block is executed, which prints an error message along with the details of the error. The try/except block is used here to catch any potential errors that might occur when executing the UPDATE statement, such as an invalid SQL syntax, a database connection error, or a constraint violation. The except block would occur when an error is encountered during the execution of the UPDATE statement. This might be due to various reasons such as an incorrect SQL syntax, a database connection error, a violation of a database constraint, or an invalid value being passed to the execute() method. The code that is repeated in each example is the connection and cursor initialization for interacting with the database. This is repeated to ensure that a new connection and cursor object are created for each database operation. It is important to close the connection and cursor objects properly after use to avoid resource leaks and improve performance.
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()
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? In the print statemements, what is the "f" and what does {uid} do?
The DELETE operation can be considered dangerous if used without proper caution since it permanently removes data from the database. It is a powerful command that can delete data from the database in one shot, and if not used carefully, can result in the loss of valuable information. The "f" in the print statement stands for "formatted string literal" and is a way to embed expressions inside string literals. The {uid} inside the string is a placeholder that gets replaced with the value of the uid variable at runtime. This technique is used to create dynamic strings that can contain variable values, making the output more readable and informative. In this case, the {uid} placeholder is used to print the user ID value that was used for the DELETE operation.
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
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? Could you refactor this menu? Make it work with a List?
The menu repeats because it is implemented recursively, i.e., the function calls itself again after each operation is performed. This causes the menu to be displayed again and again until the user chooses to exit. To refactor the menu, we can use a list to store the available operations and their corresponding functions. This way, we can avoid repetitive code and make the menu more scalable, in case we want to add more operations in the future.
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?
- In 2.4a or 2.4b lecture
- Do you see data abstraction? Complement this with Debugging example.
- Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
Reference... sqlite documentation