def save(self, knowl, who): """who is the ID of the user, who wants to save the knowl""" most_recent = self.get_knowl(knowl.id, ['id'] + self._default_fields, allow_deleted=False) new_knowl = most_recent is None if new_knowl: authors = [] else: authors = most_recent.pop('authors', []) if who and who not in authors: authors = authors + [who] search_keywords = make_keywords(knowl.content, knowl.id, knowl.title) cat = extract_cat(knowl.id) # When renaming, source is set explicitly on the knowl if knowl.type == 0 and knowl.source is not None: typ, source, name = 0, knowl.source, knowl.source_name else: typ, source, name = extract_typ(knowl.id) links = extract_links(knowl.content) defines = extract_defines(knowl.content) # id, authors, cat, content, last_author, timestamp, title, status, type, links, defines, source, source_name values = (knowl.id, authors, cat, knowl.content, who, knowl.timestamp, knowl.title, knowl.status, typ, links, defines, source, name, search_keywords) with DelayCommit(self): inserter = SQL("INSERT INTO kwl_knowls (id, {0}, _keywords) VALUES ({1})") inserter = inserter.format(SQL(', ').join(map(Identifier, self._default_fields)), SQL(", ").join(Placeholder() * (len(self._default_fields) + 2))) self._execute(inserter, values) self.cached_titles[knowl.id] = knowl.title
def save(self, knowl, who): """who is the ID of the user, who wants to save the knowl""" new_history_item = self.get_knowl(knowl.id, ['id'] + self._default_fields + ['history']) new_knowl = new_history_item is None if new_knowl: history = [] authors = [] else: history = new_history_item.pop('history') if history is not None: history += [new_history_item] else: history = [] authors = new_history_item.pop('authors', []) if authors is None: authors = [] if who and who not in authors: authors = authors + [who] search_keywords = make_keywords(knowl.content, knowl.id, knowl.title) cat = extract_cat(knowl.id) values = (authors, cat, knowl.content, who, knowl.quality, knowl.timestamp, knowl.title, history, search_keywords) with DelayCommit(self): insterer = SQL("INSERT INTO kwl_knowls (id, {0}, history, _keywords) VALUES (%s, {1}) ON CONFLICT (id) DO UPDATE SET ({0}, history, _keywords) = ({1})") insterer = insterer.format(SQL(', ').join(map(Identifier, self._default_fields)), SQL(", ").join(Placeholder() * (len(self._default_fields) + 2))) self._execute(insterer, (knowl.id,) + values + values) self.save_history(knowl, who)
def search(self, category="", filters=[], types=[], keywords="", author=None, sort=[], projection=['id', 'title'], regex=False): """ INPUT: - ``category`` -- a knowl category such as "ec" or "mf". - ``filters`` -- a list, giving a subset of "beta", "reviewed", "in progress" and "deleted". Knowls in the returned list will have their most recent status among the provided values. - ``types`` -- a list, giving a subset of ["normal", "annotations"] - ``keywords`` -- a string giving a space separated list of lower case keywords from the id, title and content. If regex is set, will be used instead as a regular expression to match against content, title and knowl id. - ``author`` -- a string or list of strings giving authors - ``sort`` -- a list of strings or pairs (x, dir) where x is a column name and dir is 1 or -1. - ``projection`` -- a list of column names, not including ``_keywords`` - ``regex`` -- whether to use regular expressions rather than keyword search """ restrictions = [] values = [] if category: restrictions.append(SQL("cat = %s")) values.append(category) if 'in progress' not in filters: restrictions.append(SQL("status != %s")) values.append(-1) if keywords: if regex: restrictions.append( SQL("content ~ %s OR title ~ %s OR id ~ %s")) values.extend([keywords, keywords, keywords]) else: keywords = [w for w in keywords.split(" ") if len(w) >= 3] if keywords: restrictions.append(SQL("_keywords @> %s")) values.append(keywords) if author is not None: restrictions.append(SQL("authors @> %s")) values.append([author]) # In order to be able to sort by arbitrary columns, we have to select everything here. # We therefore do the projection in Python, which is fine for the knowls table since it's tiny fields = ['id'] + self._default_fields sqlfields = SQL(", ").join(map(Identifier, fields)) projfields = [(col, fields.index(col)) for col in projection] if restrictions: restrictions = SQL(" WHERE ") + SQL(" AND ").join(restrictions) else: restrictions = SQL("") selecter = SQL( "SELECT DISTINCT ON (id) {0} FROM kwl_knowls{1} ORDER BY id, timestamp DESC" ).format(sqlfields, restrictions) secondary_restrictions = [] if filters: secondary_restrictions.append( SQL("knowls.{0} = ANY(%s)").format(Identifier("status"))) values.append([ knowl_status_code[q] for q in filters if q in knowl_status_code ]) else: secondary_restrictions.append(SQL("status >= %s")) values.append(0) if not types: # default to just showing normal knowls types = ["normal"] if len(types) == 1: secondary_restrictions.append(SQL("type = %s")) values.append(knowl_type_code[types[0]]) else: secondary_restrictions.append(SQL("type = ANY(%s)")) values.append([knowl_type_code[typ] for typ in types]) secondary_restrictions = SQL(" AND ").join(secondary_restrictions) if sort: sort = SQL(" ORDER BY ") + self._sort_str(sort) else: sort = SQL("") selecter = SQL("SELECT {0} FROM ({1}) knowls WHERE {2}{3}").format( sqlfields, selecter, secondary_restrictions, sort) cur = self._execute(selecter, values) return [{k: res[i] for k, i in projfields} for res in cur]
def get_create_sql(self): return SQL(' ').join( filter(not_empty, (Identifier( self.name), self.get_type_sql(), self.get_constraints_sql())))
def update(self, model_name: str, since_date): log.info('Updating index {} with changes since {}'.format( model_name, since_date)) query = SQL('SELECT * FROM {} WHERE updated_on >= \'{}\''.format( model_name, since_date)) self._replicate(model_name, model_name, query)
return await self.enqueue(message.topic, message.partition, message.value) async def enqueue(self, topic: str, partition: int, binary: bytes) -> int: """Insert row into queue table. Retrieves number of affected rows and row ID. Args: topic: Kafka topic. Example: "TicketAdded" partition: Kafka partition number. binary: Broker Message in bytes. Returns: Queue ID. Example: 12 Raises: Exception: An error occurred inserting record. """ row = await self.submit_query_and_fetchone(_INSERT_QUERY, (topic, partition, binary)) await self.submit_query(_NOTIFY_QUERY.format(Identifier(topic))) return row[0] _INSERT_QUERY = SQL("INSERT INTO consumer_queue (topic, partition, data) VALUES (%s, %s, %s) RETURNING id") _NOTIFY_QUERY = SQL("NOTIFY {}")
def get_constraints_sql(self, extra=()): return super().get_constraints_sql( (SQL('PRIMARY KEY') if self.primary_key else None, *extra))
def sent_messages_report(realm: str) -> str: title = "Recently sent messages for " + realm cols = [ "Date", "Humans", "Bots", ] query = SQL(""" select series.day::date, humans.cnt, bots.cnt from ( select generate_series( (now()::date - interval '2 week'), now()::date, interval '1 day' ) as day ) as series left join ( select date_sent::date date_sent, count(*) cnt from zerver_message m join zerver_userprofile up on up.id = m.sender_id join zerver_realm r on r.id = up.realm_id where r.string_id = %s and (not up.is_bot) and date_sent > now() - interval '2 week' group by date_sent::date order by date_sent::date ) humans on series.day = humans.date_sent left join ( select date_sent::date date_sent, count(*) cnt from zerver_message m join zerver_userprofile up on up.id = m.sender_id join zerver_realm r on r.id = up.realm_id where r.string_id = %s and up.is_bot and date_sent > now() - interval '2 week' group by date_sent::date order by date_sent::date ) bots on series.day = bots.date_sent """) cursor = connection.cursor() cursor.execute(query, [realm, realm]) rows = cursor.fetchall() cursor.close() return make_table(title, cols, rows)
def sql(self): return SQL('CREATE INDEX {} ON {} ({})').format( Identifier(self.name), Identifier(self.table.name), Identifier(self.column_name) ), ()
def sql_injection_ignored_cases(self, ids, cr2): # This cr.execute2 or cr2.execute should not be detected self._cr.execute2('SELECT name FROM account WHERE id IN %s' % (tuple(ids), )) cr2.execute('SELECT name FROM account WHERE id IN %s' % (tuple(ids), )) # Ignore when the query is built using private attributes self._cr.execute('DELETE FROM %s WHERE id IN %%s' % self._table, (tuple(ids), )) # Ignore string parsed with "".format() if args are psycopg2.sql.* calls query = "SELECT * FROM table" # imported from pyscopg2 import sql self._cr.execute( sql.SQL("""CREATE or REPLACE VIEW {} as ({})""").format( sql.Identifier(self._table), sql.SQL(query))) self._cr.execute( sql.SQL("""CREATE or REPLACE VIEW {table} as ({query})""").format( table=sql.Identifier(self._table), query=sql.SQL(query), )) # imported from pyscopg2.sql import SQL, Identifier self._cr.execute( SQL("""CREATE or REPLACE VIEW {} as ({})""").format( Identifier(self._table), SQL(query), )) self._cr.execute( SQL("""CREATE or REPLACE VIEW {table} as ({query})""").format( table=Identifier(self._table), query=SQL(query), )) # imported from pyscopg2 direclty self._cr.execute( psycopg2.SQL("""CREATE or REPLACE VIEW {} as ({})""").format( psycopg2.sql.Identifier(self._table), psycopg2.sql.SQL(query), )) self._cr.execute( psycopg2.sql.SQL( """CREATE or REPLACE VIEW {table} as ({query})""").format( table=Identifier(self._table), query=SQL(query), )) # Variables build using pyscopg2.sql.* callers table = Identifier('table_name') sql_query = SQL(query) # format params self._cr.execute( SQL("""CREATE or REPLACE VIEW {} as ({})""").format( table, sql_query, )) # format dict self._cr.execute( SQL("""CREATE or REPLACE VIEW {table} as ({query})""").format( table=table, query=sql_query, )) self._cr.execute('SELECT name FROM %(table)s' % {'table': self._table})
from usaspending_api.common.cache_decorator import cache_response from usaspending_api.common.helpers.sql_helpers import execute_sql_to_ordered_dictionary from usaspending_api.common.validator.award import get_internal_or_generated_award_id_model from usaspending_api.common.validator.tinyshield import validate_post_request ROLLUP_SQL = SQL(""" with gather_financial_accounts_by_awards as ( select a.awarding_agency_id, a.funding_agency_id, nullif(faba.transaction_obligated_amount, 'NaN') transaction_obligated_amount, faba.treasury_account_id from awards a inner join financial_accounts_by_awards faba on faba.award_id = a.id where {award_id_column} = {award_id} ) select coalesce(sum(gfaba.transaction_obligated_amount), 0.0) total_transaction_obligated_amount, count(distinct aa.toptier_agency_id) awarding_agency_count, count(distinct af.toptier_agency_id) funding_agency_count, count(distinct taa.agency_id || '-' || taa.main_account_code) federal_account_count from gather_financial_accounts_by_awards gfaba left outer join treasury_appropriation_account taa on taa.treasury_account_identifier = gfaba.treasury_account_id left outer join agency aa on aa.id = gfaba.awarding_agency_id left outer join agency af on af.id = gfaba.funding_agency_id """) @validate_post_request([get_internal_or_generated_award_id_model()]) class AwardFundingRollupViewSet(APIView): """
def createDF_byRace_anySUD(logger, race): '''Creates a dataframe with comorbid mental disorder diagnoses with SUD This function creates a dataframe of users from each race, with the first column being having any sud, the dependent variable, and the rest of the columns being the independent variables of the other mental disorder diagnoses. Decorators: lD.log Arguments: logger {logging.Logger} -- logs error information race {str} -- 'AA', 'NHPI', or 'MR' ''' try: query = SQL(''' SELECT t2.sud, t2.mood, t2.anxiety, t2.adjustment, t2.adhd, t2.psyc, t2.pers, t2.childhood, t2.impulse, t2.cognitive, t2.eating, t2.smtf, t2.disso, t2.sleep, t2.fd FROM sarah.test2 t1 INNER JOIN sarah.test3 t2 ON t1.patientid = t2.patientid WHERE t1.age BETWEEN 12 AND 100 AND t1.race = {} ''').format(Literal(race)) data = pgIO.getAllData(query) sud_data = [d[0] for d in data] mood_data = [d[1] for d in data] anxiety_data = [d[2] for d in data] adjustment_data = [d[3] for d in data] adhd_data = [d[4] for d in data] psyc_data = [d[5] for d in data] pers_data = [d[6] for d in data] childhood_data = [d[7] for d in data] impulse_data = [d[8] for d in data] cognitive_data = [d[9] for d in data] eating_data = [d[10] for d in data] smtf_data = [d[11] for d in data] disso_data = [d[12] for d in data] sleep_data = [d[13] for d in data] fd_data = [d[14] for d in data] d = { 'sud': sud_data, 'mood': mood_data, 'anxiety': anxiety_data, 'adjustment': adjustment_data, 'adhd': adhd_data, 'psyc': psyc_data, 'pers': pers_data, 'childhood': childhood_data, 'impulse': impulse_data, 'cognitive': cognitive_data, 'eating': eating_data, 'smtf': smtf_data, 'disso': disso_data, 'sleep': sleep_data, 'fd': fd_data } df = pd.DataFrame(data=d) # Change all columns to binary df.replace({False: 0, True: 1}, inplace=True) df['intercept'] = 1.0 except Exception as e: logger.error('createDF_byRace_anySUD failed because of {}'.format(e)) return df
seminar = seminars_lookup(seminar_id) else: resp, seminar = can_edit_seminar(seminar_id, new=False) if resp is not None: return resp, None, None if seminar.new: # TODO: This is where you might insert the ability to create a talk without first making a seminar flash_error("You must first create the seminar %s" % seminar_id) return redirect(url_for("edit_seminar", shortname=seminar_id), 301) if new: talk = WebTalk(seminar_id, seminar=seminar, editing=True) else: talk = WebTalk(seminar_id, seminar_ctr, seminar=seminar) return None, seminar, talk _selecter = SQL("SELECT {0} FROM (SELECT DISTINCT ON (seminar_id, seminar_ctr) {0} FROM {1} ORDER BY seminar_id, seminar_ctr, id DESC) tmp{2}") _counter = SQL("SELECT COUNT(*) FROM (SELECT 1 FROM (SELECT DISTINCT ON (seminar_id, seminar_ctr) {0} FROM {1} ORDER BY seminar_id, seminar_ctr, id DESC) tmp{2}) tmp2") _maxer = SQL("SELECT MAX({0}) FROM (SELECT DISTINCT ON (seminar_id, seminar_ctr) {1} FROM {2} ORDER BY seminar_id, seminar_ctr, id DESC) tmp{3}") def _construct(rec): if isinstance(rec, str): return rec else: return WebTalk(rec['seminar_id'], rec['seminar_ctr'], data=rec) def _iterator(cur, search_cols, extra_cols, projection): for rec in db.talks._search_iterator(cur, search_cols, extra_cols, projection): yield _construct(rec) def talks_count(query={}): """ Replacement for db.talks.count to account for versioning and so that we don't cache results. """
def get_all_licenses(in_app_db) -> dict: with DatabaseContext(in_app_db, cursor_factory=RealDictCursor) as cursor: command = SQL("select * from License") cursor.execute(command) return cursor.fetchall()
def resurrect(self, knowl): """Sets the status for all deleted copies of the knowl to beta""" updator = SQL( "UPDATE kwl_knowls SET status=%s WHERE status=%s AND id=%s") self._execute(updator, [0, -2, knowl.id]) self.cached_titles[knowl.id] = knowl.title
def delete(self, knowl): """deletes this knowl from the db. This is effected by setting the status to -2 on all copies of the knowl""" updator = SQL("UPDATE kwl_knowls SET status=%s WHERE id=%s") self._execute(updator, [-2, knowl.id]) if knowl.id in self.cached_titles: self.cached_titles.pop(knowl.id)
def add_extent(self, xmin: float, ymin: float, xmax: float, ymax: float, srid=3877) -> None: self.where_parts.append( SQL('{geom} && ST_MakeEnvelope(%(xmin)s, %(ymin)s, %(xmax)s, %(ymax)s, %(srid)s)').format( geom=self.layer_wrapper.geom_field) ) self.vars.update({'xmin': xmin, 'ymin': ymin, 'xmax': xmax, 'ymax': ymax, 'srid': srid})
def __init__(self, model, module_name=None, history=False): super(TableHandler, self).__init__(model, module_name=module_name, history=history) self._columns = {} self._constraints = [] self._fk_deltypes = {} self._indexes = [] transaction = Transaction() cursor = transaction.connection.cursor() # Create sequence if necessary if not transaction.database.sequence_exist(transaction.connection, self.sequence_name): transaction.database.sequence_create(transaction.connection, self.sequence_name) # Create new table if necessary if not self.table_exist(self.table_name): cursor.execute( SQL('CREATE TABLE {} ()').format(Identifier(self.table_name))) self.table_schema = transaction.database.get_table_schema( transaction.connection, self.table_name) cursor.execute( 'SELECT tableowner = current_user FROM pg_tables ' 'WHERE tablename = %s AND schemaname = %s', (self.table_name, self.table_schema)) self.is_owner, = cursor.fetchone() if model.__doc__ and self.is_owner: cursor.execute( SQL('COMMENT ON TABLE {} IS %s').format( Identifier(self.table_name)), (model.__doc__, )) self._update_definitions(columns=True) if 'id' not in self._columns: if not self.history: cursor.execute( SQL("ALTER TABLE {} ADD COLUMN id INTEGER " "DEFAULT nextval(%s) NOT NULL").format( Identifier(self.table_name)), (self.sequence_name, )) cursor.execute( SQL('ALTER TABLE {} ADD PRIMARY KEY(id)').format( Identifier(self.table_name))) else: cursor.execute( SQL('ALTER TABLE {} ADD COLUMN id INTEGER').format( Identifier(self.table_name))) self._update_definitions(columns=True) if self.history and '__id' not in self._columns: cursor.execute( SQL("ALTER TABLE {} ADD COLUMN __id INTEGER " "DEFAULT nextval(%s) NOT NULL").format( Identifier(self.table_name)), (self.sequence_name, )) cursor.execute( SQL('ALTER TABLE {} ADD PRIMARY KEY(__id)').format( Identifier(self.table_name))) else: default = "nextval('%s'::regclass)" % self.sequence_name if self.history: if self._columns['__id']['default'] != default: cursor.execute( SQL("ALTER TABLE {} " "ALTER __id SET DEFAULT nextval(%s::regclass)" ).format(Identifier(self.table_name)), (self.sequence_name, )) if self._columns['id']['default'] != default: cursor.execute( SQL("ALTER TABLE {} " "ALTER id SET DEFAULT nextval(%s::regclass)").format( Identifier(self.table_name)), (self.sequence_name, )) self._update_definitions()
GET_CHILD_IDVS_SQL = SQL( """ select ac.id award_id, ac.type_description award_type, ac.description, tta.name funding_agency, ttb.name awarding_agency, ac.funding_agency_id, ac.awarding_agency_id, ac.generated_unique_award_id, tf.ordering_period_end_date last_date_to_order, pac.rollup_total_obligation obligated_amount, ac.period_of_performance_current_end_date, ac.period_of_performance_start_date, ac.piid from parent_award pap inner join parent_award pac on pac.parent_award_id = pap.award_id inner join awards ac on ac.id = pac.award_id inner join transaction_fpds tf on tf.transaction_id = ac.latest_transaction_id left outer join agency a on a.id = ac.funding_agency_id left outer join agency b on b.id = ac.awarding_agency_id left outer join toptier_agency tta on tta.toptier_agency_id = a.toptier_agency_id left outer join toptier_agency ttb on ttb.toptier_agency_id = b.toptier_agency_id where pap.{award_id_column} = {award_id} order by {sort_column} {sort_direction}, ac.id {sort_direction} limit {limit} offset {offset} """ )
def add_column(self, column_name, sql_type, default=None, comment=''): cursor = Transaction().connection.cursor() database = Transaction().database column_type = database.sql_type(sql_type) match = VARCHAR_SIZE_RE.match(sql_type) field_size = int(match.group(1)) if match else None def add_comment(): if comment and self.is_owner: cursor.execute( SQL('COMMENT ON COLUMN {}.{} IS %s').format( Identifier(self.table_name), Identifier(column_name)), (comment, )) if self.column_exist(column_name): if (column_name in ('create_date', 'write_date') and column_type[1].lower() != 'timestamp(6)'): # Migrate dates from timestamp(0) to timestamp cursor.execute( SQL('ALTER TABLE {} ALTER COLUMN {} TYPE timestamp' ).format(Identifier(self.table_name), Identifier(column_name))) add_comment() base_type = column_type[0].lower() if base_type != self._columns[column_name]['typname']: if (self._columns[column_name]['typname'], base_type) in [ ('varchar', 'text'), ('text', 'varchar'), ('date', 'timestamp'), ('int4', 'int8'), ('int4', 'float8'), ('int4', 'numeric'), ('int8', 'float8'), ('int8', 'numeric'), ('float8', 'numeric'), ]: self.alter_type(column_name, base_type) else: logger.warning( 'Unable to migrate column %s on table %s ' 'from %s to %s.', column_name, self.table_name, self._columns[column_name]['typname'], base_type) if (base_type == 'varchar' and self._columns[column_name]['typname'] == 'varchar'): # Migrate size from_size = self._columns[column_name]['size'] if field_size is None: if from_size: self.alter_size(column_name, base_type) elif from_size == field_size: pass elif from_size and from_size < field_size: self.alter_size(column_name, column_type[1]) else: logger.warning( 'Unable to migrate column %s on table %s ' 'from varchar(%s) to varchar(%s).', column_name, self.table_name, from_size if from_size and from_size > 0 else "", field_size) return column_type = column_type[1] cursor.execute( SQL('ALTER TABLE {} ADD COLUMN {} {}').format( Identifier(self.table_name), Identifier(column_name), SQL(column_type))) add_comment() if default: # check if table is non-empty: cursor.execute('SELECT 1 FROM "%s" limit 1' % self.table_name) if cursor.rowcount: # Populate column with default values: cursor.execute( SQL('UPDATE {} SET {} = %s').format( Identifier(self.table_name), Identifier(column_name)), (default(), )) self._update_definitions(columns=True)
def main(): # Preserve encoding to UTF-8 os.environ['SHAPE_ENCODING'] = "utf-8" newFields = [ NewFieldDef("mow_n", ogr.OFTInteger, "0"), NewFieldDef("m1_dstart", ogr.OFTString, "0"), NewFieldDef("m1_dend", ogr.OFTString, "0"), NewFieldDef("m1_conf", ogr.OFTReal, "0"), NewFieldDef("m1_mis", ogr.OFTString, "0"), NewFieldDef("m2_dstart", ogr.OFTString, "0"), NewFieldDef("m2_dend", ogr.OFTString, "0"), NewFieldDef("m2_conf", ogr.OFTReal, "0"), NewFieldDef("m2_mis", ogr.OFTString, "0"), NewFieldDef("m3_dstart", ogr.OFTString, "0"), NewFieldDef("m3_dend", ogr.OFTString, "0"), NewFieldDef("m3_conf", ogr.OFTReal, "0"), NewFieldDef("m3_mis", ogr.OFTString, "0"), NewFieldDef("m4_dstart", ogr.OFTString, "0"), NewFieldDef("m4_dend", ogr.OFTString, "0"), NewFieldDef("m4_conf", ogr.OFTReal, "0"), NewFieldDef("m4_mis", ogr.OFTString, "0"), NewFieldDef("proc", ogr.OFTInteger, "0"), NewFieldDef("compl", ogr.OFTInteger, "0") ] parser = argparse.ArgumentParser( description="Creates the grassland mowing input shapefile") parser.add_argument('-c', '--config-file', default='/etc/sen2agri/sen2agri.conf', help="configuration file location") parser.add_argument('-s', '--site-id', type=int, help="site ID to filter by") parser.add_argument('-p', '--path', default='.', help="working path") parser.add_argument('-y', '--year', help="year") parser.add_argument('-f', '--filter-ctnum', default="", help="Filtering CTnum fields") parser.add_argument( '-a', '--add-decl-cols', default="", help= "Additional columns from declarations table to be added to the output shapefile" ) parser.add_argument('--force', help="overwrite field", action='store_true') parser.add_argument('--filter-ids-table', help="A table name containing filter ids") parser.add_argument( '--srid', help="EPSG projection to be used for the output shapefile") #parser.add_argument('--dynamic-srid', default=False, help="Compute dynamically the srid from the NDVI products") args = parser.parse_args() config = Config(args) pool = multiprocessing.dummy.Pool(1) pg_path = 'PG:dbname={} host={} port={} user={} password={}'.format( config.dbname, config.host, config.port, config.user, config.password) with psycopg2.connect(host=config.host, port=config.port, dbname=config.dbname, user=config.user, password=config.password) as conn: site_name = get_site_name(conn, config.site_id) year = args.year or date.today().year lpis_table = "decl_{}_{}".format(site_name, year) lut_table = "lut_{}_{}".format(site_name, year) commands = [] shp = args.path ctnumFilter = "" if not os.path.exists(os.path.dirname(shp)): try: os.makedirs(os.path.dirname(shp)) except OSError as exc: # Guard against race condition if exc.errno != errno.EEXIST: raise ctnums = [] str_ctnums = "" if args.filter_ctnum: ctnums = list(map(int, args.filter_ctnum.split(','))) str_ctnums = ', '.join(str(x) for x in ctnums) # Additional column names to be added from the declarations table add_col_names = [] str_add_col_names = "" if args.add_decl_cols: add_col_names = [x.strip() for x in args.add_decl_cols.split(',')] str_add_col_names = ', '.join(str(x) for x in add_col_names) str_add_col_names = ", " + str_add_col_names if len(ctnums) > 0: if args.filter_ids_table: sql = "select \"NewID\", ctnum as \"CTnum\", ori_hold as \"Ori_hold\", ori_id as \"Ori_id\", ori_crop as \"Ori_crop\", \"Area_meters\" as \"Area_meter\", wkb_geometry {} from {} natural join {} where ctnum in ({}) and \"NewID\" in (select newid from {})".format( str_add_col_names, lpis_table, lut_table, str_ctnums, args.filter_ids_table) else: sql = "select \"NewID\", ctnum as \"CTnum\", ori_hold as \"Ori_hold\", ori_id as \"Ori_id\", ori_crop as \"Ori_crop\", \"Area_meters\" as \"Area_meter\", wkb_geometry {} from {} natural join {} where ctnum in ({})".format( str_add_col_names, lpis_table, lut_table, str_ctnums) else: if args.filter_ids_table: sql = SQL( 'select \"NewID\", ori_hold as \"Ori_hold\", ori_id as \"Ori_id\", ori_crop as \"Ori_crop\", \"Area_meters\" as \"Area_meter\", wkb_geometry {} from {} where \"NewID\" in (select newid from {})' ) sql = sql.format(SQL(str_add_col_names), Identifier(lpis_table), Identifier(args.filter_ids_table)) else: sql = SQL( 'select \"NewID\", ori_hold as \"Ori_hold\", ori_id as \"Ori_id\", ori_crop as \"Ori_crop\", \"Area_meters\" as \"Area_meter\", wkb_geometry {} from {}' ) sql = sql.format(SQL(str_add_col_names), Identifier(lpis_table)) sql = sql.as_string(conn) command = [] command += ["ogr2ogr"] command += ["-sql", sql] command += [shp] command += [pg_path] command += ["-lco", "ENCODING=UTF-8"] if args.srid: command += ["-t_srs", "EPSG:" + str(args.srid)] commands.append(command) print("Starting executing the commands ...") pool.map(lambda c: run_command(c), commands) print("Finished executing commands!") dataset = ogr.Open(shp, gdalconst.GA_Update) layer = dataset.GetLayer() feature_count = layer.GetFeatureCount() print("{} feature(s) found".format(feature_count)) schema = [] ldefn = layer.GetLayerDefn() print("Updating existing values (if needed) ...") for n in range(ldefn.GetFieldCount()): fdefn = ldefn.GetFieldDefn(n) name = fdefn.GetName() fieldTypeCode = fdefn.GetType() fieldType = fdefn.GetFieldTypeName(fieldTypeCode) print("Field name = {}, fieldTypeCode= {}, fieldType = {}".format( name, fieldTypeCode, fieldType)) schema.append(fdefn.name) print("Existing column names: ".format(schema)) for newField in newFields: dataset.StartTransaction() print("Adding new field {}, type {} with default value {}".format( newField.name, newField.type, newField.defVal)) field_idx = layer.FindFieldIndex(newField.name, False) if field_idx != -1: if args.force: print("Field {} already exists, removing it".format( newField.name)) layer.DeleteField(field_idx) else: print("Field {} already exists, ignoring".format( newField.name)) continue layer.CreateField(ogr.FieldDefn(newField.name, newField.type)) dataset.CommitTransaction() print("Setting default values ...") dataset.StartTransaction() for feature in layer: for newField in newFields: field_idx = layer.FindFieldIndex(newField.name, False) feature.SetField(field_idx, newField.defVal) layer.SetFeature(feature) dataset.CommitTransaction() print("Done!")
def add_comment(): if comment and self.is_owner: cursor.execute( SQL('COMMENT ON COLUMN {}.{} IS %s').format( Identifier(self.table_name), Identifier(column_name)), (comment, ))
def sql(self): return SQL('CREATE INDEX {} ON {} USING GIST ({} gist_trgm_ops)').format( Identifier(self.name), Identifier(self.table.name), Identifier(self.column_name) ), ()
mark INT, review text );""" TABLE_answer_loyalty = """CREATE TABLE answer_loyalty ( id serial PRIMARY KEY, user_id BIGINT references users(id), answering_date DATE, loyalty INT, manager INT, delivery INT, cooking INT, dietetics INT, review text );""" with psycopg2.connect( host=DB_HOST, database=DB_NAME, user=DB_USER, password=DB_PASS, ) as conn: with conn.cursor(cursor_factory=DictCursor) as cur: cur.execute(SQL(TABLE_users)) cur.execute(SQL(TABLE_menu)) cur.execute(SQL(TABLE_dish)) cur.execute(SQL(TABLE_feedback)) cur.execute(SQL(TABLE_answer_loyalty))
def move_expired_personal_and_huddle_messages_to_archive( realm: Realm, chunk_size: int = MESSAGE_BATCH_SIZE, ) -> int: message_retention_days = realm.message_retention_days assert message_retention_days != -1 check_date = timezone_now() - timedelta(days=message_retention_days) # This function will archive appropriate messages and their related objects. internal_realm = get_realm(settings.SYSTEM_BOT_REALM) cross_realm_bot_ids = [ get_user_including_cross_realm(email, internal_realm).id for email in settings.CROSS_REALM_BOT_EMAILS ] recipient_types = (Recipient.PERSONAL, Recipient.HUDDLE) # Archive expired personal and huddle Messages in the realm, except cross-realm messages. # The condition zerver_userprofile.realm_id = {realm_id} assures the row won't be # a message sent by a cross-realm bot, because cross-realm bots have their own separate realm. query = SQL(""" INSERT INTO zerver_archivedmessage ({dst_fields}, archive_transaction_id) SELECT {src_fields}, {archive_transaction_id} FROM zerver_message INNER JOIN zerver_recipient ON zerver_recipient.id = zerver_message.recipient_id INNER JOIN zerver_userprofile ON zerver_userprofile.id = zerver_message.sender_id WHERE zerver_userprofile.realm_id = {realm_id} AND zerver_recipient.type in {recipient_types} AND zerver_message.date_sent < {check_date} LIMIT {chunk_size} ON CONFLICT (id) DO UPDATE SET archive_transaction_id = {archive_transaction_id} RETURNING id """) message_count = run_archiving_in_chunks( query, type=ArchiveTransaction.RETENTION_POLICY_BASED, realm=realm, cross_realm_bot_ids=Literal(tuple(cross_realm_bot_ids)), realm_id=Literal(realm.id), recipient_types=Literal(recipient_types), check_date=Literal(check_date.isoformat()), chunk_size=chunk_size, ) # Archive cross-realm personal messages to users in the realm. We # don't archive cross-realm huddle messages via retention policy, # as we don't support them as a feature in Zulip, and the query to # find and delete them would be a lot of complexity and potential # performance work for a case that doesn't actually happen. query = SQL(""" INSERT INTO zerver_archivedmessage ({dst_fields}, archive_transaction_id) SELECT {src_fields}, {archive_transaction_id} FROM zerver_message INNER JOIN zerver_userprofile recipient_profile ON recipient_profile.recipient_id = zerver_message.recipient_id WHERE zerver_message.sender_id IN {cross_realm_bot_ids} AND recipient_profile.realm_id = {realm_id} AND zerver_message.date_sent < {check_date} LIMIT {chunk_size} ON CONFLICT (id) DO UPDATE SET archive_transaction_id = {archive_transaction_id} RETURNING id """) message_count += run_archiving_in_chunks( query, type=ArchiveTransaction.RETENTION_POLICY_BASED, realm=realm, cross_realm_bot_ids=Literal(tuple(cross_realm_bot_ids)), realm_id=Literal(realm.id), check_date=Literal(check_date.isoformat()), chunk_size=chunk_size, ) return message_count
import contextlib import csv import lzma import os from importlib.resources import open_binary, open_text from sys import stderr from django.db import transaction from django.db.transaction import get_connection from psycopg2.sql import SQL, Identifier from data_france.type_noms import TypeNom COPY_SQL = SQL( """COPY {table} ({columns}) FROM STDIN WITH NULL AS '\\N' CSV QUOTE AS '"';""" ) CREATE_TEMP_TABLE_SQL = SQL(""" CREATE TEMPORARY TABLE {temp_table} AS SELECT {columns} FROM {reference_table} LIMIT 0; """) DROP_TEMPORARY_TABLE_SQL = SQL(""" DROP TABLE IF EXISTS {temp_table}; """) COPY_FROM_TEMP_TABLE = SQL(""" INSERT INTO {table} ({all_columns}) SELECT {all_columns} FROM {temp_table} ON CONFLICT({id_column}) DO UPDATE SET {setters}
def get_constraints_sql(self): other_table = self.source.other.Meta.table_name return super().get_constraints_sql( (SQL('REFERENCES {}(id)').format(Identifier(other_table)), ))
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()
def get_type_sql(self): return SQL(self.type)
def _set_initial_parts(self): self.select_parts.append(SQL('SELECT {pk}').format(pk=self.layer_wrapper.pk)) self.from_parts.append(SQL('FROM {table}').format(table=self.layer_wrapper.table))
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=list(), ) # 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 query(self, startindex=0, limit=10, resulttype='results', bbox=[], datetime_=None, properties=[], sortby=[], select_properties=[], skip_geometry=False, q=None): """ 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) :param select_properties: list of property names :param skip_geometry: bool of whether to skip geometry (default False) :param q: full-text search term(s) :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