def getSiteConfigKey(conn, key, site_id): value = '' with conn.cursor() as cursor: query = SQL(""" select value from config where key = {} and site_id = {} """) query = query.format(Literal(key), Literal(site_id)) print(query.as_string(conn)) cursor.execute(query) for row in cursor: value = row[0] conn.commit() # get the default value if not found if value == '': query = SQL( """ select value from config where key = {} and site_id is null """ ) query = query.format(Literal(key)) print(query.as_string(conn)) cursor.execute(query) for row in cursor: value = row[0] conn.commit() return value
def get(self, identifier): """ Query the provider for a specific feature id e.g: /collections/hotosm_bdi_waterways/items/13990765 :param identifier: feature id :returns: GeoJSON FeaturesCollection """ LOGGER.debug('Get item from Postgis') with DatabaseConnection(self.conn_dic, self.table) as db: cursor = db.conn.cursor(cursor_factory=RealDictCursor) sql_query = SQL("select {},ST_AsGeoJSON({}) \ from {} WHERE {}=%s").format(db.columns, Identifier(self.geom), Identifier(self.table), Identifier(self.id_field)) LOGGER.debug('SQL Query: {}'.format(sql_query.as_string(db.conn))) LOGGER.debug('Identifier: {}'.format(identifier)) try: cursor.execute(sql_query, (identifier, )) except Exception as err: LOGGER.error('Error executing sql_query: {}'.format( sql_query.as_string(cursor))) LOGGER.error(err) raise ProviderQueryError() row_data = cursor.fetchall()[0] feature = self.__response_feature(row_data) feature['prev'] = self.get_previous(cursor, identifier) feature['next'] = self.get_next(cursor, identifier) return feature
def get(self, identifier): """ Query the provider for a specific feature id e.g: /collections/hotosm_bdi_waterways/items/13990765 :param identifier: feature id :returns: GeoJSON FeaturesCollection """ LOGGER.debug('Get item from Postgis') with DatabaseConnection(self.conn_dic, self.table) as db: cursor = db.conn.cursor(cursor_factory=RealDictCursor) sql_query = SQL("select {0},ST_AsGeoJSON({1}) \ from {2} WHERE {3}=%s").format(db.columns, Identifier('geom'), Identifier(self.table), Identifier(self.id_field)) LOGGER.debug('SQL Query:{}'.format(sql_query.as_string(db.conn))) LOGGER.debug('Identifier:{}'.format(identifier)) try: cursor.execute(sql_query, (identifier, )) except Exception as err: LOGGER.error('Error executing sql_query: {}'.format( sql_query.as_string(cursor))) LOGGER.error('Using public schema: {}'.format(db.schema)) LOGGER.error(err) raise ProviderQueryError() self.dataDB = cursor.fetchall() feature_collection = self.__response_feature_collection() return feature_collection
def values_for_fields(self, where_key: str = None, where_values: Tuple[int, ...] = None, name: bool = True, as_column: bool = True, verbose: bool = False, **kwargs): """where_key could be 'id', 'name', etc. For any other field, you just set the fieldname to True""" if where_key is None: where_key = self.id_name fields = [self.id_name] if name is True: fields.append('name') for a_key in kwargs: if kwargs[a_key] is True: fields.append(a_key) db_params = config() conn = connect(**db_params) if where_values is None: sql = "SELECT {fields} FROM {table}" query = SQL(sql).format(table=Identifier(self.table), fields=SQL(', ').join( map(Identifier, fields))) if verbose: print(query.as_string(conn)) else: sql = "SELECT {fields} from {table} where {pkey} IN %s" query = SQL(sql).format(table=Identifier(self.table), fields=SQL(', ').join( map(Identifier, fields)), pkey=Identifier(where_key)) if verbose: print(query.as_string(conn)) cur = conn.cursor(cursor_factory=NamedTupleCursor) if verbose: print('Extracting records from Table: {aTable}'.format( aTable=self.table)) try: cur.execute(query, where_values) except OperationalError as error: print(error) records = cur.fetchall() cur.close() conn.close() if as_column is True: records = self.as_columns(records) return records
def get(self, query_params): tablename = query_params['tablename'] # Build our where statement where_stmt = self.build_where(query_params) # join fields fields = self.build_comma_separated(query_params['fields']) # create query string query = SQL(self.get_query).format(fields, Identifier(tablename), where_stmt) print 'Executing SQL: ' print query.as_string(Connection.connection) return self.resolve_query(query, True)
def set(self, query_params): tablename = query_params['tablename'] # join fields fields = self.build_comma_separated(list( query_params['fields'].keys())) values = self.build_comma_separated( list(query_params['fields'].values()), Literal) # create query string query = SQL(self.set_query).format(Identifier(tablename), fields, values) print 'Executing SQL: ' print query.as_string(Connection.connection) self.resolve_query(query)
def apply_anonymized_data(connection, temp_table, source_table, primary_key, definitions): logging.info('Applying changes on table {}'.format(source_table)) cursor = connection.cursor() create_index_sql = SQL('CREATE INDEX ON {temp_table} ({primary_key})') sql = create_index_sql.format(temp_table=Identifier(temp_table), primary_key=Identifier(primary_key)) cursor.execute(sql.as_string(connection)) column_names = get_column_names(definitions) columns_identifiers = [ SQL('{column} = s.{column}').format(column=Identifier(column)) for column in column_names ] set_columns = SQL(', ').join(columns_identifiers) sql_args = { "table": Identifier(source_table), "columns": set_columns, "source": Identifier(temp_table), "primary_key": Identifier(primary_key) } sql = SQL('UPDATE {table} t ' 'SET {columns} ' 'FROM {source} s ' 'WHERE t.{primary_key} = s.{primary_key}').format(**sql_args) cursor.execute(sql.as_string(connection)) cursor.close()
def get_min_max_pks( self, fragments: List[str], table_pks: List[Tuple[str, str]]) -> List[Tuple[Tuple, Tuple]]: """Get PK ranges for given fragments using the index (without reading the fragments). :param fragments: List of object IDs (must be registered and with the same schema) :param table_pks: List of tuples (column, type) that form the object PK. :return: List of (min, max) PK for every fragment where PK is a tuple. If a fragment doesn't exist or doesn't have a corresponding index entry, a SplitGraphError is raised. """ # If the PK isn't composite, we can read the range for the corresponding column # from the index, otherwise, the indexer stored the min/max tuple under $pk. pk = table_pks[0][0] if len(table_pks) == 1 else "$pk" fields = SQL( "object_id, index #>> '{{range,{0},0}}', index #>> '{{range,{0},1}}'" ).format(Identifier(pk)) result = { r[0]: (r[1], r[2]) for r in self.metadata_engine.run_chunked_sql( select( "get_object_meta", fields.as_string(self.metadata_engine.connection), table_args="(%s)", schema=SPLITGRAPH_API_SCHEMA, ), (fragments, ), chunk_position=0, ) } # Since the PK can't contain a NULL, if we do get one here, it's from the JSON query # (column doesn't exist in the index). min_max = [] for fragment in fragments: if fragment not in result: raise SplitGraphError("No metadata found for object %s!" % fragment) min_pk, max_pk = result[fragment] if min_pk is None or max_pk is None: raise SplitGraphError("No index found for object %s!" % fragment) if pk == "$pk": # For composite PKs, we're given back a JSON array and need to load it. min_pk = tuple(json.loads(min_pk)) max_pk = tuple(json.loads(max_pk)) else: # Single-column PKs still need to be returned as tuples. min_pk = (min_pk, ) max_pk = (max_pk, ) # Coerce the PKs to the actual Python types min_pk = tuple(adapt(v, c[1]) for v, c in zip(min_pk, table_pks)) max_pk = tuple(adapt(v, c[1]) for v, c in zip(max_pk, table_pks)) min_max.append((min_pk, max_pk)) return min_max
def createTable(self, record, isRaise=True): assert self.conn if (type(record) is type): record = record() assert isinstance(record, records.Record) sTable = type(record).__name__.lower() aFields = [ SQL('{} {}').format(Identifier(sName), SQL(sType)) for sName, sType in sorted(record.mVarCast.values()) ] if (record.aPKs): aFields.append( SQL('PRIMARY KEY ({})').format( SQL(', ').join(Identifier(sPK) for sPK in record.aPKs))) query = SQL('CREATE TABLE IF NOT EXISTS {} ({});').format( Identifier(sTable), SQL(', ').join(aFields)) log.debug(query.as_string(self.conn)) self._save('createtable') try: self._execute(query) except Exception as e: self._back('createtable') log.error(e) if (isRaise): raise return False else: log.info('"{}" table created'.format(sTable)) return True finally: self._release('createtable')
def exportFilterIdsFile(config, conn, pg_path, site_id, out_file): lpis_table = "decl_{}_{}".format(config.site_short_name, config.year) srid = get_srid(conn, lpis_table) with conn.cursor() as cursor: query = SQL(""" select lpis."NewID" as "SEQ_ID" from l4c_practices ap inner join {} lpis on lpis.ori_id = ap.orig_id where ap.site_id = {} and ap.year = {} and not lpis.is_deleted order by lpis."NewID" """) query = query.format(Identifier(lpis_table), Literal(site_id), Literal(config.year)) query_str = query.as_string(conn) print(query_str) name = os.path.basename(out_file) table_name = os.path.splitext(name)[0].lower() command = get_export_table_command(config.ogr2ogr_path, out_file, pg_path, "-nln", table_name, "-sql", query_str, "-a_srs", "EPSG:" + str(srid), "-gt", 100000) run_command(command)
def get_tile_footprints(conn, site_id): with conn.cursor() as cursor: query = SQL( """ select shape_tiles_s2.tile_id, shape_tiles_s2.epsg_code, ST_AsBinary(shape_tiles_s2.geog) as geog from shape_tiles_s2 where shape_tiles_s2.tile_id in ( select tile_id from sp_get_site_tiles({} :: smallint, 1 :: smallint) ); """ ) site_id_filter = Literal(site_id) query = query.format(site_id_filter) print(query.as_string(conn)) cursor.execute(query) results = cursor.fetchall() conn.commit() tiles = {} for (tile_id, epsg_code, geog) in results: geog = bytes(geog) geog = ogr.CreateGeometryFromWkb(geog) tiles[tile_id] = (geog, epsg_code) return tiles
def clean_product_details_l4c(conn, id): with conn.cursor() as cursor: query = SQL( """ delete from product_details_l4c where product_id = {} """ ).format(Literal(id)) print(query.as_string(conn)) cursor.execute(query) conn.commit()
def update(self, query_params): tablename = query_params['tablename'] # join fields and values fields = self.build_comma_separated(list( query_params['fields'].keys())) values = self.build_comma_separated( list(query_params['fields'].values()), Literal) # Build our where statement where_stmt = self.build_where(query_params) # create query string query = SQL(self.update_query).format(Identifier(tablename), fields, values, where_stmt) print 'Executing SQL: ' print query.as_string(Connection.connection) self.resolve_query(query)
def build_and_then_import_data(connection, table, primary_key, columns, excludes, search, total_count, chunk_size, verbose=False, dry_run=False): """ Select all data from a table and return it together with a list of table columns. :param connection: A database connection instance. :param str table: Name of the table to retrieve the data. :param str primary_key: Table primary key :param list columns: A list of table fields :param list[dict] excludes: A list of exclude definitions. :param str search: A SQL WHERE (search_condition) to filter and keep only the searched rows. :param int total_count: The amount of rows for the current table :param int chunk_size: Number of data rows to fetch with the cursor :param bool verbose: Display logging information and a progress bar. :param bool dry_run: Script is running in dry-run mode, no commit expected. """ column_names = get_column_names(columns) sql_columns = SQL(', ').join([ Identifier(column_name) for column_name in [primary_key] + column_names ]) sql_select = SQL('SELECT {columns} FROM {table}').format( table=Identifier(table), columns=sql_columns) if search: sql_select = Composed([ sql_select, SQL(" WHERE {search_condition}".format(search_condition=search)) ]) if dry_run: sql_select = Composed([sql_select, SQL(" LIMIT 100")]) cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor, name='fetch_large_result') cursor.execute(sql_select.as_string(connection)) temp_table = 'tmp_{table}'.format(table=table) create_temporary_table(connection, columns, table, temp_table, primary_key) batches = int(math.ceil((1.0 * total_count) / (1.0 * chunk_size))) for i in trange(batches, desc="Processing {} batches for {}".format(batches, table), disable=not verbose): records = cursor.fetchmany(size=chunk_size) if records: data = parmap.map(process_row, records, columns, excludes, pm_pbar=verbose) import_data(connection, temp_table, [primary_key] + column_names, filter(None, data)) apply_anonymized_data(connection, temp_table, table, primary_key, columns) cursor.close()
def associate_parent(self, parent_id: int = None, child_id: int = None, insertion_order: int = None, inserted_by: str = None, data: dict = None, verbose: bool = False): """Associate the parent and child tables using parent id. Insertion_order and inserted_by are optional""" parent_key = self.parent_table + '_id' self_key = self.table + '_id' join_table = self.table + '_' + self.parent_table db_params = config() if data is None: data = {} if inserted_by is None: inserted_by = db_params['user'] data.update({ parent_key: parent_id, self_key: child_id }, insertion_order=insertion_order, inserted_by=inserted_by) sql = 'INSERT INTO {table} ({fields}) VALUES ({values})' keys = data.keys() query = SQL(sql).format(table=Identifier(join_table), fields=SQL(', ').join(map(Identifier, keys)), values=SQL(', ').join(map(Placeholder, keys))) conn = connect(**db_params) if verbose: print(query.as_string(conn)) cur = conn.cursor(cursor_factory=NamedTupleCursor) if verbose: print('Adding new record to Table: {aTable}'.format( aTable=join_table)) try: cur.execute(query, data) except OperationalError as error: print(error) conn.commit() cur.close() conn.close()
def get_radar_products(config, conn, site_id): with conn.cursor() as cursor: query = SQL(""" select * from ( select greatest(substr(split_part(product.name, '_', 4), 2), split_part(product.name, '_', 5)) :: date as date, site_tiles.tile_id, product.orbit_type_id, split_part(product.name, '_', 6) as polarization, product.product_type_id, product.name, product.full_path from sp_get_site_tiles({} :: smallint, 1 :: smallint) as site_tiles inner join shape_tiles_s2 on shape_tiles_s2.tile_id = site_tiles.tile_id inner join product on ST_Intersects(product.geog, shape_tiles_s2.geog) where product.satellite_id = 3 and product.site_id = {} ) products where date between {} and {} order by date; """) site_id_filter = Literal(site_id) start_date_filter = Literal(config.season_start) end_date_filter = Literal(config.season_end) query = query.format(site_id_filter, site_id_filter, start_date_filter, end_date_filter) print(query.as_string(conn)) cursor.execute(query) results = cursor.fetchall() conn.commit() products = [] for ( dt, tile_id, orbit_type_id, polarization, radar_product_type, name, full_path, ) in results: if config.products is None or name in config.products: products.append( RadarProduct( dt, tile_id, orbit_type_id, polarization, radar_product_type, full_path, )) return products
def drop_table(conn, name): with conn.cursor() as cursor: query = SQL( """ drop table if exists {} """ ).format(Identifier(name)) print(query.as_string(conn)) cursor.execute(query) conn.commit()
def get_srid(conn, lpis_table): with conn.cursor() as cursor: query = SQL("select Find_SRID('public', {}, 'wkb_geometry')") query = query.format(Literal(lpis_table)) print(query.as_string(conn)) cursor.execute(query) srid = cursor.fetchone()[0] conn.commit() return srid
def get_product_path(conn, prd_id): product_path = '' with conn.cursor() as cursor: query = SQL(""" select full_path from product where id = {} """) query = query.format(Literal(prd_id)) print(query.as_string(conn)) cursor.execute(query) for row in cursor: product_path = row[0] conn.commit() return product_path
def load(self, fh, delimiter): with self.conn.cursor() as cursor: headers = list(self.headers.keys()) stmt = SQL("COPY {} ({}) FROM STDIN " "WITH CSV HEADER DELIMITER AS {} NULL AS ''") columns = Composed([Identifier(c) for c in headers]) columns = columns.join(', ') stmt = stmt.format(Identifier(self.table), columns, Literal(delimiter)) print(stmt.as_string(cursor)) cursor.copy_expert(stmt, fh) self.conn.commit()
def update_insertion_order_for_selected(self, order: dict, my_conn: Optional[dict] = None, t_log: Optional[TimeLogger] = None, verbose: bool = False): """Populates insertion_order attribute""" self.pull_grouped_data() df = self.selected_data_df() join_table: str = self.table_name + '_' + self.parent_table_name if verbose is True and t_log is None: t_log = TimeLogger() if my_conn is None: my_conn = self.my_conn else: self.my_conn = my_conn my_conn = my_connect(my_conn=my_conn, t_log=t_log, verbose=verbose) self.my_conn = my_conn conn = my_conn['conn'] sql = 'UPDATE {table} SET insertion_order = %s WHERE ({c_table_id}, {p_table_id}) = (%s, %s)' query = SQL(sql).format(table=Identifier(join_table), c_table_id=Identifier(self.id_name()), p_table_id=Identifier( self.parent_table_id_name())) cur = conn.cursor(cursor_factory=NamedTupleCursor) if verbose is True: t_log.new_event('Updating Insertion order for: ' + join_table) print(query.as_string(conn)) for eq_name, i in order.items(): p_id = self.selected_parent_id c_id = int(df.name[df.name == eq_name].index[0]) if verbose: print(cur.mogrify(query, (i, c_id, p_id))) try: cur.execute(query, (i, c_id, p_id)) except OperationalError as error: print(error) conn.commit() self.pull_grouped_data() if verbose is True: t_log.new_event('Finished Updating Insertion Order: ' + join_table)
def get_product_id_by_name(conn, prd_name): product_id = -1 with conn.cursor() as cursor: query = SQL(""" select id from product where name = {} """) query = query.format(Literal(prd_name)) print(query.as_string(conn)) cursor.execute(query) for row in cursor: product_id = row[0] conn.commit() return product_id
def exportPracticesFile(config, conn, pg_path, practice, site_id, out_file): lpis_table = "decl_{}_{}".format(config.site_short_name, config.year) lut_table = "lut_{}_{}".format(config.site_short_name, config.year) srid = get_srid(conn, lpis_table) with conn.cursor() as cursor: query = SQL(""" select lpis."NewID" as "SEQ_ID", lpis.ori_id as "FIELD_ID", ap.country as "COUNTRY", ap.year as "YEAR", ap.main_crop as "MAIN_CROP", ap.veg_start as "VEG_START", ap.h_start as "H_START", ap.h_end as "H_END", ap.practice as "PRACTICE", ap.p_type as "P_TYPE", ap.p_start as "P_START", ap.p_end as "P_END", lpis."GeomValid", lpis."Duplic", lpis."Overlap", lpis."Area_meters" as "Area_meter", lpis."ShapeInd", lut.ctnum as "CTnum", lut.ct as "CT", lut.lc as "LC", lpis."S1Pix", lpis."S2Pix" from l4c_practices ap inner join {} lpis on lpis.ori_id = ap.orig_id natural join {} lut where ap.site_id = {} and ap.year = {} and ap.practice_short_name = {} and not lpis.is_deleted order by lpis."NewID" """) query = query.format(Identifier(lpis_table), Identifier(lut_table), Literal(site_id), Literal(config.year), Literal(practice)) query_str = query.as_string(conn) print(query_str) # TODO: change natural join with inner join lut_nld_2019_2019 lut using (ori_crop) or something like this name = os.path.basename(out_file) table_name = os.path.splitext(name)[0].lower() command = get_export_table_command(config.ogr2ogr_path, out_file, pg_path, "-nln", table_name, "-sql", query_str, "-a_srs", "EPSG:" + str(srid), "-gt", 100000) run_command(command)
def generic_new_record_db(table_name: str = None, data_df: Optional[DataFrame] = None, name: str = None, new_record=None, notes: str = None, created_by: str = None, my_conn: Optional[dict] = None, t_log: Optional[TimeLogger] = None, verbose: bool = None) -> DataFrame: """Insert New Record Into math_object""" if verbose is True and t_log is None: t_log = TimeLogger() my_conn = my_connect(my_conn=my_conn, t_log=t_log, verbose=verbose) conn = my_conn['conn'] db_params = my_conn['db_params'] if new_record is None: new_record = {} next_id: int = generic_record_count(data_df) + 1 if name is None: name = "{aTable} {ID:d}".format(ID=next_id, aTable=table_name) if created_by is None: created_by = db_params['user'] new_record.update(name=name, notes=notes, created_by=created_by) query = SQL('INSERT INTO {table} ({fields}) VALUES ({values})' ).format(table=Identifier(table_name), fields=SQL(', ').join(map(Identifier, new_record.keys())), values=SQL(', ').join(map(Placeholder, new_record.keys()))) if verbose: print(query.as_string(conn)) cur = conn.cursor(cursor_factory=NamedTupleCursor) if verbose: t_log.new_event('Adding new record to Table: {aTable}'.format(aTable=table_name)) try: cur.execute(query, new_record) except OperationalError as error: print(error) # new_records = cur.fetchall() conn.commit() cur.close() updated_df = \ generic_pull_data(table_name=table_name, my_conn=my_conn, t_log=t_log, verbose=verbose) return updated_df
def import_crop_type(config, conn, pg_path, product_id, path): path = os.path.join(path, "VECTOR_DATA", "Parcels_classified_*.csv") for file in glob(path): table_name = "pd_ct_staging_{}".format(product_id) drop_table(conn, table_name) command = get_import_table_command( config.ogr2ogr_path, pg_path, file, "-nln", table_name, "-gt", 100000, "-lco", "UNLOGGED=YES", "-oo", "AUTODETECT_TYPE=YES", ) run_command(command) with conn.cursor() as cursor: query = SQL( """ insert into product_details_l4a( product_id, "NewID", "CT_decl", "CT_pred_1", "CT_conf_1", "CT_pred_2", "CT_conf_2" ) select {}, newid, ct_decl, ct_pred_1, ct_conf_1, ct_pred_2, ct_conf_2 from {} """ ).format(Literal(product_id), Identifier(table_name)) print(query.as_string(conn)) cursor.execute(query) conn.commit() drop_table(conn, table_name)
def delete_types(self, types, my_conn: Optional[dict] = None, t_log: Optional[TimeLogger] = None, verbose: bool = False): """Method to Insert New Equation Records""" if my_conn is None: my_conn = self.my_conn else: self.my_conn = my_conn if verbose is True and t_log is None: t_log = TimeLogger() my_conn = my_connect(my_conn=my_conn, t_log=t_log, verbose=verbose) conn = my_conn['conn'] cur = conn.cursor() if verbose is True: t_log.new_event(type(types)) if isinstance(types, str): print('you are here') types = tuple([types]) sql: str = 'DELETE FROM {table} WHERE type_name IN ({values});' query = SQL(sql).format(values=SQL(', ').join(map(Literal, types)), table=Identifier(self.name)) if verbose is True: print(query.as_string(cur)) t_log.new_event('Values' + str(types)) t_log.new_event('Pulling Data for: ' + self.name) try: cur.execute(query, types) conn.commit() except DatabaseError as error: print("Error: %s" % error) conn.rollback() cur.close() self.reinitialize_types_df(my_conn=my_conn, t_log=t_log, verbose=verbose) if verbose is True: t_log.new_event("execute_values() done") cur.close()
def getSiteId(conn, siteShortName): site_id = -1 with conn.cursor() as cursor: query = SQL(""" select id from site where short_name = {} """) query = query.format(Literal(siteShortName)) print(query.as_string(conn)) cursor.execute(query) for row in cursor: site_id = row[0] conn.commit() return site_id
def get_site_name(conn, site_id): with conn.cursor() as cursor: query = SQL(""" select short_name from site where id = {} """) site = Literal(site_id) query = query.format(site) print(query.as_string(conn)) cursor.execute(query) rows = cursor.fetchall() conn.commit() return rows[0][0]
def getLpisProduct(conn, site_id): lpis_prod = '' with conn.cursor() as cursor: query = SQL(""" select full_path from product where product_type_id in (select id from product_type where name = 'lpis') and site_id = {} """) query = query.format(Literal(site_id)) print(query.as_string(conn)) cursor.execute(query) for row in cursor: lpis_prod = row[0] conn.commit() return lpis_prod
def records_not_in_parent(self, parent_id: Tuple[int, ...], as_columns: bool = True, verbose: bool = False): """Get the {table} records for {parent}""".format( table=self.table, parent=self.parent_table) db_params = config() sql = '' sql = "SELECT * FROM {child_table} WHERE {id_name} NOT IN" \ " (SELECT {id_name} FROM {join_table} WHERE {parent_id_name} = %s);" # self_dict = dict(table='equation', join_table= lambda: 'equation_eqn_group', # id_name='equation_id', parent_key = lambda : 'eqn_group_id') # MyTuple = namedtuple('MyTuple', self_dict) # self = MyTuple(**self_dict) query = SQL(sql).format(child_table=Identifier(self.table), join_table=Identifier(self.join_table()), id_name=Identifier(self.id_name), parent_id_name=Identifier(self.parent_key())) conn = connect(**db_params) cur = conn.cursor(cursor_factory=NamedTupleCursor) if verbose: print(query.as_string(conn)) if isinstance(parent_id, int): cur.mogrify(query, (parent_id, )) elif isinstance(parent_id, tuple): cur.mogrify(query, parent_id) try: cur.execute(query, (parent_id, )) except TypeError: cur.execute(query, parent_id) except OperationalError as error: print(error) records = cur.fetchall() if as_columns is True: records = self.as_columns(records) cur.close() conn.close() return records