def execute_sql_from_jinja_string(conn,
sql_string,
context=None,
dict_cursor=False):
# conn: a (psycopg2) connection object
# sql_string: query template (Jinja-supported string)
# context: the context (dict-like) that will be use with the template
#
# an extra Jinja filter (surround_by_quote) is available and can be useful to double-quote field names
#
# returns the cursor object
#
# examples:
#
# execute_sql_from_jinja_string(conn, "SELECT version();")
# execute_sql_from_jinja_string(conn, "SELECT * FROM biodiv.address LIMIT {{limit}}", {'limit': 5})
e = Environment()
e.filters["surround_by_quote"] = surround_by_quote
j = JinjaSql(env=e)
if context is None:
context = {}
query, bind_params = j.prepare_query(sql_string, context)
if dict_cursor:
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
else:
cur = conn.cursor()
cur.execute(query, bind_params)
return cur
def get_query_results(payment_type: List[int], passenger_count: List[int]):
""" Function that accepts 2 inputs:
- payment_type: list
- passenger_count: list
returns a pandas dataframe
"""
with psycopg2.connect('dbname=analysis user=username password='******'pyformat')
data = {}
data['payment_type'] = payment_type
data['passenger_count'] = passenger_count
template = """
SELECT
*
FROM
yellow_tripdata_2016_01
WHERE
payment_type in {{ payment_type | inclause }}
and passenger_count in {{ passenger_count | inclause }}
limit 200
"""
query, bind_params = j.prepare_query(template, data)
# Execute prepared statement to avoid SQL injection attacks
df = pd.read_sql(query, con=conn, params=bind_params)
return df
def perform_update_or_delete_operation(sql, binding_params):
"""
TODO: Facilitate UPDATE &/ DELETE Operation on SQLITE.
:param sql: Template:
{% raw %}
UPDATE tableName
SET column_1 = {{column_1_value}}, column_2 = {{column_2_value}}
WHERE column_3 = {{ column_3_value }}
{% if projectId %}
AND projectId = {{ projectId }}
{% endif %}
{% endraw %}
:param binding_params: Template:
data = {
"column_1_value": 123,
"column_2_value": 'dfb',
"projectId": u"proton"
}
:return:
"""
try:
check_if_update = re.compile('update', re.I)
check_if_delete = re.compile('delete', re.I)
if sql.match(check_if_update) or sql.match(check_if_delete):
with self.pg_cursor_generator(self.__cursor_engine) as cursor:
__j_sql = JinjaSql(param_style='pyformat')
query, bind_params = __j_sql.prepare_query(self.generate_sql_template(sql), binding_params)
cursor.execute(query, bind_params)
return {
'status': True,
'affected_rowcount': cursor.rowcount
}
else:
return {
'status': False,
'message': 'Illegal use of this method. Please utilize this method to only perform UPDATE '
'or Delete operation on PROTON supported databases.'
}
except Exception as e:
self.model_{{ modelName }}_logger.exception('[{{modelName}} - Exception during UPDATE operation. '
'Details: {}]'.format(str(e)))
print(Fore.LIGHTRED_EX + '[{{modelName}} - Exception during UPDATE operation. Details: '
'{}]'.format(str(e)) + Style.RESET_ALL)
return {
'status': False,
'affected_rowcount': 0
}
def test_precompiled_template(self):
source = "select * from dummy where project_id = {{ request.project_id }}"
j = JinjaSql()
query, bind_params = j.prepare_query(j.env.from_string(source), _DATA)
expected_query = "select * from dummy where project_id = %s"
self.assertEquals(query.strip(), expected_query.strip())
def apply_sql_template(template, parameters):
'''
Apply a JinjaSql template (string) substituting parameters (dict) and return
the final SQL.
'''
j = JinjaSql(param_style='pyformat')
query, bind_params = j.prepare_query(template, parameters)
return get_sql_from_template(query, bind_params)
def test_django_request_as_context(self):
request = RequestFactory().get('/customer/details/?customer=1232&enterprise=9875')
j = JinjaSql()
query, bind_params = j.prepare_query("""select {{request.path}}
from dual
where customer={{request.GET.customer}}"""
, {"request": request}
)
self.assertEquals(bind_params, [u"/customer/details/", u"1232"])
def __init__(self, schema):
"""Establish the database connection.
Args:
schema (str): The customer schema to associate with
"""
super().__init__(schema)
self._datetime_format = Config.OCP_DATETIME_STR_FORMAT
self.jinja_sql = JinjaSql()
def __init__(self, schema):
"""Establish the database connection.
:param schema (str): The customer schema to associate with
"""
super().__init__(schema)
self._ebs_acct_num = schema.strip("acct")
self.date_accessor = DateAccessor()
self.jinja_sql = JinjaSql()
def __init__(self, schema):
"""Establish the database connection.
Args:
schema (str): The customer schema to associate with
"""
super().__init__(schema)
self.date_accessor = DateAccessor()
self.jinja_sql = JinjaSql()
def test_large_inclause(self):
num_of_params = 50000
alphabets = ['A'] * num_of_params
source = "SELECT 'x' WHERE 'A' in {{alphabets | inclause}}"
j = JinjaSql()
query, bind_params = j.prepare_query(source, {"alphabets": alphabets})
self.assertEquals(len(bind_params), num_of_params)
self.assertEquals(
query,
"SELECT 'x' WHERE 'A' in (" + "%s," * (num_of_params - 1) + "%s)")
def build_asneededDF(site_id, area_id=None):
"""
Create a Pandas DataFrame of the as needed rooms within the select site_id
"""
postgres_connection_URL = os.environ.get('DATABASE_URL')
engine = create_engine(postgres_connection_URL)
# Potentially add an area filter as well?
# if area_id:
# template with area_id
# else
# template
template = """SELECT *
FROM room
WHERE site_id = {{site_id}}
AND freq = -1"""
data = {"site_id": site_id}
j = JinjaSql()
query, bind_params = j.prepare_query(template, data)
# Make DF of just as needed rooms for selected site.
as_neededDF = pd.read_sql(sql=query, con=engine, params=bind_params)
as_neededDF = as_neededDF[['bm_id', 'name', 'date_last_paint']].copy()
# split dated from non-dated
no_dateDF = as_neededDF.loc[as_neededDF.date_last_paint.isna()].copy()
datedDF = as_neededDF.loc[~as_neededDF.date_last_paint.isna()].copy()
# Set to datetime, and drop the time part.
datedDF.date_last_paint = datedDF.date_last_paint.astype('datetime64[ns]')
datedDF.date_last_paint = datedDF.date_last_paint.dt.strftime('%Y-%m-%d')
# Sort them by date and bm_id
no_dateDF.sort_values(['bm_id'], inplace=True)
datedDF.sort_values(['date_last_paint', 'bm_id'],
ascending=True,
inplace=True)
sortedDF = no_dateDF.append(datedDF)
sortedDF.rename(
{
'bm_id': 'BM ID',
'name': 'Room Name',
'date_last_paint': 'Date of last Painting'
},
axis=1,
inplace=True)
# sortedDF.set_index('BM ID', inplace=True)
sortedDF.reset_index(drop=True, inplace=True)
return sortedDF
def apply_sql_template(template, parameters, func_list=None):
'''
Apply a JinjaSql template (string) substituting parameters (dict) and return
the final SQL. Use the func_list to pass any functions called from the template.
'''
j = JinjaSql(param_style='pyformat')
if func_list:
for func in func_list:
j.env.globals[func.__name__] = func
query, bind_params = j.prepare_query(template, parameters)
return get_sql_from_template(query, bind_params)
def __init__(self, schema):
"""Establish the database connection.
Args:
schema (str): The customer schema to associate with
"""
super().__init__(schema)
self._datetime_format = Config.AWS_DATETIME_STR_FORMAT
self.date_accessor = DateAccessor()
self.jinja_sql = JinjaSql()
self._table_map = AWS_CUR_TABLE_MAP
def __init__(self, poller_id):
self.poller_id = poller_id
self.jinja = JinjaSql(param_style='pyformat')
try:
self.conn = pymssql.connect(server=os.environ.get("DB_CONN"),
user=os.environ.get("DB_USER"),
password=os.environ.get("DB_PASSWORD"),
database=os.environ.get("DB_NAME"),
login_timeout=30)
except Exception as err:
sys.exit()
def __init__(self,
server=None,
user=None,
password=None,
database=None,
logger=None):
self.server = server
self.user = user
self.password = password
self.database = database
self.logger = logger
self.jinja = JinjaSql(param_style='pyformat')
def __init__(self, schema, column_map):
"""Establish the database connection.
Args:
schema (str): The customer schema to associate with
column_map (dict): A mapping of report columns to database columns
"""
super().__init__(schema, column_map)
self._datetime_format = Config.OCP_DATETIME_STR_FORMAT
self.column_map = column_map
self.jinja_sql = JinjaSql()
def yaml_test(self):
source = config['template']
for (param_style, expected_sql) in config['expected_sql'].items():
jinja = JinjaSql(param_style=param_style)
query, bind_params = jinja.prepare_query(source, _DATA)
if 'expected_params' in config:
if param_style in ('pyformat', 'named'):
expected_params = config['expected_params']['as_dict']
else:
expected_params = config['expected_params']['as_list']
self.assertEquals(list(bind_params), expected_params)
self.assertEquals(query.strip(), expected_sql.strip())
def test_include(self):
where_clause = """where project_id = {{request.project_id}}"""
source = """
select * from dummy {% include 'where_clause.sql' %}
"""
loader = DictLoader({"where_clause.sql": where_clause})
env = Environment(loader=loader)
j = JinjaSql(env)
query, bind_params = j.prepare_query(source, _DATA)
expected_query = "select * from dummy where project_id = %s"
self.assertEquals(query.strip(), expected_query.strip())
self.assertEquals(len(bind_params), 1)
self.assertEquals(list(bind_params)[0], 123)
class SQLScriptAtomicExecutorMixin:
"""This mixin accetps a jinja_sql sql script and parameters (dict) and process each statement
in the script individually for better logging within PostgreSQL"""
DEFAULT_SQL_RENDERER = JinjaSql()
DEFAULT_SQL_RENDERER_METHOD = DEFAULT_SQL_RENDERER.prepare_query
def _execute_processing_script(self,
base_module,
script_file_path,
sql_params,
sql_renderer=DEFAULT_SQL_RENDERER_METHOD):
conn = transaction.get_connection()
sql = pkgutil.get_data(base_module, script_file_path).decode("utf-8")
for sql_stmt in sql_split(sql):
sql_stmt = sql_stmt.strip()
if sql_stmt:
sql_stmt, params = sql_renderer(sql_stmt, sql_params)
with conn.cursor() as cur:
try:
cur.execute(sql_stmt, params)
except (ProgrammingError, IndexError) as exc:
if isinstance(sql_stmt, bytes):
sql_stmt = sql_stmt.decode("utf-8")
msg = [
f"ERROR in SQL statement: '{exc}'",
f"Script file {os.path.join(base_module.replace('.', os.path.sep), script_file_path)}",
f"STATEMENT: {sql_stmt}",
f"PARAMS: {params}",
f"INPUT_PARAMS: {sql_params}",
]
LOG.error(os.linesep.join(msg))
raise exc
def test_executescript(self):
"""
Test execution of a buffer containing multiple statements
"""
sqlscript = """
drop table if exists hive.{{schema | sqlsafe}}.__test_{{uuid | sqlsafe}};
create table hive.{{schema | sqlsafe}}.__test_{{uuid | sqlsafe}}
(
id varchar,
i_data integer,
t_data varchar
);
insert into hive.{{schema | sqlsafe}}.__test_{{uuid | sqlsafe}} (id, i_data, t_data)
values (cast(uuid() as varchar), 10, 'default');
insert into hive.{{schema | sqlsafe}}.__test_{{uuid | sqlsafe}} (id, i_data, t_data)
values (cast(uuid() as varchar), {{int_data}}, {{txt_data}});
select t_data from hive.{{schema | sqlsafe}}.__test_{{uuid | sqlsafe}} where i_data = {{int_data}};
drop table if exists hive.{{schema | sqlsafe}}.__test_{{uuid | sqlsafe}};
"""
conn = FakePrestoConn()
params = {
"uuid": str(uuid.uuid4()).replace("-", "_"),
"schema": self.schema_name,
"int_data": 255,
"txt_data": "This is a test",
}
results = kpdb.executescript(conn, sqlscript, params=params, preprocessor=JinjaSql().prepare_query)
self.assertEqual(results, [["eek"], ["eek"], ["eek"], ["eek"], ["eek"], ["eek"]])
class Migration(migrations.Migration):
dependencies = [
('timetable', '0010_auto_20190220_1835'),
]
path_to_sql = os.path.join(
os.path.dirname(os.path.dirname(os.path.realpath(__file__))), 'sql',
'generate_student_timetable_template.sql')
with open(path_to_sql, 'r') as sql_file:
template = sql_file.read()
j = JinjaSql()
query_a, _ = j.prepare_query(template, {"bucket_id": "a"})
query_b, _ = j.prepare_query(template, {"bucket_id": "b"})
operations = [
migrations.RunSQL(
'DROP FUNCTION IF EXISTS get_student_timetable_a(TEXT, TEXT);',
hints={"type": "raw_sql"}),
migrations.RunSQL(
'DROP FUNCTION IF EXISTS get_student_timetable_b(TEXT, TEXT);',
hints={"type": "raw_sql"}),
migrations.RunSQL(query_a, hints={"type": "raw_sql"}),
migrations.RunSQL(query_b, hints={"type": "raw_sql"}),
]
def buildquery(host, start_time, end_time):
template = '''
select
time_bucket('1 minute', ts) AS period
, min(usage) AS min_cpu_usage
, max(usage) AS max_cpu_usage
from
cpu_usage
where
host = {{ host }}
and ts > {{ start_time }} and ts < {{ end_time }}
group by
period
'''
params = {"host": host, "start_time": start_time, "end_time": end_time}
j = JinjaSql()
return j.prepare_query(template, params)
def perform_update_or_delete_operation(sql, binding_params):
"""
TODO: Facilitate UPDATE &/ DELETE Operation on SQLITE.
:param sql: Template:
{% raw %}
UPDATE tableName
SET column_1 = {{column_1_value}}, column_2 = {{column_2_value}}
WHERE column_3 = {{ column_3_value }}
{% if projectId %}
AND projectId = {{ projectId }}
{% endif %}
{% endraw %}
:param binding_params: Template:
data = {
"column_1_value": 123,
"column_2_value": 'dfb',
"projectId": u"proton"
}
:return:
"""
try:
with self.pg_cursor_generator(self.__cursor_engine) as cursor:
__j_sql = JinjaSql(param_style='pyformat')
query, bind_params = __j_sql.prepare_query(self.generate_sql_template(sql), binding_params)
cursor.execute(query, bind_params)
return {
'status': True,
'affected_rowcount': cursor.rowcount
}
except Exception as e:
self.model_{{ modelName }}_logger.exception('[{{modelName}} - Exception during UPDATE operation. Details: {}]'.format(str(e)))
print(Fore.LIGHTRED_EX + '[{{modelName}} - Exception during UPDATE operation. Details: '
'{}]'.format(str(e)) + Style.RESET_ALL)
return {
'status': False,
'affected_rowcount': 0
}
def getDataPointHumidity(sensorID=27, numRows=1):
# select * from aranet_trh_data where sensor_id=27 order by timestamp desc limit 10;
params = {"sensor_id": sensorID, "num_Rows": numRows}
humidity_transaction_template = """
select
timestamp, humidity
from
aranet_trh_data
where (sensor_id = {{ sensor_id }})
order by
timestamp desc
limit {{ num_Rows }}
"""
j = JinjaSql(param_style="pyformat")
query, bind_params = j.prepare_query(humidity_transaction_template, params)
return getData(get_sql_from_template(query=query, bind_params=bind_params))
class HCSReportDBAccessor(ReportDBAccessorBase):
"""Class to interact with customer reporting tables."""
def __init__(self, schema):
"""Establish the database connection.
:param schema (str): The customer schema to associate with
"""
super().__init__(schema)
self._ebs_acct_num = schema.strip("acct")
self.date_accessor = DateAccessor()
self.jinja_sql = JinjaSql()
def get_hcs_daily_summary(self, date, provider, provider_uuid, sql_summary_file, tracing_id):
"""Build HCS daily report.
:param date (datetime.date) The date to process
:param provider (str) The provider name
:param provider_uuid (uuid) ID for cost source
:param sql_summary_file (str) The sql file used for processing
:param tracing_id (id) Logging identifier
:returns (None)
"""
LOG.info(log_json(tracing_id, "acquiring marketplace data..."))
LOG.info(log_json(tracing_id, f"schema: {self.schema}, provider: {provider}, date: {date}"))
try:
sql = pkgutil.get_data("hcs.database", sql_summary_file)
sql = sql.decode("utf-8")
sql_params = {
"date": date,
"schema": self.schema,
"ebs_acct_num": self._ebs_acct_num,
"table": HCS_TABLE_MAP.get(provider),
}
sql, sql_params = self.jinja_sql.prepare_query(sql, sql_params)
data, description = self._execute_presto_raw_sql_query_with_description(
self.schema, sql, bind_params=sql_params
)
# The format for the description is:
# [(name, type_code, display_size, internal_size, precision, scale, null_ok)]
# col[0] grabs the column names from the query results
cols = [col[0] for col in description]
if len(data) > 0:
LOG.info(log_json(tracing_id, f"data found for date: {date}"))
csv_handler = CSVFileHandler(self.schema, provider, provider_uuid)
csv_handler.write_csv_to_s3(date, data, cols, tracing_id)
else:
LOG.info(log_json(tracing_id, f"no data found for date: {date}"))
except FileNotFoundError:
LOG.error(log_json(tracing_id, f"unable to locate SQL file: {sql_summary_file}"))
except Exception as err:
LOG.error(log_json(tracing_id, err))
def _populate_ocpaws_tag_summary(self):
"""Populate the AWS tag summary table."""
agg_sql = pkgutil.get_data("masu.database", "sql/reporting_ocpawstags_summary.sql")
agg_sql = agg_sql.decode("utf-8")
agg_sql_params = {"schema": connection.schema_name}
agg_sql, agg_sql_params = JinjaSql().prepare_query(agg_sql, agg_sql_params)
with connection.cursor() as cursor:
cursor.execute(agg_sql)
def custom_sql(table, column, args=None):
"""
Execute a custom (optionally Jinja-fromatted) SQL query and fail if
non-zero number of rows is returned.
"""
j = JinjaSql(param_style='pyformat')
if 'query_file' in args:
q_str = open(args['query_file']).read()
if 'query' in args:
q_str = args['query']
args['table'] = table.name
args['column'] = column.name
query, bind_params = j.prepare_query(q_str, args)
return query % bind_params
def test_import(self):
utils = """
{% macro print_where(value) -%}
WHERE dummy_col = {{value}}
{%- endmacro %}
"""
source = """
{% import 'utils.sql' as utils %}
select * from dual {{ utils.print_where(100) }}
"""
loader = DictLoader({"utils.sql": utils})
env = Environment(loader=loader)
j = JinjaSql(env)
query, bind_params = j.prepare_query(source, _DATA)
expected_query = "select * from dual WHERE dummy_col = %s"
self.assertEquals(query.strip(), expected_query.strip())
self.assertEquals(len(bind_params), 1)
self.assertEquals(list(bind_params)[0], 100)
class Pollerutil():
def __init__(self, poller_id):
self.poller_id = poller_id
self.jinja = JinjaSql(param_style='pyformat')
try:
self.conn = pymssql.connect(server=os.environ.get("DB_CONN"),
user=os.environ.get("DB_USER"),
password=os.environ.get("DB_PASSWORD"),
database=os.environ.get("DB_NAME"),
login_timeout=30)
except Exception as err:
sys.exit()
def get_poller_info(self):
try:
template = sql_utils.sql_templates['poller_config'].value
data = {'poll_id': str(self.poller_id)}
poller_info = self.jinja_select_query(template, data)
return_data = {}
if poller_info:
return poller_info
else:
log = 'Poller does not exist. Service not started.'
print(log)
sys.exit()
except Exception as err:
sys.exit()
def jinja_select_query(self, template=None, data=None):
resultset = []
query, bind_params = self.jinja.prepare_query(template, data)
no_except = True
while no_except:
try:
db_conn = self.conn
cursor = db_conn.cursor(as_dict=True)
cursor.execute(query, dict(bind_params))
resultset = cursor.fetchall()
no_except = False
cursor.close()
return resultset
except Exception as err:
db_conn.rollback()
if self.deadlock_validator(err):
cursor.close()
db_conn.close()
time.sleep(3)
else:
raise ValueError(
"Error encountered while selecting data from the database: %s"
% (err))
finally:
db_conn.close()
def _populate_pod_label_summary_table(self):
"""Populate pod label key and values."""
agg_sql = pkgutil.get_data(
"masu.database", f"sql/reporting_ocpusagepodlabel_summary.sql")
agg_sql = agg_sql.decode("utf-8")
agg_sql_params = {"schema": connection.schema_name}
agg_sql, agg_sql_params = JinjaSql().prepare_query(
agg_sql, agg_sql_params)
with connection.cursor() as cursor:
cursor.execute(agg_sql)
