Inserting, Updating and Deleting Data

In this code excerpt, we see how to insert code in a table that is already defined. The data is in a text file that we open and read and then populate the table. In the example below we are using the table s_programmer that has been defined in the Schaum book. The data is in the file personnel.txt.

import cx_Oracle, string, getpass

def main():
  # Get password
  pswd = getpass.getpass()
  
  # Build connection string
  user = "CS327_jdoe"
  host = "oracle.microlab.cs.utexas.edu"
  port = "1521"
  sid = "orcl"
  dsn = cx_Oracle.makedsn (host, port, sid)
  
  # Connect to Oracle and test
  con = cx_Oracle.connect (user, pswd, dsn)
  if (con):
    print "Connection successful"
    print con.version
  else:
    print "Connection not successful"

  # Create a cursor to execute SQL queries
  cursor = con.cursor()  
  
  # Read data from a file
  data = []
  infile = open ("./personnel.txt", "r")
  for line in infile:
    line = line.rstrip("\n")
    line = line.strip()
    seq = line.split(', ')
    seq[6] = int(seq[6])
    seq = tuple (seq)
    data.append(seq)
  infile.close()

  # Check that the data has been read correctly
  print
  print "Check that the data was read from file"
  print data

  # Prepare insert statement
  cursor.prepare("""insert into s_programmer (EmpNo,
  Last_Name, First_Name, Hire_Date, Project, Language,
  TaskNo, Clearance) values (:1, :2, :3, to_date(:4), :5, 
  :6, :7, :8)""")

  # Multiple execution of insert SQL statement
  cursor.executemany(None, data)

  # Get all the rows at once
  cursor.execute ("select * from s_programmer")
  print
  print "Check that the multiple entries went through" 
  result_set = cursor.fetchall()
  for rec in result_set:
    print rec[0], rec[1], rec[2], rec[3], rec[4], rec[5],
    print rec[6], rec[7]

  # Execution of an update SQL statement  
  new_clr = 'Secret'
  emp_no = '345'
  sql_st = "update s_programmer set clearance = :clr where \
  EmpNo = :empN"
  cursor.execute(sql_st, {'clr':new_clr, 'empN':emp_no})

  # Check that the update operation worked
  print
  print "Check Update Operation"
  cursor.execute ("select * from s_programmer")
  result_set = cursor.fetchall()
  for rec in result_set:
    print rec[0], rec[1], rec[2], rec[3], rec[4], rec[5],
    print rec[6], rec[7]


  # Execution of a delete SQL statement
  cursor.execute ("""delete from s_programmer where 
                   EmpNo = '789'""")
  
  # Check that the delete operation worked
  print
  print "Check Delete Operation"
  sql_st = """select EmpNo, Last_Name, First_Name,
  to_char(Hire_Date), Project, Language, TaskNo,
  Clearance from s_programmer"""
  cursor.execute(sql_st)
  result_set = cursor.fetchall()
  for rec in result_set:
    print rec[0], rec[1], rec[2], rec[3], rec[4], rec[5],
    print rec[6], rec[7]


  # Delete all data from s_programmer
  cursor.execute ("""delete from s_programmer""")
 
  # Close the cursor
  cursor.close()

  # Close the connection
  con.close()