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)