# import the sqlite3 database module import sqlite3

print("SQLite Practice - Example User Table ") # create a connection to the database file conn = sqlite3.connect("myDatabase.db")

# create a cursor that we will use to move through the database cursor = conn.cursor()

# check if the user table already exists, if so, drop it so we can start with a new table cursor.execute("DROP TABLE IF EXISTS user;")

# create the table if it doesn't already exist # note that primary keys are automatically created in sqlit3 and referenced as rowid cursor.execute("CREATE TABLE user (first_name TEXT, last_name TEXT, email TEXT)")

# create some records of data cursor.execute("INSERT INTO user VALUES (\"Tony\", \"Stark\", \"ironman@stark.com\")") cursor.execute("INSERT INTO user VALUES (\"Carol\", \"Danvers\", \"marvel@stark.com\")")

# query the table including the rowid primary key value cursor.execute("SELECT rowid, first_name, last_name, email FROM user")

# store the results of a the query to a list called users users = cursor.fetchall()

# now we can loop through the results of the query for this_user in users: print(this_user[0], this_user[1], this_user[2], this_user[3])

# save the updates to the database - if you don't commit any updates/inserts to the database will not be saved conn.commit()

# close the connection conn.close()

Use Python

With the starting code file, build upon the program by doing the following:

Add a program title and consider the user experience throughout your output.

Modify the user table and create columns/fields to store the following information: first name, last name, email address, phone number, street address, city, state, zipcode.

Populate your table with at least 5 records of unique data. Feel free to keep to a theme like I have (Marvel) or whatever theme you would like.

Create a menu for the user to interact with your data by creating functions that will perform different SQL SELECT queries.

Create two report functions of your choice and comfort level. Some suggestions for report functions include listing all users first and last names, or creating a list of names and contact information. Create an associated item in the menu so the program user can run these reports.

Build an Add User function that asks for input for the following fields and inserts a new record/row of data into your SQLite database. Be sure to add this function to the program menu.

first name

last name

email address

phone number

street address

city

state

zipcode

Use your Add User function within the program to add 5 more user records to your database.

After having 10 users in your database table, test your report functions and make sure your data is printing out properly.

Be sure to include an option to EXIT the program. The program user should be able to keep working with the menu after every option until they choose to exit the program.

The example of the output:

Welcome to the MARVEL Universe database MENU 1 - List all NAMES in the user table 2 - List NAMES and email/phone for all users 3 - List NAMES and city/state for all users 4 - Add new User 5 - Exit the program Your choice: 1 All NAMES in the USER table Carol Danvers Tony Stark MENU 1 - List all NAMES in the user table 2 - List NAMES and email/phone for all users 3 - List NAMES and city/state for all users 4 - Add new User 5 - Exit the program Your choice: 2 NAMES and email/phone for all USERS Carol Danvers carol@marvel.com 555-222-1111 Tony Stark tony@marvel.com 555-222-1234 MENU 1 - List all NAMES in the user table 2 - List NAMES and email/phone for all users 3 - List NAMES and city/state for all users