예제 #1
0
class TestDatabaseUtils(unittest.TestCase):

    def setUp(self):
        self.pgdb = DBUtils(password=os.environ['PSYCOPG2_DB_PASS'])

    def testNetworkList(self):
        net_s, net_i, net_l = self.pgdb.fetchNetworksList()

        # Verify that the list of networks are equal.
        self.assertEqual(
            len(net_i), len(net_l),
            "Length of network list doesn't match number of integers.")

        # Verify that the net_s is a single string
        self.assertEqual(
            type(net_s), str,
            "Network string is not a single string.")

        net_s_2, net_i_2 = self.pgdb.fetchNetworkCmdPrettyPrint()

        # Verify that the values returned by these two functions
        # are identical.
        self.assertEqual(
            net_s, net_s_2,
            "Network strings for command prompt don't match!")

        self.assertEqual(
            net_i, net_i_2,
            "List of valid network integers don't match.")
예제 #2
0
    def insert_details(self, data, project_path, isghb, cycle_id, status, obs_no):
        print("------------------==insert_details==----------------------")
        print(data)
        print(project_path)
        dbutils = DBUtils()
        for each_rec in data:
            print("==each_rec==", each_rec)
            lta_file = os.path.basename(each_rec)
            if len(lta_file) == 1:
                lta_file = lta_file[0]
            try:
                current_date_timestamp = datetime.datetime.fromtimestamp(currentTimeInSec).strftime('%Y-%m-%d %H:%M:%S')
                lta_details = gS.get_naps_scangroup_details(lta_file)
                utils = self
                lta_details["ltacomb_size"] = int(utils.calculalate_file_sizse_in_MB(each_rec))
                lta_details["status"] = "unprocessed"
                lta_details["base_path"] = project_path
                lta_details["start_time"] = current_date_timestamp
                lta_details["proposal_dir"] = project_path.split('/')[-1]
                lta_details["pipeline_id"] = 1
                lta_details["comments"] = status
                lta_details["counter"] = 0
                lta_details["ltacomb_file"] = lta_file
                lta_details["isghb"] = isghb
                lta_details["cycle_id"] = cycle_id
                if obs_no:
                    lta_details["observation_no"] = int(obs_no)


                projectobsno_data = {}
                for key in tableSchema.projectobsnoData.iterkeys():
                    if key in lta_details.iterkeys():
                        projectobsno_data[key] = lta_details[key]

                ltadetails_data = {}
                for key in tableSchema.ltadetailsData.iterkeys():
                    if key in lta_details.iterkeys():
                        ltadetails_data[key] = lta_details[key]
                #print ltadetails_data
                # print("ltadetails_data")
                # print(ltadetails_data)

                columnKeys = {"project_id"}
                whereKeys = {"proposal_dir": lta_details["proposal_dir"], "cycle_id": cycle_id}

                project_id = dbutils.select_test_table("projectobsno", columnKeys, whereKeys, 0)

                if project_id:
                    project_id = project_id[0]
                else:
                    project_id = dbutils.insert_into_table("projectobsno", projectobsno_data, tableSchema.projectobsnoId)

                ltadetails_data["project_id"] = project_id

                lta_id = dbutils.insert_into_table("ltadetails", ltadetails_data, tableSchema.ltadetailsId)
                print lta_id
                print("projectobsno")
                print(projectobsno_data)
            except Exception as e:
                print e
예제 #3
0
class TestDatabaseUtils(unittest.TestCase):
    def setUp(self):
        self.pgdb = DBUtils(password=os.environ['PSYCOPG2_DB_PASS'])

    def testNetworkList(self):
        net_s, net_i, net_l = self.pgdb.fetchNetworksList()

        # Verify that the list of networks are equal.
        self.assertEqual(
            len(net_i), len(net_l),
            "Length of network list doesn't match number of integers.")

        # Verify that the net_s is a single string
        self.assertEqual(type(net_s), str,
                         "Network string is not a single string.")

        net_s_2, net_i_2 = self.pgdb.fetchNetworkCmdPrettyPrint()

        # Verify that the values returned by these two functions
        # are identical.
        self.assertEqual(net_s, net_s_2,
                         "Network strings for command prompt don't match!")

        self.assertEqual(net_i, net_i_2,
                         "List of valid network integers don't match.")
