Database Programming is Program with Data
The Tri 2 Final Project is 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? describes the different rows and records of the data
- What is the purpose of a primary key in SQL database? The uid is treated as the key because that is what we use as the key in coding but the primary key is id (cen be achieved by making it unique). The data base primary key is the row or record number like 012345.
- What are the Data Types in SQL table?
A database schema is a blueprint or plan that outlines the structure of a database. It defines how data is organized, stored, and accessed within a database system. The schema includes a description of the tables, columns, relationships, and constraints that are used to store and manipulate data.
An identity column is a column in a SQL database that automatically generates a unique numeric value for each new row that is inserted into the table. It is commonly used as a primary key for the table and ensures that each row has a unique identifier.
A primary key in a SQL database is a column or set of columns that uniquely identifies each row in a table. It is used to enforce data integrity by ensuring that each row is uniquely identifiable and cannot be duplicated. The primary key is often used as a foreign key in other tables to establish relationships between data.
SQL table data types define the type of data that can be stored in a particular column of a table. Common data types in SQL include:
Integer: stores whole numbers
Float/Double: stores floating-point numbers with decimal values
Char/Varchar: stores strings of characters with a fixed or variable length
Date/Time: stores date and time values
Boolean: stores true/false values
Binary: stores binary data such as images or files.
</div> </div> </div>import sqlite3
#change to database='instance/sqlite.db'
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)
# a schema is a different data base property
# Close the database connection
#meta data is a data that describes data
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? Connection object is the sqlite3.db and it makes a conn which is a variable data type. The conn extanciates the object which connects to the database. And connects to it in order for it to interact with the database.
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?
A connection object is an object used in programming to connect to a database. After researching, I believe that a connection object is responsible for establishing a connection to a database and provides methods for executing SQL statements and managing transactions.
Similarly, a cursor object is an object used in programming to interact with the results of a SQL query. It allows you to iterate over the rows returned by a query and perform operations such as updating or deleting data.
The attributes of a connection object and a cursor object can vary depending on the specific programming language and database being used. However, common attributes of a connection object in Python's sqlite3 module include database, row_factory, and isolation_level. Common attributes of a cursor object include description, rowcount, and lastrowid.
"Results" alone is not enough information to determine whether it is an object or not. It could be a variable or a function, for example. However, if "results" refers to the output of a SQL query, then it is likely an object, such as a list of tuples or a pandas DataFrame.
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 cars').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()
The create() function in SQL can vary depending on the specific database management system being used. However, generally speaking, the CREATE TABLE statement is used to create a new table in a database. In the two SQL lessons, both implementations use the CREATE TABLE statement to create a new table. One difference between the two implementations is that in the first lesson, the data types for each column are specified explicitly, while in the second lesson, the data types are inferred based on the values in the first row of the table. This can be an advantage in the second implementation because it allows for more flexibility in the data types used in the table. On the other hand, explicitly specifying the data types can help ensure data integrity and prevent errors.
The purpose of the SQL INSERT statement is to insert new rows of data into a table. It is used to add new data to an existing table or create a new table with initial data. The INSERT statement allows you to specify the values to be inserted for each column in the table. This is different from the init method in object-oriented programming, which is used to initialize an object's attributes with specific values when the object is created. The INSERT statement is used to add data to a table, while init is used to initialize an object's attributes with specific values.
import sqlite3
def create():
license = input("Enter your license plate:")
maker = input("Enter your car maker:")
model = input("Enter your car model:")
price = input("Enter the price of your car:")
color = input("Enter the color of your car:")
# 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 cars (_license, _maker, _model, _price, _color) VALUES (?, ?, ?, ?, ?)", (license, maker, model, price, color))
# Commit the changes to the database
conn.commit()
print(f"A new car record {license} 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():
license = input("Enter license to update")
maker = input("Enter updated maker")
if len(maker) < 2:
message = "error"
maker = 'xxx'
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 cars SET _maker = ? WHERE _license = ?", (maker, license))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No license {license} was not found in the table")
else:
print(f"The row with license {license} the maker 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()
The DELETE operation in SQL can be dangerous if not used carefully, as it permanently removes data from a table. If you accidentally delete data that is important or critical to your application, it can result in data loss and potential business disruption. It is important to always use caution when performing DELETE operations and to have proper backups in place in case of accidental data loss.
In the context of a Python script, the "f" likely stands for "format string," which is a way of dynamically creating strings that contain variable values. The {uid} is a placeholder for the value of the uid variable, which is a user input. By using an f-string and placing the variable inside curly braces, the value of the variable is automatically substituted into the string at runtime. In other words, the f-string allows you to create a dynamic SQL statement that includes the user input as a parameter. This can help prevent SQL injection attacks and improve the security of the application.
import sqlite3
def delete():
license = input("Enter license 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 cars WHERE _license = ?", (license,))
if cursor.rowcount == 0:
# The not found
print(f"No license {license} was not found in the table")
else:
# The uid was found in the table and the row was deleted
print(f"The row with license {license} 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.
- Create a new Table or do something new, sqlite documentation
- In implementation in previous bullet, do you see procedural abstraction?