def __get_default_connection():
if PostgresQueryHandler.connectionDefault is None:
# connect to postgres
try:
# read database config from config file
database_config = Utils.read_config_data(Constants.CONFIG_DATABASE)
# connect to postgres
PostgresQueryHandler.connectionDefault = psycopg2.connect(database=database_config["dbname"],
user=database_config["user"],
password=database_config["password"],
host=database_config["host"],
port=database_config["port"])
PostgresQueryHandler.connectionDefault.autocommit = True
# capture connection exception
except psycopg2.OperationalError as exception:
print('Unable to connect to postgres \n Reason: {0}').format(str(exception))
# exit code
sys.exit(1)
else:
cursor = PostgresQueryHandler.connectionDefault.cursor()
# Print PostgreSQL version
cursor.execute("SELECT version();")
record = cursor.fetchone()
print("***You are connected to below Postgres database*** \n ", record, "\n")
return PostgresQueryHandler.connectionDefault
def __get_connection():
"""
:returns: Creates connection to postgres which is configured with postgres_idx_advisor.so
"""
# Create connection only if it is not done before
if PostgresQueryHandler.connection is None:
# Connect to postgres
try:
# Read database config from config file
database_config = Utils.read_config_data(
Constants.CONFIG_DATABASE)
# connect to postgres
PostgresQueryHandler.connection = psycopg2.connect(
database=database_config["dbname"],
user=database_config["user"],
password=database_config["password"],
host=database_config["host"],
port=database_config["port"])
PostgresQueryHandler.connection.autocommit = True
# Capture connection exception
except psycopg2.OperationalError as exception:
print('Unable to connect to postgres \n Reason: {0}').format(
str(exception))
# exit code
sys.exit(1)
else:
cursor = PostgresQueryHandler.connection.cursor()
# Print PostgreSQL version
cursor.execute("SELECT version();")
record = cursor.fetchone()
#print("***You are connected to below Postgres database*** \n ", record, "\n")
return PostgresQueryHandler.connection
def get_gin_properties():
"""
:returns: offset, train file and test file locations
"""
gin_config = Utils.read_config_data(Constants.CONFIG_GINPROPERTIES)
k_offset = gin_config["k_offset"]
train_file = gin_config["train_file"]
test_file = gin_config["test_file"]
agent = gin_config["agent"]
return int(k_offset), train_file, test_file, agent
def initialize_table_information():
# Get list of tables
tables = tuple(Utils.read_config_data(Constants.CONFIG_TABLES).keys())
# Call postgres to get table details from database
returned_table_details = PostgresQueryHandler.execute_select_query(
Constants.QUERY_GET_TABLE_DETAILS.format(tables))
for table_column in returned_table_details:
"""
Table_column will have
at position 0: table_name
at position 1: column_name
at position 2: data type and size
at position 3: primary key (true , false)
"""
data_type = table_column[2]
table_name = table_column[0]
column_name = table_column[1]
# Find column size
# Fixed length data types are stored in map
if data_type in Constants.POSTGRES_DATA_TYPE_SIZE_MAP:
data_size = Constants.POSTGRES_DATA_TYPE_SIZE_MAP[data_type]
# If data_type is not present in dict then it is variable length data type ,
# Data size needs to extracted from the text present data_type
else:
# Size is present with in brackets
# Examples : "character varying(44)" , "numeric(15,2)" , "character(25)"
# Extract size information
from_index = data_type.find("(")
to_index = data_type.find(")")
temp_text = str(data_type[from_index + 1:to_index])
data_size = sum(int(val) for val in temp_text.split(','))
# Check whether map entry exists for table if not create one
if table_name not in QueryExecutor.tables_map:
# Get number of rows add it to table object
QueryExecutor.tables_map[table_name] = Table(table_name,
PostgresQueryHandler.get_table_row_count(table_name))
# Add column to table object
QueryExecutor.tables_map[table_name].add_column(Column(column_name, data_type, data_size))
# Check whether map entry exists for column name if not create one
if column_name not in QueryExecutor.column_map:
QueryExecutor.column_map[column_name] = list()
# Add column as key and table as value for easier find
QueryExecutor.column_map[column_name].append(table_name)