예제 #4
0
 def getEmRo(self, fullname):
     query1 = "select employeeid from employeewithrole where lower(fullname) like lower('" + str(
         fullname) + "')"
     employeeid = DBUtils.getRow(self._conn, query1)
     #query = "select * from EmployeeRolesView where employeeid =  " + str(employeeid[0])
     query = "SELECT E.EmployeeID, E.FirstName, E.LastName, CONCAT(E.FirstName, ' ' , E.LastName) AS FULLNAME, E.isEmployeed, E.ManagerID, (SELECT FullName FROM Managers where ManagerID = E.ManagerID) AS ManagerName, R.RoleName, ER.StartDate StartDate, ER.EndDate EndDate FROM  Employees E JOIN EmployeeRoles ER ON E.EmployeeID = ER.EmployeeID JOIN Roles R ON ER.RoleID = R.RoleID where employeeid =  " + str(
         employeeid[0])
     return DBUtils.getAllRows(self._conn, query)
 def openDBConnection(self, dbUser, dbPass, dbSID, dbHost, port):
     if (self._conn != None):
         self.closeDBConnection()
     try:
         self._conn = DBUtils.openDBConnection(dbUser, dbPass, dbSID,
                                               dbHost, port)
         res = DBUtils.testConnection(self._conn)
     except psycopg2.Error as e:
         print(e)
     return res
예제 #6
0
 def updateRoles(self, EngineOilSpendingAmount, EngineOilBuyingAmount,
                 CrudeOilSpendingAmount, CrudeOilBuyingAmount,
                 MotorOilSpendingAmount, MotorOilBuyingAmount,
                 GasolineBuyingAmount, GasolineSpendingAmount,
                 PetroleumSpendingAmount, PetroleumBuyingAmount,
                 InternalSpendingBudget, RoleNameT):
     query = "update roles set EngineOilSpendingAmount = %s, EngineOilBuyingAmount = %s,CrudeOilSpendingAmount = %s,CrudeOilBuyingAmount = %s,MotorOilSpendingAmount = %s,MotorOilBuyingAmount = %s,GasolineBuyingAmount = %s,GasolineSpendingAmount = %s,PetroleumSpendingAmount = %s,PetroleumBuyingAmount = %s,InternalSpendingBudget = %s where RoleName = %s"
     DBUtils.executeUpdate(self._conn, query,
                           (EngineOilSpendingAmount, EngineOilBuyingAmount,
                            CrudeOilSpendingAmount, CrudeOilBuyingAmount,
                            MotorOilSpendingAmount, MotorOilBuyingAmount,
                            GasolineBuyingAmount, GasolineSpendingAmount,
                            PetroleumSpendingAmount, PetroleumBuyingAmount,
                            InternalSpendingBudget, RoleNameT))
예제 #7
0
 def getLoginInfo(self, loginname, password):
     query = "select count(*) from employees where loginname = '" + loginname + "' and loginpassword = '******'"
     try:
         cnt = DBUtils.getVar(self._conn, query)
         if (cnt == 0):
             return -1
         else:
             query = "select count(*) from employees where loginname = '" + loginname + "' and loginpassword = '******' and employeeid = 1"
             cnt = DBUtils.getVar(self._conn, query)
             if (cnt == 0):
                 return 1
             else:
                 return 0
     except psycopg2.Error as e:
         print(e)
         return -1
예제 #8
0
 def getWorkers(self):
     query = "select w.name, w.ssn from social_workers w "
     rows = DBUtils.getAllRows(self._conn, query)
     workers = []
     for val in rows:
         workers.append(val[0])
     return workers
예제 #9
0
 def getIssues(self):
     query = "select iss.name, iss.iid from issues iss "
     rows = DBUtils.getAllRows(self._conn, query)
     issues = []
     for val in rows:
         issues.append(val[0])
     return issues
