def GetCustomerConnect(cid): try: objcon = con.dbDetail() cur = objcon.cursor() logger.info("inside get customer connection details") cur.execute( "Select s.dbusername ,s.dbpassword ,s.dbname, d.dbserverip from customer s INNER JOIN dbservers d on s.dbserverid = d.dbserverid where s.custid = (%s)", [cid]) objcon.commit() print('Data from joins fetched successfully.') rows = cur.fetchall() dname = rows[0][2] logger.info(dname) uname = rows[0][0] pwd = rows[0][1] host = rows[0][3] return psycopg2.connect(database=dname, user=uname, password=pwd, host=host, port="5432") except Exception as e: logger.error(e) return False
def SELECT_QUERY(): objcon = con.dbDetail() cur = objcon.cursor() cur.execute("SELECT * from user_details ") print('Data entered successfully.') rows = cur.fetchall() rowarray_list = [] for row in rows: t = (row[0], row[1], row[2], row[3], row[4], row[5]) rowarray_list.append(t) # logger.info(rowarray_list) j = json.dumps(rowarray_list) # logger.info(j) objects_list = [] for row in rows: d = collections.OrderedDict() d['userid'] = row[0] d['employee_id'] = row[1] d['Firstname'] = row[2] d['Lastname'] = row[3] d['Address'] = row[4] objects_list.append(d) j = json.dumps(objects_list) logger.info("logged j value") if (objcon): objcon.close() print("\nThe connection is closed.") return j print('Data displayed successfully.')
def SELECT_DB(): objcon=con.dbDetail() cur=objcon.cursor() cur.execute("SELECT dbserverid , dbserverip from dbservers ") rows = cur.fetchall() rowarray_list = [] for row in rows: t = (row[0],row[1]) rowarray_list.append(t) # logger.info(rowarray_list) j = json.dumps(rowarray_list) # logger.info(j) objects_list = [] for row in rows: d = collections.OrderedDict() d['dbseverid'] = row[0] d['dbseverip'] = row[1] # d[row[0]] =row[1] objects_list.append(d) j = json.dumps(objects_list) logger.info(j) # logger.info("logged j value") if(objcon): objcon.close() print("\nThe connection is closed.") return j print ( 'Data displayed successfully.' )
def Validate_Login(userid, password): try: user = dob.User() objcon = con.dbDetail() cur = objcon.cursor() # query_select = ("SELECT * from user_details where user_id = '"+str(userid)+"'") logger.info("entered validate_login") cur.execute("SELECT * from user_details where user_id = (%s)", [userid]) # cur.execute(query_select) rows = cur.fetchall() logger.info(rows) logger.info("data fetched") rowarray_list = [] for row in rows: if (ut.compare_hash(password, row[5])): user.firstname = row[2] user.lastname = row[3] if (objcon): objcon.close() print("\nThe connection is closed.") return user except Exception as e: logger.error(e) return False
def CUSTOMER_ADD_QUERY (daobj) : try : objcon=con.dbDetail() cur=objcon.cursor() cur.execute("INSERT INTO customer(custname,custaddress,dbserverid,dbname,dbusername,dbpassword) VALUES(%s, %s,%s, %s, %s,%s)",(daobj.custname, daobj.custaddress,daobj.dbserverid,daobj.dbname,daobj.dbusername,daobj.dbpassword) ) objcon.commit() print ( 'Data entered successfully.' ) cur.execute("SELECT custid from customer where custname = (%s)",[daobj.custname]) rows =cur.fetchall() logger.info(rows) logger.info(str(rows)) for row in rows: customer_id = row[0] if (objcon): objcon.close() print("\nThe connection is closed.") return customer_id except Exception as e : logger.error(e) return False
def get_last_access(token): try: logger.info("entered get last access time") objcon = con.dbDetail() cur = objcon.cursor() logger.info("get last accessed") logger.info(token) # cur.execute("Select last_accessed_time from session_details where token =(%s)",(str(token))) cur.execute( "Select last_accessed_time from session_details where token_issued = '" + str(token) + "'") rows = cur.fetchall() logger.info("fetched time") for row in rows: logger.info(row[0]) if (objcon): objcon.close() print("\nThe connection is closed.") logger.info("returning time") return row[0] except Exception as e: logger.error(e) return False
def delete_token(token): try: objcon = con.dbDetail() cur = objcon.cursor() logger.info("entered delete token") objcon = con.dbDetail() cur = objcon.cursor() cur.execute("Delete from session_details where token_issued = '" + str(token) + "'") objcon.commit() logger.info("deleted") if (objcon): objcon.close() print("\nThe connection is closed.") except Exception as e: logger.error(e) return False
def CreateCustomerDB(cid): try: objcon = con.dbDetail() cur = objcon.cursor() #fetch db details of customer logger.info("inside get customer connection details") cur.execute( "Select s.dbusername ,s.dbpassword ,s.dbname, d.dbserverip from customer s INNER JOIN dbservers d on s.dbserverid = d.dbserverid where s.custid = (%s)", [cid]) objcon.commit() print('Data from joins fetched successfully.') rows = cur.fetchall() dname = rows[0][2] logger.info(dname) uname = rows[0][0] pwd = rows[0][1] host = rows[0][3] objcon.autocommit = True sqlCreateDatabase = "create database " + dname + ";" query = " CREATE USER " + uname + " WITH ENCRYPTED PASSWORD '" + pwd + "' ; GRANT ALL PRIVILEGES ON DATABASE " + dname + " TO " + uname + ";" cur.execute(sqlCreateDatabase) cur.execute(query) conn = psycopg2.connect(database=dname, user=uname, password=pwd, host=host, port="5432") # objact = dob.Action() # objact.status= "processed" conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) cursor = conn.cursor() conn.autocommit = True cursor.execute( "CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL)" ) logger.info("table created") if (rows.count == 0): return None if (objcon): objcon.close() print("\nThe connection is closed.") return except Exception as e: logger.error(e) return None
def test_CustomerAdd(self): daobj = dob.Customer() daobj.custid = 115 daobj.custname = 'customertest' daobj.custaddress = 'india' daobj.dbserverid = 1 daobj.dbname = 'testcustdb' daobj.dbusername = '******' daobj.dbpassword = '******' objcon = con.dbDetail() cur = objcon.cursor() # cur.execute("INSERT INTO customer(custname,custaddress,dbserverid,dbname,dbusername,dbpassword) VALUES(%s, %s,%s, %s, %s,%s)",(daobj.custname, daobj.custaddress,daobj.dbserverid,daobj.dbname,daobj.dbusername,daobj.dbpassword) ) self.assertTrue(cf.CUSTOMER_ADD_QUERY(daobj)) objcon.commit() if (objcon): objcon.close() print("\nThe connection is closed.")
def DBCreation(customer_id): #fetch #update #query for db creation try: logger.info("inside DBCreation") objcon= con.dbDetail() cur=objcon.cursor() objcon.autocommit = True objact = dob.Action() objact.status = "processing" Customer_Info.UPDATE_ACTIONS_STATUS(objact.status,customer_id) connect.CreateCustomerDB(customer_id) objact.status = "processed" Customer_Info.UPDATE_ACTIONS_STATUS(objact.status,customer_id) return True except Exception as e : logger.error(e) return False
def update_last_access(token): try: objcon = con.dbDetail() cur = objcon.cursor() logger.info("entered update last access") last_access = datetime.now() cur.execute("Update session_details set last_accessed_time = '" + str(last_access) + "' where token_issued ='" + str(token) + "'") objcon.commit() logger.info("updated successfully") if (objcon): objcon.close() print("\nThe connection is closed.") except Exception as e: logger.error(e) return False
def UPDATE_SERVER_QUERY(server_status,sid): try : objcon=con.dbDetail() cur=objcon.cursor() logger.info("inside update server info method") cur.execute("UPDATE dbservers set status = %s where dbserverid = (%s)",(server_status,sid) ) objcon.commit() print ( 'Data Updated successfully in dbservers.' ) if (objcon): objcon.close() print("\nThe connection is closed.") return True except Exception as e : logger.error(e) return False
def ACTION_ADD_QUERY (objact) : try : objcon=con.dbDetail() cur=objcon.cursor() logger.info("inside action add query method") cur.execute("INSERT INTO actions(customerid,status,remarks)VALUES(%s,%s, %s)",(objact.cid ,objact.status,objact.remarks) ) objcon.commit() print ( 'Data entered successfully in actions.' ) if (objcon): objcon.close() print("\nThe connection is closed.") return True except Exception as e : logger.error(e) return False
def UPDATE_ACTIONS_STATUS(status,cid): try : objcon=con.dbDetail() cur=objcon.cursor() logger.info("inside update actions status method") cur.execute("UPDATE actions set status = %s where customerid = (%s)",(status,cid) ) objcon.commit() print ( 'Data Updated successfully in actions.' ) if (objcon): objcon.close() print("\nThe connection is closed.") return True except Exception as e : logger.error(e) return False
def Reset_password(userid, password): try: objcon = con.dbDetail() cur = objcon.cursor() # Sql = "UPDATE login_credentials SET p_word= (%s) WHERE username = (%s)" # data = (password , username) # cur.execute(Sql, data) cur.execute( "UPDATE user_details SET pwd= (%s) WHERE user_id = (%s)", (password.decode('ascii'), userid)) # cur.execute("UPDATE login_credentials SET p_word= '"+str(password).replace("'","''")+"' WHERE username = '******'") objcon.commit() print('Data updated successfully.') if (objcon): objcon.close() print("\nThe connection is closed.") return True except Exception as e: logger.error(e) return False
def session_entries(userid, token, time, last_time): try: objcon = con.dbDetail() cur = objcon.cursor() cur.execute( "INSERT INTO session_details(u_id,token_issued,issued_time,last_accessed_time) VALUES(%s, %s,%s,%s)", (userid, token, time, last_time)) objcon.commit() print('Data entered successfully.') if (objcon): objcon.close() print("\nThe connection is closed.") return True except Exception as e: logger.error(e) return False
def EMPLOYEE_ADD_QUERY(daobj): try: objcon = con.dbDetail() cur = objcon.cursor() cur.execute( "INSERT INTO user_details(user_id,e_id,first_name,last_name,address,pwd) VALUES(%s, %s,%s, %s, %s,%s)", (daobj.user_id, daobj.e_id, daobj.first_name, daobj.last_name, daobj.address, daobj.pwd.decode('ascii'))) objcon.commit() print('Data entered successfully.') if (objcon): objcon.close() print("\nThe connection is closed.") return True except Exception as e: logger.error(e) return False