SHARE
TWEET

Untitled

a guest Dec 9th, 2018 51 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. # =============#
  2. #    imports   #
  3. # =============#
  4. import sqlite3 #for working with sqlite version 3 databases
  5.  
  6. # ===============#
  7. #    constants   #
  8. # ===============#
  9. SQL_FILE = "assignment_6.01-JonathanMaldonado.db"
  10. SQL_CREATE_TABLE = """
  11. CREATE TABLE person
  12. (
  13.   person_id INTEGER PRIMARY KEY,
  14.   first_name TEXT,
  15.   middle_name TEXT,
  16.   age TEXT,
  17.   favorite_thing TEXT
  18. )"""
  19. SQL_DELETE_ASSIGNMENT = """
  20. DELETE FROM person
  21. WHERE person_id = ?
  22. """
  23. SQL_INSERT_ASSIGNMENT = """
  24. INSERT INTO person
  25.   (first_name,middle_name,age,favorite_thing)
  26. VALUES
  27.   (?,?,?,?)
  28. """
  29. SQL_SELECT_ASSIGNMENTS = """
  30. SELECT
  31.   person_id,
  32.   first_name,
  33.   middle_name,
  34.   age,
  35.   favorite_thing
  36. FROM person
  37. """
  38. SQL_UPDATE_ASSIGNMENT = """
  39. UPDATE person
  40. SET age = ?
  41. WHERE person_id = ?
  42. """
  43. # ===============#
  44. #    functions   #
  45. # ===============#
  46. def createPersonTable(dbConnection):
  47.   """ creates an assignment database table """
  48.  
  49.   #fill in the function to create the table
  50.   #the SQL statement is provided as a constant
  51.   dbCursor = dbConnection.cursor()
  52.   dbCursor.execute(SQL_CREATE_TABLE)
  53.   dbCursor.close()
  54.  
  55. def deleteAssignmentByID(dbConnection,AGE):
  56.   """ deletes assignment records by their ID """
  57.  
  58.   #fill in the function to delete a record from the assignment table
  59.   #the SQL statement is provided as a constant
  60.   #be sure to parameterize as needed and check that 1 record was deleted
  61.   dbCursor = dbConnection.cursor()
  62.   arguments = [
  63.       AGE,
  64.   ]
  65.   dbCursor.execute(SQL_DELETE_ASSIGNMENT,arguments)
  66.  
  67.   #make sure the record was deleted successfully
  68.   numberOfRecordsInserted = dbCursor.rowcount
  69.   if(numberOfRecordsInserted != 1):
  70.     errorMessage = "{} records were inserted when there should have been 1"
  71.     raise RuntimeError(errorMessage.format(numberOfRecordsInserted))
  72.  
  73.   #close the cursor
  74.   dbCursor.close()
  75.  
  76. def insertAssignment(dbConnection,person):
  77.   """ inserts assignment records one at a time """
  78.  
  79.   #fill in the function to insert a record
  80.   #the SQL statement is provided as a constant
  81.   #be sure to parameterize as needed and check that 1 record was inserted
  82.   dbCursor = dbConnection.cursor()
  83.   arguments = [
  84.       person["first_name"],
  85.       person["middle_name"],
  86.       person["age"],
  87.       person["favorite_thing"],
  88.   ]
  89.   dbCursor.execute(SQL_INSERT_ASSIGNMENT,arguments)
  90.  
  91.   #make sure the record was inserted successfully
  92.   numberOfRecordsInserted = dbCursor.rowcount
  93.   if(numberOfRecordsInserted != 1):
  94.     errorMessage = "{} records were inserted when there should have been 1"
  95.     raise RuntimeError(errorMessage.format(numberOfRecordsInserted))
  96.  
  97.   #close the cursor
  98.   dbCursor.close()
  99.  
  100.  
  101. def selectAllAssignments(dbConnection):
  102.   """ returns a list of all assignment records """
  103.  
  104.   #fill in the function to return a list of assignment records
  105.   #the SQL statement is provided as a constant
  106.   dbCursor = dbConnection.cursor()
  107.   dbCursor.execute(SQL_SELECT_ASSIGNMENTS)
  108.  
  109.   #build list of assignment records
  110.   persons = []
  111.   for record in dbCursor:
  112.     person = {
  113.         "person_id": record[0],
  114.         " first_name": record[1],
  115.         "middle_name": record[2],
  116.         "age": record[3],
  117.         "favorite_thing": record[4]
  118.  
  119.     }
  120.     persons.append(person)
  121.  
  122.   #close the cursor and return the list
  123.   dbCursor.close()
  124.   return persons
  125.  
  126. # ==========#
  127. #    main   #
  128. # ==========#
  129. def main():
  130.   """ main method """
  131.  
  132.   #connect to the database using the file name provided as a constant
  133.   dbConnection = sqlite3.connect(SQL_FILE)
  134.  
  135.   #dropping table in case you need to re-run this multiple times
  136.   #no need to change this part
  137.   dbCursor = dbConnection.cursor()
  138.   try:
  139.     dbCursor.execute("DROP TABLE IF EXISTS person")
  140.   except Exception as e:
  141.     dbConnection.close()
  142.     raise #stop the rest of the script
  143.  
  144.  
  145.   #create the assignment table
  146.   #use the createAssignmentTable function and wrap in an appropriate try block
  147.   try:
  148.     createPersonTable(dbConnection)
  149.   except Exception as e:
  150.     #close the connection and stop
  151.     dbConnection.close()
  152.     raise #stop the rest of the script
  153.  
  154.  
  155.   #loop through the following assignment records and insert them
  156.   #use the insertAssignment function and wrap in an appropriate try block
  157.   persons = [
  158.       {
  159.           "first_name": "Demi",
  160.           "middle_name": "Rose",
  161.           "age": "22",
  162.           "favorite_thing": "Cute and funny",
  163.  
  164.       },
  165.       {
  166.           "first_name": "Esmeralda",
  167.           "middle_name": "Arciga",
  168.           "age": "48",
  169.           "favorite_thing": "Lovly Mother",
  170.       },
  171.       {
  172.           "first_name": "Dead",
  173.           "middle_name": "Pool",
  174.           "age": "32",
  175.           "favorite_thing": "Superhero",
  176.       }
  177.   ]
  178.   for person in persons:
  179.     try:
  180.       insertAssignment(dbConnection,person)
  181.     except Exception as e:
  182.       #roll back the transaction and stop
  183.       dbConnection.rollback()
  184.       dbConnection.close()
  185.       raise #stop the rest of the script
  186.     else:
  187.       #commit the transaction
  188.       dbConnection.commit()
  189.  
  190.   #select all of the assignment records and store in a variable
  191.   #use the selectAllAssignments function and wrap in an appropriate try block
  192.   try:
  193.     assignmentRecords = selectAllAssignments(dbConnection)
  194.   except Exception as e:
  195.     #roll back the transaction and stop
  196.     dbConnection.rollback()
  197.     dbConnection.close()
  198.     raise #stop the rest of the script
  199.   else:
  200.     pass #no need to commit since it was just a select
  201.  
  202.   #loop through the assignment records
  203.   #print the title and due date of each and then delete that record
  204.   #use the deleteAssignmentByID function and wrap in an appropriate try block
  205.   for person in assignmentRecords:
  206.     try:
  207.       print("{} {} is {} old and {}".format(person["first_name"],person["middle_name"],person["age"],person["favorite_thing"]))
  208.       deleteAssignmentByID(dbConnection,person["person_id"])
  209.     except Exception as e:
  210.       #roll back the transaction and stop
  211.       dbConnection.rollback()
  212.       dbConnection.close()
  213.       raise #stop the rest of the script
  214.     else:
  215.       #commit the transaction
  216.       dbConnection.commit()
  217.  
  218.   #close the database connection
  219.   dbConnection.close()
  220.  
  221. # kick off main
  222. if(__name__ == "__main__"):
  223.   main()
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top