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