예제 #10
0
 def getWorkers_And_Clients(self, orderByClause):
     query = (
         "select w.name as \"Social Worker\", w.title as Title, count(i.ssn) as \"Num of clients\", min(i.sw_since) as \"Oldest Current Client\" "
         "from social_workers w left join individuals i on w.ssn=i.social_worker "
         "group by w.name, w.specialization, w.title "
         "order by %s" % orderByClause)
     return DBUtils.getAllRows(self._conn, query)
예제 #11
0
 def getIndividuals(self):
     query = (
         "select i.ssn, i.name, iss.name, s.name, s.title, i.date_joined, ci.street || ' ' || ci.city || ' ' || ci.state || ', ' || ci.zip as Address "
         "from individuals i, social_workers s, issues iss, contact_info ci "
         "where i.social_worker=s.ssn and i.issue=iss.iid and i.contact_info = ci.cid"
     )
     return DBUtils.getAllRows(self._conn, query)
예제 #12
0
 def display_statistics(self):
     query = """
     select iss.name, c.state, count(iss.iid)
     from individuals i, issues iss, contact_info c
     where i.issue=iss.iid and i.contact_info=c.cid
     group by iss.name, c.state
     order by iss.name, count(iss.iid) desc, c.state"""
     return DBUtils.getAllRows(self._conn, query)
예제 #13
0
    def saveEmployeeRoles(self, fullname, managername, rolename, startdate,
                          enddate):

        try:
            if (enddate == None):
                print("Must Specify an end date")
                return False
            roleid = DBUtils.getRow(
                self._conn, "select roleid from roles where rolename like '" +
                str(rolename) + "'")
            employeeid = DBUtils.getRow(
                self._conn,
                "select employeeid from (SELECT E.EmployeeID, E.FirstName, E.LastName, CONCAT(E.FirstName, ' ' , E.LastName) AS FULLNAME FROM  Employees E JOIN EmployeeRoles ER ON E.EmployeeID = ER.EmployeeID JOIN Roles R ON ER.RoleID = R.RoleID WHERE E.isEmployeed = 0) as data where fullname like '"
                + str(fullname) + "'")
            managerid = DBUtils.getRow(
                self._conn,
                "select ManagerID from (SELECT E.ManagerID, (SELECT FullName FROM Managers where ManagerID = E.ManagerID) AS ManagerName FROM  Employees E JOIN EmployeeRoles ER ON E.EmployeeID = ER.EmployeeID JOIN Roles R ON ER.RoleID = R.RoleID WHERE E.isEmployeed = 0) as data where ManagerName like '"
                + str(managername) + "'")
            query = "update employeeroles set roleid = " + str(
                roleid[0]) + ", startdate = '" + str(
                    startdate) + "', enddate = '" + str(
                        enddate) + "' where employeeid = " + str(employeeid[0])
            DBUtils.executeUpdate(self._conn, query)
            query = "update employees set managerid = " + str(
                managerid[0]) + " where employeeid = " + str(employeeid[0])
            DBUtils.executeUpdate(self._conn, query)
            return True
        except psycopg2.Error as e:
            print(e)
            return False
예제 #14
0
    def registerIndividual(self, newContactInfo, newClient):
        try:
            query = """
					insert into Contact_Info (street, city, zip, state) values (%s,%s,%s,%s)
				"""
            DBUtils.executeUpdate(
                self._conn, query,
                (newContactInfo.getStreet(), newContactInfo.getCity(),
                 newContactInfo.getZip(), newContactInfo.getState()))
            cid = DBUtils.getVar(self._conn,
                                 "select max(cid) from Contact_Info")
            i_issue = DBUtils.getVar(
                self._conn, "select iid from Issues where name='%s'" %
                newClient.getIssue())
            worker_ssn = DBUtils.getVar(
                self._conn, "select ssn from Social_Workers where name='%s'" %
                newClient.getWorker())
            query = """
					insert into Individuals (ssn, name, date_joined, issue, social_worker, sw_since, contact_info) values (%s,%s,%s,%s,%s,%s,%s)
				"""
            DBUtils.executeUpdate(
                self._conn, query,
                (newClient.getSSN().strip(), newClient.getName(),
                 newClient.getDateJoined().strftime('%m-%d-%Y'), i_issue,
                 worker_ssn.strip(),
                 newClient.getWorkerSince().strftime('%m-%d-%Y'), cid))
        except psycopg2.Error as e:
            print(e)
        return newClient
