Anaconda Python, Spyder language used. Here is a start of a registration functio

Question

Anaconda Python, Spyder language used.

Here is a start of a registration function that creates a connection to the database and inserts specific values into the DB. Be sure to read the comments for hints

and explanations. Be sure before you try this that you save the file in the same location you have saved your database file. Also note that EmployeeID is the

primary key for the Employee table and thus must be unique...if you try to insert the same EmployeeID more than once your program will crash.

------code-------

import sqlite3

conn = sqlite3.connect("OS_Employee.db")

with conn:

cur = conn.cursor()

try:

cur.execute("""INSERT INTO Employee VALUES

('1057', 'leslie', 'albert', 'leslie@asd.com', 'leslie')""")

cur.execute("SELECT * FROM Employee WHERE (EmployeeID = '1057')")

results = cur.fetchall()

print(results)

except:

print("Connection failed")

------code------

What’s left to do:

1. Alter the code above to take the 5 values from the user as input, add your error checking for blanks (while not StringName

2. Use the .strip, .lower, .title where appropriate (note: In the database, the first and last names start with capital letters, the rest are all lower case)

3. Include a loop that will catch a repeated EmployeeID to keep our project from crashing. To do this, take the EmployID from the user, execute a Count

query against the database to see if that particular id is already in use. If it has been used (count = 1) loop and ask for another id from the user. If the

count = 0 then continue with your Insert.

4. Be sure to provide helpful prompts and feedback to your users!

5. Once it’s all tested and working well, tuck all of your registration code into a function.

I got this working so far.

import sqlite3

conn = sqlite3.connect('OS_Employee.db')

def get_values():

   emp_id()

   firstname()

   lastname()

   email()

def email():

   user_email = input("Please enter your email: ")

   print(user_email)

def lastname():

   l_name = input("Please enter your last name: ")

   l_name = l_name.lower().strip(" ").title()

   print(l_name)

def firstname():

   f_name = input("Please enter your first name: ")

   f_name = f_name.lower().strip(" ").title()

   print(f_name)

   return f_name

def emp_id():

   while True:

      try:

          emp_id = int(input("Please enter your employee ID: "))

          while len(str(emp_id)) != 4:

              print("Employee ID can only be 4 digits in length!")

              emp_id = int(input("Please enter your employee ID again: "))

          break

      except ValueError:

          print("Enter only numbers.")

   print(emp_id)

get_values()

Explanation / Answer

DROP DATABASE databaseName -- Delete the database (irrecoverable!) DROP DATABASE IF EXISTS databaseName -- Delete if it exists CREATE DATABASE databaseName -- Create a new database CREATE DATABASE IF NOT EXISTS databaseName -- Create only if it does not exists SHOW DATABASES -- Show all the databases in this server USE databaseName -- Set the default (current) database SELECT DATABASE() -- Show the default database SHOW CREATE DATABASE databaseName -- Show the CREATE DATABASE statement -- Table-Level DROP TABLE [IF EXISTS] tableName, ... CREATE TABLE [IF NOT EXISTS] tableName ( columnName columnType columnAttribute, ... PRIMARY KEY(columnName), FOREIGN KEY (columnNmae) REFERENCES tableName (columnNmae) ) SHOW TABLES -- Show all the tables in the default database DESCRIBE|DESC tableName -- Describe the details for a table ALTER TABLE tableName ... -- Modify a table, e.g., ADD COLUMN and DROP COLUMN ALTER TABLE tableName ADD columnDefinition ALTER TABLE tableName DROP columnName ALTER TABLE tableName ADD FOREIGN KEY (columnNmae) REFERENCES tableName (columnNmae) ALTER TABLE tableName DROP FOREIGN KEY constraintName SHOW CREATE TABLE tableName -- Show the CREATE TABLE statement for this tableName -- Row-Level INSERT INTO tableName VALUES (column1Value, column2Value,...) -- Insert on all Columns INSERT INTO tableName VALUES (column1Value, column2Value,...), ... -- Insert multiple rows INSERT INTO tableName (column1Name, ..., columnNName) VALUES (column1Value, ..., columnNValue) -- Insert on selected Columns DELETE FROM tableName WHERE criteria UPDATE tableName SET columnName = expr, ... WHERE criteria SELECT * | column1Name AS alias1, ..., columnNName AS aliasN FROM tableName WHERE criteria GROUP BY columnName ORDER BY columnName ASC|DESC, ... HAVING groupConstraints LIMIT count | offset count

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.