def change_address_customer(ch, id, addr): if ch == 1: sql = "update address set line1 = :line1 where customer_id = :id" cur.execute(sql, {"line1": addr, "id": id}) elif ch == 2: sql = "update address set line2 = :line2 where customer_id = :id" cur.execute(sql, {"line2": addr, "id": id}) elif ch == 3: sql = "update address set state = :state where customer_id = :id" cur.execute(sql, {"state": addr, "id": id}) elif ch == 4: sql = "update address set city = :city where customer_id = :id" cur.execute(sql, {"city": addr, "id": id}) elif ch == 5: sql = "update address set pincode = :pincode where customer_id = :id" cur.execute(sql, {"pincode": addr, "id": id}) else: return con.commit() print("Details Updated Successfully")
def avail_book(): sql = "select * from book where status='Available'" cur.execute(sql) data = cur.fetchall() for line in data: print(line[0], line[1], line[2], line[3], line[4], line[5]) con.commit
def check_history(): sql = """select * from issuehistory""" cur.execute(sql) data = cur.fetchall() for line in data: print("Book ID:", line[0], "Customer ID:", line[1], "STATUS:", line[2]) con.commit()
def change_address_customer(ch, _id, addr): if ch == 1: sql = "update address set line1 = %s where customer_id = %s" cur.execute(sql, (addr, _id)) elif ch == 2: sql = "update address set line2 = %s where customer_id = %s" cur.execute(sql, (addr, _id)) elif ch == 3: sql = "update address set state = %s where customer_id = %s" cur.execute(sql, (addr, _id)) elif ch == 4: sql = "update address set city = %s where customer_id = %s" cur.execute(sql, (addr, _id)) elif ch == 5: sql = "update address set pincode = %s where customer_id = %s" cur.execute(sql, (addr, _id)) else: return con.commit() print("Details Updated Successfully")
def __init__(self, projectid, nx = 10, ny = 10, nper = 1, lay_mode = 0, strt_mode = 0, strs_mode = 0): #mode 0 : properties defined at layer level, mode 1: properties defined at units level self.id = projectid self.dictofobjects = {} cur.execute ("""with objects as (select model_object_id from projects_model_objects where project_id = %s) select distinct discr from model_objects inner join objects on model_objects.id = objects.model_object_id;""",([self.id])) objects = cur.fetchall() for theobject in objects: self.dictofobjects[theobject[0]] = [] cur.execute(""" with objects as (select model_object_id from projects_model_objects where project_id = %s) select id as layerid from model_objects inner join objects on model_objects.id = objects.model_object_id where discr = %s; """, ([self.id, theobject[0]])) temp = cur.fetchall() for i in temp: self.dictofobjects[theobject[0]].append(i[0]) self.nx = nx self.ny = ny self.nper = nper self.lay_mode = lay_mode self.strt_mode = strt_mode self.strs_mode = strs_mode self.perlen = [] self.nstp = [] self.stress_period_list = [] #################### Perlen, Nstp ################################## if self.strs_mode == 0: for i in range(self.nper): self.perlen.append(30) self.nstp.append(30) self.stress_period_list.append(i) else: for i in range(self.nper): self.perlen.append(100) self.nstp.append(100) self.stress_period_list.append(i)
def get_report_no_fd_loan(): sql = """select customer_id,first_name,last_name from customers where customer_id not in (select distinct(customer_id) from accounts_fd) and customer_id not in (select distinct(customer_id) from accounts_loans)""" cur.execute(sql) res = cur.fetchall() return res
def update_customer(customer): id = customer.get_customer_id() status = customer.get_status() att = customer.get_login_attempts() sql = "update customers set status = :status,login_attempts = :att where customer_id = :id" cur.execute(sql, {"status": status, "att": att, "id": id}) con.commit()
def all_books(): sql = "select * from book" cur.execute(sql) data = cur.fetchall() for line in data: print(line[0], line[1], line[2], line[3], line[4], line[5]) con.commit
def update_customer(customer): id = customer.get_customer_id() status = customer.get_status() att = customer.get_login_attempts() sql = "update customers set status = %s,login_attempts = %s where customer_id = %s" cur.execute(sql, (status, att, id)) con.commit()
def issued_books(c_id): sql = """select a.book_id,a.title,b.status from book a,issuehistory b where a.book_id=b.book_id and b.customer_id= :c_id""" cur.execute(sql, {"c_id": c_id}) data = cur.fetchall() for line in data: print(line[0], line[1], line[2]) con.commit
def all_books(): sql = "select * from book" cur.execute(sql) data = cur.fetchall() for line in data: print("TITLE:", line[0], "AUTHOR:", line[1], "PUBLISHER:", line[2], "YEAR:", line[3], "BOOK ID:", line[4], "STATUS:", line[5]) con.commit
def issued_books(c_id): sql = """select a.book_id,a.title,b.status,b.book_date from book a,issuehistory b where a.book_id=b.book_id and b.customer_id= :c_id""" cur.execute(sql, {"c_id": c_id}) data = cur.fetchall() for line in data: print("BOOK ID:", line[0], "TITLE:", line[1], "STATUS:", line[2], "DATE:", line[3]) con.commit
def make_all_tables3(): sql = """create table issuehistory( book_id varchar2(5), customer_id number(5), status varchar2(15) check (status in('Issued','Returned')), )""" cur.execute(sql) con.commit()
def all_customers(): sql = "select * from customers order by customer_id" cur.execute(sql) data = cur.fetchall() for line in data: print("ID:", line[0], "FNAME:", line[1], "LNAME:", line[2], "ADDRESS:", line[3], "PASSWORD: ******") con.commit
def login_customer(c_id, password): sql = "select count(*) from customers where customer_id = %s and password = %s" cur.execute(sql, (c_id, password)) res = cur.fetchall() count = res[0][0] if count == 1: return True else: return False
def make_all_tables(): sql = """create table customers( customer_id number(5) primary key, first_name varchar2(10), last_name varchar2(10), address varchar2(40), password varchar2(20))""" cur.execute(sql) con.commit()
def login_admin(id, password): sql = "select count(*) from admin where admin_id = :id and password = :password" cur.execute(sql, {"id": id, "password": password}) res = cur.fetchall() count = res[0][0] if count == 1: return True else: return False
def login_admin(id, password): sql = "select count(*) from admin where admin_id = %s and password = %s" cur.execute(sql, (id, password)) res = cur.fetchall() count = res[0][0] if count == 1: return True else: return False
def get_loan_customer(acc_no,loan_amt,loan_term): sql = "select loan_id_sequence.nextval from dual" cur.execute(sql) res = cur.fetchall() loan_id = res[0][0] sql = "insert into loans values (:acc_no,:loan_id,:amount,:loan_term)" cur.execute(sql , {"acc_no":acc_no, "loan_id":loan_id, "loan_term":loan_term, "amount":loan_amt}) con.commit() print("Loan Availed Successfully")
def check_customer_exists(id): sql = "select count(*) from customers where customer_id = %s" cur.execute(sql, (id, )) res = cur.fetchall() count = res[0][0] if count == 1: return True else: return False
def check_book_exists(id): sql = "select count(*) from book where book_id = :id" cur.execute(sql, {"id": id}) res = cur.fetchall() count = res[0][0] if count == 1: return True else: return False
def make_all_tables2(): sql = """create table admin( admin_id number(5), password varchar2(10))""" cur.execute(sql) con.commit() sql = "insert into admin values(227,'helloadmin')" cur.execute(sql) con.commit()
def get_loan_fd_report(): sql = """select c.customer_id,c.first_name,c.last_name,sum.loan_amount,sum.amount from (select al.customer_id,al.loan_amount,af.amount from (select customer_id,sum(loan_amount) as loan_amount from accounts_loans group by customer_id) al, (select customer_id,sum(amount) as amount from accounts_fd group by customer_id) af where al.customer_id = af.customer_id) sum,customers c where sum.customer_id = c.customer_id and sum.loan_amount > sum.amount """ cur.execute(sql) res = cur.fetchall() return res
def make_all_tables1(): sql = """create table book( title varchar2(15), author varchar2(30), publisher varchar2(30), pub_year number(4), book_id varchar2(30) primary key, status varchar2(10) CHECK (status in('Issued','Due','Available')))""" cur.execute(sql) con.commit()
def get_transactions_account(acc_no, date_from, date_to): sql = """select transaction_date,type,amount,balance from transactions where account_no = :acc_no and transaction_date between :date_from and :date_to order by transaction_id""" cur.execute(sql, { "acc_no": acc_no, "date_from": date_from, "date_to": date_to }) res = cur.fetchall() return res
def get_all_info_customer(id): sql = "select * from customers where customer_id = :id" cur.execute(sql, {"id": id}) res = cur.fetchall() if len(res) == 0: return None customer = Customer() status = res[0][3] att = res[0][4] customer.set_customer_id(id) return customer
def set_single(self): """ Impotrs property values from the database for cases when they are defined for layers - no interpolation is needed, single value for the layer """ for i in self.properties_id: try: cur.execute("""WITH prop as (SELECT id FROM properties where model_object_id = %s AND model_object_property_type_id = %s) SELECT value FROM property_values INNER JOIN prop ON property_values.id = prop.id;""",([self.id, i])) val = cur.fetchall()[0][0] self.single_properties[i] = val except: print 'propery '+str(i)+' was not found'
def money_deposit_customer(account, amount): bal = account.get_balance() acc_no = account.get_account_no() type = TransactionType.credit sql = "update accounts set balance = %s where account_no = %s" cur.execute(sql, (bal, acc_no)) sql = "insert into transactions(account_no, type, amount, balance, transaction_date) values (%s, %s, %s, %s, %s);" date = datetime.datetime.now().strftime("%Y-%m-%d") data = (acc_no, type.value, amount, bal, date) cur.execute(sql, data) con.commit()
def close_account_customer(account): acc_no = account.get_account_no() balance = account.get_balance() sql = "update accounts set status='closed',balance = 0 where account_no = %s" cur.execute(sql, (acc_no, )) closed_on = datetime.datetime.now().strftime("%Y-%m-%d") sql = "insert into closed_accounts values(%s, %s)" cur.execute(sql, (acc_no, closed_on)) print("Account Closed Successfully !") print("Rs ", balance, " will be delivered to your address shortly") con.commit()
def add_book(book): title = book.get_title() author = book.get_author() publish=book.get_publish() pub_year = book.get_pub_year() b_id=book.get_b_id() status=book.get_status() sql = "insert into book values(:title,:author,:publish,:pub_year,:id,:status)" cur.execute(sql, {"title":title, "author":author, "publish":publish , "pub_year":pub_year, "id":b_id, "status":status}) con.commit() print("Book Added")
def close_account_customer(account): acc_no = account.get_account_no() balance = account.get_balance() sql = "update accounts set status='closed',balance = 0 where account_no = :acc_no" cur.execute(sql, {"acc_no": acc_no}) closed_on = datetime.datetime.now().strftime("%d-%b-%Y") sql = "insert into closed_accounts values(:acc_no,:closed_on)" cur.execute(sql, {"acc_no": acc_no, "closed_on": closed_on}) print("Account Closed Successfully !") print("Rs ", balance, " will be delivered to your address shortly") con.commit()
def get_all_info_customer(id): sql = "select * from customers where customer_id = %s" cur.execute(sql, (id, )) res = cur.fetchall() if len(res) == 0: return None customer = Customer() status = res[0][3] att = res[0][4] customer.set_customer_id(id) customer.set_status(status) customer.set_login_attempts(att) return customer
def __init__(self, id_list): self.id_list = id_list self.points = [] self.values_list = [] self.xlist = [] self.ylist = [] p_index = 0 for point in self.id_list: cur.execute(""" select st_x(geometry) from observation_points where id = %s;""", [point]) x = cur.fetchall()[0][0] self.points.append([x]) self.xlist.append(x) cur.execute(""" select st_y(geometry) from observation_points where id = %s;""", [point]) y = cur.fetchall()[0][0] self.points[p_index].append(y) self.ylist.append(y) p_index += 1 val_list = [] for i in range(len(self.id_list)): val_list.append([]) cur.execute("""with prop_id as (select id from properties where model_object_id = %s) select id from values where property = (select id from prop_id) limit 24;""", [self.id_list[i]]) val_id = cur.fetchall() for j in val_id: cur.execute(""" select value from property_time_values where id = %s; """, [j[0]]) val_list[i].append(cur.fetchall()[0][0]) for i in range(len(val_list[0])): self.values_list.append([]) for j in val_list: self.values_list[i].append(j[i])
def __init__(self, area_id, nx, ny): self.areaid = area_id[0] self.nx = nx self.ny = ny cur.execute(""" SELECT ST_XMIN(geometry) from areas where id = %s; """, ([self.areaid])) self.xmin = cur.fetchall()[0][0] cur.execute(""" SELECT ST_XMAX(geometry) from areas where id = %s; """, ([self.areaid])) self.xmax = cur.fetchall()[0][0] cur.execute(""" SELECT ST_YMIN(geometry) from areas where id = %s; """, ([self.areaid])) self.ymin = cur.fetchall()[0][0] cur.execute(""" SELECT ST_YMAX(geometry) from areas where id = %s; """, ([self.areaid])) self.ymax = cur.fetchall()[0][0] self.dx = (self.xmax - self.xmin)/self.nx self.dy = (self.ymax - self.ymin)/self.ny
def __init__(self, bo_ids, op, area): self.area = area self.bo = bo_ids self.op = op self.values_list = op.values_list self.line = [] for i in self.bo: cur.execute("""with dump as (select (st_dumppoints(geometry)).* from boundaries where id = %s) select st_x(geom) from dump;""", [i]) xlist = cur.fetchall() cur.execute("""with dump as (select (st_dumppoints(geometry)).* from boundaries where id = %s) select st_y(geom) from dump;""", [i]) ylist = cur.fetchall() for i in xlist: self.line.append(list(i)) for i in range(len(ylist)): self.line[i] += ylist[i] self.SPD_multi = {} #!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! self.line_cols, self.line_rows = intersector.line_area_intersect(line = self.line, xmax = self.area.xmax, xmin = self.area.xmin, ymax = self.area.ymax, ymin = self.area.ymin, nx = self.area.nx, ny = self.area.ny) #!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! self.vals = []
def set_properties(self): """ """ cur.execute("""SELECT geological_layer_id FROM geological_layers_geological_units WHERE geological_unit_id = %s;""",([self.id])) self.geological_layer_id = cur.fetchall()[0][0] for i in self.properties_id: # sets values of the properties to the proprties dictionary cur.execute("""WITH prop as (SELECT id FROM properties where model_object_id = %s AND property_type_id = %s), id as (select id FROM values where property = (select id from prop)) select value from property_values where id = (select id from id);""",([self.id, i])) try: val = cur.fetchall()[0][0] self.properties[i] = val except: self.properties[i] = -9999 cur.execute("""SELECT geological_point_id FROM geological_units where id = %s;""",([self.id])) self.borehole_id = cur.fetchall()[0][0] cur.execute("""SELECT top_elevation FROM geological_units where id = %s;""",([self.id])) self.top = cur.fetchall()[0][0] cur.execute("""SELECT bottom_elevation FROM geological_units where id = %s;""",([self.id])) self.bottom = cur.fetchall()[0][0] cur.execute("""SELECT ST_X(geometry) FROM geological_points WHERE id = %s;""",([self.borehole_id])) self.x = cur.fetchall()[0][0] cur.execute("""SELECT ST_Y(geometry) FROM geological_points WHERE id = %s;""",([self.borehole_id])) self.y = cur.fetchall()[0][0]