def associate_actors(actors, CorActor, pk_name, pk_value): for actor in actors: if not actor['uuid_organism']: continue statement = pg_insert(Organisme) \ .values( uuid_organisme=actor['uuid_organism'], nom_organisme=actor['organism'], email_organisme=actor['email'], ).on_conflict_do_update( index_elements=['uuid_organisme'], set_=dict( nom_organisme=actor['organism'], email_organisme=actor['email'], ), ) db.session.execute(statement) # retrieve organism id org = Organisme.query \ .filter_by(uuid_organisme=actor['uuid_organism']) \ .first() statement = pg_insert(CorActor) \ .values( #id_acquisition_framework=af.id_acquisition_framework, id_organism=org.id_organisme, id_nomenclature_actor_role=func.ref_nomenclatures.get_id_nomenclature( "ROLE_ACTEUR", actor["actor_role"] ), **{pk_name: pk_value}, ).on_conflict_do_nothing( index_elements=[pk_name, 'id_organism', 'id_nomenclature_actor_role'], ) db.session.execute(statement)
def insert_clusters(self, clusters: list): for cluster in clusters: if cluster['id'] is None: clust = cluster.copy() points = clust.pop('points') clust.pop('id') ins = pg_insert(self.table_cluster).on_conflict_do_nothing( index_elements=['id']).values(clust) res = self.engine.execute(ins) clust_id = res.inserted_primary_key[0] if clust_id is not None: for i in range(len(points)): points[i]["cluster_id"] = clust_id ins_points = pg_insert( self.table_cluster_point).on_conflict_do_nothing( index_elements=['id']).values(points) self.engine.execute(ins_points) else: raise Exception('cluster_id is None!') else: clust = cluster.copy() points = clust.pop('points') ins = pg_insert(self.table_cluster).values(clust) ins = ins.on_conflict_do_update( index_elements=['id'], set_=clust).values( clust) # Maybe I should pop the id before self.engine.execute(ins) ins_points = pg_insert( self.table_cluster_point).on_conflict_do_nothing( index_elements=['id']).values(points) self.engine.execute(ins_points) pass
def sync_af_and_ds(): cas_api = INPNCAS() mtd_api = MTDInstanceApi( config["MTD_API_ENDPOINT"], config['MTD']['ID_INSTANCE_FILTER']) af_list = mtd_api.get_af_list() for af in af_list: with db.session.begin_nested(): add_unexisting_digitizer(af['id_digitizer']) actors = af.pop('actors') statement = ( pg_insert(TAcquisitionFramework) .values(**af) .on_conflict_do_update( index_elements=['unique_acquisition_framework_id'], set_=af) .returning(TAcquisitionFramework.id_acquisition_framework) ) af_id = db.session.execute(statement).scalar() af = TAcquisitionFramework.query.get(af_id) associate_actors(actors, CorAcquisitionFrameworkActor, 'id_acquisition_framework', af.id_acquisition_framework) # TODO: remove actors removed from MTD db.session.commit() ds_list = mtd_api.get_ds_list() for ds in ds_list: with db.session.begin_nested(): add_unexisting_digitizer(ds['id_digitizer']) actors = ds.pop('actors') af_uuid = ds.pop('uuid_acquisition_framework') af = TAcquisitionFramework.query.filter_by(unique_acquisition_framework_id=af_uuid).first() if af is None: continue ds['id_acquisition_framework'] = af.id_acquisition_framework ds = { k: func.ref_nomenclatures.get_id_nomenclature(NOMENCLATURE_MAPPING[k], v) if k.startswith('id_nomenclature') else v for k, v in ds.items() if v is not None } statement = pg_insert(TDatasets) \ .values(**ds) \ .on_conflict_do_update( index_elements=['unique_dataset_id'], set_=ds) db.session.execute(statement) ds = TDatasets.query \ .filter_by(unique_dataset_id=ds['unique_dataset_id']) \ .first() associate_actors(actors, CorDatasetActor, 'id_dataset', ds.id_dataset) db.session.commit()
async def _set_skills(conn, contractor_id, skills): """ create missing subjects and qualification levels, then create contractor skills for them. """ if not skills: # just delete skills and return await conn.execute(sa_con_skills.delete().where( sa_con_skills.c.contractor == contractor_id)) return async with conn.begin(): await conn.execute( pg_insert(sa_subjects).values([{ 'id': s.subject_id, 'name': s.subject, 'category': s.category } for s in _distinct(skills, 'subject_id') ]).on_conflict_do_nothing()) await conn.execute( pg_insert(sa_qual_levels).values([{ 'id': s.qual_level_id, 'name': s.qual_level, 'ranking': s.qual_level_ranking } for s in _distinct(skills, 'qual_level_id') ]).on_conflict_do_nothing()) con_skills_to_create = {(s.subject_id, s.qual_level_id) for s in skills} q = select([ sa_con_skills.c.id, sa_con_skills.c.subject, sa_con_skills.c.qual_level ]).where(sa_con_skills.c.contractor == contractor_id) to_delete = set() async for r in conn.execute(q): key = r.subject, r.qual_level try: con_skills_to_create.remove(key) except KeyError: # skill doesn't exist in con_skills, it should be deleted to_delete.add(r.id) to_delete and await conn.execute(sa_con_skills.delete().where( sa_con_skills.c.id.in_(to_delete))) con_skills_to_create and await conn.execute( sa_con_skills.insert().values([ dict(contractor=contractor_id, subject=subject, qual_level=qual_level) for subject, qual_level in con_skills_to_create ]))
def process_college(self, college_data): college = College() college.unitid = college_data['UNITID'] college.name = college_data['INSTNM'] college.address1 = college_data['ADDR'] college.city = college_data['CITY'] college.state = college_data['STABBR'] college.phone = college_data['GENTELE'] college.web_url = college_data['WEBADDR'] college.admissions_url = college_data['ADMINURL'] college.netprice_url = college_data['NPRICURL'] college.sector = college_data['SECTOR'] college.locale = college_data['LOCALE'] college.hbcu = college_data['HBCU'] college.latitude = college_data['LATITUDE'] college.longitude = college_data['LONGITUD'] #upsert statement = pg_insert(College).values( **college.as_dict()).on_conflict_do_update( constraint='college_pkey', set_=college.props_dict()) self.db.execute(statement) self.db.commit() return college.name
async def upsert_service_access_rights( self, new_access_rights: List[ServiceAccessRightsAtDB] ) -> None: # update the services_access_rights table (some might be added/removed/modified) for rights in new_access_rights: insert_stmt = pg_insert(services_access_rights).values( **rights.dict(by_alias=True) ) on_update_stmt = insert_stmt.on_conflict_do_update( index_elements=[ services_access_rights.c.key, services_access_rights.c.version, services_access_rights.c.gid, services_access_rights.c.product_name, ], set_=rights.dict(by_alias=True, exclude_unset=True), ) try: async with self.db_engine.acquire() as conn: await conn.execute( # pylint: disable=no-value-for-parameter on_update_stmt ) except ForeignKeyViolation: logger.warning( "The service %s:%s is missing from services_meta_data", rights.key, rights.version, )
def upsert_records(self, records): for record in records: statement = pg_insert(Records) \ .values(**record.as_dict()) \ .on_conflict_do_update(constraint=Records.PKEY_NAME, set_=record.props_dict()) self.db.session.execute(statement) self.db.session.commit()
def upsert_discussions(session: Session, discussions: Iterable[Discussion]) -> None: stmt_values = [] urls = set() for d in discussions: urls.add(d.url) stmt_values.append({ "external_discussion_id": d.external_id, "discussion_source_id": d.source.value, "url_uuid": d.url.url_uuid, "comment_count": d.comment_count, "created_at": d.created_at, "title": d.title, }) discussion_count = len(stmt_values) log.info("upserting %d discussions across %d urls", discussion_count, len(urls)) if discussion_count == 0: # Nothing to upsert return upsert_urls(session, urls) insert_stmt = pg_insert(SQLDiscussion.__table__).values(stmt_values) upsert_stmt = insert_stmt.on_conflict_do_update( index_elements=["discussion_source_id", "external_discussion_id"], set_={ "url_uuid": insert_stmt.excluded.url_uuid, "comment_count": insert_stmt.excluded.comment_count, "created_at": insert_stmt.excluded.created_at, "title": insert_stmt.excluded.title, }, ) session.execute(upsert_stmt)
async def test_task_codewars_challenge(client, caplog): await tasks.post_slack_codewars_challenge(client.app["slack_client"], client.app["pg"]) mocked_request = client.app["slack_client"]._request mocked_request.assert_called_once() mocked_request_args = mocked_request.call_args.args payload = json.loads(mocked_request_args[3]) assert payload["channel"] == "CEFJ9TJNL" assert "No challenge found" in payload["attachments"][0]["text"] async with client.app["pg"].acquire() as conn: await conn.execute(pg_insert(models.codewars).values(id="foo")) client.app["slack_client"]._request.reset_mock() await tasks.post_slack_codewars_challenge(client.app["slack_client"], client.app["pg"]) mocked_request = client.app["slack_client"]._request mocked_request.assert_called_once() mocked_request_args = mocked_request.call_args.args payload = json.loads(mocked_request_args[3]) assert payload["channel"] == "CEFJ9TJNL" assert payload["attachments"][0][ "title_link"] == "https://www.codewars.com/kata/foo"
def create_default_status_mappings(status_mapping_dict: dict): unmapped_statuses = [] for status, status_category_str in status_mapping_dict.items(): try: status_category = StatusCategory(status_category_str) except ValueError: current_app.logger.error( f"Unable to map status `{status}` to status category `{status_category_str}`: Invalid status category value" ) unmapped_statuses.append(status) continue # on conflict, assume manually set so ignore stmt = (pg_insert(StatusCategoryStatusMapping).values( status=status, status_category=status_category).on_conflict_do_nothing()) db.session.execute(stmt) db.session.commit() current_app.logger.info( f"Loaded default status mappings: {status_mapping_dict}") if unmapped_statuses: msg = f"Unmapped statuses (need manual assignment): {unmapped_statuses}" flash(msg, "error") current_app.logger.info(msg) else: flash("All statuses successfully mapped", "success")
async def allowed_email(self, email: str) -> bool: # this really should be in the schema validation, but it doesn't support async checks (yet). if "@" not in email: return False _, domain = email.lower().split("@") logger.info("Checking if domain %s should be allowed", domain) async with self.pg.acquire() as conn: row = await conn.fetchrow( select([domains.c.blocked, domains.c.domain]).where(domains.c.domain == domain) ) if row is None: logger.info("Domain unknown, saving and allowing") await conn.fetchrow( pg_insert(domains) .values(domain=domain, blocked=False, source=Source.INVITE) .on_conflict_do_nothing(index_elements=[domains.c.domain]) ) return True if row[domains.c.blocked.name]: logger.info("Domain '%s' on the blocklist, not allowing.", domain) return False return True
def historize_classification(self, only_qual=None): """ Copy current classification information into history table, for all rows in self. :param only_qual: If set, only historize for current rows with this classification. """ # Light up a bit the SQLA expressions oh = ObjectHeader och = ObjectsClassifHisto # What we want to historize, as a subquery sel_subqry = select([ oh.objid, oh.classif_when, text("'M'"), oh.classif_id, oh.classif_qual, oh.classif_who ]) if only_qual is not None: qual_cond = oh.classif_qual.in_(only_qual) else: qual_cond = true() sel_subqry = sel_subqry.where( and_(oh.objid == any_(self.object_ids), oh.classif_when.isnot(None), qual_cond)) # Insert into the log table ins_qry: Insert = pg_insert(och.__table__) ins_qry = ins_qry.from_select([ och.objid, och.classif_date, och.classif_type, och.classif_id, och.classif_qual, och.classif_who ], sel_subqry) ins_qry = ins_qry.on_conflict_do_nothing( constraint='objectsclassifhisto_pkey') logger.info("Histo query: %s", ins_qry.compile(dialect=postgresql.dialect())) nb_objs = self.session.execute(ins_qry).rowcount logger.info(" %d out of %d rows copied to log", nb_objs, len(self.object_ids)) return oh
def run(self): session = self.session() engine = session._WopMarsSession__session.bind conn = engine.connect() # snp2phenotype_path = self.input_file(InsertSNP2Phenotype.__input_file_snp2phenotype) snp_model = self.input_table(InsertSNP2Phenotype.__input_table_snp) phenotype_model = self.input_table(InsertSNP2Phenotype.__input_table_phenotype) snp2phenotype_model = self.output_table(InsertSNP2Phenotype.__output_table_snp2phenotype) snp2phenotype_df = pandas.read_table(snp2phenotype_path, header=None) # # read input file input_file_obj_list = [] for line in csv.reader(open(snp2phenotype_path, 'r', encoding='utf-8'), delimiter="\t"): snp_rsid = int(line[0]) phenotype_name = line[1] input_file_obj_list.append({'snp_rsid' : snp_rsid, 'phenotype_name' : phenotype_name}) # # create insert snp_select = select([snp_model.id]).where(snp_model.rsid==bindparam('snp_rsid')) phenotype_select = select([phenotype_model.id]).where(phenotype_model.name==bindparam('phenotype_name')) output_table_insert = insert(table=snp2phenotype_model.__table__, values={'snp_id': snp_select, 'phenotype_id': phenotype_select}) # if len(input_file_obj_list) > 0: if str(engine.__dict__['url']).split("://")[0]=='sqlite': engine.execute(output_table_insert.prefix_with("OR IGNORE", dialect='sqlite'), input_file_obj_list) elif str(engine.__dict__['url']).split("://")[0]=='mysql': from warnings import filterwarnings # three lines to suppress mysql warnings import MySQLdb as Database filterwarnings('ignore', category = Database.Warning) engine.execute(output_table_insert.prefix_with("IGNORE", dialect='mysql'), input_file_obj_list) elif str(engine.__dict__['url']).split("://")[0]=='postgresql': from sqlalchemy.dialects.postgresql import insert as pg_insert output_table_insert_pg = pg_insert(table=snp2phenotype_model.__table__, values={'snp_id': snp_select, 'phenotype_id': phenotype_select}).on_conflict_do_nothing(index_elements=['snp_id', 'phenotype_id']) engine.execute(output_table_insert_pg, input_file_obj_list)
async def sync_slack_channels(slack_client: SlackAPI, pg: asyncpg.pool.Pool) -> None: logger.debug("Refreshing slack channels cache.") try: async with pg.acquire() as conn: async for channel in slack_client.iter( slack.methods.CONVERSATIONS_LIST): values = { "name": channel["name"], "created": datetime.datetime.fromtimestamp(channel["created"]), "archived": channel["is_archived"], "members": channel["num_members"], "topic": channel["topic"]["value"], "purpose": channel["purpose"]["value"], } await conn.execute( pg_insert(models.SlackChannels).values( id=channel["id"], **values).on_conflict_do_update( index_elements=[models.SlackChannels.c.id], set_=values)) except asyncio.CancelledError: logger.debug("Slack channels cache refresh canceled") except Exception: # pylint: disable=broad-except logger.exception("Error refreshing slack channels cache")
def load_projects(self): return_url = get_redirect_target() or self.get_url(".index_view") try: current_app.logger.info("Attempting to connect to jira") jira = jira_core.connect() except Exception as e: status = "Jira not configured" current_app.logger.error(f"{status}: {e}") flash(status, "error") else: if jira is not None: # load projects projects = jira.projects() for project in projects: # create or update in case of change in Jira. stmt = (pg_insert(JiraProject).values( project_key=project.key, project_name=project.name).on_conflict_do_update( constraint="uq_jira_projects_project_key", set_=dict(project_name=project.name), )) db.session.execute(stmt) db.session.commit() status = f"{len(projects)} project/s loaded successfully." current_app.logger.info(f"{status}") flash(status, "success") else: flash("Jira failed to connect", "error") return redirect(return_url)
def from_csv(table): filepath = os.path.join(dirpath, table.name + ".csv") with io.open(filepath, mode="r", encoding="utf-8") as csv_file: csv_reader = csv.reader(csv_file) header = next(csv_reader) conn = self.db.connect() with self.lock, conn.begin(): for row in csv_reader: values = dict(zip(header, row)) if self.engine_dialect_is(self.DIALECT_SQLITE): identifier = values[table.c.id.name] # try to update entry stmt = update(table).values(values).where( table.c.id == identifier) if conn.execute(stmt).rowcount == 0: # identifier doesn't match any => insert new entry stmt = insert(table).values(values) conn.execute(stmt) elif self.engine_dialect_is(self.DIALECT_POSTGRESQL): stmt = pg_insert(table).values(values)\ .on_conflict_do_update(index_elements=[table.c.id], set_=values) conn.execute(stmt) if self.engine_dialect_is(self.DIALECT_POSTGRESQL): # update sequence sql = "SELECT setval('{table}_id_seq', max(id)) FROM {table}".format( table=table.name) conn.execute(text(sql)) conn.close()
def run(self): session = self.session() engine = session._WopMarsSession__session.bind conn = engine.connect() # snp_path = self.input_file(InsertSnp.__input_file_snp) snp_model = self.output_table(InsertSnp.__output_table_snp) # # read input file input_file_obj_list = [] for line in csv.reader(open(snp_path, 'r', encoding='utf-8'), delimiter="\t"): chrom = int(line[0]) position = int(line[1]) rsid = int(line[2]) input_file_obj = {'chrom': chrom, 'position': position, 'rsid': rsid} input_file_obj_list.append(input_file_obj) # # insert input_file_obj_list if len(input_file_obj_list) > 0: if str(engine.__dict__['url']).split("://")[0]=='sqlite': engine.execute(snp_model.__table__.insert().prefix_with("OR IGNORE"), input_file_obj_list) elif str(engine.__dict__['url']).split("://")[0]=='mysql': from warnings import filterwarnings # three lines to suppress mysql warnings import MySQLdb as Database filterwarnings('ignore', category = Database.Warning) engine.execute(snp_model.__table__.insert().prefix_with("IGNORE"), input_file_obj_list) elif str(engine.__dict__['url']).split("://")[0]=='postgresql': from sqlalchemy.dialects.postgresql import insert as pg_insert engine.execute(pg_insert(snp_model.__table__).on_conflict_do_nothing(index_elements=['rsid']), input_file_obj_list) else: raise "Error: This engine is not implemented."
def from_csv(table): filepath = os.path.join(dirpath, table.name + ".csv") with io.open(filepath, mode="r", encoding="utf-8") as csv_file: csv_reader = csv.reader(csv_file) header = next(csv_reader) with self.lock, self.conn.begin(): for row in csv_reader: values = dict(zip(header, row)) if self.engine.dialect.name == self.DIALECT_SQLITE: identifier = values[table.c.id] # try to update entry stmt = update(table).values(values).where( table.c.id == identifier) if self.conn.execute(stmt).rowcount == 0: # identifier doesn't match any => insert new entry stmt = insert(table).values(values) self.conn.execute(stmt) elif self.engine.dialect.name == self.DIALECT_POSTGRESQL: stmt = pg_insert(table).values(values)\ .on_conflict_do_update(index_elements=[table.c.id], set_=values) self.conn.execute(stmt) if self.DIALECT_POSTGRESQL == self.engine.dialect.name: # update sequences self.conn.execute( text( "SELECT setval('profiles_id_seq', max(id)) FROM profiles" )) self.conn.execute( text( "SELECT setval('spools_id_seq', max(id)) FROM spools" ))
def postgres_upsert(engine, connection, table, values, if_row_exists): """ Prepares and executes a PostgreSQL INSERT ON CONFLICT...DO NOTHING or DO UPDATE statement via sqlalchemy.dialects.postgresql.insert Parameters ---------- engine : sqlalchemy.engine.base.Engine connection : sqlalchemy.engine.base.Connection table : sqlalchemy.sql.schema.Table values : list of dict if_row_exists : {'update', 'ignore'} * If 'update' issues a ON CONFLICT...DO UPDATE statement * If 'ignore' issues a ON CONFLICT...DO NOTHING statement """ insert_stmt = pg_insert(table).values(values) if if_row_exists == 'ignore': upsert = insert_stmt.on_conflict_do_nothing() elif if_row_exists == 'update': update_cols = [ c.name for c in table.c if c not in list(table.primary_key.columns) ] upsert = insert_stmt.on_conflict_do_update( index_elements=table.primary_key.columns, set_={k: getattr(insert_stmt.excluded, k) for k in update_cols}) # execute upsert connection.execute(upsert)
def _upsert(table: sqa.Table, engine: sqa.engine, clean_data: pd.DataFrame): """ insert data into a table, replacing any duplicate indices postgres - see: https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#insert-on-conflict-upsert """ # print(engine.dialect.dbapi.__name__) with engine.begin() as con: for index, row in clean_data.iterrows(): # check index uniqueness by attempting insert; if it fails, update row = {**row.dropna().to_dict(), clean_data.index.name: index} try: if engine.dialect.dbapi.__name__ == 'psycopg2': insert = pg_insert(table).values( row).on_conflict_do_update( index_elements=[clean_data.index.name], set_=row) else: insert = table.insert().values(row) con.execute(insert) except sqa.exc.IntegrityError: upsert = table.update() \ .where(table.c[clean_data.index.name] == index) \ .values(row) con.execute(upsert)
def upsert_matchups(self, matchups): for m in matchups: statement = pg_insert(Matchups) \ .values(**m.as_dict()) \ .on_conflict_do_update(constraint=Matchups.PKEY_NAME, set_=m.props_dict()) self.db.session.execute(statement) self.db.session.commit()
async def create_service( self, new_service: ServiceMetaDataAtDB, new_service_access_rights: List[ServiceAccessRightsAtDB], ) -> ServiceMetaDataAtDB: for access_rights in new_service_access_rights: if ( access_rights.key != new_service.key or access_rights.version != new_service.version ): raise ValueError( f"{access_rights} does not correspond to service {new_service.key}:{new_service.version}" ) async with self.db_engine.acquire() as conn: # NOTE: this ensure proper rollback in case of issue async with conn.begin() as _transaction: row: RowProxy = await ( await conn.execute( # pylint: disable=no-value-for-parameter services_meta_data.insert() .values(**new_service.dict(by_alias=True)) .returning(literal_column("*")) ) ).first() created_service = ServiceMetaDataAtDB(**row) for access_rights in new_service_access_rights: insert_stmt = pg_insert(services_access_rights).values( **access_rights.dict(by_alias=True) ) await conn.execute(insert_stmt) return created_service
def gen_and_save_token(self, user_name): """ Generate unique token for each user. Insert or update new token table object. :param user_name: target username :return: :type: str """ user_id = self.check_user_exist(user_name) if user_id: user_token = creds.gen_uid() # generate unique token user_token_obj = { 'user_id': user_id, 'token': user_token } # basic input save dict exclude_key = ['user_id'] # exclude keys exclude_obj = [dbm.UserAuth.user_id] # exclude keys as objects # make input dict with exclude keys user_token_obj_conflict = { k: v for k, v in user_token_obj.items() if k not in exclude_key } # insert or update statement statement = pg_insert( dbm.UserAuth).values(user_token_obj).on_conflict_do_update( index_elements=exclude_obj, set_=user_token_obj_conflict) # execute changes self.engine.execute(statement) # save changes self.session.commit() # return new token return user_token else: # return if user not found return False
def upsert_teams(self, teams): for team in teams: statement = pg_insert(Teams) \ .values(**team.as_dict()) \ .on_conflict_do_update(constraint=Teams.PKEY_NAME, set_=team.props_dict()) self.db.session.execute(statement) self.db.session.commit()
def upsert(table: Table, data: dict, update_on_conflict: bool = False) -> Insert: insert_data = pg_insert(table).values(**data) if update_on_conflict: return insert_data.on_conflict_do_update(constraint=table.primary_key, set_=data) return insert_data.on_conflict_do_nothing(constraint=table.primary_key)
async def appointment_webhook(request): apt_id = request.match_info['id'] appointment: AppointmentModel = request['model'] conn = await request['conn_manager'].get_connection() v = await conn.execute(select([ser_c.company]).where(ser_c.id == appointment.service_id)) r = await v.first() if r and r.company != request['company'].id: raise HTTPConflictJson( status='service conflict', details=f'service {appointment.service_id} already exists and is associated with another company', ) service_insert_update = dict( name=appointment.service_name, colour=appointment.colour, extra_attributes=[ ea.dict(exclude={'sort_index'}) for ea in sorted(appointment.extra_attributes, key=attrgetter('sort_index')) ], ) await conn.execute( pg_insert(sa_services) .values(id=appointment.service_id, company=request['company'].id, **service_insert_update) .on_conflict_do_update( index_elements=[ser_c.id], where=ser_c.id == appointment.service_id, set_=service_insert_update, ) ) apt_insert_update = appointment.dict( include={'attendees_max', 'attendees_count', 'attendees_current_ids', 'start', 'finish', 'price', 'location'} ) apt_insert_update['topic'] = appointment.appointment_topic await conn.execute( pg_insert(sa_appointments) .values(id=apt_id, service=appointment.service_id, **apt_insert_update) .on_conflict_do_update( index_elements=[apt_c.id], where=apt_c.id == apt_id, set_=apt_insert_update, ) ) return json_response(request, status='success')
def wallet_donate( cls, db: Session, idempotency_key: uuid.UUID, request: WalletDonateRequest, ) -> WalletResponse: try: db_wallet = ( db.query(Wallet) .filter(Wallet.wallet_id == request.wallet_id) .with_for_update() .first() ) if not db_wallet: raise HTTPException( status_code=404, detail='Wallet is not found', ) utcnow = datetime.datetime.utcnow() db_wallet.amount = db_wallet.amount + request.amount db_wallet.updated_at = utcnow db.add(db_wallet) insert_transaction = ( pg_insert(Transaction) .values( idempotency_key=idempotency_key, amount=request.amount, to_wallet_id=db_wallet.wallet_id, to_wallet_amount=db_wallet.amount, created_at=utcnow, ) .returning(Transaction.to_wallet_amount) ) db.execute(insert_transaction).first() db.commit() return WalletResponse( wallet_id=db_wallet.wallet_id, amount=db_wallet.amount, ) except exc.IntegrityError: db.rollback() existed_transaction = ( db.query(Transaction) .filter(Transaction.idempotency_key == idempotency_key) .first() ) return WalletResponse( wallet_id=existed_transaction.to_wallet_id, amount=existed_transaction.to_wallet_amount, )
async def checkout(self, repo_id: int, commit_id: int) -> int: """checks out working copy of project_uuid to commit ref_id :raises RuntimeError: if local copy has changes (i.e. dirty) :return: commit id :rtype: int """ async with self.engine.acquire() as conn: repo, head_commit, _ = await self._update_state(repo_id, conn) if head_commit is None: raise NoCommitError( details="Cannot checkout without commit changes first") # check if working copy has changes, if so, fail if repo.project_checksum != head_commit.snapshot_checksum: raise CleanRequiredError( details= "Your local changes would be overwritten by checkout. " "Cannot checkout without commit changes first.") # already in head commit if head_commit.id == commit_id: return commit_id async with conn.begin(): commit = (await self.CommitsOrm(conn).set_filter( id=commit_id).fetch("snapshot_checksum")) assert commit # nosec # restores project snapshot snapshot = (await self.SnapshotsOrm(conn).set_filter( commit.snapshot_checksum).fetch("content")) assert snapshot # nosec await self.ProjectsOrm(conn).set_filter( uuid=repo.project_uuid).update(**snapshot.content) # create detached branch that points to (repo_id, commit_id) # upsert "detached" branch insert_stmt = (pg_insert(projects_vc_branches).values( repo_id=repo_id, head_commit_id=commit_id, name="DETACHED", ).returning(projects_vc_branches.c.id)) upsert_tag = insert_stmt.on_conflict_do_update( constraint="repo_branch_uniqueness", set_=dict( head_commit_id=insert_stmt.excluded.head_commit_id), ) branch_id = await conn.scalar(upsert_tag) # updates head await self.HeadsOrm(conn).set_filter(repo_id=repo_id).update( head_branch_id=branch_id) return commit_id
async def grant_rights_to_services(services: List[Tuple[str, str]]) -> None: for service_key, service_version in services: metada_data_values = dict( key=service_key, version=service_version, owner=1, # a user is required in the database for ownership of metadata name="", description=f"OVERRIDEN BY TEST in {__file__}", ) access_rights_values = dict( key=service_key, version=service_version, gid=1, execute_access=True, write_access=True, ) async with aiopg_engine.acquire() as conn: # pylint: disable=no-value-for-parameter await conn.execute( pg_insert(services_meta_data) .values(**metada_data_values) .on_conflict_do_update( index_elements=[ services_meta_data.c.key, services_meta_data.c.version, ], set_=metada_data_values, ) ) await conn.execute( pg_insert(services_access_rights) .values(**access_rights_values) .on_conflict_do_update( index_elements=[ services_access_rights.c.key, services_access_rights.c.version, services_access_rights.c.gid, services_access_rights.c.product_name, ], set_=access_rights_values, ) )
def _upsert(table: sqa.Table, engine: sqa.engine, cleaned_data: pd.DataFrame): """ insert data into a table, replacing any rows with duplicate indices When upsert finds duplicate records, it overwrites ALL VALUES that are present in source DataFrame, including NaN. postgres - see: https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#insert-on-conflict-upsert """ if isinstance(cleaned_data.index, pd.MultiIndex): multi = True names = cleaned_data.index.names index_elements = names else: multi = False names = cleaned_data.index.name index_elements = [names] cleaned_data = cleaned_data.astype('object') def map2none(val): """sqlalchemy freaks out about NaNs, so replace them with None""" if pd.notna(val): return val with engine.begin() as con: for row in cleaned_data.reset_index(drop=False).itertuples(index=False): # check index uniqueness by attempting insert; if it fails, update row = {k: map2none(v) for k, v in row._asdict().items()} try: if engine.dialect.dbapi.__name__ == 'psycopg2': insert = pg_insert(table).values(row).on_conflict_do_update( index_elements=index_elements, set_=row ) else: insert = table.insert().values(row) con.execute(insert) except sqa.exc.IntegrityError: if multi: upsert = table.update() for n in names: upsert = upsert.where(table.c[n] == row[n]) con.execute(upsert.values(row)) else: upsert = table.update() \ .where(table.c[names] == row[names]) \ .values(row) con.execute(upsert)
def upsert(table: Table, data: dict, update_on_conflict: bool = False) -> Insert: insert_data = pg_insert(table).values( **data ) if update_on_conflict: return insert_data.on_conflict_do_update( constraint=table.primary_key, set_=data ) return insert_data.on_conflict_do_nothing( constraint=table.primary_key )
def update_selection(self, identifier, client_id, data): with self.lock, self.conn.begin(): values = dict() if self.engine.dialect.name == self.DIALECT_SQLITE: stmt = insert(self.selections).prefix_with("OR REPLACE")\ .values(tool=identifier, client_id=client_id, spool_id=data["spool"]["id"]) elif self.engine.dialect.name == self.DIALECT_POSTGRESQL: stmt = pg_insert(self.selections)\ .values(tool=identifier, client_id=client_id, spool_id=data["spool"]["id"])\ .on_conflict_do_update(constraint="selections_pkey", set_=dict(spool_id=data["spool"]["id"])) self.conn.execute(stmt) return self.get_selection(identifier, client_id)
def test_insert_on_conflict_renders(connection): statement = pg_insert(User).values(name='John', fullname="John Doe", password='******') statement = statement.on_conflict_do_nothing() connection.execute(statement) assert len(xray_recorder.current_segment().subsegments) == 1 sql_meta = xray_recorder.current_segment().subsegments[0].sql assert sql_meta['sanitized_query'].startswith('INSERT INTO users') assert 'ON CONFLICT DO NOTHING' in sql_meta['sanitized_query']