def check_eq(self, col1, col2, constraint={}):
return self._run_query(
SQL("{0} != {1}").format(Identifier(col1), Identifier(col2)),
constraint)
def write(self, message):
"""Database writer.
Persists a message to the postgresql database.
The message value is expected to be a JSON with the following keys:
- "url"
- "ts"
- "status"
- "latency"
- "regex_match" (optional)
Args:
message (confluent_kafka.Message): The message as received from the Kafka topic.
"""
if message.error() is None:
self.logger.info("Writing message to database")
payload = json.loads(message.value())
with self.db_conn.cursor(cursor_factory=DictCursor) as cursor:
cursor.execute(
SQL( # See https://stackoverflow.com/a/6722460
"""
WITH insert_row AS (
INSERT INTO {} (url)
SELECT %(url)s WHERE NOT EXISTS (
SELECT * FROM urls WHERE url = %(url)s
)
RETURNING *
)
SELECT * FROM insert_row
UNION
SELECT * FROM urls WHERE url = %(url)s
""").format(Identifier("urls")),
{"url": payload["url"]},
)
url_id = cursor.fetchone()["id"]
cursor.execute(
SQL("INSERT INTO {} VALUES (%(url_id)s, %(ts)s, %(status)s, %(latency)s)"
).format(Identifier("status")),
{
"url_id": url_id,
"ts": payload["check_time"],
"status": payload["status"],
"latency": payload["latency"],
},
)
if payload.get("regex_match"):
cursor.execute(
SQL("INSERT INTO {} VALUES (%(url_id)s, %(ts)s, %(match)s)"
).format(Identifier("regex")),
{
"url_id": url_id,
"ts": payload["check_time"],
"match": payload["regex_match"],
},
)
else:
self.logger.warn("Received an error message. Ignoring.")
def to_sql(self):
expr_sql, expr_args = self.expression.to_sql()
return (SQL('({})').format(expr_sql), expr_args)
def get_weather_data(startdate,
enddate,
cat_cols,
db_table_name="shop_dates_weather"):
# start DB instance
start_instance()
# get data out of the Postgres database
cast_dict = {
"shop_id": "uint8",
"sdw_elevation": "int16",
"sdw_distance": "float32",
"sdw_prcp": "uint16",
"sdw_tmax": "float32",
"sdw_tmin": "float32",
"sdw_only_neg_temp_ind": "uint8",
"sdw_only_pos_temp_ind": "uint8",
"sdw_tmin_diff_lag": "float32",
"sdw_tmax_diff_lag": "float32",
"sdw_tmin_diff_lead": "float32",
"sdw_tmax_diff_lead": "float32",
"sdw_prcp_diff_lag": "int16",
"sdw_prcp_diff_lead": "int16",
"sdw_days_w_prcp_last_7d": "uint8",
"sdw_total_prcp_last_7d": "int16",
"sdw_tmax_diff_lag7": "float32",
"sdw_tmax_diff_lag14": "float32",
"sdw_tmax_diff_lag21": "float32",
}
# df = df_from_sql_table(db_table_name, cast_dict, date_list=["sale_date"])
sql_str = ("SELECT "
"{0} "
"FROM {1} "
"WHERE {2} >= %(start_dt)s AND {2} <= %(end_dt)s;")
sql = SQL(sql_str).format(
SQL(", ").join([
Identifier(col) for col in list(cast_dict.keys()) + ['sale_date']
]),
Identifier(db_table_name),
Identifier('sale_date'),
)
params = {"start_dt": startdate, "end_dt": enddate}
df = df_from_sql_query(sql,
cast_dict,
params=params,
date_list=['sale_date'])
# subset to relevant columns
# df = df.filter(items=list(cast_dict.keys()) + ["sale_date"])
# rename binary columns to format consistent with other binary cols
bin_cols = ("sdw_only_neg_temp_ind", "sdw_only_pos_temp_ind")
last_bin_col_num = max([int(x.split("_")[1]) for x in cat_cols])
rename_dict = {
col: f"cat_{i}_{col}"
for i, col in enumerate(bin_cols, last_bin_col_num + 1)
}
df.rename(rename_dict, axis=1, inplace=True)
return df
def get_as_sql(self, expression_sql):
return SQL('{} AS {}').format(expression_sql,
Identifier(self.alias)), ()
def check_fricke_eigenval(self):
"""
if present, check that fricke_eigenval is product of atkin_lehner_eigenvals
"""
# TIME about 3s
return self._run_query(SQL('fricke_eigenval != prod2(atkin_lehner_eigenvals)'), {'fricke_eigenval':{'$exists':True}})
def check_hecke_ring_index_factorization(self):
"""
if present, verify that hecke_ring_index_factorization matches hecke_ring_index
"""
# TIME about 2s
return self._run_query(SQL('hecke_ring_index != prod_factorization(hecke_ring_index_factorization)'), {'hecke_ring_index_factorization':{'$exists':True}})
def loadTransferTimes(self):
if self.parent_stops:
sql_exists = """SELECT EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_name = %s
);
"""
#sql_exists = SQL(sql_exists).format(Identifier("transfers_"+str(self.region)))
try:
cursor = self.conn.getCursor()
cursor.execute(sql_exists, ('transfers_' + str(self.region), ))
(exists, ) = cursor.fetchone()
if not exists:
print("Table does not exist!")
return
print("Loading transfer times...")
sql = """SELECT from_stop_id, to_stop_id, transfer_type, min_transfer_time, from_route_id, to_route_id, from_trip_id
FROM {};
"""
sql = SQL(sql).format(
Identifier("transfers_" + str(self.region)))
cursor.execute(sql)
row = cursor.fetchone()
while row is not None:
(from_stop_id, to_stop_id, transfer_type,
min_transfer_time, from_route_id, to_route_id,
from_trip_id) = row
if not min_transfer_time:
min_transfer_time = MultimodalNetwork.MIN_TRANSFER_TIME
if from_stop_id != to_stop_id and not from_trip_id:
if not from_route_id:
if from_stop_id in self.stop_transfers:
self.stop_transfers[from_stop_id][
to_stop_id] = min_transfer_time
else:
self.stop_transfers[from_stop_id] = {
to_stop_id: min_transfer_time
}
else:
if from_route_id in self.route_transfers:
if to_route_id in self.route_transfers[
from_route_id]:
if from_stop_id in self.route_transfers[
from_route_id][to_route_id]:
self.route_transfers[from_route_id][
to_route_id][from_stop_id][
to_stop_id] = min_transfer_time
else:
self.route_transfers[from_route_id][
to_route_id][from_stop_id] = {
to_stop_id: min_transfer_time
}
else:
self.route_transfers[from_route_id][
to_route_id] = {
from_stop_id: {
to_stop_id: min_transfer_time
}
}
else:
self.route_transfers[from_route_id] = {
to_route_id: {
from_stop_id: {
to_stop_id: min_transfer_time
}
}
}
row = cursor.fetchone()
cursor.close()
except IOError as e:
print("I/O error({0}): {1}".format(e.errno, e.strerror))
except (Exception, psycopg2.DatabaseError) as error:
print(error)
except:
print("Unexpected error:", sys.exc_info()[0])
def loadTrips(self):
print("Loading trips...")
sql = """SELECT s.*, t.service_id FROM {0} as s, {1} as t
WHERE s.trip_id in
(select trip_id from {1} where route_id=%s) and
t.trip_id = s.trip_id
order by trip_id, stop_sequence
;
"""
sql = SQL(sql).format(Identifier("stop_times_" + str(self.region)),
Identifier("trips_" + str(self.region)))
try:
cursor = self.conn.getCursor()
for route in self.routes_id:
#print(route)
self.edges_timetable = {}
node_mapping = {}
cursor.execute(sql, (route, ))
row = cursor.fetchone()
previous_trip_id = -1
previous_node = -1
previous_departure_time = None
while row is not None:
(trip_id, arrival_time, departure_time, stop_id,
stop_sequence, service_id) = row
#print(trip_id)
if trip_id != previous_trip_id:
previous_node = -1
if stop_id in node_mapping:
node_id = node_mapping[stop_id]
else:
if stop_id not in self.stops:
previous_node = -1
row = cursor.fetchone()
continue
node_id = self.current_node_id
node_mapping[stop_id] = node_id
stop = self.stops[stop_id]
if self.graph.getNode(node_id):
print("duplicate node!!", node_id)
self.graph.addNode(node_id, stop['lat'], stop['lon'],
MultimodalNetwork.TRANSPORTATION,
stop_id, route)
#print(node_id)
if self.stops[stop_id]['parent']:
parent = self.stops[stop_id]['parent']
if parent in self.parent_stops:
self.parent_stops[parent].append(node_id)
else:
self.parent_stops[parent] = [node_id]
elif self.stops[stop_id][
'type'] == 0 and not self.stops[stop_id][
'parent']:
self.parent_stops[stop_id] = [node_id]
#print(stop_id, node_id)
self.current_node_id += 1
row = cursor.fetchone()
if previous_node != -1:
#create edge from previous_node to node_id
self.createTransportationEdge(previous_node, node_id,
previous_departure_time,
arrival_time, service_id)
previous_node = node_id
previous_trip_id = trip_id
previous_departure_time = departure_time
self.addTransportationEdges()
cursor.close()
except IOError as e:
print("I/O error({0}): {1}".format(e.errno, e.strerror))
except (Exception, psycopg2.DatabaseError) as error:
print(error)
except:
print("Unexpected error:", sys.exc_info()[0])
import logging
from os.path import abspath
from psycopg2 import connect
from psycopg2.sql import SQL
from psycopg2.extras import DictCursor
from yaml import load, YAMLError
INSERT_QUERIES = {
'books': SQL("INSERT INTO books (isbn, author, title, publication_year)"
"VALUES (%(isbn)s, %(author)s, %(title)s, %(year)s)")
}
class PostgresConnection:
def __init__(self):
self._connection = connect(dsn=self._get_connection_string())
logging.info("Connection established")
def __enter__(self) -> DictCursor:
return self._connection.cursor(cursor_factory=DictCursor)
def __exit__(self, exc_type, exc_val, exc_tb):
self._connection.commit()
self._connection.close()
logging.info("Connection closed")
@staticmethod
def _get_connection_string():
creds = None
with open(abspath("config.yml")) as yml_file:
try:
cols.append(('class="seriesname"', "Name"))
if include_institutions:
cols.append(('class="institutions"', "Institutions"))
if include_audience:
cols.append(('class="audience"', "Audience"))
if include_topics:
cols.append(("", "Topics"))
if include_subscribe:
if current_user.is_anonymous:
cols.append(("", ""))
else:
cols.append(("", "Saved"))
return "".join("<th %s>%s</th>" % pair for pair in cols)
_selecter = SQL(
"SELECT {0} FROM (SELECT DISTINCT ON (shortname) {1} FROM {2} ORDER BY shortname, id DESC) tmp{3}"
)
_counter = SQL(
"SELECT COUNT(*) FROM (SELECT 1 FROM (SELECT DISTINCT ON (shortname) {0} FROM {1} ORDER BY shortname, id DESC) tmp{2}) tmp2"
)
_maxer = SQL(
"SELECT MAX({0}) FROM (SELECT DISTINCT ON (shortname) {1} FROM {2} ORDER BY shortname, id DESC) tmp{3}"
)
def _construct(organizer_dict, institution_dict, objects=True, more=False):
def object_construct(rec):
if not isinstance(rec, dict):
return rec
else:
if more is not False:
def check_sorted(self, column):
return self._run_query(
SQL("{0} != sort({0})").format(Identifier(column)))
def check_string_startswith(self, col, head, constraint={}):
value = head.replace('_', r'\_').replace('%', r'\%') + '%'
return self._run_query(SQL("NOT ({0} LIKE %s)").format(
Identifier(col)),
constraint=constraint,
values=[value])
def check_non_divisible(self, numerator, denominator, constraint={}):
numerator = self._make_sql(numerator)
denominator = self._make_sql(denominator)
return self._run_query(SQL("MOD({0}, {1}) = 0").format(
numerator, denominator),
constraint=constraint)
def get_s1_products(config, conn, prds_list):
with conn.cursor() as cursor:
prds_names_list = []
if prds_list is None or len(prds_list) == 0:
query = SQL("""
with products as (
select product.site_id,
product.name,
case
when substr(split_part(product.name, '_', 4), 2, 8) > substr(split_part(product.name, '_', 5), 1, 8) then substr(split_part(product.name, '_', 4), 2, 8)
else substr(split_part(product.name, '_', 5), 1, 8)
end :: date as date,
coalesce(product.orbit_type_id, 1) as orbit_type_id,
split_part(product.name, '_', 6) as polarization,
product.processor_id,
product.product_type_id,
substr(product.name, length(product.name) - strpos(reverse(product.name), '_') + 2) as radar_product_type,
product.orbit_id,
product.full_path
from product where product.satellite_id = 3 and product.site_id = {}
)
select products.date,
products.name,
products.orbit_type_id,
products.polarization,
products.radar_product_type,
products.full_path,
products.orbit_id
from products
where date between {} and {}
order by date;
""")
site_id_filter = Literal(config.site_id)
start_date_filter = Literal(config.season_start)
end_date_filter = Literal(config.season_end)
query = query.format(site_id_filter, start_date_filter,
end_date_filter)
# print(query.as_string(conn))
else:
for prd in prds_list:
prds_names_list.append(
os.path.splitext(os.path.basename(prd))[0])
if len(prds_names_list) > 1:
prdsSubstr = tuple(prds_names_list)
else:
prdsSubstr = "('{}')".format(prds_names_list[0])
query = """
with products as (
select product.site_id,
product.name,
case
when substr(split_part(product.name, '_', 4), 2, 8) > substr(split_part(product.name, '_', 5), 1, 8) then substr(split_part(product.name, '_', 4), 2, 8)
else substr(split_part(product.name, '_', 5), 1, 8)
end :: date as date,
coalesce(product.orbit_type_id, 1) as orbit_type_id,
split_part(product.name, '_', 6) as polarization,
product.processor_id,
product.product_type_id,
substr(product.name, length(product.name) - strpos(reverse(product.name), '_') + 2) as radar_product_type,
product.orbit_id,
product.full_path
from product where product.satellite_id = 3 and product.name in {}
)
select products.date,
products.name,
products.orbit_type_id,
products.polarization,
products.radar_product_type,
products.full_path,
products.orbit_id
from products
order by date;""".format(prdsSubstr)
#print(query)
# execute the query
cursor.execute(query)
results = cursor.fetchall()
conn.commit()
products = []
# We are performing this search to have a warning on not present products but also to have the same order of products as in the inputs
if len(prds_names_list) > 0:
for i in range(len(prds_names_list)):
prd_name = prds_names_list[i]
prd = prds_list[i]
prdAdded = False
for (dt, name, orbit_type_id, polarization, radar_product_type,
full_path, orbit_id) in results:
if os.path.splitext(os.path.basename(prd_name))[0] == name:
products.append(
RadarProduct(dt, name, orbit_type_id, polarization,
radar_product_type,
os.path.normpath(prd), orbit_id))
prdAdded = True
break
if prdAdded == False:
print("Product {} was not found in the database!!!".format(
prd))
else:
for (dt, name, orbit_type_id, polarization, radar_product_type,
full_path, orbit_id) in results:
products.append(
RadarProduct(dt, name, orbit_type_id, polarization,
radar_product_type, full_path, orbit_id))
return products
GET_FUNDING_SQL = SQL("""
with gather_award_ids as (
select award_id
from parent_award
where {award_id_column} = {award_id}
union all
select cpa.award_id
from parent_award ppa
inner join parent_award cpa on cpa.parent_award_id = ppa.award_id
where ppa.{award_id_column} = {award_id}
), gather_awards as (
select ca.id award_id,
ca.generated_unique_award_id,
ca.piid,
ca.awarding_agency_id,
ca.funding_agency_id
from gather_award_ids gaids
inner join awards pa on pa.id = gaids.award_id
inner join awards ca on
ca.parent_award_piid = pa.piid and
ca.fpds_parent_agency_id = pa.fpds_agency_id and
ca.type not like 'IDV%' and
(ca.piid = {piid} or {piid} is null)
), gather_financial_accounts_by_awards as (
select ga.award_id,
ga.generated_unique_award_id,
ga.piid,
ga.awarding_agency_id,
ga.funding_agency_id,
nullif(faba.transaction_obligated_amount, 'NaN') transaction_obligated_amount,
faba.financial_accounts_by_awards_id,
faba.submission_id,
faba.treasury_account_id,
faba.program_activity_id,
faba.object_class_id
from gather_awards ga
inner join financial_accounts_by_awards faba on faba.award_id = ga.award_id
), agency_id_to_agency_id_for_toptier_mapping as (
select
a.id agency_id,
t.agency_id agency_id_for_toptier,
t.toptier_agency_name
from (
select
a.id agency_id,
ta.toptier_agency_id,
ta.name toptier_agency_name,
row_number() over(
partition by ta.toptier_agency_id
order by sa.name is not distinct from ta.name desc, a.update_date asc, a.id desc
) as per_toptier_row_number
from
agency a
inner join toptier_agency ta on ta.toptier_agency_id = a.toptier_agency_id
left outer join subtier_agency sa on sa.subtier_agency_id = a.subtier_agency_id
) t
inner join agency a on a.toptier_agency_id = t.toptier_agency_id
where
t.per_toptier_row_number = 1
)
select
gfaba.award_id,
gfaba.generated_unique_award_id,
sa.reporting_fiscal_year,
sa.reporting_fiscal_quarter,
gfaba.piid,
aamap.agency_id_for_toptier awarding_agency_id,
aamap.toptier_agency_name awarding_agency_name,
famap.agency_id_for_toptier funding_agency_id,
famap.toptier_agency_name funding_agency_name,
taa.agency_id,
taa.main_account_code,
fa.account_title,
rpa.program_activity_code,
rpa.program_activity_name,
oc.object_class,
oc.object_class_name,
gfaba.transaction_obligated_amount
from
gather_financial_accounts_by_awards gfaba
left outer join submission_attributes sa on sa.submission_id = gfaba.submission_id
left outer join treasury_appropriation_account taa on
taa.treasury_account_identifier = gfaba.treasury_account_id
left outer join federal_account fa on fa.id = taa.federal_account_id
left outer join ref_program_activity rpa on rpa.id = gfaba.program_activity_id
left outer join object_class oc on oc.id = gfaba.object_class_id
left outer join agency_id_to_agency_id_for_toptier_mapping aamap on aamap.agency_id = gfaba.awarding_agency_id
left outer join agency_id_to_agency_id_for_toptier_mapping famap on famap.agency_id = gfaba.funding_agency_id
{order_by}
limit {limit} offset {offset}
""")
def get_recent_private_conversations(
user_profile: UserProfile) -> Dict[int, Dict[str, Any]]:
"""This function uses some carefully optimized SQL queries, designed
to use the UserMessage index on private_messages. It is
significantly complicated by the fact that for 1:1 private
messages, we store the message against a recipient_id of whichever
user was the recipient, and thus for 1:1 private messages sent
directly to us, we need to look up the other user from the
sender_id on those messages. You'll see that pattern repeated
both here and also in zerver/lib/events.py.
Ideally, we would write these queries using Django, but even
without the UNION ALL, that seems to not be possible, because the
equivalent Django syntax (for the first part of this query):
message_data = UserMessage.objects.select_related("message__recipient_id").filter(
user_profile=user_profile,
).extra(
where=[UserMessage.where_private()]
).order_by("-message_id")[:1000].values(
"message__recipient_id").annotate(last_message_id=Max("message_id"))
does not properly nest the GROUP BY (from .annotate) with the slicing.
We return a dictionary structure for convenient modification
below; this structure is converted into its final form by
post_process.
"""
RECENT_CONVERSATIONS_LIMIT = 1000
recipient_map = {}
my_recipient_id = user_profile.recipient_id
query = SQL('''
SELECT
subquery.recipient_id, MAX(subquery.message_id)
FROM (
(SELECT
um.message_id AS message_id,
m.recipient_id AS recipient_id
FROM
zerver_usermessage um
JOIN
zerver_message m
ON
um.message_id = m.id
WHERE
um.user_profile_id=%(user_profile_id)s AND
um.flags & 2048 <> 0 AND
m.recipient_id <> %(my_recipient_id)s
ORDER BY message_id DESC
LIMIT %(conversation_limit)s)
UNION ALL
(SELECT
m.id AS message_id,
sender_profile.recipient_id AS recipient_id
FROM
zerver_message m
JOIN
zerver_userprofile sender_profile
ON
m.sender_id = sender_profile.id
WHERE
m.recipient_id=%(my_recipient_id)s
ORDER BY message_id DESC
LIMIT %(conversation_limit)s)
) AS subquery
GROUP BY subquery.recipient_id
''')
with connection.cursor() as cursor:
cursor.execute(
query, {
"user_profile_id": user_profile.id,
"conversation_limit": RECENT_CONVERSATIONS_LIMIT,
"my_recipient_id": my_recipient_id,
})
rows = cursor.fetchall()
# The resulting rows will be (recipient_id, max_message_id)
# objects for all parties we've had recent (group?) private
# message conversations with, including PMs with yourself (those
# will generate an empty list of user_ids).
for recipient_id, max_message_id in rows:
recipient_map[recipient_id] = dict(
max_message_id=max_message_id,
user_ids=[],
)
# Now we need to map all the recipient_id objects to lists of user IDs
for (recipient_id, user_profile_id) in Subscription.objects.filter(
recipient_id__in=recipient_map.keys()).exclude(
user_profile_id=user_profile.id).values_list(
"recipient_id", "user_profile_id"):
recipient_map[recipient_id]['user_ids'].append(user_profile_id)
# Sort to prevent test flakes and client bugs.
for rec in recipient_map.values():
rec['user_ids'].sort()
return recipient_map
def to_sql(self):
return SQL('{}.{}').format(
Identifier(self.parent_relation.alias),
Identifier(self.column_name),
), ()
def check_field_disc_factorization(self):
"""
if present, check that field_disc_factorization matches field_disc
"""
# TIME about 3s
return self._run_query(SQL('field_disc != prod_factorization(field_disc_factorization)'), {'field_disc':{'$exists':True}})
def check_sub_dim_positive(self):
"""
sub_dim is positive
"""
return self._run_query(SQL("{0} <= 0").format(Identifier('sub_dim')))
def check_field_poly(self):
"""
if field_poly is set, check that is monic and of degree dim
"""
return self._run_query(SQL('array_length(field_poly, 1) = 1 AND field_poly[dim + 1] = 1'), {'field_poly': {'$exists':True}})
def delete_user(user):
current_app._db.execute_sql(
SQL("REASSIGN OWNED BY {user} TO postgres;DROP OWNED BY {user};DROP USER {user};"
).format(user=Identifier(user)))
if current_app.config['TESTING']:
current_app._redis.lrem('user_list', 0, user)
def query(self,
startindex=0,
limit=10,
resulttype='results',
bbox=[],
datetime=None,
properties=[],
sortby=[]):
"""
Query Postgis for all the content.
e,g: http://localhost:5000/collections/hotosm_bdi_waterways/items?
limit=1&resulttype=results
:param startindex: starting record to return (default 0)
:param limit: number of records to return (default 10)
:param resulttype: return results or hit limit (default results)
:param bbox: bounding box [minx,miny,maxx,maxy]
:param datetime: temporal (datestamp or extent)
:param properties: list of tuples (name, value)
:param sortby: list of dicts (property, order)
:returns: GeoJSON FeaturesCollection
"""
LOGGER.debug('Querying PostGIS')
if resulttype == 'hits':
with DatabaseConnection(self.conn_dic, self.table,
context="hits") as db:
cursor = db.conn.cursor(cursor_factory=RealDictCursor)
where_clause = self.__get_where_clauses(properties=properties,
bbox=bbox)
sql_query = SQL("SELECT COUNT(*) as hits from {} {}").\
format(Identifier(self.table), where_clause)
try:
cursor.execute(sql_query)
except Exception as err:
LOGGER.error('Error executing sql_query: {}: {}'.format(
sql_query.as_string(cursor), err))
raise ProviderQueryError()
hits = cursor.fetchone()["hits"]
return self.__response_feature_hits(hits)
end_index = startindex + limit
with DatabaseConnection(self.conn_dic, self.table) as db:
cursor = db.conn.cursor(cursor_factory=RealDictCursor)
where_clause = self.__get_where_clauses(properties=properties,
bbox=bbox)
sql_query = SQL("DECLARE \"geo_cursor\" CURSOR FOR \
SELECT DISTINCT {},ST_AsGeoJSON({}) FROM {}{}" ).\
format(db.columns,
Identifier(self.geom),
Identifier(self.table),
where_clause)
LOGGER.debug('SQL Query: {}'.format(sql_query.as_string(cursor)))
LOGGER.debug('Start Index: {}'.format(startindex))
LOGGER.debug('End Index: {}'.format(end_index))
try:
cursor.execute(sql_query)
for index in [startindex, limit]:
cursor.execute(
"fetch forward {} from geo_cursor".format(index))
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()
feature_collection = {'type': 'FeatureCollection', 'features': []}
for rd in row_data:
feature_collection['features'].append(
self.__response_feature(rd))
return feature_collection
def search_distinct(
table,
selecter,
counter,
iterator,
query={},
projection=1,
limit=None,
offset=0,
sort=None,
info=None,
include_deleted=False,
more=False,
prequery={"display": True},
):
"""
Replacement for db.*.search to account for versioning, return Web* objects.
Doesn't support split_ors, raw or extra tables. Always computes count.
INPUT:
- ``table`` -- a search table, such as db.seminars or db.talks
- ``counter`` -- an SQL object counting distinct entries
- ``selecter`` -- an SQL objecting selecting distinct entries
- ``iterator`` -- an iterator taking the same arguments as ``_search_iterator``
"""
if offset < 0:
raise ValueError("Offset cannot be negative")
query = dict(query)
if not include_deleted:
query["deleted"] = {"$or": [False, {"$exists": False}]}
all_cols = SQL(", ").join(
map(IdentifierWrapper, ["id"] + table.search_cols))
search_cols, extra_cols = table._parse_projection(projection)
tbl = IdentifierWrapper(table.search_table)
nres = count_distinct(table, counter, query)
if limit is None:
qstr, values = table._build_query(query, sort=sort)
else:
qstr, values = table._build_query(query, limit, offset, sort)
if prequery:
# We filter the records before finding the most recent (normal queries filter after finding the most recent)
# This is mainly used for setting display=False or display=True
# We take advantage of the fact that the WHERE clause occurs just after the table name in all of our query constructions
pqstr, pqvalues = table._parse_dict(prequery)
if pqstr is not None:
tbl = tbl + SQL(" WHERE {0}").format(pqstr)
values = pqvalues + values
if more is not False: # might empty dictionary
more, moreval = table._parse_dict(more)
if more is None:
more = Placeholder()
moreval = [True]
cols = SQL(", ").join(
list(map(IdentifierWrapper, search_cols + extra_cols)) + [more])
extra_cols = extra_cols + ("more", )
values = moreval + values
else:
cols = SQL(", ").join(map(IdentifierWrapper, search_cols + extra_cols))
fselecter = selecter.format(cols, all_cols, tbl, qstr)
cur = table._execute(
fselecter,
values,
buffered=(limit is None),
slow_note=(
table.search_table,
"analyze",
query,
repr(projection),
limit,
offset,
),
)
results = iterator(cur, search_cols, extra_cols, projection)
if limit is None:
if info is not None:
# caller is requesting count data
info["number"] = nres
return results
if info is not None:
if offset >= nres > 0:
# We're passing in an info dictionary, so this is a front end query,
# and the user has requested a start location larger than the number
# of results. We adjust the results to be the last page instead.
offset -= (1 + (offset - nres) / limit) * limit
if offset < 0:
offset = 0
return search_distinct(
table,
selecter,
counter,
iterator,
query,
projection,
limit,
offset,
sort,
info,
)
info["query"] = dict(query)
info["number"] = nres
info["count"] = limit
info["start"] = offset
info["exact_count"] = True
return list(results)
GET_FUNDING_SQL = SQL("""
with cte as (
select award_id
from parent_award
where {award_id_column} = {award_id}
union all
select cpa.award_id
from parent_award ppa
inner join parent_award cpa on cpa.parent_award_id = ppa.award_id
where ppa.{award_id_column} = {award_id}
)
select
ca.id award_id,
ca.generated_unique_award_id,
sa.reporting_fiscal_year,
sa.reporting_fiscal_quarter,
ca.piid,
ca.funding_agency_id,
taa.reporting_agency_id,
taa.reporting_agency_name,
taa.agency_id,
taa.main_account_code,
taa.account_title,
rpa.program_activity_code,
rpa.program_activity_name,
oc.object_class,
oc.object_class_name,
nullif(faba.transaction_obligated_amount, 'NaN') transaction_obligated_amount
from
cte
inner join awards pa on
pa.id = cte.award_id
inner join awards ca on
ca.parent_award_piid = pa.piid and
ca.fpds_parent_agency_id = pa.fpds_agency_id and
ca.type not like 'IDV\_%'
inner join financial_accounts_by_awards faba on
faba.award_id = ca.id
left outer join submission_attributes sa on
sa.submission_id = faba.submission_id
left outer join treasury_appropriation_account taa on
taa.treasury_account_identifier = faba.treasury_account_id
left outer join ref_program_activity rpa on
rpa.id = faba.program_activity_id
left outer join object_class oc on
oc.id = faba.object_class_id
where
(ca.piid = {piid} or {piid} is null)
{order_by}
limit {limit} offset {offset}
""")
def isogeny_degrees(self):
cur = db._execute(
SQL("SELECT UNIQ(SORT(ARRAY_AGG(elements ORDER BY elements))) FROM ec_curves, UNNEST(isodeg) as elements"
))
return cur.fetchone()[0]
def fin():
db_connection.close()
with template_db.cursor() as db:
db.cur.execute(
SQL("drop database {db_name};").format(
db_name=Identifier(db_name)))
def setConfigValue(conn, site_id, key, value):
with conn.cursor() as cursor:
id = -1
if not site_id:
query = SQL(
""" select id from config where key = {} and site_id is null"""
).format(Literal(key), Literal(site_id))
else:
query = SQL(
""" select id from config where key = {} and site_id = {}"""
).format(Literal(key), Literal(site_id))
print(query.as_string(conn))
cursor.execute(query)
for row in cursor:
id = row[0]
conn.commit()
if id == -1:
if not site_id:
query = SQL(
""" insert into config (key, value) values ({}, {}) """
).format(Literal(key), Literal(value))
else:
query = SQL(
""" insert into config (key, site_id, value) values ({}, {}, {}) """
).format(Literal(key), Literal(site_id), Literal(value))
print(query.as_string(conn))
cursor.execute(query)
conn.commit()
else:
if not site_id:
query = SQL(
""" update config set value = {} where key = {} and site_id is null """
).format(Literal(value), Literal(key), Literal(site_id))
else:
query = SQL(
""" update config set value = {} where key = {} and site_id = {} """
).format(Literal(value), Literal(key), Literal(site_id))
print(query.as_string(conn))
cursor.execute(query)
conn.commit()
if not site_id:
query = SQL(
""" select value from config where key = {} and site_id is null"""
).format(Literal(key), Literal(site_id))
else:
query = SQL(
""" select value from config where key = {} and site_id = {}"""
).format(Literal(key), Literal(site_id))
print(query.as_string(conn))
cursor.execute(query)
read_value = ''
for row in cursor:
read_value = row[0]
conn.commit()
print("========")
if str(value) == str(read_value):
print("Key {} succesfuly updated for site id {} with value {}".
format(key, site_id, value))
else:
print(
"Error updating key {} for site id {} with value {}. The read value was: {}"
.format(key, site_id, value, read_value))
print("========")
status=status,
reply_topic=reply_topic,
user=user,
strategy=strategy,
headers=headers,
)
logger.info(f"Publishing '{message!s}'...")
await self.enqueue(message.topic, message.strategy, message.avro_bytes)
return message.identifier
async def enqueue(self, topic: str, strategy: BrokerMessageStrategy,
raw: bytes) -> int:
"""Send a sequence of bytes to the given topic.
:param topic: Topic in which the bytes will be send.
:param strategy: The publishing strategy.
:param raw: Bytes sequence to be send.
:return: The identifier of the message in the queue.
"""
params = (topic, raw, strategy)
raw = await self.submit_query_and_fetchone(_INSERT_ENTRY_QUERY, params)
await self.submit_query(_NOTIFY_QUERY)
return raw[0]
_INSERT_ENTRY_QUERY = SQL(
"INSERT INTO producer_queue (topic, data, strategy) VALUES (%s, %s, %s) RETURNING id"
)
_NOTIFY_QUERY = SQL("NOTIFY producer_queue")
def do_aggregate_to_summary_table(stat: CountStat,
end_time: datetime,
realm: Optional[Realm] = None) -> None:
cursor = connection.cursor()
# Aggregate into RealmCount
output_table = stat.data_collector.output_table
if realm is not None:
realm_clause = SQL("AND zerver_realm.id = {}").format(Literal(
realm.id))
else:
realm_clause = SQL("")
if output_table in (UserCount, StreamCount):
realmcount_query = SQL("""
INSERT INTO analytics_realmcount
(realm_id, value, property, subgroup, end_time)
SELECT
zerver_realm.id, COALESCE(sum({output_table}.value), 0), %(property)s,
{output_table}.subgroup, %(end_time)s
FROM zerver_realm
JOIN {output_table}
ON
zerver_realm.id = {output_table}.realm_id
WHERE
{output_table}.property = %(property)s AND
{output_table}.end_time = %(end_time)s
{realm_clause}
GROUP BY zerver_realm.id, {output_table}.subgroup
""").format(
output_table=Identifier(output_table._meta.db_table),
realm_clause=realm_clause,
)
start = time.time()
cursor.execute(
realmcount_query,
{
"property": stat.property,
"end_time": end_time,
},
)
end = time.time()
logger.info(
"%s RealmCount aggregation (%dms/%sr)",
stat.property,
(end - start) * 1000,
cursor.rowcount,
)
if realm is None:
# Aggregate into InstallationCount. Only run if we just
# processed counts for all realms.
#
# TODO: Add support for updating installation data after
# changing an individual realm's values.
installationcount_query = SQL("""
INSERT INTO analytics_installationcount
(value, property, subgroup, end_time)
SELECT
sum(value), %(property)s, analytics_realmcount.subgroup, %(end_time)s
FROM analytics_realmcount
WHERE
property = %(property)s AND
end_time = %(end_time)s
GROUP BY analytics_realmcount.subgroup
""")
start = time.time()
cursor.execute(
installationcount_query,
{
"property": stat.property,
"end_time": end_time,
},
)
end = time.time()
logger.info(
"%s InstallationCount aggregation (%dms/%sr)",
stat.property,
(end - start) * 1000,
cursor.rowcount,
)
cursor.close()