class PostgresQuerier(object): def __init__(self): self.connector = PostgresConnector(CONFIG_FILE_NAME, CONFIG_SECTION_NAME) self.SQL_constructor = PostgresSQLConstructor() def GetVersion(self): SQL = self.SQL_constructor.GetVersion() self.connector.ExecuteSQL(SQL) return self.connector.GetResults(1) def GetCurrentDate(self): SQL = self.SQL_constructor.GetCurrentDate() self.connector.ExecuteSQL(SQL) return self.connector.GetResults(1) def _SetDatestyleGerman(self): SQL = "SET DATESTYLE=%s;"; data = ("German",) self.connector.ExecuteSQL(SQL, data) return "Datestyle set to German" def SelectColumnsFromTable(self, columns, table, limit=100): SQL_select = self.SQL_constructor.GetSELECTColumns(columns) SQL_from = self.SQL_constructor.GetFROMTable(table) SQL_limit = self.SQL_constructor.GetLIMIT(limit) SQL = SQL_select + SQL_from + SQL_limit self.connector.ExecuteSQL(SQL) return self.connector.GetAllResults() def FindWordsInColumnsInTableThenBoldAndRank(self, search_words, columns, table, operation="OR"): search_phrase = self.SQL_constructor.ConstructSearchPhrase(search_words, operation) headline_columns = self.SQL_constructor.GetHeadlineColumnsWithPhrase(columns, search_phrase) rank_column = self.SQL_constructor.Getts_rankColumnByPhraseWithLengthPenaltyWithAlias(columns[0], search_phrase, 1, "rank") headline_columns.append(rank_column) vector_query_pairs = [self.SQL_constructor.GetVectorColumnHasQueryPhrase(x, search_phrase) for x in columns] SQL_select = self.SQL_constructor.GetSELECTColumns(headline_columns) SQL_from = self.SQL_constructor.GetFROMTable(table) SQL_where = self.SQL_constructor.GetWHERE() SQL_conditions = " OR".join(vector_query_pairs) SQL_order = self.SQL_constructor.GetORDERByAliasInDirection("rank", "DESC") SQL = SQL_select + SQL_from + SQL_where + SQL_conditions + SQL_order self.connector.ExecuteSQL(SQL) return self.connector.GetAllResults() def FindWordsInDocumentInTableThenBoldAndRank(self, search_words, document, table, operation="OR"): search_phrase = self.SQL_constructor.ConstructSearchPhrase(search_words, operation) document_columns = ["title", "categories", "summary", "description"] headline_columns = self.SQL_constructor.GetHeadlineColumnsWithPhrase(document_columns, search_phrase) rank_column = self.SQL_constructor.Getts_rankDocumentByPhraseWithLengthPenaltyWithAlias(document, search_phrase, 1, "rank") headline_columns.append(rank_column) SQL_select = self.SQL_constructor.GetSELECTColumns(headline_columns) SQL_from = self.SQL_constructor.GetFROMTable(table) SQL_where = self.SQL_constructor.GetWHERE() SQL_conditions = self.SQL_constructor.GetDocumentHasQueryPhrase(document, search_phrase) SQL_order = self.SQL_constructor.GetORDERByAliasInDirection("rank", "DESC") SQL = SQL_select + SQL_from + SQL_where + SQL_conditions + SQL_order self.connector.ExecuteSQL(SQL) return self.connector.GetAllResults() def SuggestBasedOnPhraseInColumnInTable(self, phrase, column, table): similarity_function = self.SQL_constructor.GetSimilarityOfColumnAndStringWithAlias(column, phrase, "sameness") SQL_select = self.SQL_constructor.GetSELECTColumns([column, similarity_function]) SQL_from = self.SQL_constructor.GetFROMTable(table) SQL_order = self.SQL_constructor.GetORDERByAliasInDirection("sameness", "DESC") SQL = SQL_select + SQL_from + SQL_order self.connector.ExecuteSQL(SQL) return self.connector.GetResults(5) def PivotMovies(self, start_datetime, stop_datetime, granulation): iso_start_datetime = start_datetime.isoformat(" ") iso_end_datetime = stop_datetime.isoformat(" ") diff = stop_datetime - start_datetime days, seconds = diff.days, diff.seconds hours = days * 24 + seconds // 3600 if granulation == "hour": format = ["time_interval" + str(x) + " bigint" for x in range(hours + 1)] elif granulation == "day": format = ["time_interval" + str(x) + " bigint" for x in range(days + 1)] else: raise Exception("WARNING: incorrect granulation; possible granulation are (hour, day)") format.insert(0, "query character varying(255)") sub_query = ("select query," " date_trunc(''" + granulation + "'', date_and_time) as periods," " count(*)" " from queries" " where date_and_time >= ''" + iso_start_datetime + "'' and" " date_and_time <= ''" + iso_end_datetime + "''" " group by query, periods" " order by query, periods;") sequence = ("select d" " from generate_series(''" + iso_start_datetime + "''::timestamp," "''" + iso_end_datetime + "''," "''1 " + granulation + "'')" " d;") SQL = ("select * from crosstab('" + sub_query + "'," "'" + sequence + "')" "as ct(" + ", ".join(format) + ");") self.connector.ExecuteSQL(SQL) return self.connector.GetAllResults() def InsertIntoMovies(self, title, categories="No categories.", summary="No summary.", description="No description."): table = "movies" columns = ["title", "categories", "summary", "description"] values = [title, categories, summary, description] SQL_insert = self.SQL_constructor.GetINSERTIntoTableColumns(table, columns) SQL_values = self.SQL_constructor.GetVALUES(values) SQL = SQL_insert + SQL_values self.connector.ExecuteSQL(SQL) self.connector.CommitTransaction() return "Insertion successful." def _GetTableColumnsAfterIndex(self, table, index=0): return [x[0] for x in self.DescribeTable(table)][index:] def Prototype(self): SQL = ("SELECT title, similarity (title, 'The Dencing Master')" "FROM movies" "WHERE title % 'The Dencing Master'" ) self.connector.ExecuteSQL(SQL) return self.connector.GetAllResults() def DescribeTable(self, table): SQL = "SELECT column_name FROM information_schema.columns WHERE table_name = '" + table + "'"; self.connector.ExecuteSQL(SQL) return self.connector.GetAllResults() def Close(self): self.connector.Close()