def show(id: int, authorization=Header(None)): if authorization is None: return Response(status_code=401) request_cost = 1 with LazyItgs() as itgs: user_id, _, perms = users.helper.get_permissions_from_header( itgs, authorization, (helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM, helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM, *ratelimit_helper.RATELIMIT_PERMISSIONS)) if not ratelimit_helper.check_ratelimit(itgs, user_id, perms, request_cost): return Response(status_code=429, headers={'x-request-cost': str(request_cost)}) if user_id is None: return Response(status_code=403, headers={'x-request-cost': str(request_cost)}) can_view_others_auth_methods = helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM in perms can_view_deleted_auth_methods = helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM in perms auth_methods = Table('password_authentications') query = (Query.from_(auth_methods).select( auth_methods.human, auth_methods.deleted).where(auth_methods.id == Parameter('%s'))) args = [id] if not can_view_others_auth_methods: query = query.where(auth_methods.user_id == Parameter('%s')) args.append(user_id) if not can_view_deleted_auth_methods: query = query.where(auth_methods.deleted.eq(False)) itgs.read_cursor.execute(query.get_sql(), args) row = itgs.read_cursor.fetchone() if row is None: return Response(status_code=404, headers={'x-request-cost': str(request_cost)}) (main, deleted) = row authtokens = Table('authtokens') itgs.read_cursor.execute( Query.from_(authtokens).select(Count( Star())).where(authtokens.expires_at < Now()).where( authtokens.source_type == Parameter('%s')).where( authtokens.source_id == Parameter('%s')).get_sql(), ('password_authentication', id)) (active_grants, ) = itgs.read_cursor.fetchone() return JSONResponse(status_code=200, content=models.AuthMethod( main=main, deleted=deleted, active_grants=active_grants).dict(), headers={'x-request-cost': str(request_cost)})
def init(self): self.meta = self.model_class._meta self.pika_table = Table(self.meta.table) column_names = [] columns = [] self.columns = columns self.column_names = column_names for name, field in self.meta.fields_map.items(): column_names.append(name) columns.append(field) self.filters = PikaTableFilters(self.pika_table, self.meta.filters) self.insert_all_sql = str( PostgreSQLQuery.into( self.pika_table).columns(*column_names).insert( *[self.parameter(i) for i in range(len(column_names))]).get_sql()) self.delete_sql = str( PostgreSQLQuery.from_(self.pika_table).where( self.pika_table[self.meta.db_pk_field] == self.parameter( 0)).delete().get_sql()) self.delete_table_sql = str( PostgreSQLQuery.from_(self.pika_table).delete().get_sql()) self.drop_table_sql = str(f"DROP TABLE IF EXISTS {self.meta.table};") self.update_cache = {}
def test_create(self): with helper.clear_tables(self.conn, self.cursor, ['responses', 'response_histories']),\ helper.user_with_token(self.conn, self.cursor, ['responses']) as (user_id, token): r = requests.post(f'{HOST}/responses', headers={'authorization': f'bearer {token}'}, json={ 'name': 'foobar', 'body': 'my body', 'desc': 'my desc' }) r.raise_for_status() self.assertEqual(r.status_code, 200) responses = Table('responses') self.cursor.execute( Query.from_(responses).select( responses.id, responses.response_body, responses.description).where( responses.name == Parameter('%s')).get_sql(), ('foobar', )) row = self.cursor.fetchone() self.assertIsNotNone(row) (respid, body, desc) = row self.assertEqual(body, 'my body') self.assertEqual(desc, 'my desc') resp_hists = Table('response_histories') self.cursor.execute( Query.from_(resp_hists).select(1).where( resp_hists.response_id == Parameter('%s')).limit( 1).get_sql(), (respid, )) row = self.cursor.fetchone() self.assertIsNotNone(row)
def execute_get_missing_initial_alerts(itgs): endpoint_users = Table('endpoint_users') endpoint_alerts = Table('endpoint_alerts') users = Table('users') usage_after_filters = Table('usage_after_filters') query = (Query.with_( Query.from_(endpoint_users).where( Not( Exists( Query.from_(endpoint_alerts).where( endpoint_alerts.endpoint_id == endpoint_users.endpoint_id).where( endpoint_alerts.user_id == endpoint_users.user_id)) )).select(endpoint_users.endpoint_id.as_('endpoint_id'), endpoint_users.user_id.as_('user_id'), Min(endpoint_users.created_at).as_('first_usage'), Max(endpoint_users.created_at).as_('last_usage'), Count(endpoint_users.id).as_('count_usage')).groupby( endpoint_users.endpoint_id, endpoint_users.user_id), 'usage_after_filters').from_(usage_after_filters).join(users).on( users.id == usage_after_filters.user_id).select( usage_after_filters.user_id, users.username, usage_after_filters.endpoint_id, usage_after_filters.first_usage, usage_after_filters.last_usage, usage_after_filters.count_usage).orderby( usage_after_filters.user_id)) sql = query.get_sql() itgs.read_cursor.execute(sql)
def delete_all_sessions(id: int, authorization=Header(None)): if authorization is None: return Response(status_code=401) request_cost = 5 with LazyItgs(no_read_only=True) as itgs: user_id, _, perms = users.helper.get_permissions_from_header( itgs, authorization, (helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM, helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM, helper.CAN_MODIFY_OTHERS_AUTHENTICATION_METHODS_PERM, *ratelimit_helper.RATELIMIT_PERMISSIONS)) if not ratelimit_helper.check_ratelimit(itgs, user_id, perms, request_cost): return Response(status_code=429, headers={'x-request-cost': str(request_cost)}) if user_id is None: return Response(status_code=403, headers={'x-request-cost': str(request_cost)}) can_view_others_auth_methods = helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM in perms can_view_deleted_auth_methods = helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM in perms can_modify_others_auth_methods = ( helper.CAN_MODIFY_OTHERS_AUTHENTICATION_METHODS_PERM in perms) auth_methods = Table('password_authentications') itgs.read_cursor.execute( Query.from_(auth_methods).select( auth_methods.deleted, auth_methods.user_id).where( auth_methods.id == Parameter('%s')).get_sql(), (id, )) row = itgs.read_cursor.fetchone() if row is None: return Response(status_code=404, headers={'x-request-cost': str(request_cost)}) (deleted, auth_method_user_id) = row if deleted and not can_view_deleted_auth_methods: return Response(status_code=404, headers={'x-request-cost': str(request_cost)}) if auth_method_user_id != user_id and not can_view_others_auth_methods: return Response(status_code=404, headers={'x-request-cost': str(request_cost)}) if auth_method_user_id != user_id and not can_modify_others_auth_methods: return Response(status_code=403, headers={'x-request-cost': str(request_cost)}) authtokens = Table('authtokens') itgs.write_cursor.execute( Query.from_(authtokens).delete().where( authtokens.source_type == Parameter('%s')).where( authtokens.source_id == Parameter('%s')).get_sql(), ('password_authentication', id)) itgs.write_conn.commit() return Response(status_code=200, headers={'x-request-cost': str(request_cost)})
def update_response(name: str, change: models.ResponseEditArgs, authorization: str = Header(None)): if len(change.edit_reason) < 5: return JSONResponse(status_code=422, content={ 'detail': { 'loc': ['body', 'edit_reason'] }, 'msg': 'minimum 5 characters', 'type': 'too_short' }) with LazyItgs(no_read_only=True) as itgs: authed, user_id = users_helper.check_permissions_from_header( itgs, authorization, 'responses') if not authed: return Response(status_code=403) users = Table('users') itgs.write_cursor.execute( Query.from_(users).select( users.id).where(users.id == Parameter('%s')).get_sql() + ' FOR SHARE', (user_id, )) row = itgs.write_cursor.fetchone() if row is None: itgs.write_conn.rollback() return Response(status_code=403) responses = Table('responses') itgs.write_cursor.execute( Query.from_(responses).select( responses.id, responses.response_body, responses.description).where( responses.name == Parameter('%s')).get_sql() + ' FOR UPDATE', (name, )) row = itgs.write_cursor.fetchone() if row is None: itgs.write_conn.rollback() return Response(status_code=404) (resp_id, old_body, old_desc) = row resp_hists = Table('response_histories') itgs.write_cursor.execute( Query.into(resp_hists).columns( resp_hists.response_id, resp_hists.user_id, resp_hists.old_raw, resp_hists.new_raw, resp_hists.reason, resp_hists.old_desc, resp_hists.new_desc).insert( *[Parameter('%s') for _ in range(7)]).get_sql(), (resp_id, user_id, old_body, change.body, change.edit_reason, old_desc, change.desc)) itgs.write_cursor.execute( Query.update(responses).set( responses.response_body, Parameter('%s')).set( responses.description, Parameter('%s')).set( responses.updated_at, ppfns.Now()).where( responses.id == Parameter('%s')).get_sql(), (change.body, change.desc, resp_id)) itgs.write_conn.commit() return Response(status_code=200)
async def update_links_from_html(ob, *contents): storage = get_storage() if storage is None: return if hasattr(ob, "context"): uuid = ob.context.uuid else: uuid = ob.uuid links = set() for content in contents: dom = html.fromstring(content) for node in dom.xpath("//a") + dom.xpath("//img"): url = node.get("href", node.get("src", "")) if "resolveuid/" not in url: continue _, _, uid = url.partition("resolveuid/") uid = uid.split("/")[0].split("?")[0] links.add(_safe_uid(uid)) if len(links) == 0: # delete existing if there are any async with storage.pool.acquire() as conn: await conn.execute( str( Query.from_(links_table).where( links_table.source_id == _safe_uid(uuid)).delete())) return async with storage.pool.acquire() as conn: # make sure to filter out bad links existing_oids = set() query = str( Query.from_(objects_table).select("zoid").where( objects_table.zoid == "$1") # noqa ).replace("'$1'", "any($1)") results = await conn.fetch(query, list(links)) for record in results: existing_oids.add(record["zoid"]) # first delete all existing ones await conn.execute( str( Query.from_(links_table).where( links_table.source_id == _safe_uid(uuid)).delete())) # then, readd links = links & existing_oids if len(links) > 0: query = Query.into(links_table).columns("source_id", "target_id") for link in links: query = query.insert(_safe_uid(str(uuid)), _safe_uid(link)) await conn.execute(str(query))
async def remove_user(user_id=None, username=None): if user_id is not None: query = Query.from_(users_table).where(users_table.id == user_id) else: query = Query.from_(users_table).where( users_table.username == username) db = await get_db() async with db.acquire() as conn: await conn.execute(str(query.delete())) await notify(UserRemovedEvent(user_id, username))
def count_sql(self, estimate=True): if estimate: stats_table = Table("pg_stat_user_tables") return Query.from_(stats_table).select( stats_table.n_live_tup ).where(stats_table.relname == self.initial_table_name).where( stats_table.schemaname == self.initial_schema_name).get_sql() self.current_query = Query.from_(self.current_dataset) return self.current_query.select( pypika_fn.Count(self.current_dataset.star)).get_sql()
def revoke_permissions(itgs: 'LazyItgs', user_id: int, reason: str, passwd_auth_id: int, perm_ids_to_revoke: list, commit=False): """Revokes all the given permissions from the given password authentication id. The password authentication must have all of the permissions. This will record the event in the audit table. This will log the user out. Arguments: - `itgs (LazyItgs)`: The integrations to connect to third part services on - `user_id (int)`: The user the password authentication belong sto. - `reason (str)`: The reason for revoking permissions - `passwd_auth_id (int)`: The password authentication to revoke permissions on. - `perm_ids_to_revoke (list[int])`: The ids of the permissions to revoke - `commit (bool)`: True to commit the transaction immediately, false not to. """ passwd_auth_perms = Table('password_auth_permissions') itgs.write_cursor.execute( Query.from_(passwd_auth_perms).delete().where( passwd_auth_perms.password_authentication_id == Parameter( '%s')).where( passwd_auth_perms.permission_id.isin( tuple(Parameter('%s') for _ in perm_ids_to_revoke))).get_sql(), (passwd_auth_id, *perm_ids_to_revoke)) passwd_auth_events = Table('password_authentication_events') loansbot_user_id = get_loansbot_user_id(itgs) args = [] for perm_id in perm_ids_to_revoke: args.append(passwd_auth_id) args.append('permission-revoked') args.append(reason) args.append(loansbot_user_id) args.append(perm_id) itgs.write_cursor.execute( Query.into(passwd_auth_events).columns( passwd_auth_events.password_authentication_id, passwd_auth_events.type, passwd_auth_events.reason, passwd_auth_events.user_id, passwd_auth_events.permission_id).insert(*(tuple( Parameter('%s') for _ in range(5)) for _ in perm_ids_to_revoke)).get_sql(), args) authtokens = Table('authtokens') itgs.write_cursor.execute( Query.from_(authtokens).delete().where( authtokens.user_id == Parameter('%s')).get_sql(), (user_id, )) if commit: itgs.write_conn.commit()
def create_server_trust_comment(itgs, comment, user_id=None, username=None): """Create an autogenerated comment on the given users trustworthiness, where the user is specified either via id or username. This does not commit the comment. This will create the user if specified via username and they do not exist. Arguments: - `itgs (LazyIntegrations)`: The lazy integrations to use - `comment (str)`: The comment to post - `user_id (int, None)`: The id of the user to post the comment on or None if the user is specified via the username. - `username (str, None)`: The username of the user to post the comment on or None if the user is specified via the user id. """ assert (user_id is None) != (username is None), f'user_id={user_id}, username={username}' usrs = Table('users') if user_id is None: itgs.read_cursor.execute( Query.from_(usrs).select(usrs.id) .where(usrs.username == Parameter('%s')) .get_sql(), (username.lower(),) ) row = itgs.read_cursor.fetchone() if row is None: user_id = users.helper.create_new_user(itgs, username.lower()) else: (user_id,) = row itgs.read_cursor.execute( Query.from_(usrs).select(usrs.id) .where(usrs.username == Parameter('%s')) .get_sql(), ('loansbot',) ) row = itgs.read_cursor.fetchone() if row is None: loansbot_user_id = users.helper.create_new_user(itgs, 'loansbot') else: (loansbot_user_id,) = row trust_comments = Table('trust_comments') itgs.write_cursor.execute( Query.into(trust_comments).columns( trust_comments.author_id, trust_comments.target_id, trust_comments.comment ).insert(*[Parameter('%s') for _ in range(3)]) .get_sql(), (loansbot_user_id, user_id, comment) )
def load_game(self, chat_id: int) -> Optional[GameState]: if not self.use_db: return None games, players = Tables('games', 'players') logging.info(f"Loading game {chat_id} from DB") load_game_query = PostgreSQLQuery.from_(games) \ .select(games.player1, games.player2, games.size_x, games.size_y, games.state, games.turn_color, games.turn_player, games.last_stone, games.last_capt_stone, games.player_passed) \ .where(games.chat_id == chat_id) self._cur.execute(load_game_query.get_sql()) rows = self._cur.fetchall() if not rows: logging.info(f"Game {chat_id} not fround in DB") return None row = rows[0] get_player1_query = PostgreSQLQuery.from_(players) \ .select(players.name) \ .where(players.id == row[0]) self._cur.execute(get_player1_query.get_sql()) player1_name = self._cur.fetchall()[0][0] get_player2_query = PostgreSQLQuery.from_(players) \ .select(players.name) \ .where(players.id == row[1]) self._cur.execute(get_player2_query.get_sql()) player2_name = self._cur.fetchall()[0][0] return GameState( player_ids=(row[0], row[1]), size_x=row[2], size_y=row[3], board=row[4], turn_color=row[5], turn_player=row[6], last_stone=row[7], last_capt_stone=row[8], player_passed=row[9], player1_name=player1_name, player2_name=player2_name )
def get_id_device_elements(vl_url: str, filters: List[str], inhibit_filters: List[str]) -> List: re_user_filters = [] re_inhibit_user_filter = [] result_filter: List[str] result_inhibit_filter: List[str] gg = vl_url.replace('/', '\\\\') for inhibit in inhibit_filters: result_inhibit_filter = inhibit.split('\r\n') re_inhibit_user_filter = [w.replace('\\', '\\\\') for w in result_inhibit_filter] re_inhibit_user_filter = [w.replace('*', "%") for w in re_inhibit_user_filter] for filter_user in filters: result_filter = filter_user.split('\r\n') re_user_filters = [w.replace('\\', '\\\\') for w in result_filter] re_user_filters = [w.replace('*', "%") for w in re_user_filters] logging.debug('Получение таблицы балансов') p = Table('askue_rs_point', alias='p') q = (PostgreSQLQuery.from_(p) .select(p.id, p.object_name) .where(p.object_name == gg) ) filter_sql = db_work.get_query_by_user_filter(q, result_filter, result_inhibit_filter) sql = filter_sql.get_sql() re_user_filters.extend(re_inhibit_user_filter) result_elements = [] logging.debug(f'SQL: {sql}') try: result = engine.execute(sql, re_user_filters) for row in result: result_elements.append(row[0]) except Exception as e: logging.error("Error is ", e) logging.debug("get_id_device_elements() OK...") return result_elements
def get_result_day_by_day_elements(list_id: int, start: str, end: str) -> List: logging.debug('Получение таблицы балансов') start_utc = datetime.strptime(start, '%Y-%m-%d').astimezone(pytz.UTC).replace(tzinfo=None) end_utc = datetime.strptime(end, '%Y-%m-%d').astimezone(pytz.UTC).replace(tzinfo=None) p = Table('calc_balance', alias='p') try: q = (PostgreSQLQuery.from_(p) .select(p.start_period, p.time_start_write) .where( (p.time_start_write[start_utc:end_utc]) & (p.id_tu == list_id[0]) ).orderby(p.time_start_write, order=Order.asc)) sql = q.get_sql() except Exception as e: logging.error("Error is ", e) result_elements = [] logging.debug(f'SQL: {sql}') try: result = engine.execute(sql) for row in result: result_elements.append(row) except Exception as e: logging.error("Error is ", e) logging.debug("get_result_day_by_day_elements() OK...") return result_elements
def test_delete_returning(self): q1 = PostgreSQLQuery.from_(self.table_abc).where( self.table_abc.foo == self.table_abc.bar).delete().returning( self.table_abc.id) self.assertEqual('DELETE FROM "abc" WHERE "foo"="bar" RETURNING id', str(q1))
def show(name: str, authorization: str = Header(None)): with LazyItgs() as itgs: if not users_helper.check_permissions_from_header( itgs, authorization, 'responses')[0]: return Response(status_code=403) responses = Table('responses') itgs.read_cursor.execute( Query.from_(responses).select( responses.id, responses.name, responses.response_body, responses.description, responses.created_at, responses.updated_at).where( responses.name == Parameter('%s')).get_sql(), (name, )) row = itgs.read_cursor.fetchone() if row is None: return Response(status_code=404) return JSONResponse(status_code=200, content=models.ResponseShow( id=row[0], name=row[1], body=row[2], desc=row[3], created_at=int(row[4].timestamp()), updated_at=int(row[5].timestamp())).dict(), headers={ 'Cache-Control': 'public, max-age=86400, stale-if-error=2629746' })
def get_elements_for_light(list_id: List, start: str, end: str) -> List: logging.debug('Получение данных для таблицы УО') start_utc = datetime.strptime(str(start), '%Y-%m-%d').astimezone( pytz.UTC).replace(tzinfo=None) end_utc = datetime.strptime(str(end), '%Y-%m-%d').astimezone( pytz.UTC).replace(tzinfo=None) p = Table('calc_balance', alias='p') pp = Table('calc_balance', alias='pp') try: q = (PostgreSQLQuery.from_(p).join(pp).on(p.id_tu == pp.id_tu).select( p.id_tu, p.dtp, p.locality, p.name_of_accounting_point, p.str_ra, p.pxx, p.loss_xx, p.ktt, p.head_of_counter, p.start_period, pp.start_period.as_("end_period"), p.q_slim, p.time_start_write, p.country, p.driver).where((p.time_start_write == start_utc) & (pp.time_start_write == end_utc) & (p.id_tu.isin(list_id)))) sql = q.get_sql() except Exception as e: logging.error("Error is ", e) result_elements = [] logging.debug(f'SQL: {sql}') try: result = engine.execute(sql) for row in result: result_elements.append(row) except Exception as e: logging.error("Error is ", e) logging.debug("get_elements_for_light() OK...") return result_elements
def check_if_pkey_exists(cursor, tblname): pg_indices = Table('pg_index') cursor.execute( Query.from_(pg_indices).select(1).where( pg_indices.indrelid == Cast(Parameter('%s'), 'regclass')).where( pg_indices.indisprimary).get_sql(), (tblname, )) return cursor.fetchone() is not None
def _get_query_sql(self, queryset): values = [] table = self.pika_table query = PostgreSQLQuery.from_(table).select(*self.column_names) i = 0 criterion, where_values = self._expressions_to_criterion( queryset._expressions, i) query = query.where(criterion) values.extend(where_values) i += len(where_values) if queryset._distinct: query = query.distinct() if queryset._limit: query = query.limit(queryset._limit) if queryset._orderings: for field_name, order in queryset._orderings: query = query.orderby(getattr(table, field_name), order=order) if queryset._offset: query = query.offset(queryset._offset) sql = str(query.get_sql()) return sql, values
def test_existing(self): resps = Table('responses') with LazyIntegrations() as itgs: itgs.write_cursor.execute( Query.into(resps).columns( resps.name, resps.response_body, resps.description).insert( *[Parameter('%s') for _ in range(3)]).returning(resps.id).get_sql(), ('my_response', 'I like to {foo} the {bar}', 'Testing desc')) (respid, ) = itgs.write_cursor.fetchone() try: itgs.write_conn.commit() res: str = responses.get_response(itgs, 'my_response', foo='open', bar='door') self.assertEqual(res, 'I like to open the door') res: str = responses.get_response(itgs, 'my_response', foo='eat', buzz='bear') self.assertIsInstance(res, str) self.assertTrue(res.startswith('I like to eat the '), res) # it's not important how we choose to format the error, but it # needs the missing key or debugging will be a pain self.assertIn('bar', res) finally: itgs.write_conn.rollback() itgs.write_cursor.execute( Query.from_(resps).delete().where( resps.id == Parameter('%s')).get_sql(), (respid, )) itgs.write_conn.commit()
def index_permissions(id: int, authorization=Header(None)): if authorization is None: return Response(status_code=401) request_cost = 50 with LazyItgs() as itgs: user_id, _, perms = users.helper.get_permissions_from_header( itgs, authorization, (helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM, helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM, *ratelimit_helper.RATELIMIT_PERMISSIONS)) if not ratelimit_helper.check_ratelimit(itgs, user_id, perms, request_cost): return Response(status_code=429, headers={'x-request-cost': str(request_cost)}) if user_id is None: return Response(status_code=403, headers={'x-request-cost': str(request_cost)}) can_view_others_auth_methods = helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM in perms can_view_deleted_auth_methods = helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM in perms auth_methods = Table('password_authentications') auth_perms = Table('password_auth_permissions') permissions = Table('permissions') query = (Query.from_(auth_methods).select( permissions.name).join(auth_perms).on( auth_perms.password_authentication_id == auth_methods.id).join( permissions).on( permissions.id == auth_perms.permission_id).where( auth_methods.deleted.eq(False)).where( auth_methods.id == Parameter('%s'))) args = [id] if not can_view_others_auth_methods: query = query.where(auth_methods.user_id == Parameter('%s')) args.append(user_id) if not can_view_deleted_auth_methods: query = query.where(auth_methods.deleted.eq(False)) itgs.read_cursor.execute(query.get_sql(), args) result = [] row = itgs.read_cursor.fetchone() while row is not None: result.append(row[0]) row = itgs.read_cursor.fetchone() return JSONResponse( status_code=200, headers={ 'x-request-cost': str(request_cost), 'Cache-Control': 'private, max-age=60, stale-while-revalidate=540' }, content=models.AuthMethodPermissions(granted=result).dict())
def applications(authorization: str = Header(None)): """Returns application ids mapped to the corresponding application names.""" with LazyItgs() as itgs: if not users.helper.check_permissions_from_header( itgs, authorization, 'logs')[0]: return Response(status_code=403) apps = Table('log_applications') itgs.read_cursor.execute( Query.from_(apps).select(apps.id, apps.name).get_sql()) result = {} while True: row = itgs.read_cursor.fetchone() if row is None: break result[row[0]] = models.LogApplicationResponse(name=row[1]) return JSONResponse( status_code=200, content=models.LogApplicationsResponse(applications=result).dict(), headers={ 'Cache-Control': 'public, max-age=86400, stale-if-error=2419200' })
def calculate_etag(itgs, loan_id) -> str: """Calculates a valid etag for the loan with the given id. If no such loan exists this returns None. """ loans = Table('loans') event_tables = [ Table(t) for t in ['loan_admin_events', 'loan_repayment_events', 'loan_unpaid_events'] ] q = (Query.from_(loans).select( Greatest(loans.created_at, loans.unpaid_at, loans.deleted_at, *[tbl.created_at for tbl in event_tables]))) for tbl in event_tables: q = q.left_join(tbl).on(loans.id == tbl.loan_id) q = q.where(loans.id == Parameter('%s')) itgs.read_cursor.execute(q.get_sql(), (loan_id, )) row = itgs.read_cursor.fetchone() if row is None: return None (updated_at, ) = row raw_str = f'{loan_id}-{updated_at.timestamp()}' return hashlib.sha256(raw_str.encode('ASCII')).hexdigest()
def get_result_device_day_elements(list_id: List, start: str) -> float: logging.debug('Получение таблицы балансов') start_utc = datetime.strptime(start, '%Y-%m-%d').astimezone(pytz.UTC).replace(tzinfo=None) p = Table('calc_balance', alias='p') try: q = (PostgreSQLQuery.from_(p) .select(p.start_period) .where( (p.time_start_write == start_utc) & (p.id_tu.isin(list_id))) .orderby(p.dtp, order=Order.asc) ) sql = q.get_sql() except Exception as e: logging.error("Error is ", e) result_elements = 0 logging.debug(f'SQL: {sql}') try: result = engine.execute(sql) for row in result: result_elements = row[0] except Exception as e: logging.error("Error is ", e) logging.debug("get_result_device_day_elements() OK...") return result_elements
def get_points_for_uo(filters: List[str], inhibit_filters: List[str]) -> List: re_user_filters = [] re_inhibit_user_filter = [] result_filter: List[str] result_inhibit_filter: List[str] for inhibit in inhibit_filters: result_inhibit_filter = inhibit.split('\r\n') re_inhibit_user_filter = [ w.replace('\\', '\\\\') for w in result_inhibit_filter ] re_inhibit_user_filter = [ w.replace('*', "%") for w in re_inhibit_user_filter ] for filter_user in filters: result_filter = filter_user.split('\r\n') re_user_filters = [w.replace('\\', '\\\\') for w in result_filter] re_user_filters = [w.replace('*', "%") for w in re_user_filters] logging.debug('Получение подстанций, присоединений для таблицы балансов') p = Table('askue_rs_point', alias='p') q = (PostgreSQLQuery.from_(p).select(p.object_name)) filter_sql = db_work.get_query_by_user_filter(q, result_filter, result_inhibit_filter) sql = filter_sql.get_sql() result_elements = [] re_user_filters.extend(re_inhibit_user_filter) logging.debug(f'SQL: {sql}') try: result = engine.execute(sql, re_user_filters) for row in result: if row[0].rfind('УО') != -1: result_elements.append(row) except Exception as e: logging.error("Error is ", e) logging.debug("get_points_for_uo() OK...") return result_elements
def main(): conn = setup_connection() cursor = conn.cursor() tbls = Schema('information_schema').tables cursor.execute( Query.from_(tbls).where(tbls.table_type == 'BASE TABLE').where( tbls.table_schema == 'public').select(tbls.table_name).get_sql(), ) rows = cursor.fetchall() rows = map(lambda r: r[0], rows) cursor.close() conn.close() cfg = settings.load_settings() db_host = cfg['DATABASE_HOST'] db_port = int(cfg['DATABASE_PORT']) db_user = cfg['DATABASE_USER'] db_pass = cfg['DATABASE_PASSWORD'] db_name = cfg['DATABASE_DBNAME'] old_pg_pass = os.environ.get('PGPASSWORD') os.environ['PGPASSWORD'] = db_pass for tbl in rows: print('=' * 50) print(f'DESCRIBE {tbl}') os.system(f'psql -d {db_name} -h {db_host} -p {db_port} ' f'-U {db_user} -c "\\d+ {tbl}"') print() if old_pg_pass is not None: os.environ['PGPASSWORD'] = old_pg_pass else: del os.environ['PGPASSWORD']
def test_postgres_query_uses_double_quote_chars(self): q = PostgreSQLQuery.from_(self.table_abc).select( self.table_abc.foo).groupby( self.table_abc.foo).having(self.table_abc.buz == 'fiz') self.assertEqual( "SELECT \"foo\" FROM \"abc\" GROUP BY \"foo\" HAVING \"buz\"='fiz'", str(q))
def test_postgres_query_uses_double_quote_chars(self): q = (PostgreSQLQuery.from_(self.table_abc).select( self.table_abc.foo).groupby( self.table_abc.foo).having(self.table_abc.buz == "fiz")) self.assertEqual( 'SELECT "foo" FROM "abc" GROUP BY "foo" HAVING "buz"=\'fiz\'', str(q))
def __init__(self, model): super().__init__() self.fields = model._meta.db_fields self.model = model if not hasattr(model._meta.db, 'query_class'): # use PostgreSQLQuery if model doesn't have query_class self.query = Query.from_(model._meta.table) else: self.query = model._meta.db.query_class.from_(model._meta.table) self._prefetch_map = {} # type: Dict[str, Set[str]] self._prefetch_queries = {} # type: Dict[str, QuerySet] self._single = False # type: bool self._get = False # type: bool self._count = False # type: bool self._db = None # type: Optional[BaseDBAsyncClient] self._limit = None # type: Optional[int] self._offset = None # type: Optional[int] self._filter_kwargs = {} # type: Dict[str, Any] self._orderings = [] # type: List[str] self._q_objects_for_resolve = [] # type: List[Q] self._distinct = False # type: bool self._annotations = {} # type: Dict[str, Aggregate] self._having = {} # type: Dict[str, Any] self._available_custom_filters = {} # type: Dict[str, dict]
def store_letter_message(itgs: 'LazyItgs', user_id: int, letter_name: str, commit=False): """This function is responsible for storing that we sent an onboarding letter message to the given user, where the subject and body were fetched as if by `lbshared.responses.get_letter_response` Arguments: - `itgs (LazyItgs)`: The integrations to use to connect to networked components. - `user_id (int)`: The id of the user who we sent the message to - `letter_name (str)`: The base part of the response for both the title and subject. The title response name is formed by appending `_title` and the subject is formed by appending `_body` - `commit (bool)`: True to commit the change immediately, false not to """ (body_name, title_name) = (f'{letter_name}_body', f'{letter_name}_title') responses = Table('responses') itgs.read_cursor.execute( Query.from_(responses) .select(responses.id, responses.name) .where(responses.name.isin((Parameter('%s'), Parameter('%s')))) .get_sql(), (body_name, title_name) ) rows = itgs.read_cursor.fetchall() if len(rows) != 2: raise Exception(f'expected 2 rows for letter base {letter_name}, got {len(rows)}') (body_id, title_id) = [r[0] for r in sorted(rows, key=lambda x: x[1])] store_letter_message_with_id_and_names( itgs, user_id, title_id, title_name, body_id, body_name, commit=commit )
def test_postgres_query_uses_double_quote_chars(self): q = PostgreSQLQuery.from_(self.table_abc).select( self.table_abc.foo ).groupby( self.table_abc.foo ).having( self.table_abc.buz == 'fiz' ) self.assertEqual("SELECT \"foo\" FROM \"abc\" GROUP BY \"foo\" HAVING \"buz\"='fiz'", str(q))
def test_delete_returning_str(self): q1 = PostgreSQLQuery.from_(self.table_abc).where( self.table_abc.foo == self.table_abc.bar ).delete().returning('id') self.assertEqual('DELETE FROM "abc" WHERE "foo"="bar" RETURNING id', str(q1))
def test_postgres_query_uses_double_quote_chars(self): q = PostgreSQLQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo) self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
def test_postgresql_query_uses_double_quote_chars(self): q = PostgreSQLQuery.from_('abc').select('foo', 'bar') self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q))