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 test_insert_returning_functions(self): query = PostgreSQLQuery.into(self.table_abc).insert(1).returning( Cast(self.table_abc.f1, "int")) self.assertEqual( 'INSERT INTO "abc" VALUES (1) RETURNING CAST("f1" AS INT)', str(query))
def inside_operation(a, b): path = "{{{}}}".format(selector.replace(LOOKUP_SEP, ',')) a = a.get_path_text_value(path) type_b = type(b) if type_b in AsyncpgFilter.TYPE_MAP: a = Cast(a, AsyncpgFilter.TYPE_MAP[type_b]) return opr(a, b)
async def createOrder(self, order: Order.CreateInfo) -> int: if len(order.products) == 0: raise Exception('No products') orderSql = Query.into(orders).columns( orders.address, orders.name, orders.surname, orders.user_id, orders.status, orders.shipping_price).insert( order.address, order.name, order.surname, order.userId, Cast('NEW', 'ORDER_STATUS'), order.shippingPrice).returning(orders.id) productsSql = Query.into(order_product_link).columns( order_product_link.order_id, order_product_link.product_id, order_product_link.amount) async with databaseClient.transaction() as t: orderId = (await t.query(orderSql.get_sql()))[0][0] for prodId, amount in order.products.items(): productsSql = productsSql.insert(orderId, prodId, amount) await t.execute(productsSql.get_sql()) return orderId
def insensitive_exact(field: Term, value: str) -> Criterion: return Upper(Cast(field, SqlTypes.VARCHAR)).eq(Upper(f"{value}"))
def ends_with(field: Term, value: str) -> Criterion: return Cast(field, SqlTypes.VARCHAR).like(f"%{value}")
def starts_with(field: Term, value: str) -> Criterion: return Cast(field, SqlTypes.VARCHAR).like(f"{value}%")
def contains(field: Term, value: str) -> Criterion: return Cast(field, SqlTypes.VARCHAR).like(f"%{value}%")
def insensitive_ends_with(field: Term, value: str) -> Criterion: return Upper(Cast(field, SqlTypes.VARCHAR)).like(Upper(f"%{value}"))
def parse_sources(self, config_name: str, sources: List[Dict]) -> List[Dict]: sql_sources = [] for source in sources: if source.get("raw", None) is not None: sql_sources.append(source["raw"]) else: # sql query builder table = Table(source["table"]["name"]).as_(source["table"].get( "alias", source["table"]["name"])) fields = [] for field in source["fields"]: function = field.get("function", None) if function is None: value = Field( field["name"].split(".")[1], table=Table(field["name"].split(".")[0]), ).as_(field.get("alias", field["name"])) if cast_is_required(field): value = Cast(value, field.get("cast", None))\ .as_(field.get("alias", field["name"])) fields.append(value) else: f = Field( field["name"].split(".")[1], table=Table(field["name"].split(".")[0]), ).as_(field.get("alias", field["name"])) func = SQL_FUNCTION_MAPPING[function](f).as_( field.get("alias", field["name"])).as_( field.get("alias", field["name"])) if cast_is_required(field): func = Cast(func, field.get("cast", None))\ .as_(field.get("alias", field["name"])) fields.append(func) query = Query.from_(table) # parse tables to join for join_table_config in source.get("join", []): join_table = Table(join_table_config["table"]["name"]).as_( join_table_config["table"].get( "alias", join_table_config["table"]["name"])) # parse fields to join the table on join_on_fields = [] for field in join_table_config["on"]: table_name, field_name = field.split(".") if table_name == join_table.get_table_name(): join_on_fields.append( Field(field_name, table=join_table)) elif table_name == table.get_table_name(): join_on_fields.append( Field(field_name, table=table)) else: raise ExportConfigError( f'ON field name "{field}" in configuration for "{config_name}" ' f'does not recognize table "{table_name}".') if join_table_config[ "cond"] in COMPARISON_OPERATORS_MAPPING.keys(): on_cond = COMPARISON_OPERATORS_MAPPING[ join_table_config["cond"]](join_on_fields[0], join_on_fields[1]) else: on_cond = SQL_SPATIAL_JOIN_MAPPING[ join_table_config["cond"]](join_on_fields[0], join_on_fields[1]) query = query.join( join_table, JoinType[ join_table_config["type"].lower()]).on(on_cond) # parse GROUP BY parameters for group_by_field in source.get("group_by", []): query = query.groupby(Field(group_by_field)) query = query.select(*fields) # add RAW statements provided by a user for raw_statement in [ source.get("filter", ""), source.get("having", ""), ]: if raw_statement: query = str(query) + " " + raw_statement # append SQL query string to the configuration sources sql_sources.append(str(query)) return sql_sources
def _mysql_json_is_null(left: Term, is_null: bool): if is_null: return operator.eq(left, Cast("null", "JSON")) else: return not_equal(left, Cast("null", "JSON"))
def insensitive_ends_with(field: Term, value: str) -> Criterion: return Like(Upper(Cast(field, SqlTypes.VARCHAR)), field.wrap_constant(Upper(f"%{escape_like(value)}")))
def ends_with(field: Term, value: str) -> Criterion: return Like(Cast(field, SqlTypes.VARCHAR), field.wrap_constant(f"%{escape_like(value)}"))
def read_account_points(self, rv: int, id_component_type: int) -> List[AccountPoint]: """ Выполняет чтение всех точек учета :return: массив точек учета """ a = Table('AccountPoint', alias='a') t = Table('Tag', alias='t') n = Table('Node', alias='n') n2 = Table('Node', alias='n2') n3 = Table('Node', alias='n3') n4 = Table('Node', alias='n4') p = Table('ObjPassport', alias='p') c = Table('ComponentType', alias='c') s = Table('DataString', alias='c') sub_query = ( Query.from_(a).join( t, how=JoinType.inner).on(a.Id == t.AccountId).join( n, how=JoinType.inner).on(a.DriverId == n.Id).join( c, how=JoinType.inner).on(n.IdComponentType == c.Id).join( n2, how=JoinType.inner).on(a.Id == n2.Id).join( n3, how=JoinType.inner). on((n2.IdOwn == n3.IdOwn) & (n3.BrowseName == 'Diagnostic')).join( n4, how=JoinType.inner).on((n3.Id == n4.IdOwn) & (n4.BrowseName == 'SerialNumber')). where((a.Del == self._FALSE_) & (t.TagCategory == 'CounterAplus') & (t.TagName == 'Day1') & (a.Rv > 0))) if self.__needRvCast: sub_query = sub_query.select(a.Id, a.DisplayName, t.IdTagDef, a.DriverId, c.Guid, Cast(a.Rv, 'BIGINT', 'Rv'), n3.BrowseName, n4.Id.as_('serial_num')) else: sub_query = sub_query.select(a.Id, a.DisplayName, t.IdTagDef, a.DriverId, c.Guid, a.Rv, n3.BrowseName, n4.Id.as_('serial_num')) ap = AliasedQuery("ap") query = (Query.select( ap.Id, ap.DisplayName, ap.IdTagDef, ap.Rv, p.PostIndex, p.FlatS, p.Flat, p.Region, p.Country, ap.serial_num, Case().when( ap.Guid == 'est.by:Bus.GranDrvClientImpl', 'СС-301').when( ap.Guid == 'est.by:Bus.Gran101DrvClientImpl', 'СС-101').when( ap.Guid == 'est.by:Bus.EmeraDrvClientImpl', 'СЕ-102').else_("UNKNOWN").as_('Driver')).with_( sub_query, "ap").from_(ap).join(n, how=JoinType.left). on((n.IdOwn == ap.Id) & (n.IdComponentType == id_component_type)).join( p, how=JoinType.left).on(n.Id == p.Id)) sql = query.get_sql() ret_val: List[AccountPoint] = [] try: self._logger.debug(f'SQL: {sql}') result = self._engine_main.execute(sql) for row in result: data = AccountPoint(row['Id'], row['DisplayName'], row['IdTagDef'], row['PostIndex'], row['FlatS'], row['Flat'], row['Region'], row['Country'], row['serial_num'], row['Driver'], row['Rv']) ret_val.append(data) except Exception as e: self._logger.error(e) return ret_val
def insensitive_contains(field: Term, value: str) -> Criterion: return Upper(Cast(field, SqlTypes.VARCHAR)).like(Upper(f"%{value}%"))
def convert_date(self, dt: datetime) -> Function: return Cast(dt, 'datetimeoffset')
def index_loans(request: Request, id: int = 0, after_time: int = 0, before_time: int = 0, borrower_id: int = 0, lender_id: int = 0, includes_user_id: int = 0, borrower_name: str = '', lender_name: str = '', includes_user_name: str = '', principal_cents: int = 0, principal_repayment_cents: int = -1, unpaid: int = -1, repaid: int = -1, format: int = 2, limit: int = 10): id = _zero_to_none(id) after_time = _zero_to_none(after_time) before_time = _zero_to_none(before_time) borrower_id = _zero_to_none(borrower_id) lender_id = _zero_to_none(lender_id) includes_user_id = _zero_to_none(includes_user_id) borrower_name = _blank_to_none(borrower_name) lender_name = _blank_to_none(lender_name) includes_user_name = _blank_to_none(includes_user_name) principal_cents = _zero_to_none(principal_cents) principal_repayment_cents = _neg1_to_none(principal_repayment_cents) unpaid = _neg1_to_none(unpaid) repaid = _neg1_to_none(repaid) limit = _zero_to_none(limit) attempt_request_cost = 5 headers = {'x-request-cost': str(attempt_request_cost)} with LazyItgs() as itgs: auth = find_bearer_token(request) user_id, _, perms = users.helper.get_permissions_from_header( itgs, auth, ratelimit_helper.RATELIMIT_PERMISSIONS) resp = try_handle_deprecated_call(itgs, request, SLUG, user_id=user_id) if resp is not None: return resp if not ratelimit_helper.check_ratelimit(itgs, user_id, perms, attempt_request_cost): return JSONResponse(content=RATELIMIT_RESPONSE.dict(), status_code=429, headers=headers) if limit is not None and (limit < 0 or limit >= 1000): return JSONResponse(content=PHPErrorResponse(errors=[ PHPError( error_type='INVALID_PARAMETER', error_message=( 'Limit must be 0 or a positive integer less than 1000' )) ]).dict(), status_code=400) if limit is None and user_id is None: headers[ 'x-limit-warning'] = 'unauthed requests limit=0 replaced with limit=100' limit = 100 if format not in (0, 1, 2, 3): return JSONResponse(content=PHPErrorResponse(errors=[ PHPError(error_type='INVALID_PARAMETER', error_message=('Format must be 0, 1, 2, or 3')) ]).dict(), status_code=400) loans = Table('loans') if limit is None: real_request_cost = 100 else: real_request_cost = min(100, limit) if format == 0: real_request_cost = math.ceil(math.log(real_request_cost + 1)) elif format < 3: # Cost needs to be greater than loans show real_request_cost = 25 + real_request_cost * 2 else: # We need to ensure the cost is greater than using the /users show # endpoint for getting usernames real_request_cost = 25 + math.ceil(real_request_cost * 4.1) headers['x-request-cost'] = str(attempt_request_cost + real_request_cost) if not ratelimit_helper.check_ratelimit(itgs, user_id, perms, real_request_cost): return JSONResponse(content=RATELIMIT_RESPONSE.dict(), status_code=429, headers=headers) moneys = Table('moneys') principals = moneys.as_('principals') principal_repayments = moneys.as_('principal_repayments') usrs = Table('users') lenders = usrs.as_('lenders') borrowers = usrs.as_('borrowers') query = (Query.from_(loans).where(loans.deleted_at.isnull()).orderby( loans.id, order=Order.desc)) params = [] joins = set() def _add_param(val): params.append(val) return Parameter(f'${len(params)}') def _ensure_principals(): nonlocal query if 'principals' in joins: return joins.add('principals') query = (query.join(principals).on( principals.id == loans.principal_id)) def _ensure_principal_repayments(): nonlocal query if 'principal_repayments' in joins: return joins.add('principal_repayments') query = (query.join(principal_repayments).on( principal_repayments.id == loans.principal_repayment_id)) def _ensure_lenders(): nonlocal query if 'lenders' in joins: return joins.add('lenders') query = (query.join(lenders).on(lenders.id == loans.lender_id)) def _ensure_borrowers(): nonlocal query if 'borrowers' in joins: return joins.add('borrowers') query = (query.join(borrowers).on( borrowers.id == loans.borrower_id)) if id is not None: query = query.where(loans.id == _add_param(id)) if after_time is not None: query = (query.where(loans.created_at > _add_param( datetime.fromtimestamp(after_time / 1000.0)))) if before_time is not None: query = (query.where(loans.created_at < _add_param( datetime.fromtimestamp(before_time / 1000.0)))) if principal_cents is not None: _ensure_principals() query = (query.where( principals.amount_usd_cents == _add_param(principal_cents))) if principal_repayment_cents is not None: _ensure_principal_repayments() query = (query.where(principal_repayments.amount_usd_cents == _add_param(principal_repayment_cents))) if borrower_id is not None: query = (query.where(loans.borrower_id == _add_param(borrower_id))) if lender_id is not None: query = (query.where(loans.lender_id == _add_param(lender_id))) if includes_user_id is not None: prm = _add_param(includes_user_id) query = (query.where((loans.borrower_id == prm) | (loans.lender_id == prm))) if borrower_name is not None: _ensure_borrowers() query = (query.where( borrowers.username == _add_param(borrower_name.lower()))) if lender_name is not None: _ensure_lenders() query = (query.where( lenders.username == _add_param(lender_name.lower()))) if includes_user_name is not None: _ensure_lenders() _ensure_borrowers() prm = _add_param(includes_user_name) query = (query.where((lenders.username == prm) | (borrowers.username == prm))) if unpaid is not None: if unpaid: query = query.where(loans.unpaid_at.notnull()) else: query = query.where(loans.unpaid_at.isnull()) if repaid is not None: if repaid: query = query.where(loans.repaid_at.notnull()) else: query = query.where(loans.repaid_at.isnull()) if limit is not None: query = query.limit(limit) query = query.select(loans.id) if format > 0: _ensure_principals() _ensure_principal_repayments() event_tables = (Table('loan_repayment_events'), Table('loan_unpaid_events'), Table('loan_admin_events')) latest_events = Table('latest_events') query = (query.with_( Query.from_(loans).select( loans.id.as_('loan_id'), Greatest(loans.created_at, *(tbl.created_at for tbl in event_tables )).as_('latest_event_at')).groupby(loans.id), 'latest_events').left_join(latest_events).on( latest_events.loan_id == loans.id).select( loans.lender_id, loans.borrower_id, principals.amount_usd_cents, principal_repayments.amount_usd_cents, (Case().when( loans.unpaid_at.isnull(), 'false').else_('true')), Cast( Extract('epoch', loans.created_at) * 1000, 'bigint'), Cast( Extract('epoch', latest_events.latest_event_at) * 1000, 'bigint'))) if format == 3: creation_infos = Table('loan_creation_infos') _ensure_borrowers() _ensure_lenders() query = (query.join(creation_infos).on( creation_infos.loan_id == loans.id).select( Function('SUBSTRING', creation_infos.parent_fullname, 4), Function('SUBSTRING', creation_infos.comment_fullname, 4), lenders.username, borrowers.username)) sql, args = convert_numbered_args(query.get_sql(), params) headers['Cache-Control'] = 'public, max-age=600' if format == 0: return _UltraCompactResponse((sql, args), headers, 'LOANS_ULTRACOMPACT') elif format == 1: return _CompactResponse((sql, args), headers, 'LOANS_COMPACT') elif format == 2: return _StandardResponse((sql, args), headers, 'LOANS_STANDARD') else: return _ExtendedResponse((sql, args), headers, 'LOANS_EXTENDED')