예제 #15
0
 def getEmployeeData(self, fullname):
     employeeRoles = None
     #query = "select * from EmployeeRolesView where lower(fullname) like lower('" + str(fullname) + "%')"
     query = "select * from (SELECT E.EmployeeID,E.FirstName,E.LastName,CONCAT(E.FirstName, ' ' , E.LastName) AS FULLNAME, E.isEmployeed,E.ManagerID,(SELECT FullName FROM Managers where ManagerID = E.ManagerID) AS ManagerName,R.RoleName,ER.StartDate StartDate,ER.EndDate EndDate FROM  Employees E JOIN EmployeeRoles ER ON E.EmployeeID = ER.EmployeeID JOIN Roles R ON ER.RoleID = R.RoleID) as data where lower(fullname) like lower('" + str(
         fullname) + "%')"
     row = DBUtils.getRow(self._conn, query)
     employeeRoles = EmployeeRole(row[0], row[1], row[2], row[3], row[4],
                                  row[5], row[6], row[7], row[8], row[9])
     return employeeRoles
예제 #16
0
 def getFilteredEmployees(self, fullname, managername, rolename):
     if (rolename == "All"):
         rolename = '%'
     if (managername == "All"):
         managername = '%'
     query = "SELECT * FROM (SELECT E.FirstName,E.LastName,CONCAT(E.FirstName, ' ' , E.LastName) AS FULLNAME,E.EmailAddress,R.RoleName,(SELECT FullName FROM Managers where ManagerID = E.ManagerID) AS ManagerName FROM  Employees E JOIN EmployeeRoles ER ON E.EmployeeID = ER.EmployeeID JOIN Roles R ON ER.RoleID = R.RoleID WHERE E.isEmployeed = 0 and lower(rolename) like lower('" + rolename + "%')) AS DATA WHERE lower(managername) like lower('" + str(
         managername) + "%') AND lower(FULLNAME) like lower('" + str(
             fullname) + "%')"
     return DBUtils.getAllRows(self._conn, query)
예제 #17
0
 def getAudits(self, num):
     query = ""
     if (num == 1):
         query = "select * from AuditEmployees"
     elif (num == 2):
         query = "select * from AuditEmployeeRoles"
     else:
         query = "select * from AuditRoles"
     return DBUtils.getAllRows(self._conn, query)
 def getFirstUser(self):
     try:
         query = """
             SELECT *
             FROM Customers
             LIMIT 1;
         """
     except psycopg2.Error as e:
         print(e)
     return DBUtils.getAllRows(self._conn, query)
예제 #19
0
 def display_worker_no_clients(self):
     query = """
     SELECT w.ssn, w.name, w.title, iss.name as Specialization, ci.state as "State Of Operation"
     FROM   social_workers w, issues iss, contact_info ci
     WHERE  w.ssn NOT IN (SELECT i.social_worker
                    FROM   individuals i, 
                           social_workers w
                    WHERE  w.ssn = i.social_worker) 
     and w.specialization=iss.iid and w.contact_info = ci.cid"""
     return DBUtils.getAllRows(self._conn, query)
    def getUserRating(self, deal, customer):
        try:
            # Find what a user rated a value, i.e. you rated this deal x out of 5
            # First check if the value exists
            query = """
                SELECT COUNT(*) FROM Ratings
                WHERE cid='{cid}' and did='{did}'
                """.format(cid=customer.getCid(), did=deal.getDid())
            result = DBUtils.getVar(self._conn, query)
            if result < 1:
                return None

            query = """
                SELECT value FROM Ratings
                WHERE cid='{cid}' and did='{did}'
                """.format(cid=customer.getCid(), did=deal.getDid())
            result = DBUtils.getVar(self._conn, query)
        except psycopg2.Error as e:
            print(e)
        return result
