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 a blueprint or a logical representation of the entire database. It defines how the data is organized, how the tables relate to each other, and what constraints are placed on the data. A database schema is essential for ensuring data consistency, accuracy, and security.
- What is the purpose of identity Column in SQL database? In SQL databases, an identity column is a column that is used to automatically generate a unique value for each row in a table. It is often used as a primary key or as a surrogate key to uniquely identify each row in the table. The values of an identity column are generated automatically by the database management system (DBMS), typically using a sequence or an auto-incrementing integer.
- What is the purpose of a primary key in SQL database? In SQL databases, a primary key is a column or a set of columns that uniquely identifies each row in a table. The primary key is used to enforce data integrity by ensuring that no two rows in the table have the same values for the primary key. The primary key is also used to create relationships between tables, such as foreign key constraints.
- What are the Data Types in SQL table? Could be a couploe: integer, boolean, blob, 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('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?
- an object that represents a connection to a database. After searching, I found that it is a Python object that is returned by a database driver when a connection to the database is established. It is used to execute queries and commands against the database, and also provides access to transaction management and other features.
- Same for cursor object?
- an object used to interact with a database by executing SQL queries and fetching the results. After searching, I found that it is created from a connection object and is used to execute SQL statements against the database. It provides methods to fetch results, iterate over result sets, and access metadata about the query.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- The attributes of a connection object and a cursor object can vary depending on the specific database and driver being used. In general, however, a connection object may have attributes such as the connection string, the database name, the server hostname and port, the username and password used to authenticate, and the transaction isolation level. A cursor object may have attributes such as the SQL statement being executed, the number of rows affected by the last operation, and the result set being returned.
- Is "results" an object? How do you know?
- "results" is likely not an object itself, but rather a variable or data structure that contains the results of a query executed using a cursor object. Depending on the specific database and driver being used, the results may be returned as a list of tuples, a pandas DataFrame, or some other data structure. To know for sure, we would need to inspect the code or documentation that defines the "results" variable.
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()
Updating a User in table in Sqlite.db
Uses SQL UPDATE to modify password
- What does the hacked part do?
- Without more context, it's difficult to say what the "hacked part" refers to. Please provide more information or context so that I can answer your question more accurately.
- Explain try/except, when would except occur?
- try/except is a control flow structure used in Python to handle exceptions that may occur during program execution. The try block contains code that may raise an exception, while the except block contains code that will be executed if an exception is raised. The purpose of using try/except is to provide a way to gracefully handle errors and prevent the program from crashing or exiting unexpectedly. The except block will occur when an exception is raised within the try block.
- What code seems to be repeated in each of these examples to point, why is it repeated?
- It appears that the code repeated in each of these examples is the conn.cursor() method call. This is because a cursor object is required in order to execute SQL queries against the database. By creating a new cursor object for each query, the code is able to execute multiple queries against the database without overwriting the results of previous queries. Additionally, creating a new cursor object can help ensure that each query is executed in a new transaction, which can be useful for maintaining consistency and isolation in the data.
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()
read()
update()
read()
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()
read()
delete()
read()
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