Applied Programming/Databases/Python3 SQLite
databases.py
edit"""This program demonstrates SQLite3 database processing.
Input:
None
Output:
Sample data.
References:
https://en.wikiversity.org/wiki/Python_Programming/Databases
"""
import sqlite3
import sys
DATABASE = "users.db"
def execute_sql(sql):
"""Executes the given sql statement.
Args:
sql (string): A valid SQL statement to execute.
Returns:
None.
"""
try:
connection = sqlite3.connect(DATABASE)
except:
print(f"Unable to connect to {DATABASE}")
raise
try:
cursor = connection.cursor()
cursor.execute(sql)
connection.commit()
except Exception as exception:
print(f"Unable to execute {sql}")
print(exception)
finally:
connection.close()
def create_table():
"""Creates the Users table.
Args:
None.
Returns:
None.
"""
sql = "DROP TABLE IF EXISTS Users;"
execute_sql(sql)
sql = """
CREATE TABLE Users(
UserID INT PRIMARY KEY NOT NULL,
User TEXT NOT NULL
);
"""
execute_sql(sql)
def insert_users():
"""Inserts data into the Users table.
Args:
None.
Returns:
None.
"""
sql = "INSERT INTO Users(UserID, User) VALUES(1, 'Moe');"
execute_sql(sql)
sql = "INSERT INTO Users(UserID, User) VALUES(2, 'Larry');"
execute_sql(sql)
sql = "INSERT INTO Users(UserID, User) VALUES(3, 'Curly');"
execute_sql(sql)
def display_table():
"""Displays the Users table.
Args:
None.
Returns:
None.
"""
try:
connection = sqlite3.connect(DATABASE)
except:
print(f"Unable to connect to {DATABASE}")
raise
try:
cursor = connection.cursor()
sql = """
SELECT UserID, User FROM Users;
"""
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
print(row)
print()
except Exception as exception:
print(f"Error processing {sql}")
print(exception)
finally:
connection.close()
def update_user():
"""Updates the Users table.
Args:
None.
Returns:
None.
"""
sql = """
UPDATE Users
SET User = 'Shemp'
WHERE UserID = 3;
"""
execute_sql(sql)
def delete_user():
"""Deletes a record from the Users table.
Args:
None.
Returns:
None.
"""
sql = """
DELETE FROM Users
WHERE UserID = 3;
"""
execute_sql(sql)
def main():
"""Runs the main program logic."""
try:
print("Users")
create_table()
insert_users()
display_table()
print("Users After Update")
update_user()
display_table()
print("Users After Delete")
delete_user()
display_table()
except:
print("Unexpected error.")
print("Error:", sys.exc_info()[1])
print("File: ", sys.exc_info()[2].tb_frame.f_code.co_filename)
print("Line: ", sys.exc_info()[2].tb_lineno)
main()
Try It
editCopy and paste the code above into one of the following free online development environments or use your own Python3 SQLite compiler / interpreter / IDE.