예제 #21
0
 def search(self, name):
     query = """
     select s.ssn, s.name, 'Social Worker' as type, ci.State 
     from social_workers s, contact_info ci
     where lower(s.name) like '%%%s%%' and s.contact_info=ci.cid
     UNION 
     select i.ssn, i.name, 'Individual' as type, ci.State
     from Individuals i, contact_info ci 
     where lower(i.name) like '%%%s%%' and i.contact_info=ci.cid 
     order by 1""" % (name.lower(), name.lower())
     return DBUtils.getAllRows(self._conn, query)
예제 #22
0
 def getRoleComponent(self, rolename):
     if (rolename == "All"):
         rolename = "%"
     role = None
     query = "select * from roles where rolename like '" + str(
         rolename) + "'"
     row = DBUtils.getRow(self._conn, query)
     role = Role(row[0], row[1], row[2], row[3], row[4], row[5], row[6],
                 row[7], row[8], row[9], row[10], row[11], row[12], row[13],
                 row[14])
     return role
 def getAllDeals(self):
     try:
         query = """SELECT Deals.title, Deals.description, Deals.avgRating, Deals.imageURL,
                 Deals.startDate, Deals.endDate, Business.name, Business.phoneNum, Business.businessId, Deals.dealId
                 FROM Deals
                 INNER JOIN Business ON Deals.bid=Business.businessId
                 ORDER BY Business.name
                 """
     except psycopg2.Error as e:
         print(e)
     return DBUtils.getAllRows(self._conn, query)
예제 #24
0
 def saveNewRoles2(self, RoleNameT, EngineOilSpendingAmount,
                   EngineOilBuyingAmount, CrudeOilSpendingAmount,
                   CrudeOilBuyingAmount, MotorOilSpendingAmount,
                   MotorOilBuyingAmount, GasolineBuyingAmount,
                   GasolineSpendingAmount, PetroleumSpendingAmount,
                   PetroleumBuyingAmount, InternalSpendingBudget):
     cnt = DBUtils.getVar(
         self._conn, "select count(*) from roles where rolename like '" +
         str(RoleNameT) + "'")
     if (RoleNameT is not None):
         if (len(str(RoleNameT)) > 0):
             if (cnt == 0):
                 query = "insert into roles (RoleName,EngineOilSpendingAmount,EngineOilBuyingAmount,CrudeOilSpendingAmount,CrudeOilBuyingAmount,MotorOilSpendingAmount,MotorOilBuyingAmount,GasolineBuyingAmount,GasolineSpendingAmount,PetroleumSpendingAmount,PetroleumBuyingAmount,InternalSpendingBudget) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                 DBUtils.executeUpdate(
                     self._conn, query,
                     (RoleNameT, EngineOilSpendingAmount,
                      EngineOilBuyingAmount, CrudeOilSpendingAmount,
                      CrudeOilBuyingAmount, MotorOilSpendingAmount,
                      MotorOilBuyingAmount, GasolineBuyingAmount,
                      GasolineSpendingAmount, PetroleumSpendingAmount,
                      PetroleumBuyingAmount, InternalSpendingBudget))
