def downgrade(migrate_engine): meta.bind = migrate_engine instance_table = Table('instances', meta, autoload=True) instance_table.c.datastore_version_id.alter(nullable=True) update( table=instance_table, whereclause="datastore_version_id='%s'" % LEGACY_VERSION_ID, values=dict(datastore_version_id=None) ).execute() datastores_table = Table('datastores', meta, autoload=True) datastore_versions_table = Table('datastore_versions', meta, autoload=True) delete( table=datastore_versions_table, whereclause="id='%s'" % LEGACY_VERSION_ID ).execute() delete( table=datastores_table, whereclause="id='%s'" % LEGACY_DATASTORE_ID ).execute()
def rulesCallback(ip,port,rules): serverid = getServerId(ip,port) for rule in rules: if rule[0] == 'tickrate': db.execute( update(tbl_server_history) .where( and_( tbl_server_history.c.id==serverid, tbl_server_history.c.date==tm, ) ) .values( tickrate=rule[1] ) ) elif rule[0] == 'ent_count': db.execute( update(tbl_server_history) .where( and_( tbl_server_history.c.id==serverid, tbl_server_history.c.date==tm, ) ) .values( ent_count=rule[1].replace(',','') ) )
def upgrade(migrate_engine): meta.bind = migrate_engine instance_table = Table('instances', meta, autoload=True) if has_instances_wo_datastore_version(instance_table): instances = find_all_instances_wo_datastore_version(instance_table) image_id = find_image("mysql") datastores_table = Table('datastores', meta, autoload=True) datastore_versions_table = Table('datastore_versions', meta, autoload=True) version_id = create_legacy_version(datastores_table, datastore_versions_table, image_id) for instance in instances: update( table=instance_table, whereclause="id='%s'" % instance.id, values=dict(datastore_version_id=version_id) ).execute() instance_table.c.datastore_version_id.alter(nullable=False)
def release(self): with self._session() as session: query = update(QueuedURL).values({QueuedURL.status: Status.todo.value})\ .where(QueuedURL.status==Status.in_progress.value) session.execute(query) query = update(QueuedFile).values({QueuedFile.status: Status.todo.value}) \ .where(QueuedFile.status==Status.in_progress.value) session.execute(query)
def _update_nodes(nodemap, instances, migrations): """For each migration and matching instance record, update the node columns if the referenced host is single-node. Skip updates for multi-node hosts. In that case, there's no way to determine which node on a host the record should be associated with. """ q = select([migrations.c.id, migrations.c.source_compute, migrations.c.dest_compute, instances.c.uuid, instances.c.host, instances.c.node], whereclause=and_(migrations.c.source_compute != None, migrations.c.dest_compute != None, instances.c.deleted == False, migrations.c.status != 'reverted', migrations.c.status != 'error'), from_obj=migrations.join(instances, migrations.c.instance_uuid == instances.c.uuid) ) result = q.execute() for migration_id, src, dest, uuid, instance_host, instance_node in result: values = {} nodes = nodemap.get(src, []) if len(nodes) == 1: # the source host is a single-node, safe to update node node = nodes[0] values['source_node'] = node if src == instance_host and node != instance_node: update(instances).where(instances.c.uuid == uuid).\ values(node=node) nodes = nodemap.get(dest, []) if len(nodes) == 1: # the dest host is a single-node, safe to update node node = nodes[0] values['dest_node'] = node if dest == instance_host and node != instance_node: update(instances).where(instances.c.uuid == uuid).\ values(node=node) if values: q = update(migrations, values=values, whereclause=migrations.c.id == migration_id) q.execute()
def GenCode(self, kwargs): getCurrCode = select([CodeListing]).where(CodeListing.name_tbl == kwargs["table"]) codeTbl = 0 result = self.conn.execute(getCurrCode).fetchone() currentCode = 1 if result != None: currentCode = int(result.curCode)+1 self.conn.execute(update(CodeListing).where(CodeListing.code == int(result.code)).values(curCode=currentCode)) else: if "column" not in kwargs: kwargs["column"] = "code" description = "" headCode = 0 code = CodeGen().getCodeLstingCode() if "description" in kwargs: description = kwargs["description"] elif "headCode" in kwargs: headCode=int(kwargs["headCode"]) self.conn.execute(insert(CodeListing).values(curCode=currentCode, name_tbl=kwargs["table"], name_col=kwargs["column"], description=description, headCode=headCode, code=code)) return currentCode
def populate_files(self): with self._session() as session: datetime_ago = datetime.datetime.utcnow() - datetime.timedelta(days=3) query = session.query(IAItem.id).filter( or_( IAItem.refresh_date.is_(None), IAItem.public_date > datetime_ago )) for row in query: identifier = row[0] _logger.info('Populating item %s.', identifier) files = yield self._api.get_item_files(identifier) query = insert(File).prefix_with('OR IGNORE') values = [] for filename, size in files: values.append({ 'ia_item_id': identifier, 'filename': filename, 'size': size, }) session.execute(query, values) query = update(IAItem).where(IAItem.id == identifier) session.execute( query, {'refresh_date': datetime.datetime.utcnow()} ) session.commit()
def write(self, path, buf, offset): print "*** write", path, buf, offset if getDepth(path) >= 3: pe = getParts(path) else: return -errno.ENOSYS if getDepth(path) == 4 and (pe[-2] == "data"): table = Table(pe[-3], self.meta, schema=pe[-4], autoload=True) values = buf.split(",") a = pe[-1].split("#_") # u = table.update().\ # where(table.c[str(a[0])]==bindparam('old'+str(a[0]))).\ # values(table.c[str(a[0])]=bindparam('new'+str(a[0]))) dk = [] dv = {} for k in table.columns.keys(): dk.append(k) i = 0 for v in values: dv[str(dk[i])] = v i += 1 dbg() ret = self.engine.execute(update(table, values=dv, whereclause=table.c[str(a[0])] == a[1])) elif getDepth(path) == 3 and (pe[-1] == "definition"): pass # table = Table(pe[-2], self.meta, schema=pe[-3], autoload=True) # a = str(CreateTable(table).compile(self.engine)) return len(buf)
def populate_daily_stats(self): with self._session() as session: query = delete(DailyStat) session.execute(query) query = session.query(IAItem.id, IAItem.public_date) for ia_item_id, public_date in query: date = public_date.date() total_size = 0 rows = session.query(File.size)\ .filter_by(ia_item_id=ia_item_id)\ .filter(File.job_id.isnot(None)) for size, in rows: total_size += size session.execute( insert(DailyStat).prefix_with('OR IGNORE'), {'date': date} ) query = update(DailyStat)\ .values({'size': DailyStat.size + total_size})\ .where(DailyStat.date == date) session.execute(query)
def infoTimeout(info): print "Server timed out!", info serverKey = "%s:%s" % info if serverKey in allTimeouts: del allTimeouts[serverKey] if not serverKey in numRetries or numRetries[serverKey] > 0: numRetries[serverKey] -= 1 serverlistCallback(info) else: print "Server retry limit reached:",info serverid = getServerId(info[0],info[1]) db.execute(insert(tbl_server_history).prefix_with('IGNORE'), id=serverid, date=tm, status=0, ) db.execute( update(tbl_servers) .where ( tbl_servers.c.id==serverid) .values( last_sample=tm ) )
def update_experiment(start, end, session): ExperimentMeasurement = get_ExperimentMeasurement() _sample_id = ExperimentMeasurement.sample_id session.execute( update(ExperimentMeasurement).where( _sample_id == Sample.id and start <= _sample_id <= end).values( experiment_id=Sample.experiment))
def check_in(self, url, new_status, increment_try_count=True, url_result=None): with self._session() as session: values = { QueuedURL.status: new_status.value } if url_result: values.update(url_result.database_items()) if increment_try_count: values[QueuedURL.try_count] = QueuedURL.try_count + 1 # TODO: rewrite as a join for clarity subquery = select([URLString.id]).where(URLString.url == url)\ .limit(1) query = update(QueuedURL).values(values)\ .where(QueuedURL.url_string_id == subquery) session.execute(query) if new_status == Status.done and url_result and url_result.filename: query = insert(QueuedFile).prefix_with('OR IGNORE').values({ 'queued_url_id': subquery }) session.execute(query)
def write(self,path,buf,offset): print '*** write', path, buf, offset if not self.layout(path): return -errno.ENOSYS #Error Not Found if getDepth(path) >= 2: pe = getParts(path) else: return -errno.ENOSYS if getDepth(path) == 4 and ( pe[-2] == 'data' ): table = Table(pe[-3], self.meta, schema=pe[-4], autoload=True) if isinstance(buf, int): print "buff is int" return buf values = str(buf).split(',') a=pe[-1].split('#_') dv=dict(zip(table.columns.keys(), values)) #try: ret = self.engine.execute(update(table,values=dv, whereclause=table.c[str(a[0])]==a[1]) ) #except IntegrityError: # ret = self.engine.execute(insert(table,values=dv) ) elif getDepth(path) == 3 and ( pe[-1] == 'define' ): pass#table = Table(pe[-2], self.meta, schema=pe[-3], autoload=True) #a = str(CreateTable(table).compile(self.engine)) return len(str(buf))
def raw_update(self, where, row): assert 'writer' == self.role row['updated_ts'] = int(time.time()) stmt = update(self.table). \ where(where). \ values(row) return self.execute(stmt)
def equip(self, item: Item) -> bool: equipped = session.execute( update(db.Equipment).where( db.Equipment.user_id == self.user.id).values( right_hand=item.item_id)) return equipped
def check_in(self, url, new_status, increment_try_count=True, url_result=None): with self._session() as session: values = {QueuedURL.status: new_status.value} if url_result: values.update(url_result.database_items()) if increment_try_count: values[QueuedURL.try_count] = QueuedURL.try_count + 1 # TODO: rewrite as a join for clarity subquery = select([URLString.id]).where(URLString.url == url)\ .limit(1) query = update(QueuedURL).values(values)\ .where(QueuedURL.url_string_id == subquery) session.execute(query) if new_status == Status.done and url_result and url_result.filename: query = insert(QueuedFile).prefix_with('OR IGNORE').values( {'queued_url_id': subquery}) session.execute(query)
def forcibly_preserve_special_notes_for_device(cls, req: CamcopsRequest, device_id: int) -> None: """ Force-preserve all special notes for a given device. WRITES TO DATABASE. For update methods, see also: http://docs.sqlalchemy.org/en/latest/orm/persistence_techniques.html """ dbsession = req.dbsession new_era = req.now_era_format # METHOD 1: use the ORM, object by object # # noinspection PyProtectedMember # notes = dbsession.query(cls)\ # .filter(cls._device_id == device_id)\ # .filter(cls._era == ERA_NOW)\ # .all() # for note in notes: # note._era = new_era # METHOD 2: use the Core, in bulk # You can use update(table)... or table.update()...; # http://docs.sqlalchemy.org/en/latest/core/dml.html#sqlalchemy.sql.expression.update # noqa # noinspection PyUnresolvedReferences dbsession.execute( update(cls.__table__) .where(cls.device_id == device_id) .where(cls.era == ERA_NOW) .values(era=new_era) )
def upgrade(): session = sa.orm.Session(bind=op.get_bind()) my_bd_l3outs = get_bd_l3out_values(session) for bd_l3out in my_bd_l3outs: bd_update = update(bridge_domains).values( {'limit_ip_learn_to_subnets': True}).where( bridge_domains.c.aim_id == bd_l3out['bd_aim_id']) session.execute(bd_update)
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine modules = Table('modules', meta, autoload=True) is_nullable = True if migrate_engine.name == "sqlite" else False column = Column(COLUMN_NAME_1, Boolean(), nullable=is_nullable, default=0) modules.create_column(column) column = Column(COLUMN_NAME_2, Integer(), nullable=is_nullable, default=5) modules.create_column(column) column = Column(COLUMN_NAME_3, Boolean(), nullable=is_nullable, default=0) modules.create_column(column) modules.c.contents.alter(Text(length=4294967295)) # mark all non-visible, auto-apply and all-tenant modules as is_admin update(table=modules, values=dict(is_admin=1), whereclause="visible=0 or auto_apply=1 or tenant_id is null" ).execute()
def update_supplier(db: Session, id: int, supplier_update: schemas.SupplierUpdate): properties_to_update = {key: value for key, value in supplier_update.dict().items() if value is not None} update_statement = update(models.Supplier) \ .where(models.Supplier.SupplierID == id) \ .values(**properties_to_update) db.execute(update_statement) db.commit() return get_supplier(db, id)
def update_player(pid, name, corp_id, runner_id): db = get_db() plr = metadata.tables["player"] with db.begin() as conn: conn.execute( update(plr).where(plr.c.id == pid).values(p_name=name, corp_id=corp_id, runner_id=runner_id))
def convert_check_in(self, file_id, status): with self._session() as session: values = {'status': status.value} query = update(QueuedFile).values(values) \ .where(QueuedFile.id == file_id) session.execute(query)
def cmd(): """ Updating a specific part of the JSON using an execute update command that creates an SQL command, partly bypassing the models. It needs more work - For the moment, I didn't manage to do the updates in this way. """ # Importing the engine from sqla_json.sqla_management import engine from sqlalchemy.orm import sessionmaker from sqla_json.models.node import DbNode from sqlalchemy.orm.attributes import flag_modified from sqla_json import timezone # from sqlalchemy.dialects.postgresql import insert from sqlalchemy.sql.expression import update import json import time sec = timezone.now().second msec = timezone.now().microsecond # Creating the needed session Session = sessionmaker(bind=engine) session = Session() print "Finding all the nodes and updating them one by one" counter = 0 start_time = time.time() # # The following works # session.execute(update(DbNode, values={DbNode.attributes: json.dumps( # ['attr', msec, {'bar': ('baz', 'bulk_update_json_model_sql', # sec, 2)}])}) # ) # And the following too def get_json(): get_json.counter += 1 return json.dumps( ['attr', msec + get_json.counter, {'bar': ('baz', 'bulk_update_json_model_sql', sec + get_json.counter, 2)}]) get_json.counter = 0 # dbn.extras[2]['bar'][0] = "aaa" # session.execute(update(DbNode, values={DbNode.extras[2]['bar'][0]: "aaa"})) session.execute(update(DbNode, values={DbNode.extras[2]: "aaa"})) # DbNode.update().values(extras='b') # .values(DbNode.extras[2] = "aaa") session.commit() end_time = time.time() print("Updated the json fields (attributes and extras) " "of {} nodes.".format(counter)) print "Elapsed time --- {} seconds --- ".format(end_time - start_time)
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine ds_table = Table('datastores', meta, autoload=True) ds_version_table = Table('datastore_versions', meta, autoload=True) ds_version_table.create_column( Column('version', String(255), nullable=True)) ds_versions = select(columns=[text("id"), text("name")], from_obj=ds_version_table).execute() # Use 'name' value as init 'version' value for version in ds_versions: update(table=ds_version_table, whereclause=text("id='%s'" % version.id), values=dict(version=version.name)).execute() # Change unique constraint, need to drop the foreign key first and add back # later constraint_names = db_utils.get_foreign_key_constraint_names( engine=migrate_engine, table='datastore_versions', columns=['datastore_id'], ref_table='datastores', ref_columns=['id']) db_utils.drop_foreign_key_constraints( constraint_names=constraint_names, columns=[ds_version_table.c.datastore_id], ref_columns=[ds_table.c.id]) UniqueConstraint('datastore_id', 'name', name='ds_versions', table=ds_version_table).drop() UniqueConstraint('datastore_id', 'name', 'version', name='ds_versions', table=ds_version_table).create() db_utils.create_foreign_key_constraints( constraint_names=constraint_names, columns=[ds_version_table.c.datastore_id], ref_columns=[ds_table.c.id])
def clean_cookies(cls, session, username): """ clean password and cookie for username :param session: db session :param username: Cookie.username """ session.execute(update(Cookie). where(Cookie.username == username). values(password='', cookie='', updated_at=text('NOW()')))
def upgrade(): session = sa.orm.Session(bind=op.get_bind()) my_bd_l3outs = get_bd_l3out_values(session) for bd_l3out in my_bd_l3outs: bd_update = update(bridge_domains).values({ 'limit_ip_learn_to_subnets': True }).where(bridge_domains.c.aim_id == bd_l3out['bd_aim_id']) session.execute(bd_update)
def set_voice_category(self, xid: int, value: str) -> str: max_len = Channel.voice_category.property.columns[ 0].type.length # type: ignore name = value[:max_len] query = (update(Channel).where(Channel.xid == xid).values( voice_category=name).execution_options(synchronize_session=False)) DatabaseSession.execute(query) DatabaseSession.commit() return name
def populate_jobs(self): with self._session() as session: query = session.query(File.ia_item_id, File.filename, File.size)\ .filter_by(job_id=None) for row in query: ia_item_id, filename, size = row filename_info = parse_filename(filename) if not filename_info: continue job_ident = filename_info['ident'] or \ '{}{}'.format(filename_info['date'], filename_info['time']) query = insert(Job).prefix_with('OR IGNORE') value = { 'id': job_ident, 'domain': filename_info['domain'], } session.execute(query, [value]) values = {} if filename_info['aborted']: values['aborts'] = Job.aborts + 1 if filename_info['extension'] == 'warc.gz': values['warcs'] = Job.warcs + 1 values['size'] = Job.size + size elif filename_info['extension'] == 'json': values['jsons'] = Job.jsons + 1 if values: query = update(Job).values(values)\ .where(Job.id == job_ident) session.execute(query) query = update(File)\ .values({'job_id': job_ident})\ .where(File.ia_item_id == ia_item_id)\ .where(File.filename == filename) session.execute(query)
def convert_check_in(self, file_id, status): with self._session() as session: values = { 'status': status.value } query = update(QueuedFile).values(values) \ .where(QueuedFile.id == file_id) session.execute(query)
def upgrade(migrate_engine): meta.bind = migrate_engine instance_table = Table('instances', meta, autoload=True) datastore_versions_table = Table('datastore_versions', meta, autoload=True) if has_instances_wo_datastore_version(instance_table): instances = find_all_instances_wo_datastore_version(instance_table) image_id = find_image("mysql") datastores_table = Table('datastores', meta, autoload=True) version_id = create_legacy_version(datastores_table, datastore_versions_table, image_id) for instance in instances: update( table=instance_table, whereclause="id='%s'" % instance.id, values=dict(datastore_version_id=version_id) ).execute() constraint_names = db_utils.get_foreign_key_constraint_names( engine=migrate_engine, table='instances', columns=['datastore_version_id'], ref_table='datastore_versions', ref_columns=['id']) db_utils.drop_foreign_key_constraints( constraint_names=constraint_names, columns=[instance_table.c.datastore_version_id], ref_columns=[datastore_versions_table.c.id]) instance_table.c.datastore_version_id.alter(nullable=False) db_utils.create_foreign_key_constraints( constraint_names=constraint_names, columns=[instance_table.c.datastore_version_id], ref_columns=[datastore_versions_table.c.id])
def __iter__(self): # First, set all the users in the SQL DB to inactive set_all_inactive = update(self.table) self.connection.execute(set_all_inactive, {'active': '0'}) transaction.commit() for item in self.previous: try: # Then set the ones still contained in the LDAP to active u = update(self.table, self.table.c.get('userid') == item.get('userid')) self.connection.execute(u, {'active': '1'}) yield item except OperationalError, e: transaction.abort() self.logger.warn("SQL operational error: %s" % e) except:
def get_load(): header = { 'Authorization': 'Basic ' + b64encode(config.api_credentials).decode() } url = 'https://www.mysportsfeeds.com/api/feed/pull/\ mlb/2017-regular/cumulative_player_stats.json?playerstats=AB,H,R,HR,ER' print('Getting data...', str(datetime.now())) resp = r.get(url=url, headers=header) print('Done.') data = json.loads(resp.text)['cumulativeplayerstats'] players = data['playerstatsentry'] engine = create_engine(config.db_uri) Base = declarative_base() class player_stats(Base): __tablename__ = 'player_stats' playerid = Column(Integer, primary_key=True) firstname = Column(String) lastname = Column(String) team = Column(String) homeruns = Column(Integer) last_updated = Column(DateTime) class entries(Base): __tablename__ = 'entries' name = Column(String, primary_key=True) playerid = Column(Integer, primary_key=True) session = sessionmaker() session.configure(bind=engine) s = session() print('Loading data...') now = str(datetime.now()) for p in players: upd = update(table=player_stats)\ .where(and_(player_stats.firstname==p['player']['FirstName'], player_stats.lastname==p['player']['LastName'], player_stats.team==p['team']['Name'],))\ .values(homeruns=p['stats']['Homeruns']['#text'], last_updated=now) s.execute(upd) s.commit() s.close() print('Done.')
def switch_tournament_activity(tid): db = get_db() tourn = metadata.tables["tournament"] with db.begin() as conn: t = conn.execute(select(tourn).where(tourn.c.id == tid)).fetchone() if t.active: setto = False else: setto = True conn.execute( update(tourn).where(tourn.c.id == tid).values(active=setto))
def __iter__(self): # First, set all the users in the SQL DB to inactive set_all_inactive = update(self.table) self.connection.execute(set_all_inactive, {'active': '0'}) transaction.commit() for item in self.previous: try: # Then set the ones still contained in the LDAP to active u = update( self.table, self.table.c.get('userid') == item.get('userid')) self.connection.execute(u, {'active': '1'}) yield item except OperationalError, e: transaction.abort() self.logger.warn("SQL operational error: %s" % e) except:
def set_signature_for_document_id(self, document_id, systematic_node): ''' Does what the method name says. ''' if systematic_node is None: value = None else: value = "%s" % systematic_node.id query = update(self.doc_table).values( standort=value).where(self.doc_table.c.laufnr == document_id) self.connection.execute(query)
def set_motd(self, xid: int, message: Optional[str] = None) -> str: if message: max_len = Channel.motd.property.columns[ 0].type.length # type: ignore motd = message[:max_len] else: motd = "" query = (update(Channel).where(Channel.xid == xid).values( motd=motd).execution_options(synchronize_session=False)) DatabaseSession.execute(query) DatabaseSession.commit() return motd
async def _update_info(self, info: dict): self.log.info(f'updating info of symbol {info["name"]}') engine, session = await self._create_session() async with session() as session: await session.execute( update(StockInfo).where( StockInfo.symbol == info['symbol']).values(info)) await session.commit() await self._close(session, engine)
def record_result(mid, corp_score, runner_score): db = get_db() table_match = metadata.tables["match"] with db.begin() as conn: match = conn.execute( select(table_match).where(table_match.c.id == mid)).fetchone() if get_player(match["corp_id"]).is_bye or get_player( match["runner_id"]).is_bye: return conn.execute( update(table_match).where(table_match.c.id == mid).values( corp_score=corp_score, runner_score=runner_score))
def _update_named_records(self, items, item_model, name_model, primary_key, primary_updates, name_updates): session = self.dbsession id_map = {} keyfn = itemgetter(primary_key) items.sort(key=keyfn) for key, group in groupby(items, keyfn): id_map[key] = list(group) source = set(id_map.keys()) pk = getattr(item_model, primary_key) existing = set(x[0] for x in session.query(pk).all()) to_update = existing & source if primary_updates: kw = {getattr(item_model, x): bindparam(x) for x in primary_updates} u = update(item_model.__table__).where( getattr(item_model, primary_key) == bindparam('pk')).values(kw) upd = (id_map[x][0] for x in to_update) cols = [primary_key] + primary_updates keyfn = itemgetter(*cols) cols = ['pk'] + primary_updates session.execute(u, [dict(list(zip(cols, keyfn(x)))) for x in upd]) if name_model: kw = {getattr(name_model, x): bindparam(x) for x in name_updates} u = update(name_model.__table__).where(and_( getattr(name_model, primary_key) == bindparam('pk'), name_model.LangID == bindparam('langid'))).values(kw) upd = (y for x in to_update for y in id_map[x]) cols = [primary_key, 'LangID'] + name_updates keyfn = itemgetter(*cols) cols = ['pk', 'langid'] + name_updates session.execute(u, [dict(list(zip(cols, keyfn(x)))) for x in upd])
def upgrade(): bind = op.get_bind() if isinstance(bind, MockEngineStrategy.MockConnection): log.warning("Using mock connection; skipping step") return # logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) # for echo # bind._echo = True # echo on, in a hacky way dbsession = orm.Session(bind=bind) # How to do this? # (1) One approach is to create a "cut-down" Patient class with just two # columns (pk_, uuid) and use the ORM. That works fine but is slightly # confusing in that we have two Patient classes across the code base. # (2) Or, we could use the main Patient class. However, a query on that, # like dbsession.query(Patient).filter(Patient.uuid.is_(None)), executes a # SELECT for all columns -- which may include columns that don't yet exist # in this database revision. # (3) Or, we could use SQLAlchemy Core, like this: # Some shorthand: # noinspection PyUnresolvedReferences patient_table = Patient.__table__ # type: Table # noinspection PyProtectedMember pk_col = patient_table.columns._pk # type: Column uuid_col = patient_table.columns.uuid # type: Column # SELECT patient._pk FROM patient WHERE patient.uuid IS NULL: pk_query = select([pk_col]).where(uuid_col.is_(None)) rows = dbsession.execute(pk_query) pks_needing_uuid = [row[0] for row in rows] # Determine new UUIDs: update_values = [ {"pk": _pk, "uuid": uuid.uuid4()} # generates a random UUID for _pk in pks_needing_uuid ] if update_values: # UPDATE patient SET uuid=%(uuid)s WHERE patient._pk = %(pk)s: update_statement = ( update(patient_table) .where(pk_col == bindparam("pk")) .values(uuid=bindparam("uuid")) ) # ... with many parameter pairs: # https://docs.sqlalchemy.org/en/14/tutorial/data_update.html dbsession.execute(update_statement, update_values) # COMMIT: dbsession.commit()
def update_one(self, url, **kwargs): with self._session() as session: values = {} for key, value in kwargs.items(): values[getattr(QueuedURL, key)] = value # TODO: rewrite as a join for clarity subquery = select([URLString.id]).where(URLString.url == url)\ .limit(1) query = update(QueuedURL).values(values)\ .where(QueuedURL.url_string_id == subquery) session.execute(query)
def update_one(self, url, **kwargs): with self._session() as session: values = {} for key, value in kwargs.items(): values[getattr(URL, key)] = value # TODO: rewrite as a join for clarity subquery = select([URLString.id]).where(URLString.url == url)\ .limit(1) query = update(URL).values(values)\ .where(URL.url_str_id == subquery) session.execute(query)
def update(self, table, data, condition=None): if isinstance(table, str): table = self.get_table(table) if condition is None: condition = build_pkey_condition(table, data) elif isinstance(condition, dict): condition = build_condition_from_dict(table, condition) data = preprocess_table_data(table, data) stmt = update(table).where(condition).values(data[0]) return self.execute(stmt)
def save(self, values, user, row=None): if not row: row = Slide() row.created = datetime.now() row.create_uid = user.id row.from_dict(values) row.updated = datetime.now() row.update_uid = user.id row.is_aktif = 'is_aktif' in values and values['is_aktif'] and 1 or 0 row.disabled = 'disabled' in values and values['disabled'] and 1 or 0 DBSession.add(row) DBSession.flush() if 'is_aktif' in values and values['is_aktif']: stmt = update(Slide).where(Slide.id!=row.id).\ values(is_aktif=0) DBSession.execute(stmt) DBSession.flush() return row
def update(self, url, increment_try_count=False, **kwargs): assert isinstance(url, str) with self._session() as session: values = {} url_id_map = URLStrDBRecord.get_map(session, [url]) url_str_id = url_id_map[url] for key, value in kwargs.items(): values[getattr(URLDBRecord, key)] = value if increment_try_count: values[URLDBRecord.try_count] = URLDBRecord.try_count + 1 query = update(URLDBRecord)\ .values(values)\ .where(URLDBRecord.url_str_id == url_str_id) session.execute(query)
def check_in(self, url, new_status, increment_try_count=True, **kwargs): with self._session() as session: values = { URL.status: new_status } for key, value in kwargs.items(): values[getattr(URL, key)] = value if increment_try_count: values[URL.try_count] = URL.try_count + 1 # TODO: rewrite as a join for clarity subquery = select([URLString.id]).where(URLString.url == url)\ .limit(1) query = update(URL).values(values)\ .where(URL.url_str_id == subquery) session.execute(query)
def downgrade(): connection = op.get_bind() for table_name in _table_names: # first update the adaptive_min_delta algo to adaptive algo table = sa.table(table_name, sa.Column('id', sa.Integer()), sa.Column('pairing_algorithm', EnumType(_NewPairingAlgorithm))) stmt = update(table).\ where(table.c.pairing_algorithm == bindparam('from_algo')).\ values(pairing_algorithm = bindparam('to_algo')) connection.execute(stmt, [{ 'from_algo': _NewPairingAlgorithm.adaptive_min_delta, 'to_algo': _NewPairingAlgorithm.adaptive}]) # then modify the enum type with op.batch_alter_table(table_name, naming_convention=convention) as batch_op: batch_op.alter_column('pairing_algorithm', type_=EnumType(_OldPairingAlgorithm), existing_type=EnumType(_NewPairingAlgorithm), existing_server_default=null(), existing_nullable=True)
def move_to(self, position): db.session.execute('SET CONSTRAINTS task_rank_key DEFERRED') query = ( expression.update(Task) .where(Task.rank >= position) .where(Task.id != self.id) .values(rank = Task.rank + 1) .returning( Task.id, Task.created_at, Task.updated_at, Task.completed_at, Task.rank, Task.description, ) ) results = db.session.execute(query) self.rank = position db.session.add(self) return results
def populate_json_files(self): with self._session() as session: query = session.query(File.ia_item_id, File.filename, File.job_id)\ .filter(File.filename.endswith('.json'))\ .filter(File.job_id.isnot(None)) for identifier, filename, job_id in query: json_id = filename.replace('.json', '') if session.query(JSONMetadata.id).filter_by(id=json_id).scalar(): continue response = yield self._api.download_item_file(identifier, filename) try: doc = json.loads(response.body.decode('utf-8', 'replace')) except ValueError: _logger.exception('JSON error!') continue url = doc.get('url') query = insert(JSONMetadata) values = { 'id': json_id, 'job_id': job_id, 'url': url, 'started_by': doc.get('started_by') } session.execute(query, [values]) if job_id and url: query = update(Job)\ .values({'url': url}).where(Job.id == job_id) session.execute(query) session.commit()
def release(self): with self._session() as session: query = update(URL).values({URL.status: Status.todo})\ .where(URL.status==Status.in_progress) session.execute(query)
def update_instance_vips(): update(table = instance_vip, whereclause="deleted = 0", values=dict(virtual_instance_id = instance_vip.c.instance_id)).execute()
def infoCallback(serverinfo): serverKey = "%s:%s" % (serverinfo['server_ip'],serverinfo['query_port']) if serverKey in allTimeouts: allTimeouts[serverKey].cancel() del allTimeouts[serverKey] if serverKey in numRetries: del numRetries[serverKey] #print serverinfo serverid = getServerId(serverinfo['server_ip'],serverinfo['query_port']) serverversion = serverinfo['version'] if serverversion == '1.0.0.0': gd = serverinfo['gamedata'].split('|') serverversion = gd[0] db.execute(insert(tbl_server_history).prefix_with('IGNORE'), id=serverid, date=tm, map=serverinfo['map'], numplayers=serverinfo['numplayers'], maxplayers=serverinfo['maxplayers'], numbots=serverinfo['numbots'], password=serverinfo['password'], tickrate=0, version=serverversion, status=1, ) db.execute( update(tbl_servers) .where ( tbl_servers.c.id==serverid) .values( last_sample=tm ) ) qc.startRulesQuery((serverinfo['server_ip'],serverinfo['query_port'])) donameinsert = False res = db.execute( select([tbl_server_name_history], and_( tbl_server_name_history.c.id==serverid, tbl_server_name_history.c.date_end=='0000-00-00 00:00:00' ) ) ) if res.rowcount == 0: donameinsert = True else: row = res.first() if row['server_name'].lower().strip() != serverinfo['server_name'].lower().strip(): donameinsert = True db.execute( update(tbl_server_name_history) .where( and_( tbl_server_name_history.c.id==serverid, tbl_server_name_history.c.date_end=='0000-00-00 00:00:00' ) ) .values( date_end=datetime.datetime.now() ) ) if donameinsert: db.execute(insert(tbl_server_name_history), id=serverid, date_end='0000-00-00 00:00:00', date_start=datetime.datetime.now(), server_name=serverinfo['server_name'], )