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_user_agent_by_string(self, user_agent_string): shard_id = MySQL.get_shard_id_for_string(user_agent_string) hash_ = UserAgent.generate_hash(user_agent_string) rows = MySQL.get_by_shard_id(shard_id).query("SELECT * FROM user_agents WHERE user_agent_hash = %s", (hash_,)) if not len(rows): raise UserAgentNotFoundException() row = rows[0] if row['id'] not in self._user_agents: self._user_agents[row['id']] = UserAgent(row['id'], row['user_agent_string'].decode("utf-8")) return self._user_agents[row['id']]
def _get_user_agent_by_string(self, user_agent_string): shard_id = MySQL.get_shard_id_for_string(user_agent_string) hash_ = UserAgent.generate_hash(user_agent_string) rows = MySQL.get_by_shard_id(shard_id).query( "SELECT * FROM user_agents WHERE user_agent_hash = %s", (hash_, )) if not len(rows): raise UserAgentNotFoundException() row = rows[0] if row['id'] not in self._user_agents: self._user_agents[row['id']] = UserAgent( row['id'], row['user_agent_string'].decode("utf-8")) return self._user_agents[row['id']]
def get_auth_by_provider_id(self, auth_class, provider_id): type_id = self._class_to_type_id(auth_class) shard_id = MySQL.get_shard_id_for_string(provider_id) rows = MySQL.get_by_shard_id(shard_id).query("SELECT * FROM auth WHERE provider_id=%s AND provider_type=%s", (provider_id, type_id)) if not len(rows): raise NoAuthFoundException(provider_id) row = rows[0] try: auth = self._row_to_model(row) except RowDeletedException: raise NoAuthFoundException(provider_id) auth.update_stored_state() return auth
def get_auth_by_provider_id(self, auth_class, provider_id): type_id = self._class_to_type_id(auth_class) shard_id = MySQL.get_shard_id_for_string(provider_id) rows = MySQL.get_by_shard_id(shard_id).query( "SELECT * FROM auth WHERE provider_id=%s AND provider_type=%s", (provider_id, type_id)) if not len(rows): raise NoAuthFoundException(provider_id) row = rows[0] try: auth = self._row_to_model(row) except RowDeletedException: raise NoAuthFoundException(provider_id) auth.update_stored_state() return auth