예제 #25
0
def __prerequisites():
    """
    Fetch the basic proposal data depending on the CYCLE_ID
    This is basically stage0
    :return:
    """
    CYCLE_ID = Pipeline().pipeline_configuration()["cycle_id"]
    CYCLE_PATH = Pipeline().pipeline_configuration()["cycle_path"]
    CYCLE_ID = str(CYCLE_ID)

    sql_query = "select distinct o.observation_no, p.proposal_id, g.file_path, p.backend_type from " \
                "gmrt.proposal p inner join das.observation o on p.proposal_id = o.proj_code " \
                "inner join das.scangroup g on g.observation_no = o.observation_no " \
                "inner join das.scans s on s.scangroup_id = g.scangroup_id " \
                "inner join gmrt.sourceobservationtype so on p.proposal_id = so.proposal_id " \
                "where p.cycle_id ='" + CYCLE_ID + "' " \
                                                   "and so.obs_type not like 'pulsar%' " \
                                                   "and so.obs_type not like 'phased array'" \
                                                   "and s.sky_freq1=s.sky_freq2 " \
                                                   "and s.sky_freq1 < 900000000 " \
                                                   "and s.chan_width >= 62500 " \
                                                   "and o.proj_code not like '16_279' " \
                                                   "and o.proj_code not like '17_072' " \
                                                   "and o.proj_code not like '18_031' " \
                                                   "and o.proj_code not like '19_043' " \
                                                   "and o.proj_code not like '20_083' " \
                                                   "and o.proj_code not like '21_057';"

    dbutils = DBUtils()

    data = dbutils.select_query(sql_query)
    gadpudata = {}
    for each_row in data:
        gadpudata[each_row[0]] = {
            "proposal_id": each_row[1],
            "file_path": each_row[2],
            "backend_type": each_row[3],
            "cycle_id": CYCLE_ID
        }
    return (gadpudata, CYCLE_PATH)
    def favoriteDeal(self, deal, customer):
        try:
            # Check if the customer has already favorited this deal (select from Favorites).
            query = """SELECT count(*) FROM Favorites 
                    WHERE cid='{cid}' and did='{did}'""".format(
                cid=customer.getCid(), did=deal.getDid())
            result = DBUtils.getVar(self._conn, query)

            # If so, return. You can only favorite once per deal
            if (result > 0):
                print("{name} has already favorited this deal".format(
                    name=customer.getFName()))
                return

            # Add favorite to favorite table, (linking customer and the deal)
            query = """INSERT INTO Favorites VALUES (%s, %s)"""
            DBUtils.executeUpdate(self._conn, query,
                                  (customer.getCid(), deal.getDid()))

            # Get the business associated with the deal
            query = """
                SELECT bid from Deals where dealId='{did}'
                """.format(did=deal.getDid())
            busID = DBUtils.getVar(self._conn, query)

            # Update business's num favorites
            query = """UPDATE Business SET numFavouritedDeals = numFavouritedDeals + 1
                    WHERE businessId='{bid}'
                    """.format(bid=busID)
            DBUtils.executeUpdate(self._conn, query)
            print("Deal favorited! Check your profile.")
        except psycopg2.Error as e:
            print(e)
