def fetchall(self): if self._prefetch: res = _cursor.fetchall(self) if self._query_executed: self._build_index() if not self._prefetch: res = _cursor.fetchall(self) return res
def fetchall(self): if self._prefetch: res = _cursor.fetchall(self) if self._query_executed: self._build_index() if not self._prefetch: res = _cursor.fetchall(self) return res
def find_old_ids() -> None: recips = ', '.join(str(id) for id in recipient_ids) is_topic_muted = build_topic_mute_checker(cursor, user_profile) query = ''' SELECT zerver_usermessage.id, zerver_message.recipient_id, zerver_message.subject FROM zerver_usermessage INNER JOIN zerver_message ON ( zerver_message.id = zerver_usermessage.message_id ) WHERE ( zerver_usermessage.user_profile_id = %s AND zerver_usermessage.message_id <= %s AND (zerver_usermessage.flags & 1) = 0 AND zerver_message.recipient_id in (%s) ) ''' % (user_profile.id, pointer, recips) logger.info(''' EXPLAIN analyze''' + query.rstrip() + ';') cursor.execute(query) rows = cursor.fetchall() for (um_id, recipient_id, topic) in rows: if not is_topic_muted(recipient_id, topic): user_message_ids.append(um_id) logger.info('rows found: %d' % (len(user_message_ids), ))
def build_topic_mute_checker( cursor: CursorObj, user_profile: UserProfile) -> Callable[[int, Text], bool]: ''' This function is similar to the function of the same name in zerver/lib/topic_mutes.py, but it works without the ORM, so that we can use it in migrations. ''' query = ''' SELECT recipient_id, topic_name FROM zerver_mutedtopic WHERE user_profile_id = %s ''' cursor.execute(query, [user_profile.id]) rows = cursor.fetchall() tups = {(recipient_id, topic_name.lower()) for (recipient_id, topic_name) in rows} def is_muted(recipient_id: int, topic: Text) -> bool: return (recipient_id, topic.lower()) in tups return is_muted
def find() -> None: recips = ', '.join(str(id) for id in recipient_ids) query = ''' SELECT zerver_usermessage.id FROM zerver_usermessage INNER JOIN zerver_message ON ( zerver_message.id = zerver_usermessage.message_id ) WHERE ( zerver_usermessage.user_profile_id = %s AND (zerver_usermessage.flags & 1) = 0 AND zerver_message.recipient_id in (%s) ) ''' % (user_profile.id, recips) logger.info(''' EXPLAIN analyze''' + query.rstrip() + ';') cursor.execute(query) rows = cursor.fetchall() for row in rows: user_message_ids.append(row[0]) logger.info('rows found: %d' % (len(user_message_ids), ))
def build_topic_mute_checker(cursor: CursorObj, user_profile: UserProfile) -> Callable[[int, str], bool]: ''' This function is similar to the function of the same name in zerver/lib/topic_mutes.py, but it works without the ORM, so that we can use it in migrations. ''' query = ''' SELECT recipient_id, topic_name FROM zerver_mutedtopic WHERE user_profile_id = %s ''' cursor.execute(query, [user_profile.id]) rows = cursor.fetchall() tups = { (recipient_id, topic_name.lower()) for (recipient_id, topic_name) in rows } def is_muted(recipient_id: int, topic: str) -> bool: return (recipient_id, topic.lower()) in tups return is_muted
def poll_times_df(connection: Maybe[connection] = Nothing(), ) -> pd.DataFrame: with connection.or_else_lazy(default_connection) as connection: with connection.cursor() as cursor: cursor.execute( "select distinct poll_time from passage_responses order by poll_time asc" ) return pd.DataFrame(cursor.fetchall(), columns=["poll_time"])
def timetable( timetable_id: int, connection: Maybe[connection] = Nothing() ) -> Either[str, Timetable]: with connection.or_else_lazy(default_connection) as conn: with conn.cursor() as cursor: cursor.execute( """ select caption from timetables where id = %s """, [timetable_id], ) result = cursor.fetchone() if result is None: return Left(f"Timetable {timetable_id} not in database") caption = result[0] cursor.execute( """ select id from timetable_variants where timetable_id = %s """, [timetable_id], ) variants = (timetable_variant(id, Just(conn)) for (id, ) in cursor.fetchall()) return Right( Timetable(caption, {v.value for v in variants if isinstance(v, Right)}))
def fetchall(self): """ Fetch all tuples as ordered dictionary list. """ tuples = _cursor.fetchall(self) if tuples is not None: return [self._dict_tuple(t) for t in tuples]
def find_old_ids() -> None: is_topic_muted = build_topic_mute_checker(cursor, user_profile) query = SQL(''' SELECT zerver_usermessage.id, zerver_message.recipient_id, zerver_message.subject FROM zerver_usermessage INNER JOIN zerver_message ON ( zerver_message.id = zerver_usermessage.message_id ) WHERE ( zerver_usermessage.user_profile_id = %(user_profile_id)s AND zerver_usermessage.message_id <= %(pointer)s AND (zerver_usermessage.flags & 1) = 0 AND zerver_message.recipient_id in %(recipient_ids)s ) ''') cursor.execute( query, { "user_profile_id": user_profile.id, "pointer": pointer, "recipient_ids": tuple(recipient_ids), }) rows = cursor.fetchall() for (um_id, recipient_id, topic) in rows: if not is_topic_muted(recipient_id, topic): user_message_ids.append(um_id) logger.info('rows found: %d', len(user_message_ids))
def find_old_ids(): # type: () -> None recips = ', '.join(str(id) for id in recipient_ids) query = ''' SELECT zerver_usermessage.id, zerver_recipient.type_id, subject FROM zerver_usermessage INNER JOIN zerver_message ON ( zerver_message.id = zerver_usermessage.message_id ) INNER JOIN zerver_recipient ON ( zerver_recipient.id = zerver_message.recipient_id ) WHERE ( zerver_usermessage.user_profile_id = %s AND zerver_usermessage.message_id <= %s AND (zerver_usermessage.flags & 1) = 0 AND zerver_message.recipient_id in (%s) ) ''' % (user_profile.id, pointer, recips) print(''' EXPLAIN analyze''' + query.rstrip() + ';') cursor.execute(query) rows = cursor.fetchall() for (um_id, stream_id, topic) in rows: if not is_topic_muted(stream_id, topic): user_message_ids.append(um_id) print('rows found: %d' % (len(user_message_ids),))
def timetable_variant( variant_id: int, connection: Maybe[connection] = Nothing() ) -> Either[str, TimetableVariant]: with connection.or_else_lazy(default_connection) as conn: with conn.cursor() as cursor: cursor.execute( """ select route_name from timetable_variants where id = %s """, [variant_id], ) variant_row = cursor.fetchone() if variant_row is None: return Left(f"Variant {variant_id} not in database") route_name = variant_row[0] cursor.execute( """ select stop from variant_stops where variant = %s order by position asc """, [variant_id], ) stop_ids = (stop for (stop, ) in cursor.fetchall()) stops = tuple(stop_by_id(conn, StopId(s)) for s in stop_ids) if all(map(lambda s: isinstance(s, Just), stops)): just_stops = cast(Tuple[Just[Stop], ...], stops) return Right( TimetableVariant(route_name, tuple(s.value for s in just_stops))) else: return Left( "Error in database, stop in variant_stops but not in stops" )
def find() -> None: query = SQL( """ SELECT zerver_usermessage.id FROM zerver_usermessage INNER JOIN zerver_message ON ( zerver_message.id = zerver_usermessage.message_id ) WHERE ( zerver_usermessage.user_profile_id = %(user_profile_id)s AND (zerver_usermessage.flags & 1) = 0 AND zerver_message.recipient_id in %(recipient_ids)s ) """ ) cursor.execute( query, { "user_profile_id": user_profile.id, "recipient_ids": tuple(recipient_ids), }, ) rows = cursor.fetchall() for row in rows: user_message_ids.append(row[0]) logger.info("rows found: %d", len(user_message_ids))
def find_old_ids() -> None: recips = ', '.join(str(id) for id in recipient_ids) is_topic_muted = build_topic_mute_checker(cursor, user_profile) query = ''' SELECT zerver_usermessage.id, zerver_message.recipient_id, zerver_message.subject FROM zerver_usermessage INNER JOIN zerver_message ON ( zerver_message.id = zerver_usermessage.message_id ) WHERE ( zerver_usermessage.user_profile_id = %s AND zerver_usermessage.message_id <= %s AND (zerver_usermessage.flags & 1) = 0 AND zerver_message.recipient_id in (%s) ) ''' % (user_profile.id, pointer, recips) logger.info(''' EXPLAIN analyze''' + query.rstrip() + ';') cursor.execute(query) rows = cursor.fetchall() for (um_id, recipient_id, topic) in rows: if not is_topic_muted(recipient_id, topic): user_message_ids.append(um_id) logger.info('rows found: %d' % (len(user_message_ids),))
def find() -> None: recips = ', '.join(str(id) for id in recipient_ids) query = ''' SELECT zerver_usermessage.id FROM zerver_usermessage INNER JOIN zerver_message ON ( zerver_message.id = zerver_usermessage.message_id ) WHERE ( zerver_usermessage.user_profile_id = %s AND (zerver_usermessage.flags & 1) = 0 AND zerver_message.recipient_id in (%s) ) ''' % (user_profile.id, recips) logger.info(''' EXPLAIN analyze''' + query.rstrip() + ';') cursor.execute(query) rows = cursor.fetchall() for row in rows: user_message_ids.append(row[0]) logger.info('rows found: %d' % (len(user_message_ids),))
def _test(cls, connection, hostname=None): cursor = connection.cursor() tables = ('ir_model', 'ir_model_field', 'ir_ui_view', 'ir_ui_menu', 'res_user', 'res_group', 'ir_module', 'ir_module_dependency', 'ir_translation', 'ir_lang', 'ir_configuration') cursor.execute('SELECT table_name FROM information_schema.tables ' 'WHERE table_name IN %s', (tables,)) if len(cursor.fetchall()) != len(tables): return False if hostname: cursor.execute( 'SELECT hostname FROM ir_configuration') hostnames = {h for h, in cursor.fetchall() if h} if hostnames and hostname not in hostnames: return False return True
def fetchall(self): """ Fetch all tuples as ordered dictionary list. """ tuples = _cursor.fetchall(self) if tuples is not None: return [self._dict_tuple(t) for t in tuples]
def _test(cls, connection): cursor = connection.cursor() cursor.execute( 'SELECT 1 FROM information_schema.tables ' 'WHERE table_name IN %s', (('ir_model', 'ir_model_field', 'ir_ui_view', 'ir_ui_menu', 'res_user', 'res_group', 'ir_module', 'ir_module_dependency', 'ir_translation', 'ir_lang'), )) return len(cursor.fetchall()) != 0
def get_snapshots(db: connection, chain_id: int, start: int, end: Optional[int]) -> Dict[str, List]: cursor = db.cursor() cursor.execute(queries['snapshots'], { 'chain_id': chain_id, 'start': start, 'end': end }) return [row._asdict() for row in cursor.fetchall()]
def _test(cls, connection): cursor = connection.cursor() cursor.execute('SELECT 1 FROM information_schema.tables ' 'WHERE table_name IN %s', (('ir_model', 'ir_model_field', 'ir_ui_view', 'ir_ui_menu', 'res_user', 'res_group', 'ir_module', 'ir_module_dependency', 'ir_translation', 'ir_lang'),)) return len(cursor.fetchall()) != 0
def execute(connection: connection, statement: str) -> Optional[List[Tuple[str, ...]]]: """Execute PGSQL statement and fetches the statement response. Parameters ---------- connection: psycopg2.extensions.connection Active connection to a PostGreSQL database. statement: str PGSQL statement to run against the database. Returns ------- response: list or None List of tuples, where each tuple represents a formatted line of response from the database engine, where each tuple item roughly corresponds to a column. For instance, while a raw SELECT response might include the table headers, psycopg2 returns only the rows that matched. If no response was given, None is returned. """ response = list() # type: List # See the following link for reasoning behind both with statements: # http://initd.org/psycopg/docs/usage.html#with-statement # # Additionally, the with statement makes this library safer to use with # higher-level libraries (e.g. SQLAlchemy) that don't inherently respect # PostGreSQL's autocommit isolation-level, since the transaction is # properly completed for each statement. with connection: with connection.cursor(cursor_factory=Psycopg2Cursor) as cursor: cursor.execute(statement) connection.commit() # Get response try: response = cursor.fetchall() if not response: # Empty response list log('<No Response>', logger_name=_LOGGER_NAME) return None except ProgrammingError as e: if e.args and e.args[0] == 'no results to fetch': # No response available (i.e. no response given) log('<No Response>', logger_name=_LOGGER_NAME) return None # Some other programming error; re-raise raise e log('Response', logger_name=_LOGGER_NAME) log('--------', logger_name=_LOGGER_NAME) for line in response: log(str(line), logger_name=_LOGGER_NAME) return response
def safe_insert_bulk(self, table, data, return_insert=True, commit=False): """ Insert rows as list of dictionaries Parameters ---------- table: str SQL table data: list[dict] dictionary representing sql row to insert, dict keys should be the same as the sql table's columns return_insert: bool return the inserted rows commit: bool commit inserts to database Returns ------- list of dict or tuple a list of dictionary or tuple representing the inserted rows. """ if not data: raise Exception("No Data to insert") rv = None cursor = self.cursor() columns = self.get_columns(cursor, table) subs = [] row = "" for d in data: filtered_data = {k: v for k, v in d.items() if k in columns} subs.append(tuple(filtered_data.values())) row += f",%s" if len(row): row = row[1:] subs.insert(0, AsIs(",".join(filtered_data.keys()))) if return_insert: subs.append(AsIs(",".join(columns))) insert_stmt = f"""INSERT INTO {table} (%s) VALUES {row} RETURNING %s;""" else: insert_stmt = f"""INSERT INTO {table} (%s) VALUES {row};""" stmt = cursor.mogrify(insert_stmt, tuple(subs)) cursor.execute(stmt) if return_insert: rv = self._format_row(cursor.fetchall(), columns) cursor.close() if commit: self.commit() return rv
def fetchall(self, back_as=None): ts = TupleCursor.fetchall(self) cols = self.description back_as = back_as or self.back_as if back_as: try: back_as = self.connection.back_as_registry[back_as] except KeyError: raise BadBackAs(back_as, self.connection.back_as_registry) return [back_as(cols, t) for t in ts] else: return ts
def processor(data): qdata['query'] = self._format_query(query, vars) if self.explain_breadcrumbs or talisker.Context.debug: try: cursor = base_connection.cursor() cursor.execute('EXPLAIN ' + query, vars) plan = '\n'.join(l[0] for l in cursor.fetchall()) qdata['plan'] = plan except Exception as e: qdata['plan'] = 'could not explain query: ' + str(e) data['data'].update(qdata)
def do_batch_update(cursor: CursorObj, table: str, cols: List[str], vals: List[str], batch_size: int = 10000, sleep: float = 0.1, escape: bool = True) -> None: # nocoverage # The string substitution below is complicated by our need to # support multiple postgres versions. stmt = ''' UPDATE %s SET %s WHERE id >= %%s AND id < %%s ''' % (table, ', '.join(['%s = %%s' % (col) for col in cols])) cursor.execute("SELECT MIN(id), MAX(id) FROM %s" % (table, )) (min_id, max_id) = cursor.fetchall()[0] if min_id is None: return print("\n Range of rows to update: [%s, %s]" % (min_id, max_id)) while min_id <= max_id: lower = min_id upper = min_id + batch_size print(' Updating range [%s,%s)' % (lower, upper)) params = list(vals) + [lower, upper] if escape: cursor.execute(stmt, params=params) else: cursor.execute(stmt % tuple(params)) min_id = upper time.sleep(sleep) # Once we've finished, check if any new rows were inserted to the table if min_id > max_id: cursor.execute("SELECT MAX(id) FROM %s" % (table, )) max_id = cursor.fetchall()[0][0] print(" Finishing...", end='')
def do_batch_update(cursor: CursorObj, table: str, cols: List[str], vals: List[str], batch_size: int=10000, sleep: float=0.1, escape: bool=True) -> None: # nocoverage stmt = ''' UPDATE %s SET (%s) = (%s) WHERE id >= %%s AND id < %%s ''' % (table, ', '.join(cols), ', '.join(['%s'] * len(cols))) cursor.execute("SELECT MIN(id), MAX(id) FROM %s" % (table,)) (min_id, max_id) = cursor.fetchall()[0] if min_id is None: return print("\n Range of rows to update: [%s, %s]" % (min_id, max_id)) while min_id <= max_id: lower = min_id upper = min_id + batch_size print(' Updating range [%s,%s)' % (lower, upper)) params = list(vals) + [lower, upper] if escape: cursor.execute(stmt, params=params) else: cursor.execute(stmt % tuple(params)) min_id = upper time.sleep(sleep) # Once we've finished, check if any new rows were inserted to the table if min_id > max_id: cursor.execute("SELECT MAX(id) FROM %s" % (table,)) max_id = cursor.fetchall()[0][0] print(" Finishing...", end='')
def execute_query(self, query, all=True): """ Execute a simple query without arguments for the given connection :raises psycopg2.ProgrammingError: if there was no result set when executing the query """ cursor = self._conn.cursor() cursor.execute(query) if all: query_results = cursor.fetchall() else: query_results = cursor.fetchone() cursor.close() return query_results
def get_chain_meta(db: connection, chain_id: int) -> Optional[Dict]: cursor = db.cursor() cursor.execute('SELECT * FROM chain_meta WHERE id = %s', [chain_id]) chain_record = cursor.fetchone() if chain_record is None: return None cursor.execute('SELECT * FROM scores WHERE batch_id = %s', [chain_record.batch_id]) scores = [{ k: v for k, v in row._asdict().items() if k != 'batch_id' and v is not None } for row in cursor.fetchall()] return {**chain_record._asdict(), 'scores': scores}
def timetables( route: RouteId, connection: Maybe[connection] = Nothing() ) -> Iterator[Either[str, Timetable]]: """Gets the timetables for a specific route from the database.""" with connection.or_else_lazy(default_connection) as conn: with conn.cursor() as cursor: cursor.execute( """ select timetable from route_timetables where route = %s """, [route.raw], ) for (timetable_id, ) in cursor.fetchall(): yield timetable(timetable_id, Just(conn))
def _cached_fetchall(self, query, max_age): cache = self.connection.postgres.cache entry = cache.lookup(query, max_age) if entry: return entry with cache.get_lock(query): # Check that an entry hasn't been inserted after our first lookup # but before we obtained the lock. entry = cache.lookup(query, max_age) if entry: return entry # Okay, send the query to the database and cache the result. self.run(query) rows = TupleCursor.fetchall(self) entry = CacheEntry(query, max_age, self.description, rows) cache[query] = entry return entry
def safe_insert(self, table, data, return_insert=False, commit=False): """ Insert row as dictionary Parameters ---------- table: str SQL table data: dict dictionary representing sql row to insert, dict keys should be the same as the sql table's columns return_insert: bool return the inserted row commit: bool commit insert to database Returns ------- list of dict or tuple a list of dictionary or tuple representing the inserted rows. """ rv = None cursor = self.cursor() columns = self.get_columns(cursor, table) filtered_data = {k: v for k, v in data.items() if k in columns} if return_insert: insert_stmt = f"""INSERT INTO {table} (%s) VALUES %s RETURNING %s;""" sub = ( AsIs(",".join(filtered_data.keys())), tuple(filtered_data.values()), AsIs(",".join(columns)), ) else: insert_stmt = f"""INSERT INTO {table} (%s) VALUES %s;""" sub = (AsIs(",".join(filtered_data.keys())), tuple(filtered_data.values())) stmt = cursor.mogrify(insert_stmt, sub) cursor.execute(stmt) if return_insert: rv = self._format_row(cursor.fetchall(), columns) cursor.close() if commit: self.commit() return rv
def get_columns(self, cursor: cursor, table: str): """ Get column name for table Parameters ---------- cursor: psycopg2.extensions.cursor psycopg2 cursor table: str SQL table Returns ------- list a list of str representing the table columns. """ cursor.execute( f"""select column_name from information_schema.columns where table_name='{table}'""" ) return [a[0] for a in cursor.fetchall()]
def find_recipients() -> None: query = ''' SELECT zerver_subscription.recipient_id FROM zerver_subscription INNER JOIN zerver_recipient ON ( zerver_recipient.id = zerver_subscription.recipient_id ) WHERE ( zerver_subscription.user_profile_id = '%s' AND zerver_recipient.type = 2 AND (NOT zerver_subscription.active) ) ''' cursor.execute(query, [user_profile.id]) rows = cursor.fetchall() for row in rows: recipient_ids.append(row[0]) logger.info(str(recipient_ids))
def find_recipients() -> None: query = ''' SELECT zerver_subscription.recipient_id FROM zerver_subscription INNER JOIN zerver_recipient ON ( zerver_recipient.id = zerver_subscription.recipient_id ) WHERE ( zerver_subscription.user_profile_id = '%s' AND zerver_recipient.type = 2 AND (NOT zerver_subscription.active) ) ''' cursor.execute(query, [user_profile.id]) rows = cursor.fetchall() for row in rows: recipient_ids.append(row[0]) logger.info(str(recipient_ids))
def find_non_muted_recipients() -> None: query = SQL(''' SELECT zerver_subscription.recipient_id FROM zerver_subscription INNER JOIN zerver_recipient ON ( zerver_recipient.id = zerver_subscription.recipient_id ) WHERE ( zerver_subscription.user_profile_id = %(user_profile_id)s AND zerver_recipient.type = 2 AND (NOT zerver_subscription.is_muted) AND zerver_subscription.active ) ''') cursor.execute(query, {"user_profile_id": user_profile.id}) rows = cursor.fetchall() for row in rows: recipient_ids.append(row[0]) logger.info(str(recipient_ids))
def select_query(self, columns: str, table: str, condition=""): """ Database select query 'Select [columns] from [table] [condition]' Parameters ---------- columns: list columns selected in SQL statement table: list SQL table condition: str condition clause at the end of SQL statement (i.e WHERE, ORDER) Returns ------- list of dict or tuple a list of dictionary or tuple representing the rows. """ cursor = self.cursor() query, columns = self._select_stmt(cursor, columns, table, condition) cursor.execute(query) return self._format_row(cursor.fetchall(), columns)
def find_non_muted_recipients(): # type: () -> None query = ''' SELECT zerver_subscription.recipient_id FROM zerver_subscription INNER JOIN zerver_recipient ON ( zerver_recipient.id = zerver_subscription.recipient_id ) WHERE ( zerver_subscription.user_profile_id = '%s' AND zerver_recipient.type = 2 AND zerver_subscription.in_home_view AND zerver_subscription.active ) ''' cursor.execute(query, [user_profile.id]) rows = cursor.fetchall() for row in rows: recipient_ids.append(row[0]) print(recipient_ids)
def execute_sql_string(self, plpgsqlstatement: str, **params: Any) -> List[Tuple[Any, ...]]: """ Execute PL/PGSQL string. For parametrized SQL queries please consider psycopg2 guide on the subject: http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries *Args:*\n _plpgsqlstatement_ - PL/PGSQL string;\n _params_ - PL/PGSQL string parameters;\n *Raises:*\n PostgreSQL error in form of psycopg2 exception. *Returns:*\n PL/PGSQL string execution result. *Example:*\n | @{query}= | Execute Sql String | SELECT CURRENT_DATE, CURRENT_DATE+1 | | Set Test Variable | ${sys_date} | ${query[0][0]} | | Set Test Variable | ${next_date} | ${query[0][1]} | | @{query}= | Execute Sql String | SELECT CURRENT_DATE, CURRENT_DATE+%(d)s | d=1 | | Set Test Variable | ${sys_date} | ${query[0][0]} | | Set Test Variable | ${next_date} | ${query[0][1]} | """ cursor = None try: cursor = self.connection.cursor() self._execute_sql(cursor, plpgsqlstatement, params) query_result = cursor.fetchall() self.result_logger(query_result) return query_result finally: if cursor: self.connection.rollback()
def dictfetchall(self): res = [] rows = _2cursor.fetchall(self) for row in rows: res.append(self.__build_dict(row)) return res
def fetchall(self): ts = _cursor.fetchall(self) nt = self.Record if nt is None: nt = self.Record = self._make_nt() return [nt(*t) for t in ts]
def fetchall(self): if self._query_executed: self._build_index() return _cursor.fetchall(self)