Advertisement
JAS_Software

CRUD

May 15th, 2021
54
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. import sqlite3
  2. import sys
  3. import os
  4.  
  5. def getConnection():
  6.     #create connection to the database
  7.     print('Creating connection to the Database')
  8.     conn = ''
  9.     try:
  10.         conn = sqlite3.connect('computer_cards.db')
  11.     except Exception as ex:
  12.         print(ex)
  13.         conn = ''
  14.     return conn
  15.  
  16. def displayRecords(conn):
  17.     #cursor data needs to be fetched before you can use it    
  18.     #fetchall returns a list of lists
  19.     cursor = conn.execute('SELECT * FROM {}'.format(getTableName()))
  20.     records = cursor.fetchall()
  21. #    records = cursor.fetchmany(5)
  22.     for record in records:
  23.         item = ''
  24.         print(buildRecordString(record))
  25.  
  26. def displayRecord(rid,conn):
  27.     table = getTableName()
  28.     cursor = conn.execute("SELECT * FROM {} WHERE name = '{}'".format(table,rid))
  29.     record = cursor.fetchone()
  30.     if(record == None):
  31.         print('No records found with a name of {}'.format(rid))
  32.     else:
  33.         print(buildRecordString(record))
  34.  
  35. def updateRecord(rid,conn,name,cores,speed,ram,cost):
  36.     sql = "UPDATE {} SET name = '{}', cores = {}, cpu_speed = {}, ram = {}, cost = {} WHERE name = '{}'".format(getTableName(),name,cores,speed,ram,cost,rid)
  37.     cursor = conn.execute(sql)
  38.     print("Rows updated: {}".format(cursor.rowcount));
  39.    
  40. def leaveRecord(rid):
  41.     print('A record with the name {} does not exist'.format(rid))
  42.    
  43. def buildRecordString(record):
  44.     item = ''
  45.     for i in range(len(record)):
  46.         item += str(record[i]) + '\t' + '\t'
  47.     return item
  48.    
  49. def closeConnection(conn):
  50.     print('Closing connection to the Database')
  51.     try:
  52.         conn.close()
  53.     except Exception as ex:
  54.         print('Error closing connection {}'.format(ex))
  55.  
  56.  
  57. def getCommand():
  58.     response = ''
  59.     while response == '':
  60.         response = input('Enter command: ').strip().upper()
  61.     return response
  62.  
  63. def displayMenu():
  64.     os.system('cls')
  65.     print('A > Add Record')
  66.     print('P > Print Records')
  67.     print('R > Print Record')
  68.     print('U > Update Record')
  69.     print('D > Delete Record')
  70.     print('M > Menu')
  71.     print('Q > Quit')
  72.  
  73. def validateText(text):
  74.     text = text.strip()
  75.     if len(text)>0:
  76.         valid = True
  77.     else:
  78.         valid = False
  79.     return text,valid
  80.  
  81. def validateInt(text):
  82.     text = text.strip()
  83.     try:
  84.         text = int(text)
  85.         valid = True
  86.     except:
  87.         valid = False
  88.     return text,valid
  89.  
  90. def validateFloat(text):
  91.     text = text.strip()
  92.     try:
  93.         text = float(text)
  94.         valid = True
  95.     except:
  96.         valid = False
  97.     return text,valid
  98.  
  99.  
  100. def getData():
  101.     #TODO Need to validation input
  102.     valid = True
  103.     name, valid = validateText(input('Enter Computer Name: '))
  104.     if valid:
  105.         cores = -1
  106.         speed = -1
  107.         ram = -1
  108.         cost = -1
  109.         if valid:
  110.             cores,valid = validateInt(input('Enter number of cores: '))
  111.             if valid:
  112.                 speed,valid = validateInt(input('Enter CPU speed: '))
  113.                 if valid:
  114.                     ram,valid = validateInt(input('Enter amount of RAM: '))
  115.                     if valid:
  116.                         cost,valid = validateFloat(input('Enter cost: '))
  117.     return name,cores,speed,ram,cost,valid
  118.  
  119. def addRecord(conn,name,cores,speed,ram,cost):
  120.     try:
  121.         sql = "INSERT INTO computer(name, cores,cpu_speed,ram,cost) VALUES ('{}', {}, {}, {}, {})".format(name, cores,speed,ram,cost)
  122.         cursor = conn.execute(sql)
  123.         conn.commit()
  124.         print("Rows inserted: {}".format(cursor.rowcount));
  125.     except Exception:
  126.         print('Add Record Failed')
  127.         #traceback.print_exc()
  128.  
  129. def getRecordID():
  130.     try:
  131.         cid = input('Enter Computer ID: ').strip()
  132.     except:
  133.         cid = 0
  134.     return cid
  135.  
  136. def deleteRecord(cid,conn):
  137.     try:
  138.         sql = "DELETE FROM computer WHERE name = '{}'".format(cid)
  139.         cursor = conn.execute(sql)
  140.         conn.commit()
  141.         print("Rows deleted: {}".format(cursor.rowcount));
  142.     except Exception:
  143.         print('Delete Record Failed')
  144.  
  145. def checkCursor(cursor):
  146.     if cursor == '':
  147.         print('No table cursor established')
  148.     else:
  149.         print('Have Cursor - {}'.format(cursor))
  150.  
  151. def getTableName():
  152.     return 'computer'
  153.  
  154. def recordExists(rid,conn):
  155.     exists = False
  156.     cursor = conn.execute("SELECT * FROM {} WHERE name = '{}'".format(getTableName(),rid))
  157.     record = cursor.fetchone()
  158.     if(record == None):
  159.         exists = False
  160.     else:
  161.         exists = True
  162.     return exists
  163.    
  164. def mainLoop(conn):
  165.     action = ''
  166.     while action != 'Q':
  167.         action = getCommand()
  168.         if action == 'P':
  169.             displayRecords(conn)
  170.         elif action == 'R':
  171.             rid = getRecordID()
  172.             if rid != '':
  173.                 displayRecord(rid,conn)
  174.         elif action == 'U':
  175.             rid = getRecordID()
  176.             if rid != '':
  177.                 if(recordExists(rid,conn)):
  178.                     name,cores,speed,ram,cost,valid = getData()
  179.                     if valid:
  180.                         updateRecord(rid,conn,name,cores,speed,ram,cost)
  181.                 else:
  182.                     leaveRecord(rid)                    
  183.         elif action == 'M':
  184.             displayMenu()
  185.         elif action == 'A':
  186.             name,cores,speed,ram,cost,valid = getData()
  187.             if valid:
  188.                 addRecord(conn,name,cores,speed,ram,cost)
  189.         elif action == 'D':
  190.             rid = getRecordID()
  191.             if rid != '':
  192.                 deleteRecord(rid,conn)
  193.  
  194. def leave():
  195.     print('Exit')
  196.  
  197.    
  198. #Main Program
  199. conn = getConnection()
  200. if conn == '':
  201.     print('Failed to establish connection to database')
  202. else:    
  203.     displayMenu()
  204.     mainLoop(conn)
  205.     closeConnection(conn)
  206. leave()
  207.  
Advertisement
Advertisement
Advertisement
RAW Paste Data Copied
Advertisement