예제 #27
0
    def __prerequisites_ghb(self):
        CYCLE_ID = 15
        CYCLE_PATH = '/GARUDATA/IMAGING15/CYCLE15/'
        CYCLE_ID = str(CYCLE_ID)

        sql_query = "select distinct o.observation_no, p.proposal_id, g.file_path, p.backend_type from " \
                    "gmrt.proposal p inner join das.observation o on p.proposal_id = o.proj_code " \
                    "inner join das.scangroup g on g.observation_no = o.observation_no " \
                    "inner join das.scans s on s.scangroup_id = g.scangroup_id " \
                    "inner join gmrt.sourceobservationtype so on p.proposal_id = so.proposal_id " \
                    "where p.cycle_id ='" + CYCLE_ID + "' " \
                                                       "and so.obs_type not like 'pulsar%' " \
                                                       "and so.obs_type not like 'phased array'" \
                                                       "and s.sky_freq1=s.sky_freq2 " \
                                                       "and s.sky_freq1 < 900000000 " \
                                                       "and s.chan_width >= 62500 " \
                                                       "and o.proj_code not like '16_279' " \
                                                       "and o.proj_code not like '17_072' " \
                                                       "and o.proj_code not like '18_031' " \
                                                       "and o.proj_code not like '19_043' " \
                                                       "and o.proj_code not like '20_083' " \
                                                       "and o.proj_code not like '23_066' " \
                                                       "and o.proj_code not like '26_063' " \
                                                       "and o.proj_code not like 'ddtB014' " \
                                                       "and o.proj_code not like 'ddtB015' " \
                                                       "and o.proj_code not like 'ddtB028' " \
                                                       "and o.proj_code not like '21_057';"
        dbutils = DBUtils()
        data = dbutils.select_query(sql_query)
        gadpudata = {}
        for each_row in data:
            gadpudata[each_row[0]] = {
                "proposal_id": each_row[1],
                "file_path": each_row[2],
                "backend_type": each_row[3],
                "cycle_id": CYCLE_ID
            }
        return (gadpudata, CYCLE_PATH)
 def getFavoritedDeals(self, customer):
     try:
         query = """
             SELECT Deals.title, Deals.description, Deals.avgRating, Deals.imageURL,
                 Deals.startDate, Deals.endDate, Business.name, Business.phoneNum,
                 Business.businessId, Deals.dealId
             FROM Deals
             INNER JOIN Favorites ON Favorites.did=Deals.dealId
             INNER JOIN Business ON Deals.bid=Business.businessId
             WHERE Favorites.cid='{cid}'
             """.format(cid=customer.getCid())
     except psycopg2.Error as e:
         print(e)
     return DBUtils.getAllRows(self._conn, query)
 def getAllRetailers(self):
     try:
         # Businesses sorted by "reputation", which is num_visits + num_favorites * 2
         query = """
             Select name, imageURL, homePageURL, categoryName
             FROM Business
             INNER JOIN (SELECT * from Belongs_To group by cid, bid) belongTo
             on belongTo.bid=Business.businessId
             INNER JOIN Category
             on belongTo.cid=Category.categoryId
             order by (numvisited + numFavouritedDeals*2) desc
         """
     except psycopg2.Error as e:
         print(e)
     return DBUtils.getAllRows(self._conn, query)
 def searchForDeal(self, deal_name):
     try:
         # Get deal info where name or description contains a substring of the passed
         # in deal_name search string
         query = """
                 SELECT Deals.title, Deals.description, Deals.avgRating, Deals.imageURL,
                     Deals.startDate, Deals.endDate, Business.name,
                     Business.phoneNum, Business.businessId, Deals.dealId
                 FROM Deals
                 INNER JOIN Business ON Deals.bid=Business.businessId
                 where title LIKE \'%{deal_name}%\' or description LIKE \'%{deal_name}%\'
                 ORDER BY Deals.avgrating DESC
                     """.format(deal_name=deal_name)
     except psycopg2.Error as e:
         print(e)
     return DBUtils.getAllRows(self._conn, query)
 def getTopNDeals(self, n):
     # Return the top n best deals (best as in highest average rating)
     try:
         query = """
             SELECT Deals.title, Deals.description, Deals.avgRating, Deals.imageURL,
                 Deals.startDate, Deals.endDate, Business.name, Business.phoneNum,
                 Business.businessId, Deals.dealId
             FROM Deals
             INNER JOIN Business ON Deals.bid=Business.businessId
             WHERE Deals.avgRating IS NOT NULL
             ORDER BY Deals.avgRating desc
             LIMIT {n}
         """.format(n=n)
     except psycopg2.Error as e:
         print(e)
     return DBUtils.getAllRows(self._conn, query)
