def __init__(self): #PG-connection setup
print(
"AUTHORS NOTE: If you submit faulty information here, I am not responsible for the consequences."
)
print "The idea is that you, the authorized database user, log in."
print "Then the interface is available to employees whos should only be able to enter shipments as they are made."
while True:
try:
params = {
'host': 'nestor2.csc.kth.se',
'user':
pgdb.escape_string(raw_input("Username: "******"Password: "******"Record a shipment", "Show stock", "Show shipments", "Exit"
]
self.cur = self.conn.cursor()
def insert(self):
"""inserts tuples.
Will query the user for the information required to create tuples."""
#First we should get which table to insert into
table = pgdb.escape_string(raw_input("Choose table: "))
print table
columns = pgdb.escape_string(raw_input("Choose column(s): "))
#Then filter (as seen in the select func above, i.e. the where clause)
#values = pgdb.escape_string(raw_input("Values of tuple to insert: "))
values = raw_input("Values of tuple to insert: ")
#valueList = values.split(",")
#print "we got values=>>%s<<" % values
# try/define query, inspired by line 119--122
try:
query = """INSERT INTO %s(%s) VALUES (%s);""" % (table, columns, values)
except (NameError,ValueError, TypeError,SyntaxError):
print " Bad input."
return
print query
#execute the query (line 128)
self.cur.execute(query)
#print "Comitting..."
self.conn.commit() #this might better be done only in the exit method
def makeShipments(self):
try:
CID = int(raw_input("cutomerID: "))
SID = int(input("shipment ID: "))
Sisbn= pgdb.escape_string((raw_input("isbn: ").strip()))
Sdate= pgdb.escape_string(raw_input("Ship date: ").strip())
except (NameError,ValueError, TypeError,SyntaxError):
print("Incorrect input.Try again...")
return
query = "SELECT stock FROM stock WHERE isbn='%s';" % (Sisbn)
print query
#start a transaction
try:
self.cur.commit()
self.cur.execute("set transaction isolation level serializable")
try:
self.cur.execute(query)
except(pgdb.DatabaseError, pgdb.OperationalError):
print("Incorrect query.")
return
stock_result = self.cur.fetchone()
if stock_result is None:
print("No books found with isbn: %s" % Sisbn)
return
cnt = stock_result[0];
if cnt < 1:
print("No more books in stock :(")
return
else:
print "We have the book in stock"
query = """UPDATE stock SET stock=stock-1 WHERE isbn='%s';""" % (Sisbn)
self.cur.execute(query)
print "stock decremented"
query = """INSERT INTO shipments VALUES (%i, %i, '%s','%s');""" % (SID,CID,Sisbn,Sdate)
print query
try:
self.cur.execute(query) # execute insert
except (pgdb.DatabaseError, pgdb.OperationalError):
print "Failed on insert!"
return
print "shipment created"
# This ends the transaction (and starts a new one)
self.conn.commit()
except (pgdb.DatabaseError, pgdb.OperationalError) as e:
print " Exception encountered while modifying table data."
self.conn.rollback()
print " Rolling back."
return
def shipments(self):
#Test input is ID=671, fname='Chuck', lname='Brown'
try:
#Check that input is integer
ID = int(raw_input("customerID: "))
except (NameError, ValueError, TypeError, SyntaxError):
print("Non numerical ID. Try again...")
return
#Good against SQL injections attacks because escape characters are removed
fname = pgdb.escape_string(raw_input("First Name: ").strip())
lname = pgdb.escape_string(raw_input("Last Name: ").strip())
query = "SELECT first_name, last_name FROM customers WHERE customer_id = %s;" % ID
print(query)
print("------------------------------------------------------------")
try:
self.cur.execute(query)
except (NameError, ValueError, TypeError, SyntaxError):
print("Query execution failed.")
return
#fetchone() retrieves one result row for the query that was executed.
#empty list (none) -> nothing was retrieved from the DB
customer_list = self.cur.fetchone()
if customer_list is None:
print("Customer does not exist in DB")
return
else:
if customer_list[0].lower() == fname.lower(
) and customer_list[1].lower() == lname.lower():
print("Welcome %s %s" % (fname, lname))
else:
print(
"Name %s %s does not match %s" % (fname, lname, ID)
) #ID exists but first name or/and last name are incorrect
return
#isbn alone is ambigous because it's a key to 2 tables (stock,shipments)
#and for that reason it must be specified on which table the SQL should check
query = """SELECT shipment_id,ship_date,shipments.isbn,title
FROM Shipments
JOIN editions ON shipments.isbn = editions.isbn
JOIN books ON editions.book_id = books.book_id
WHERE customer_id = %s; """ % ID
print("------------------------------------------------------------")
try:
self.cur.execute(query)
print("Customer: %d | %s | %s" % (ID, fname, lname))
print("shipment_id, ship_date, isbn, title")
self.print_answer()
except (NameError, ValueError, TypeError, SyntaxError):
print("Query execution failed.")
return
print("------------------------------------------------------------\n")
def shipments(self):
# These input funtions are not correct so exceptions caught and handled.
# ID should be hard typed to an integer
# So think that they can enter: 1 OR 1=1
while True:
try:
ID = int(input("ID: "))
break
except NameError:
print "Please enter a number"
# These names inputs are terrible and allow injection attacks.
# So think that they can enter: Hilbert' OR 'a'='a
fname = pgdb.escape_string(raw_input("First Name: ").strip())
lname = pgdb.escape_string(raw_input("Last Name: ").strip())
# THIS IS NOT RIGHT YOU MUST FIGURE OUT WHAT QUERY MAKES SENSE
query = "SELECT first_name, last_name FROM customers WHERE customer_id = '%s';" %(ID)
#NEED TO Catch excemptions ie bad queries (ie there are pgdb.someError type errors codes)
try:
self.cur.execute(query)
except pg.ProgrammingError as detail:
print "Something is not right... please try again"
# NEED TO figure out how to get and test the output to see if the customer is in customers
# test code here...
# HINT: in python lists are accessed from 0 that is mylist[0] is the first element
# also a list of a list (such as the result of a query) has two indecies so starts with mylist[0][0]
result = self.cur.fetchone()
if result:
if result[0].lower() == fname.lower() and result[1].lower() == lname.lower():
# now the test is done
print "Customer %d %s %s:"%(ID, fname, lname)
# THIS IS NOT RIGHT YOU MUST PRINT OUT a listing of shipment_id,ship_date,isbn,title for this customer
else:
print "The ID does not match with the name you entered"
return
else:
print "Sorry we could no find the ID in the database"
return
query = """SELECT shipment_id, ship_date, isbn, title
FROM shipments NATURAL JOIN editions NATURAL JOIN books
WHERE customer_id = %s"""%(ID)
# YOU MUST CATCH EXCEPTIONS HERE AGAIN
try:
self.cur.execute(query)
except pg.ProgrammingError as detail:
print detail
# Here the list should print for example:
# Customer 860 Tim Owens:
# shipment_id,ship_date,isbn,title
# 510, 2001-08-14 16:33:47+02, 0823015505, Dynamic Anatomy
self.print_answer()
def makeShipments(self):
#THESE INPUT LINES ARE NOT GOOD ENOUGH
# YOU NEED TO TYPE CAST/ESCAPE THESE AND CATCH EXCEPTIONS
try:
# Try to obtain the input.
CID = int(input("cutomerID: "))
SID = int(input("shipment ID: "))
Sisbn = pgdb.escape_string(raw_input("isbn: ").strip())
Sdate = pgdb.escape_string(raw_input("Ship date: ").strip())
# THIS IS NOT RIGHT YOU MUST FORM A QUERY THAT HELPS
query = "SELECT stock FROM stock WHERE isbn='%s'" % Sisbn
except (NameError, ValueError, TypeError, SyntaxError) as detail:
print detail.message.split('\n')[0]
return
print query
# HERE YOU SHOULD start a transaction
try:
#YOU NEED TO Catch exceptions ie bad queries
self.cur.execute(query)
# HERE YOU NEED TO USE THE RESULT OF THE QUERY TO TEST IF THERE ARE
# ANY BOOKS IN STOCK
result = self.cur.fetchone()
if not result:
print("No books found with isbn '%s'" % Sisbn)
return
# YOU NEED TO CHANGE THIS TO SOMETHING REAL
cnt = result[0]
if cnt < 1:
print("No more books in stock :(")
return
else:
print "WE have the book in stock"
query = """UPDATE stock SET stock=stock-1 WHERE isbn='%s';""" % Sisbn
print query
#YOU NEED TO Catch exceptions and rollback the transaction
self.cur.execute(query)
print "stock decremented"
query = """INSERT INTO shipments VALUES (%i, %i, '%s','%s');""" % (SID, CID, Sisbn, Sdate)
print query
#YOU NEED TO Catch exceptions and rollback the transaction
self.cur.execute(query)
print "shipment created"
# This ends the transaction (and starts a new one)
self.conn.commit()
except (pgdb.DatabaseError, pgdb.OperationalError) as error:
print " Exception encountered while modifying table data." + error.message
self.conn.rollback()
print " Rolling back."
return
def _getShipmentInfo(self):
try:
cid = int(input("customerID: "))
sid = int(input("shipment ID: "))
shipment_isbn = pgdb.escape_string((raw_input("isbn: ")).strip())
shipment_date = pgdb.escape_string(raw_input("Ship date: ")).strip()
# THIS IS NOT RIGHT YOU MUST FORM A QUERY THAT HELPS
query ="SELECT stock FROM stock WHERE isbn='%s'" % shipment_isbn
except (NameError, ValueError, TypeError, SyntaxError) as e:
print "Error in input" + str(e) # Not sure if it works, haven't tested
return None
#print "query=" + query
result = (sid, cid, shipment_isbn, shipment_date, query)
return result
def getnodes(self, ids):
nodes = []
idstr = ""
first = True
if len(ids) == 0:
idstr = "-1"
for i in ids:
if first:
idstr += "%d" % (i)
first = False
else:
idstr += ", %d" % (i)
sql = "SELECT ID,DATA FROM %sNODES WHERE ID IN (%s)" % (self._prefix,
pgdb.escape_string(idstr))
try:
cursor = self._get_cur()
cursor.execute(sql)
row = cursor.fetchone()
while row != None:
node = cPickle.loads(str(row[1]))
node.set_id(row[0])
nodes.append(node)
row = cursor.fetchone()
except pgdb.DatabaseError, e:
raise DatabaseException("Postgresql: %s" % (e))
def __make_query(key, operator, pattern):
if not key:
return None
# Translate keyed values. That function returns the input
# value unchanged if there's no reason to translate anything.
pattern = _coded_value(key, pattern)
pattern = pgdb.escape_string(pattern)
key = key.replace('"', r'\"')
if '~' in operator:
# 1. dot is not a wildcard here but rather a literal dot
pattern = pattern.replace('.', '\.')
# 2. a * indicates to not do a substring search
if '*' in pattern:
pattern = pattern.replace('*', '.*')
pattern = '^%s$' % (pattern)
# 3. empty pattern means search for everything
if pattern == '':
pattern = '.*'
return "\"%s\" %s '%s'" % (key, operator, pattern)
def quote(self, value):
"""
Returns quoted version of the specified value.
"""
# The pgdb._quote function is good enough for general SQL
# quoting, except for array types.
if isinstance(value, (list, tuple, set)):
return "ARRAY[%s]" % ", ".join(map (PostgreSQL.quote_string, value))
else:
ret = None
if isinstance(value, str):
ret = "'" + pgdb.escape_string( value ) + "'"
else:
ret = pgdb.escape_string( str(value) )
return ret
def getnodes(self, ids):
nodes = []
idstr = ""
first = True
if len(ids) == 0:
idstr = "-1"
for i in ids:
if first:
idstr += "%d" % (i)
first = False
else:
idstr += ", %d" % (i)
sql = "SELECT ID,DATA FROM %sNODES WHERE ID IN (%s)" % (
self._prefix, pgdb.escape_string(idstr))
try:
cursor = self._get_cur()
cursor.execute(sql)
row = cursor.fetchone()
while row != None:
node = cPickle.loads(str(row[1]))
node.set_id(row[0])
nodes.append(node)
row = cursor.fetchone()
except pgdb.DatabaseError, e:
raise DatabaseException("Postgresql: %s" % (e))
def escape(self, str):
if self.dbinfo.dbtype == T_MYSQL:
return MySQLdb.escape_string(str)
elif self.dbinfo.dbtype == T_PGDB:
return pgdb.escape_string(str)
return str
def makeShipments(self):
#THESE INPUT LINES ARE NOT GOOD ENOUGH
# YOU NEED TO TYPE CAST/ESCAPE THESE AND CATCH EXCEPTIONS
while True:
try:
CID = int(raw_input("customerID: "))
break
except (NameError, ValueError, TypeError, SyntaxError):
print 'Input must be a number.'
while True:
try:
SID = int(input("shipment ID: "))
break
except (NameError, ValueError, TypeError, SyntaxError):
print 'Input must be a number.'
Sisbn = pgdb.escape_string(raw_input("isbn: ").strip())
while True:
try:
Sdate = datetime.datetime.strptime(
raw_input("Ship date: ").strip(), '%Y-%m-%d')
break
except ValueError:
print "Incorrect data format, should be YYYY-MM-DD"
# THIS IS NOT RIGHT YOU MUST FORM A QUERY THAT HELPS
query = "SELECT * FROM shipments WHERE customer_id = '%i' AND shipment_id = '%i'" % (
CID, SID)
print query
# HERE YOU SHOULD start a transaction
#YOU NEED TO Catch exceptions ie bad queries
self.cur.execute(query)
#HERE YOU NEED TO USE THE RESULT OF THE QUERY TO TEST IF THER ARE
#ANY BOOKS IN STOCK
# YOU NEED TO CHANGE THIS TO SOMETHING REAL
cnt = 0
if cnt < 1:
print("No more books in stock :(")
return
else:
print "WE have the book in stock"
query = """UPDATE stock SET stock=stock-1 WHERE isbn='%s';""" % (Sisbn)
print query
#YOU NEED TO Catch exceptions and rollback the transaction
self.cur.execute(query)
print "stock decremented"
query = """INSERT INTO shipments VALUES (%i, %i, '%s','%s');""" % (
SID, CID, Sisbn, Sdate)
print query
#YOU NEED TO Catch exceptions and rollback the transaction
self.cur.execute(query)
print "shipment created"
# This ends the transaction (and starts a new one)
self.conn.commit()
def shipments(self):
# These input funtions are not correct so exceptions caught and handled.
# ID must be integer
while True:
try:
ID = int(raw_input("customerID: "))
break
except (NameError, ValueError, TypeError, SyntaxError):
print("That was not a number, try again.")
# Escape_string to avoid injection attacks.
fname = pgdb.escape_string(raw_input("First Name: ").strip())
lname = pgdb.escape_string(raw_input("Last Name: ").strip())
#---Ask for the user
query = "SELECT * FROM customers WHERE customer_id = '%i' AND first_name ='%s' AND last_name='%s'" % (
ID, fname, lname)
print query
#NEED TO Catch excemptions ie bad queries (ie there are pgdb.someError type errors codes)
try:
self.cur.execute(query)
except (NameError, ValueError, TypeError, SyntaxError):
print('Opps, something went wrong with the query..')
#--Check if user exsists (if there is any query-response)
try:
response = self.cur.fetchall()
# Error if no tuples in response
response[0][0]
print('User found!')
query = "SELECT shipment_id, ship_date, shipments.isbn, title FROM shipments, customers, books, editions WHERE shipments.isbn = editions.isbn AND editions.book_id = books.book_id AND shipments.customer_id = customers.customer_id AND shipments.customer_id = '%i' AND first_name = '%s' AND last_name = '%s';" % (
ID, fname, lname)
except (TypeError, IndexError):
print "Sorry, user not found.."
return
#--Ask for shipments
try:
self.cur.execute(query)
except (NameError, ValueError, TypeError, SyntaxError):
print('Opps, something went wrong with the query..')
self.print_answer()
def convList2Array(list):
"""
Converts python list to pg array.
"""
array = '{'
for item in list:
array += "'%s'," % pgdb.escape_string(item)
# trim ending ','
if len(array) > 1:
array = array[0:-1]
array += '}'
return array
def convList2Array(list):
"""
Converts python list to pg array.
"""
array = '{'
for item in list:
array += "'%s'," % pgdb.escape_string(item)
# trim ending ','
if len(array) > 1:
array = array[0:-1]
array += '}'
return array
def convList2Array(list):
"""
Converts python list to pg array.
"""
array = '{'
for item in list:
if isinstance(item, str):
item = pgdb.escape_string(item)
array += "%s," % str(item)
# trim ending ','
if len(array) > 1:
array = array[0:-1]
array += '}'
return array
def get_tiles_to_flag(self, flaggingname=None):
if (self.db is None):
logger.error('Cannot retrieve flagging information without database connection')
return
if (flaggingname is not None):
flagged_tiles = dbobj.execute('select flagged_tiles from tile_flags where starttime < %d and stoptime > %d and name = %s' %
(self.gpstime, self.gpstime, pgdb.escape_string(flaggingname)), db=self.db)
else:
flagged_tiles = list(itertools.chain.from_iterable(dbobj.execute('select flagged_tiles from tile_flags where starttime < %d and stoptime > %d' %
(self.gpstime, self.gpstime), db=self.db)))
# logical or of all of the flagging sets that are returned
for x in flagged_tiles:
for z in x.replace('{','').replace('}','').split(','):
self.tiles_to_flag.add(z)
logger.info('Will flag tiles %s' % list(self.tiles_to_flag))
def check_name_avail(name):
conn = _get_db_conn()
cur = conn.cursor()
cur.execute("SELECT name FROM vmmachines WHERE name = '" + MySQLdb.escape_string(name) + "'")
row = cur.fetchone()
if row is None:
pg_conn = _get_pg_db_conn()
pg_cur = pg_conn.cursor()
pg_cur.execute("SELECT hostname FROM hosts WHERE hostname = '" + pgdb.escape_string(name) + "'")
row = pg_cur.fetchone()
if row is not None:
# Found in PG Start DB, FAIL
return {'status': 'OK', 'avail':0}
#_release_db_conn()
# NOT Found in Start OR vm list, OK
return {'status': 'OK', 'avail': 1}
#_release_db_conn()
# Found Somewhere, FAIL
return {'status': 'OK', 'avail': 0}
def remove(self):
"""Removes tuples.
Will query the user for the information required to identify a tuple.
If the filter field is left blank, no filters will be used."""
#First we should get which table to remove from
table = pgdb.escape_string(raw_input("Choose table: "))
print table
#Then filter (as seen in the select func above, i.e. the where clause)
filters = raw_input("Apply filters: ")
# try/define query, inspired by line 119--122
try:
query = """DELETE FROM %s%s;""" % (table, "" if filters =="" else " WHERE %s" % filters)
except (NameError,ValueError, TypeError,SyntaxError):
print " Bad input."
return
print(query)
#execute the query (line 128)
self.cur.execute(query)
def sync(filename):
for line in open(filename,'r').readlines()[1:]:
elems = line.strip().split('~')
artist = ""
title = ""
if len(elems) == 2:
(artist,title) = elems
if artist != 'Various':
cursor.execute("select records.recordnumber,author,title from records,track,groops,lineupset,lineup where records.recordnumber = track.recordnumber and track.trackrefnumber = lineupset.tracknumber and lineupset.lineupnumber = lineup.lineupnumber AND lineup.groopnumber = groops.groopnumber and lower(records.title) = '" + pgdb.escape_string(title.lower()) + "' AND lower(groops.show_name) = '" + pgdb.escape_string(artist.lower()) + "'")
else:
cursor.execute("select recordnumber from records where author = '" + pgdb.escape_string(artist) + "' AND title = '" + pgdb.escape_string(title) + "'")
row = cursor.fetchone()
seen = False
done = []
while row != None:
if not seen:
if (artist,title) not in done:
print artist,title,"(" + filename + ")"
seen = True
if row[0] not in done:
print "\t",row[0]
done.append(row[0])
row = cursor.fetchone()
def makeShipments(self):
#---Get customer id, shipment id, isbn and shipment date. Avoid insertons and catch errors.
#---Get customer id
while True:
try:
CID = int(raw_input("customerID: "))
break
except (NameError, ValueError, TypeError, SyntaxError):
print 'Input must be a number.'
#---Get shipment ID
while True:
try:
SID = int(input("shipment ID: "))
break
except (NameError, ValueError, TypeError, SyntaxError):
print 'Input must be a number.'
#---Get isbn
Sisbn = pgdb.escape_string(raw_input("isbn: ").strip())
#---Get date or set today as default if no input
while True:
try:
Sdate = pgdb.escape_string(raw_input("Ship date: ").strip())
if Sdate == '':
Sdate = datetime.datetime.now()
Sdate = '%s-%s-%s' % (Sdate.year, Sdate.month, Sdate.day)
Sdate = datetime.datetime.strptime(Sdate, '%Y-%m-%d')
break
except ValueError:
print "Incorrect data format, should be YYYY-MM-DD"
#---Create query to get stock of choosen book
query = "SELECT stock FROM stock WHERE isbn = '%s';" % (Sisbn)
print query
#---Starts a new transaction (nothing to commit) - Might be unneccesary since there is a commit at the end aswell
self.conn.commit()
#---Get stock of choosen book:
try:
self.cur.execute(query)
stock = self.cur.fetchall()[0][0]
except (NameError, ValueError, TypeError, SyntaxError,
pgdb.DatabaseError):
print('Opps, something went wrong with the query..')
return
#---Check if book is in stock:
if stock < 1:
print("No more books in stock :(")
return
else:
print "WE have the book in stock"
#---Create query for stock decreasement
query = """UPDATE stock SET stock=stock-1 WHERE isbn='%s';""" % (Sisbn)
print query
#---Decrease stock but catch exceptions and rollback the transaction if there's any problem
try:
self.cur.execute(query)
print "Stock decremented"
except (NameError, ValueError, TypeError, SyntaxError,
pgdb.DatabaseError):
print('Opps, something went wrong with the query..')
self.conn.rollback
return
#---Create query for new shipment
query = """INSERT INTO shipments VALUES (%i, %i, '%s','%s');""" % (
SID, CID, Sisbn, Sdate)
print query
#---Add to shipments but catch exceptions and rollback the transaction if there's any problem
try:
self.cur.execute(query)
print "Shipment created"
except (NameError, ValueError, TypeError, SyntaxError):
print('Opps, something went wrong with the query..')
self.conn.rollback
return
except (pgdb.DatabaseError):
print('ERROR! Shipment ID already exsits, try again..')
self.conn.rollback
return
# This ends the transaction (and starts a new one)
self.conn.commit()
def find(self, title):
self.cur.execute("SELECT tile, stock FROM books NATURAL JOIN editions NATURAL JOIN stock WHERE title='" + pgdb.escape_string(title) + "';")
def shipments(self):
# These input funtions are not correct so exceptions caught and handled.
qFromS = ""
# ID should be hard typed to an integer
# So think that they can enter: 1 OR 1=1
try:
ID = int(raw_input("customerID: ")) #castar till int
except (NameError, ValueError, TypeError, SyntaxError):
print(
"Something went wrong in your input of ID, try starting over.."
)
return
# These names inputs are terrible and allow injection attacks.
# So think that they can enter: Hilbert' OR 'a'='a
fname = pgdb.escape_string(raw_input("First Name: ").strip(
)) #escape_string - tar bort alla "farliga" tecken
lname = pgdb.escape_string(raw_input(
"Last Name: ").strip()) #slipper ocksa skriva ex. 'title'
# THIS IS NOT RIGHT YOU MUST FIGURE OUT WHAT QUERY MAKES SENSE
try:
query = "SELECT first_name, last_name FROM customers WHERE customer_id=%s" % ID
except (NameError, ValueError, TypeError, SyntaxError):
print("Something went wrong in your input, try starting over..")
#"100 OR 1=1"
#WHERE customer_id = 7216784 OR 1=1;
print query
#NEED TO Catch excemptions ie bad queries (ie there are pgdb.someError type errors codes)
try:
self.cur.execute(query)
print "SUCCESS... Welcome %s %s" % (fname, lname)
except pgdb.Error as e:
print("sry cant find you in DB")
#-------- SETTING VAR TO LOWERCASE TO MAKE COMPARE EASIER ------------------
fnameL = fname.lower()
lnameL = lname.lower()
#----------------------------------------------------------------------------
tupleFromS = self.cur.fetchone(
) #Getting matching tuple from Shipments according to ID
printCols = "shipment_id, ship_date, isbn, title" #Declaring new variable for SELECTION and final print of res
if tupleFromS[0].lower() == fnameL and tupleFromS[1].lower() == lnameL:
print "SHOW EVERYTHING FROM SHIPMENTS"
try:
qFromS = "SELECT " + printCols + " FROM (SELECT * FROM editions NATURAL JOIN (SELECT * FROM shipments NATURAL JOIN books) AS t1) AS t2 WHERE customer_id = %s" % ID
except (NameError, ValueError, TypeError, SyntaxError):
print "Something went wrong looking up your shipments.."
try:
self.cur.execute(qFromS)
except (pgdb.Error) as e:
print e
print "Looks like no match can be found.."
else:
print "sry cannot find ID in DB"
# NEED TO figure out how to get and test the output to see if the customer is in customers
# test code here...
# HINT: in pyton lists are accessed from 0 that is mylist[0] is the first element
# also a list of a list (such as the result of a query) has two indecies so starts with mylist[0][0]
# now the test is done
#print "good name"
# THIS IS NOT RIGHT YOU MUST PRINT OUT a listing of shipment_id,ship_date,isbn,title for this customer
#query ="SELECT shipment_id, ship_date, isbn, title FROM shipments WHERE customer_id IN(%s)" %.......
# YOU MUST CATCH EXCEPTIONS HERE AGAIN
#self.cur.execute(query)
# Here the list should print for example:
# Customer 860 Tim Owens:
# shipment_id,ship_date,isbn,title
# 510, 2001-08-14 16:33:47+02, 0823015505, Dynamic Anatomy
#--------- PRINTING ACCORDING TO EX ------------------------------
print("\n-----------------------------------")
print("Customer " + str(ID) + " " + fname + " " + lname)
print(printCols)
self.print_answer()
print("-----------------------------------\n")
def Escape(query_str):
return pgdb.escape_string(query_str)
def safe_input(self,str):
return pgdb.escape_string(raw_input(str))
def makeShipments(self):
#THESE INPUT LINES ARE NOT GOOD ENOUGH
# YOU NEED TO TYPE CAST/ESCAPE THESE AND CATCH EXCEPTIONS
while True:
try:
CID = int(raw_input("customerID: "))
break
except (NameError, ValueError, TypeError, SyntaxError):
print 'Input must be a number.'
while True:
try:
SID = int(input("shipment ID: "))
break
except (NameError, ValueError, TypeError, SyntaxError):
print 'Input must be a number.'
Sisbn = pgdb.escape_string(raw_input("isbn: ").strip())
#---Check date format or set today as default if no input
while True:
try:
Sdate = pgdb.escape_string(raw_input("Ship date: ").strip())
if Sdate == '':
Sdate = datetime.datetime.now()
Sdate = '%s-%s-%s' % (Sdate.year, Sdate.month, Sdate.day)
Sdate = datetime.datetime.strptime(Sdate, '%Y-%m-%d')
break
except ValueError:
print "Incorrect data format, should be YYYY-MM-DD"
# Check current shipments
query = "SELECT stock FROM stock WHERE isbn = '%s';" % (Sisbn)
print query
# HERE YOU SHOULD start a transaction
#---not done this yet...
#---Get stock of choosen book:
try:
self.cur.execute(query)
stock = self.cur.fetchall()[0][0]
except (NameError, ValueError, TypeError, SyntaxError):
print('Opps, something went wrong with the query..')
# print stock
#---Check if book is in stock:
if stock < 1:
print("No more books in stock :(")
return
else:
print "WE have the book in stock"
query = """UPDATE stock SET stock=stock-1 WHERE isbn='%s';""" % (Sisbn)
print query
#YOU NEED TO Catch exceptions and rollback the transaction
self.cur.execute(query)
print "stock decremented"
query = """INSERT INTO shipments VALUES (%i, %i, '%s','%s');""" % (
SID, CID, Sisbn, Sdate)
print query
#YOU NEED TO Catch exceptions and rollback the transaction
self.cur.execute(query)
print "shipment created"
# This ends the transaction (and starts a new one)
self.conn.commit()
def create_vm(uname, hname, dsize, ssize, imagename, mac, allocid, mem, owner, start_register, start_range):
user = get_user_info(uname);
conn = _get_db_conn();
cur = conn.cursor();
adsize = None
assize = None
amac = None
amem = None
aowner = None
usingPrealloc = 0
if allocid == 'new':
allocid = None
# Check if we got an alloc id or else that we're an admin
if (allocid == None and user['admin'] != 1):
#_release_db_conn()
return "{status: 'FAIL', reason: 'Not an admin. Must use preallocated VM'}";
# Get the allocation we're going to use, if we're using one.
if (allocid != None):
cur.execute("SELECT mac, disk, swap, mem, owner FROM allocvmmachines WHERE id = " + str(int(allocid)) + ";");
row = cur.fetchone()
if (row != None):
usingPrealloc = 1
amac = row[0]
adsize = row[1]
assize = row[2]
amem = row[3]
aowner = row[4]
if (usingPrealloc == 0 and user['admin'] != 1):
#_release_db_conn()
return "{status: 'FAIL', reason: 'Not authorized to create new VM'}"
# Check that the base image name is legitimate
cur.execute("SELECT name FROM images WHERE name = '" + MySQLdb.escape_string(imagename) + "'")
row = cur.fetchall()
if (row is None):
#_release_db_conn()
return {'status': 'OK', 'reason': 'image name is not valid'}
# If we're using a pre-allocated VM, make sure un-changeable settings aren't changed.
if (usingPrealloc == 1 and ((amac != None and amac != mac) or (adsize != None and int(adsize) != int(dsize)) or (assize != None and int(assize) != int(ssize)) or (amem != None and int(amem) != int(mem)) or (aowner != None and aowner != owner))):
#_release_db_conn()
return "{status: 'FAIL', reason: 'Locked attributes do not match amac:" + str(amac) + " adsize:" + str(adsize) + " assize:" + str(assize) + " amem:" + str(amem) + " aowner:" + str(aowner) + "'}"
# If the user chose to do a registration on start, register it
if (start_register == 'on'):
# DO Network Registration
pg_conn = _get_pg_db_conn()
pg_cur = pg_conn.cursor()
# Figure out the IP range we want to deal with.
if (start_range == 'userrack'):
first_ip = '129.21.50.176'
last_ip = '129.21.60.249'
elif (start_range == 'projects'):
first_ip = '129.21.50.1'
last_ip = '129.21.50.64'
pg_cur.execute("SELECT ip_address FROM host_cache WHERE ip_address BETWEEN '" + first_ip + "' AND '" + last_ip + "' AND in_use = false ORDER BY ip_address LIMIT 1")
row = pg_cur.fetchone()
if (row is not None):
ip_addr = row[0]
pg_cur.execute("UPDATE host_cache SET in_use = true WHERE ip_address = '" + str(ip_addr) +"'")
try:
pg_cur.execute("INSERT INTO hosts (hardware_address, ip_address, hostname, username, auth_user) VALUES ('" + pgdb.escape_string(mac) + "', '" + ip_addr + "', '" + pgdb.escape_string(hname) + "', '" + pgdb.escape_string(owner) + "', 'adinardi')")
except pgdb.DatabaseError:
# END HERE. Return failure since we can't register
pg_conn.rollback()
#_release_db_conn()
return {'status': 'FAIL', 'reason': 'Hostname or MAC address already registered.'}
pg_cur.execute("INSERT INTO process_queue (event_class) VALUES ('RELDHCP')")
pg_conn.commit()
else:
return {'status': 'FAIL', 'reason': 'No more IP addresses available to be registered'}
# ADD MYSQL DB WRITE HERE.
cur.execute("INSERT INTO vmmachines (name, owner, mac, disk, mem, swap) VALUES('" + MySQLdb.escape_string(hname) + "', '" + MySQLdb.escape_string(owner) + "', '" + MySQLdb.escape_string(mac) + "', '" + MySQLdb.escape_string(dsize) + "', '" + MySQLdb.escape_string(mem) + "', '" + MySQLdb.escape_string(ssize) + "')");
vmid = conn.insert_id()
cur.execute("INSERT INTO vmdisks (file, device, vmmachineid) VALUES ('disk', 'xvda2', " + str(vmid) + ")")
cur.execute("INSERT INTO vmdisks (file, device, vmmachineid) VALUES ('swap', 'xvda1', " + str(vmid) + ")")
if (usingPrealloc == 1):
cur.execute("DELETE FROM allocvmmachines WHERE id = " + str(int(allocid)))
# Write Config
FILE = open('/mnt/vms/configs/new/' + hname + '.defs', "w");
FILE.write("-n " + hname + " -d " + dsize + " -s " + ssize + " -b " + imagename + " -m " + mac + " -e " + mem);
FILE.close();
#_release_db_conn()
return "{status: 'OK'}"
def shipments(self):
print "In shipments()"
# These input funtions are not correct so exceptions caught and handled.
# ID should be hard typed to an integer
# So think that they can enter: 1 OR 1=1
while True:
try:
ID = int(raw_input("customerID: "))
break
except ValueError:
print "customerID has to be an integer!"
# These names inputs are terrible and allow injection attacks.
# So think that they can enter: Hilbert' OR 'a'='a
#fname = (raw_input("First Name: ").strip())
#lname = raw_input("Last Name: ").strip()
fname = pgdb.escape_string((raw_input("First Name: ").strip()))
lname = pgdb.escape_string(raw_input("Last Name: ").strip())
fname = "'%s'" % fname
lname = "'%s'" % lname
# THIS IS NOT RIGHT YOU MUST FIGURE OUT WHAT QUERY MAKES SENSE
#query = "SELECT something FROM somewhere WHERE something"
query = "SELECT customer_id, last_name, first_name FROM customers "
query += "WHERE customer_id={ID} AND last_name={lname} AND first_name={fname};".format(
ID=ID, fname=fname, lname=lname)
#print "query='%s'\n" % query
#NEED TO Catch excemptions ie bad queries (ie there are pgdb.someError type errors codes)
try:
self.cur.execute(query)
#print "The query executed successfully!"
except pgdb.DatabaseError as e:
print "There was an exception executing the query '%s': %s" %\
(query, str(e))
self.exit()
if len(fname) >= 2:
fname = fname[1:-1]
if len(lname) >= 2:
lname = lname[1:-1]
# NEED TO figure out how to get and test the output to see if the customer is in customers
# test code here...
# HINT: in pyton lists are accessed from 0 that is mylist[0] is the first element
# also a list of a list (such as the result of a query) has two indecies so starts with mylist[0][0]
# now the test is done
if self.cur.fetchone():
#print "Good name!"
pass
else:
print "Couldn't find the customer {lname}, {fname} with id {id}.".format(
id=ID, fname=fname, lname=lname)
print "Check your ID, first name or last name (consider the case!)"
return
# THIS IS NOT RIGHT YOU MUST PRINT OUT a listing of shipment_id,ship_date,isbn,title for this customer
#query ="SELECT something FROM somewhere WHERE conditions"
#query ="SELECT shipment_id, ship_date, isbn, title FROM shipments WHERE customer_id={id}".format(id=ID)
#query ="SELECT shipment_id, ship_date, isbn, title FROM shipments, books WHERE customer_id={id}".format(id=ID)
query = """SELECT shipment_id, ship_date, isbn, title
FROM shipments NATURAL JOIN books NATURAL JOIN editions
WHERE customer_id={id}""".format(id=ID)
# YOU MUST CATCH EXCEPTIONS HERE AGAIN
try:
self.cur.execute(query)
#print "The query executed successfully!"
except pgdb.DatabaseError as e:
print "There was an exception executing the query '%s': %s" %\
(query, str(e))
self.exit()
values = self.cur.fetchall()
print "Customer {id} {fname} {lname}:".format(
id=ID,
#fname=pgdb.escape_string(fname),
#lname=pgdb.escape_string(lname))
fname=fname,
lname=lname)
#print "\tshipment_id\tship_date\tisbn\ttitle"
print "\tship_id\tship_date\t\tisbn\t\ttitle"
for shipment in values:
print "\t" + "\t".join(map(str, shipment))
def escapeArrayElement(query_str):
# also escape backslashes and double quotes, in addition to the doubling of single quotes
return pgdb.escape_string(query_str.encode(errors='backslashreplace')).decode(errors='backslashreplace').replace('\\','\\\\').replace('"','\\"')
def makeShipments(self):
#THESE INPUT LINES ARE NOT GOOD ENOUGH
#YOU NEED TO TYPE CAST/ESCAPE THESE AND CATCH EXCEPTIONS
try:
CID = int(raw_input("customer ID: "))
SID = int(input("shipment ID: "))
Sisbn = pgdb.escape_string(raw_input("isbn: ")).strip()
Sdate = pgdb.escape_string(raw_input("Ship date: ").strip())
#QUERY THAT HELPS
query = "SELECT stock FROM stock WHERE isbn = '%s';" % (Sisbn)
print query
except (NameError, ValueError, TypeError, SyntaxError) as e:
print "Your input is wrong: " + e
return
#HERE YOU SHOULD start a transaction
#YOU NEED TO Catch exceptions ie bad queries
#---------------- TRANSACTION -------------------------
try:
self.cur.execute(query)
stockInfo = self.cur.fetchall() #matrix [[31]]
print stockInfo
print stockInfo[0][
0] #printing what is in our first row&col of cursor i.e. stock siffra
for stock in stockInfo:
print "STOOOOOCK FOR ISBN: " + str(stock)
cnt = 0
if stockInfo:
cnt = stockInfo[0][0]
if cnt < 1:
print "No more books in stock :("
return
else:
print "We have the book in stock (" + str(
cnt) + " books left)"
else:
print "Sry, no more books w that ISBN in stock"
except (pgdb.Error) as e:
print e
print "Sry cannot find any stock..."
return
#HERE YOU NEED TO USE THE RESULT OF THE QUERY TO TEST IF THER ARE
#ANY BOOKS IN STOCK
# YOU NEED TO CHANGE THIS TO SOMETHING REAL
#ev en till check av stock>0 har?
query = """UPDATE stock SET stock=stock-1 WHERE isbn='%s';""" % (Sisbn)
print query
#YOU NEED TO Catch exceptions and rollback the transaction
try:
self.cur.execute(query)
self.conn.commit()
print "stock decremented"
except pgdb.Error as e:
self.conn.rollback()
print e
return
query = """INSERT INTO shipments VALUES (%i, %i, '%s','%s');""" % (
SID, CID, Sisbn, Sdate)
print query
#YOU NEED TO Catch exceptions and rollback the transaction
try:
self.cur.execute(query)
print("\n---------------")
print "shipment created"
print("---------------\n")
except pgdb.Error as e:
self.conn.rollback()
print e
return
# This ends the transaction (and starts a new one)
self.conn.commit()
def escape_pg_string(string):
return "'" + pgdb.escape_string(string) + "'"
def make_instr_config(self, flaggingname=None):
"""
based on a recipe from Bryna Hazelton
2012-05
"""
if (self.gpstime is None):
logger.error('Must supply gpstime for determining instr_config')
return
if (self.db is None):
logger.error('Cannot retrieve configuration information without database connection')
return
self.get_tiles_to_flag(flaggingname=flaggingname)
# figure out which receivers are active
active_receivers = dbobj.execute('select receiver_id from receiver_info where active = true and begintime < %d and endtime > %d' % (self.gpstime,self.gpstime), db=self.db)
self.active_receivers = [x[0] for x in active_receivers]
logger.info('Found active receivers %s' % self.active_receivers)
if len(self.active_receivers)<1:
logger.error('No active receivers found for gpstime=%d' % self.gpstime)
return
if 102 in self.active_receivers:
# the offset for mapping from each Rx to the slot numbers
rxslotoffset={1:0,102:16,3:32,4:48}
else:
rxslotoffset={1:0,2:16,3:32,4:48}
slotispowered={}
for receiver in self.active_receivers:
slotispowered[int(receiver)]=True
try:
slot_power=dbobj.execute('select slot_power from obsc_recv_cmds where rx_id=%s and starttime=%d' % (
pgdb.escape_string(receiver),self.gpstime), db=self.db)[0][0]
# if any of them is False
if 'f' in slot_power:
slotispowered[int(receiver)]=False
except:
logger.warning('Unable to retrieve slot power information for gpstime=%d, Rx=%s' % (self.gpstime,receiver))
pass
# loop over each input
# figure out which receiver and tile it connects to
for inputnumber in xrange(_NINP):
for receiver in rxslotoffset.keys():
# get the offset
if rxslotoffset[receiver]==(inputnumber / _INPUTSPERRX)*_INPUTSPERRX:
break
slotoffset=rxslotoffset[receiver]
slot=self._inptoslot[inputnumber-slotoffset]
pol=self._inptopol[inputnumber-slotoffset]
tile=dbobj.execute('select tile from tile_connection where receiver_id = %s and receiver_slot = %d and begintime < %d and endtime > %d' % (
receiver,slot,self.gpstime,self.gpstime), db=self.db)[0][0]
cable_electrical_length=dbobj.execute('select physlength from cable_info ci inner join tile_connection tc on ci.name = tc.cable_name where tc.tile=%s and ci.begintime < %d and ci.endtime > %d and tc.begintime < %d and tc.endtime > %d' % (
pgdb.escape_string(tile),self.gpstime,self.gpstime,
self.gpstime,self.gpstime), db=self.db)[0][0]
# flagging
flagthisinput=False
if not slotispowered[int(receiver)]:
flagthisinput=True
if tile in self.tiles_to_flag or str(tile) in self.tiles_to_flag:
flagthisinput=True
t=tile_config(tile=tile, receiver=receiver, inputnumber=inputnumber, length=cable_electrical_length,
pol=pol.upper(), slot=slot, flag=flagthisinput)
self.inputs[inputnumber]=t
def escape_pg_string(string):
return "'" + pgdb.escape_string(string) + "'"
def update_stock(self, title, stock):
self.cur.execute("UPDATE stock SET stock='" + pgdb.escape_string(stock) + "' WHERE title='" + pgdb.escape_string(title) + "';")
