def get_auth_for_user(self, user_id, auth_classes=None): where_clause = And([("user_id", "=", "%s")]) vals = [user_id] qb = SQLQueryBuilder.select("auth_lookup", backtick=False) if auth_classes is not None: if isinstance(auth_classes, type): auth_classes = [auth_classes] esses = [] for auth_class in auth_classes: auth_type = self._class_to_type_id(auth_class) vals.append(auth_type) esses.append("%s") where_clause.append( ("provider_type", "IN", "(" + ",".join(esses) + ")")) qb = qb.where(where_clause) rows = MySQL.get(user_id).query(qb.build(), vals) rows = self._filter_deleted(rows) return [self._row_to_model(row) for row in rows]
def multi_shard_insert(self, table_name, shard_by_col_name, dicts_to_insert, cols_to_update = None): count = 0 #construct mapping of inserted objects to shard that they go to shard_to_dicts = {} for d in dicts_to_insert: try: primary_id = d[shard_by_col_name] shard_id = Id(primary_id).get_shard_id() if shard_id not in shard_to_dicts: shard_to_dicts[shard_id] = [] shard_to_dicts[shard_id].append(d) except Exception as e: #skip objects that don't have the shard_by_col, or in wrong format if not self._catch_errors: raise e for shard_id, dict_list in shard_to_dicts.items(): #make sure we only have a separate call for each column set dict_lists_by_cols = {} for d in dict_list: cols = list(d.keys()) cols.sort() col_str = ",".join(cols) if not col_str in dict_lists_by_cols: dict_lists_by_cols[col_str] = [] dict_lists_by_cols[col_str].append(d) for col_str, dict_list in dict_lists_by_cols.items(): #get vals array vals = [ ["%s" for k in d.keys()] for d in dict_list ] #create parameter placeholders params = [ v for d in dict_list for v in d.values()] cols = dict_list[0].keys() qb = SQLQueryBuilder.insert(table_name).columns(cols).values(vals) if cols_to_update: update_list = [ ("`" + c + "`", "VALUES(`" + c+ "`)") for c in cols_to_update ] qb.on_duplicate_key_update(update_list) #do insert shard = MySQL.get_by_shard_id(shard_id, self._pool.get_id()) try: count = count + shard.query(qb.build(), params, self._use_multi) except Exception as e: if not self._catch_errors: raise e count = 0 return count
def _get_list(self, table_name, id, id_name, sort_by="sort_index", count=None, offset=None, has_permissions=None, missing_permissions=None, sort=SORT_HI_TO_LO): where_clause = And([(id_name, "=", "%s"), ("deleted", "=", "0")], backtick=False) if has_permissions: where_clause.append( ("`permission`|" + str(has_permissions), "=", "`permission`")) if missing_permissions: where_clause.append(("`permission`&" + str(missing_permissions), "=", "`permission`")) if offset and sort_by: if SORT_HI_TO_LO: comp = "<" else: comp = ">" where_clause.append((sort_by, comp, offset)) query_builder = (SQLQueryBuilder.select(table_name).where( where_clause).limit(count)) if sort_by: query_builder.order_by(sort_by, sort) try: ret = MySQL.get(id).query(query_builder.build(), [id]) except BadIdError: raise framework.models.data_access_object.RowNotFoundException() return ret