예제 #32
0
    def parse_args(db_config_file):
        # Query the database to gather some items for argument output.
        pgdb = DBUtils(config_file=db_config_file)
        valid_db_opts = pgdb.getIDs()

        # Parse the arguments
        parser = argparse.ArgumentParser(
            description="Launches SCOOP parallelized version "
            "of genetic algorithm evaluation.",
            formatter_class=argparse.RawTextHelpFormatter)

        parser.add_argument("trail", type=int,
            metavar='trail',
            help="Trail to use.",
            choices=valid_db_opts["trail"])
        parser.add_argument("population", type=int,
            metavar="mu",
            help="Size of the population. Serves as mu "
                " in varOr type runs.")
        parser.add_argument("lambda_",
            metavar="lambda",
            type=int,
            help="Size of the offspring pool (lambda). "
                "Required in varOr type runs.")
        parser.add_argument("moves",
            type=int,
            metavar="moves",
            help="Maximum moves for agent.")
        parser.add_argument("network", type=int,
            metavar='network',
            nargs='*',
            help=textwrap.dedent("Network type to use."),
            choices=valid_db_opts["network"])

        group = parser.add_argument_group('Application Options')
        group.add_argument("--disable-db",
            action='store_true',
            help="Disables logging of run to database.")
        group.add_argument("--debug",
            action='store_true',
            help="Enables debug messages and flag for data in DB.")
        group.add_argument("-q", "--quiet",
            action='store_true',
            help="Disables all output from application.")
        group.add_argument("--script-mode",
            action='store_true',
            help="Disables progress bar and prints information to stdout.")
        group.add_argument("-r", "--repeat", type=int, nargs="?",
            default=1, help="Number of times to repeat simulations.")
        group.add_argument("--no-early-quit",
            action='store_true',
            help='Disables automatic or early termination.')

        group = parser.add_argument_group('Genetic Algorithm Configuration')
        group.add_argument("-g", "--generations", type=int, nargs="?",
            default=GENS_DEF,
            help="Number of generations to run for.")
        group.add_argument("--variation", type=int,
            default=VARIATION_DEF,
            help="Variation type to use in DEAP.",
            choices=valid_db_opts["variations"])
        group.add_argument("--mutate-type", type=int, nargs="?",
            default=MUTATE_DEF,
            help="Mutation type.",
            choices=valid_db_opts["mutate"])
        group.add_argument("--prob-mutate", type=float, nargs="?",
            default=P_MUTATE_DEF,
            help="Probability of a mutation to occur.")
        group.add_argument("--prob-crossover", type=float, nargs="?",
            default=P_CROSSOVER_DEF,
            help="Probability of crossover to occur.")
        group.add_argument("--weight-min", type=float, nargs="?",
            default=WEIGHT_MIN_DEF,
            help="Minimum weight.")
        group.add_argument("--weight-max", type=float, nargs="?",
            default=WEIGHT_MAX_DEF,
            help="Maximum weight")
        group.add_argument("--mean-check-length", type=int,
            default=DEF_MEAN_CHANGE,
            help="Only used with variation 3. Specifies the number of "
            "previous generations\nto see if there is no change in average food "
            "consumed. Stops algorithm\nif there is no change for this period "
            "of time. Defaults to {0}.".format(DEF_MEAN_CHANGE))

        group = parser.add_argument_group('Genetic Algorithm '
            'Selection Configuration')
        group.add_argument("-s", "--selection", type=int,
            default=SELECTION_DEF,
            help="Selection type to use.",
            choices=valid_db_opts["selection"])
        group.add_argument("--tournament-size", type=int,
            default=DEF_ERROR_VAL,
            help="If using tournament selection, the size of the tournament.")

        args = parser.parse_args()

        utils.__check_args(args)

        return args
예제 #33
0
import re;
import json;
import os
import numpy as np
from IPython.display import HTML
import pandas as pd

PyUtilsPATH="../../PyUtils";
sys.path.append(PyUtilsPATH)
SQL_TXT = "/opt/SCHAS/data/sql/SQL.txt";

from DBUtils import  DBUtils
from GenUtils import *

#db = DBUtils(conn='postgresql://*****:*****@localhost:5400/SCHASDB')
db = DBUtils(conn='postgresql://*****:*****@localhost:5432/SCHASDB')
db.load(file=SQL_TXT);
#--------------------------------------------------------------------------
js = '''
$("#t1 th").resizable({handles:''})

$("#t1 th").each(function (i) {
    var th = $(this);
    th.css("background-color", '#ededed');
    th.css("text-align", 'center');
});
'''
def _getQueryHTML(q, limit=25, htmlname='dbtable', rType='html' ):
    global js, df;
    
    df = db.execQ(q, limit =limit)
예제 #34
0
 def setUp(self):
     self.pgdb = DBUtils(password=os.environ['PSYCOPG2_DB_PASS'])