def delete_member(self, id):
"""
@param id: primary key (int) of document.
Query the document object, verify the flag `dt_del`. If setted,
that means that this document was deleted once, but it's index was not
successfull deleted. So we delete the document. If not, will try to
delete it's index and document. In case of failure, will SET all
columns to NULL and clear document, leaving only it's metadata.
"""
stmt1 = delete(self.entity.__table__).where(
self.entity.__table__.c.id_doc == id)
stmt2 = delete(self.file_entity.__table__).where(
self.file_entity.__table__.c.id_doc == id)
self.session.execute(stmt1)
self.session.execute(stmt2)
self.session.commit()
self.session.close()
if self.index.is_indexable:
Thread(target=self.async_delete_member,
args=(id, self.session)).start()
# Clear cache
cache.clear_collection_cache(self.base_name)
return True
def insert_messages(reader, session, japanese_module):
'''
retrieve new messages from reader limit MESSAGE_COUNT_PER_COMMIT
insert messages into database if in Japanese.
'''
mid = _insert_tmp_messages(reader, session)
# delete messages without need from tmp table
_mark_tmpmsg_from_known_senders(session)
_mark_tmpmsg_in_japanese(session, japanese_module)
stmt = delete(TmpMessage.__table__).where(TmpMessage.need == False)
session.execute(stmt)
# register new senders and new messages from tmp table.
for sender, in _get_new_tmp_senders(session):
session.add(Sender(sender))
session.flush()
_register_new_tmp_messages(session)
# delete all records from tmp table.
session.execute(delete(TmpMessage.__table__))
# record last registered mid into parameters table.
if mid:
session.merge(Parameter(LAST_MID_OF_INSERT_MESSAGE, mid))
def deleteItem(category_id=None, item_id=None, item_name=None):
if "username" not in login_session or login_session["username"] == "":
print "why"
return "fap"
redirect(url_for("showCategories"))
print request.method
if request.method == "POST":
deleted = None
print "hey1"
if item_id:
deleted = delete(Item).where(Item.id == item_id)
if item_name:
print "hey2"
deleted = delete(Item).where(Item.title == item_name)
session.execute(deleted)
session.commit()
flash("the Item was deleted")
return redirect(url_for("showCategories"))
if request.method == "GET":
item = None
if item_id:
item = session.query(Item).filter(Item.id == item_id).first()
if item_name:
item = session.query(Item).filter(Item.title == item_name).first()
return render_template("deleteitem.html", item=item)
def delete_entry(fav_id):
try:
delete(favorites_table, favorites_table.c.id == fav_id).execute()
except:
print 'There was an error deleting this favorite.'
return redirect(url_for('show_favorites'))
def downgrade(migrate_engine):
meta = MetaData()
meta.bind = migrate_engine
aggregates = Table('aggregates', meta, autoload=True)
aggregate_metadata = Table('aggregate_metadata', meta, autoload=True)
availability_zone = Column('availability_zone', String(255))
aggregates.create_column(availability_zone)
# Migrate data back
# NOTE(jhesketh): This needs to be done with individual inserts as multiple
# results in an update sub-query do not work with MySQL. See bug/1207956.
record_list = list(aggregates.select().execute())
for rec in record_list:
result = aggregate_metadata.select().where(
aggregate_metadata.c.key == 'availability_zone'
).where(
aggregate_metadata.c.aggregate_id == rec['id']
)
aggregates.update().values(
availability_zone=list(result.execute())[0]['value']
).where(
aggregates.c.id == rec['id']
).execute()
delete(aggregate_metadata,
aggregate_metadata.c.key == 'availability_zone').execute()
aggregates.c.availability_zone.alter(nullable=False)
def delete_member(self, id):
"""
@param id: primary key (int) of document.
Delete a "file type" record. Normally these records are linked
to "doc type" records. However, there are cases where these
records ("file type") are not associated with any "doc type",
so we've used this method to delete them.
"""
if isinstance(id, int):
# NOTE: Quando a operação DELETE envolve id_doc! By John Doe
stmt = delete(self.entity.__table__).where(
self.entity.__table__.c.id_doc == id)
else:
# NOTE: Quando a operação DELETE envolve id_file! By John Doe
stmt = delete(self.entity.__table__).where(
self.entity.__table__.c.id_file == id)
self.session.execute(stmt)
# NOTE: Now commits and closes session in the view instead of here
# flush() pushes operations to DB's buffer - DCarv
self.session.flush()
# NOTE: Clear cache! By John Doe
cache.clear_collection_cache(self.base_name)
return True
def tearDown(self):
table_name = self.conf[mk.TARGET_DB_TABLE]
guid_batch = self.conf['guid_batch']
with get_udl_connection() as conn:
table = conn.get_table(table_name)
try:
delete(table).where(table.c.guid_batch == guid_batch)
except Exception as e:
print('Exception -- ', e)
def test_delete_returning(self):
table1 = table(
'mytable', column('myid', Integer),
column('name', String(128)), column('description', String(128)))
d = delete(table1).returning(table1.c.myid, table1.c.name)
self.assert_compile(d,
'DELETE FROM mytable OUTPUT deleted.myid, '
'deleted.name')
d = delete(table1).where(table1.c.name == 'bar'
).returning(table1.c.myid,
table1.c.name)
self.assert_compile(d,
'DELETE FROM mytable OUTPUT deleted.myid, '
'deleted.name WHERE mytable.name = :name_1')
def opt_in(self, msg_hash):
"""Removes recipient of the email from the opt-out list"""
email = self.get_recipient(msg_hash)
if email:
o = self.opt_table
if self.has_opted_out(email):
sa.delete(o, o.c.email == email).execute()
clear_memo('r2.models.mail_queue.has_opted_out',
email)
clear_memo('r2.models.mail_queue.opt_count')
return (email, True)
else:
return (email, False)
return (None, False)
def drop_samples_fields(engine, test_suite_id, trans):
"""In the TestSuiteSampleFields, drop entries related to the test_suite_id.
This extra function is needed because in MySQL it can't sort out the forign
keys in the same table.
"""
samples_table = introspect_table(engine, 'TestSuiteSampleFields')
order_files = delete(samples_table,
and_(samples_table.c.TestSuiteID == test_suite_id,
samples_table.c.status_field.isnot(None)))
trans.execute(order_files)
order_files = delete(samples_table,
samples_table.c.TestSuiteID == test_suite_id)
trans.execute(order_files)
return samples_table
def deleteCategory(category_id):
if "username" not in login_session or login_session["username"] == "":
return redirect(url_for("showCategories"))
if request.method == "POST":
deletedCategory = delete(Category).where(Category.id == category_id)
deletedItems = delete(Item).where(Item.category_id == category_id)
session.execute(deletedItems)
session.execute(deletedCategory)
session.commit()
return redirect(url_for("showCategories"))
if request.method == "GET":
category = session.query(Category).filter(Category.id == category_id).first()
items = session.query(Item).filter(Item.category_id == category_id)
print category.id
return render_template("deletecategory.html", category=category, items=items)
def delete_files(self, member, files):
"""
Will delete files that are not present in document.
@param member: LBDoc_<base> object (mapped ORM entity).
@param files: List of files ids present in document.
"""
where_clause = [(self.file_entity.__table__.c.id_doc==member.id_doc)]
if len(files) > 0:
notin_clause = self.file_entity.__table__.c.id_file.notin_(files)
where_clause.append(notin_clause)
where_clause = and_(*where_clause)
stmt = delete(self.file_entity.__table__).where(where_clause)
else:
stmt = delete(self.file_entity.__table__).where(*where_clause)
self.session.execute(stmt)
def opt_in(self, msg_hash):
"""Removes recipient of the email from the opt-out list"""
email = self.get_recipient(msg_hash)
if email:
o = self.opt_table
if self.has_opted_out(email):
sa.delete(o, o.c.email == email).execute()
#clear caches
has_opted_out(email, _update = True)
opt_count(_update = True)
return (email, True)
else:
return (email, False)
return (None, False)
def deleteCategory(category_id):
if 'username' not in login_session or login_session['username'] == '':
return redirect(url_for('showCategories'))
if request.method == 'POST':
deletedCategory = delete(Category).where(Category.id == category_id)
deletedItems = delete(Item).where(Item.category_id == category_id)
session.execute(deletedItems)
session.execute(deletedCategory)
session.commit()
return redirect(url_for('showCategories'))
if request.method == 'GET':
category = session.query(Category).filter(Category.id == category_id).first()
items = session.query(Item).filter(Item.category_id == category_id)
print category.id
return render_template('deletecategory.html', category=category, items=items)
def delete_collection(self):
"""Delete database collection of objects. This method needs a valid JSON
query and a valid query path . Will query database objects, and update
each path (deleting the respective path). Return count of successes and
failures.
"""
collection=self.get_collection(render_to_response=False)
in_clause=tuple(map(self.mapper, collection))
stmt=delete(self.context.entity.__table__).where(
self.context.entity.__table__.c.id_error.in_(in_clause))
# TODO: Talvez seja necessário um "begin" e um "commit" aqui!
# By Questor
# self.context.session.begin()
# self.context.session.commit()
self.context.session.execute(stmt)
# NOTE: Tentar fechar a conexão de qualquer forma!
# -> Na criação da conexão "coautocommit=True"!
# By Questor
try:
if self.context.session.is_active:
self.context.session.close()
except:
pass
return Response('OK')
def setUpClass(cls):
from sqlalchemy import engine_from_config
engine = engine_from_config({'url': 'sqlite://'}, prefix='')
qry = open('monasca_api/tests/sqlite_alarm.sql', 'r').read()
sconn = engine.raw_connection()
c = sconn.cursor()
c.executescript(qry)
sconn.commit()
c.close()
cls.engine = engine
def _fake_engine_from_config(*args, **kw):
return cls.engine
cls.fixture = fixtures.MonkeyPatch(
'sqlalchemy.create_engine', _fake_engine_from_config)
cls.fixture.setUp()
metadata = MetaData()
cls.nm = models.create_nm_model(metadata)
cls._delete_nm_query = delete(cls.nm)
cls._insert_nm_query = (insert(cls.nm)
.values(
id=bindparam('id'),
tenant_id=bindparam('tenant_id'),
name=bindparam('name'),
type=bindparam('type'),
address=bindparam('address'),
created_at=bindparam('created_at'),
updated_at=bindparam('updated_at')))
def downgrade(migrate_engine):
meta = MetaData()
meta.bind = migrate_engine
aggregates = Table('aggregates', meta, autoload=True)
aggregate_metadata = Table('aggregate_metadata', meta, autoload=True)
operational_state = Column('operational_state', String(255))
aggregates.create_column(operational_state)
aggregates.update().values(operational_state=select(
[aggregate_metadata.c.value]).where(aggregates.c.id ==
aggregate_metadata.c.aggregate_id and aggregate_metadata.c.key ==
'operational_state')).execute()
delete(aggregate_metadata, aggregate_metadata.c.key == 'operational_state')
aggregates.c.operational_state.alter(nullable=False)
aggregate_hosts = Table('aggregate_hosts', meta, autoload=True)
aggregate_hosts.c.host.alter(unique=True)
def test_cascade_release_deletion(self):
release_component_table = self.meta.tables['release_components']
release_table = self.meta.tables['releases']
release_id = insert_table_row(
release_table,
{
'name': 'release_with_components',
'version': '2014.2.2-6.1',
'operating_system': 'ubuntu',
'state': 'available'
}
)
component_id = insert_table_row(
self.meta.tables['components'],
{'name': six.text_type(uuid.uuid4()), 'type': 'hypervisor'}
)
insert_table_row(
release_component_table,
{'release_id': release_id, 'component_id': component_id}
)
db.execute(
sa.delete(release_table).where(release_table.c.id == release_id))
deleted_plugin_components = db.execute(
sa.select([sa.func.count(release_component_table.c.id)]).
where(release_component_table.c.release_id == release_id)
).fetchone()[0]
self.assertEqual(deleted_plugin_components, 0)
def tearDown(self):
d = sql.delete(
self.storage.track_table,
self.storage.track_table.c.id > 0)
con = self.storage._get_connection()
con.execute(d)
self.storage._close_connection(con)
def delete(self):
self.logger.info("{SELF} deleting db entry".format(SELF=self))
with user_database.get_session(self, acquire=True) as session:
session.execute(delete(user_database.Metadata).where(
user_database.Metadata.id == self.db_id
))
self.db_id = None
def delete_member(self):
# NOTE: Obtêm o nome do índice textual na rota submetida!
# By Questor
nm_idx = self.request.matchdict['nm_idx']
self.single_member = True
try:
stmt = delete(self.entity.__table__).where(
self.entity.__table__.c.nm_idx == nm_idx)
except Exception as e:
raise LbException("Failed to format persistence object!", str(e))
try:
result = self.session.execute(stmt)
self.session.commit()
# NOTE: Manipula o cache conforme as regras dessa rota!
# By Questor
if result.rowcount > 0:
self.remove_item(nm_idx)
return result.rowcount
except Exception as e:
raise LbException("Failed to persist data!", str(e))
finally:
self.session.close()
def test_correlated(self):
table1, table2 = self.tables.mytable, self.tables.myothertable
# test a non-correlated WHERE clause
s = select([table2.c.othername], table2.c.otherid == 7)
self.assert_compile(
delete(table1, table1.c.name == s),
"DELETE FROM mytable "
"WHERE mytable.name = ("
"SELECT myothertable.othername "
"FROM myothertable "
"WHERE myothertable.otherid = :otherid_1"
")",
)
# test one that is actually correlated...
s = select([table2.c.othername], table2.c.otherid == table1.c.myid)
self.assert_compile(
table1.delete(table1.c.name == s),
"DELETE FROM mytable "
"WHERE mytable.name = ("
"SELECT myothertable.othername "
"FROM myothertable "
"WHERE myothertable.otherid = mytable.myid"
")",
)
def downgrade(migrate_engine):
meta = MetaData()
meta.bind = migrate_engine
aggregates = Table('aggregates', meta, autoload=True)
aggregate_metadata = Table('aggregate_metadata', meta, autoload=True)
availability_zone = Column('availability_zone', String(255))
aggregates.create_column(availability_zone)
# migrate data
aggregates.update().values(availability_zone=select(
[aggregate_metadata.c.value]).where(aggregates.c.id ==
aggregate_metadata.c.aggregate_id).where(aggregate_metadata.c.key ==
'availability_zone')).execute()
delete(aggregate_metadata,
aggregate_metadata.c.key == 'availability_zone').execute()
aggregates.c.availability_zone.alter(nullable=False)
def test_delete_stmt_with_comma_subquery_alias_join():
parent_ = sa.alias(product)
del_stmt = (
sa.delete(items)
.where(items.c.order_id == orders.c.id)
.where(orders.c.customer_id.in_(sa.select([customers.c.id]).where(customers.c.email.endswith("test.com"))))
.where(items.c.product_id == product.c.id)
.where(product.c.parent_id == parent_.c.id)
.where(parent_.c.id != hammy_spam.c.ham_id)
)
expected = """
DELETE FROM items
USING orders, products, products AS products_1, "ham, spam"
WHERE items.order_id = orders.id
AND orders.customer_id IN
(SELECT customers.id
FROM customers
WHERE (customers.email LIKE '%%' || 'test.com'))
AND items.product_id = products.id
AND products.parent_id = products_1.id
AND products_1.id != "ham, spam".ham_id"""
assert clean(compile_query(del_stmt)) == clean(expected)
def test_delete_stmt_joinedwhereclause1():
del_stmt = sa.delete(orders).where(orders.c.customer_id == customers.c.id)
expected = """
DELETE FROM orders
USING customers
WHERE orders.customer_id = customers.id"""
assert clean(compile_query(del_stmt)) == clean(expected)
def test_delete_stmt_simplewhereclause2():
del_stmt = sa.delete(customers).where(customers.c.email.endswith("test.com"))
expected = """
DELETE FROM customers
WHERE customers.email
LIKE '%%' || 'test.com'"""
assert clean(compile_query(del_stmt)) == clean(expected)
def calculate_tf_idf(session):
'''
calculate and update TF-IDF for all scores.
'''
all_sender_count = session.query(Sender).count()
session.execute(delete(Score.__table__))
session.execute("""
insert into
scores
select
sid,
words.wid,
1. * sum(message_words.count)
* :all_sender_count / sender_count as tfidf
from
words,
messages,
message_words
where
words.wid = message_words.wid
and message_words.mid = messages.mid
group by
sid,
words.wid
order by
sid,
message_words.wid,
tfidf desc
""", {'all_sender_count': all_sender_count})
def test_delete_stmt_on_alias():
parent_ = sa.alias(product)
del_stmt = sa.delete(product).where(product.c.parent_id == parent_.c.id)
expected = """
DELETE FROM products
USING products AS products_1
WHERE products.parent_id = products_1.id"""
assert clean(compile_query(del_stmt)) == clean(expected)
def test_delete_stmt_on_subquerycomma():
del_stmt = sa.delete(ham).where(ham.c.id.in_(sa.select([hammy_spam.c.ham_id])))
expected = """
DELETE FROM ham
WHERE ham.id IN
(SELECT "ham, spam".ham_id
FROM "ham, spam")"""
assert clean(compile_query(del_stmt)) == clean(expected)
def _cull(self):
# remove items in cache to make more room
cache = self._store
# remove items that have timed out
delete(
cache, cache.c.expires < datetime.now().replace(microsecond=0),
).execute()
# remove any items over the maximum allowed number in the cache
length = len(self)
if length >= self._max_entries:
cull = length if length < self._maxcull else self._maxcull
# get list of keys
keys = list(i[0] for i in select(
[cache.c.key], limit=cull * 2
).execute().fetchall())
# delete keys at random
delete(cache, cache.c.key.in_(sample(keys, cull))).execute()
def test_delete_against_cte_directly(self):
"""test #6464.
SQL-Server specific arrangement seems to allow
DELETE from a CTE directly.
"""
products = table("products", column("id"), column("price"))
cte = products.select().cte("pd")
assert "autocommit" not in cte.select()._execution_options
stmt = delete(cte)
eq_(stmt.compile().execution_options["autocommit"], True)
self.assert_compile(
stmt,
"WITH pd AS (SELECT products.id AS id, products.price AS price "
"FROM products) DELETE FROM pd",
)
eq_(stmt.compile().isdelete, True)
def update_factor_master(ds, **kwargs):
ref_date, this_date = process_date(ds)
flag = check_holiday(this_date)
if not flag:
return
tables = [Uqer, Gogoal, Experimental, RiskExposure]
meta = MetaData(bind=engine, reflect=True)
df = pd.DataFrame(
columns=['factor', 'source', 'alias', 'updateTime', 'description'])
for t in tables:
source = t.__table__.name
table = meta.tables[source]
columns = table.columns.keys()
columns = list(
set(columns).difference({
'trade_date', 'code', 'secShortName', 'exchangeCD',
'updateTime', 'COUNTRY'
}))
col_alias = [c + '_' + source for c in columns]
new_df = pd.DataFrame({
'factor': columns,
'source': [source] * len(columns),
'alias': col_alias
})
df = df.append(new_df)
query = delete(FactorMaster)
engine.execute(query)
df['updateTime'] = arrow.now().format('YYYY-MM-DD, HH:mm:ss')
df.to_sql(FactorMaster.__table__.name,
engine,
if_exists='append',
index=False)
async def vote_end_loop(self):
try:
with session_manager() as session:
now = time.time()
votes = session.execute(select(Votes.vote_id, Votes.author_id, Votes.guild_id, Votes.title, Votes.end_time)
.where(Votes.end_time < now)).all()
for v_id, a_id, g_id, title, end_time in votes:
if v_id in self.vote_manager.sent_votes.keys():
vote = self.vote_manager.get_vote_from_id(v_id)
results = await get_results(self.bot, vote)
embed = await make_result_embed(vote, results)
try:
if vote.chair:
try:
chair = await self.bot.fetch_user(vote.chair)
await chair.send(f"Your vote {title} has closed")
await chair.send(embed=embed)
except discord.Forbidden:
user = await self.bot.fetch_user(vote.author)
await user.send(f"Your vote {title} has closed")
await user.send(embed=embed)
else:
try:
user = await self.bot.fetch_user(vote.author)
await user.send(f"Your vote {title} has closed")
await user.send(embed=embed)
except discord.Forbidden:
guild = await self.bot.fetch_guild(vote.guild)
user = await self.bot.fetch_user(guild.owner_id)
await user.send(f"A vote in your guild titled {title} has closed and the chair is unavailable.")
await user.send(embed=embed)
session.execute(delete(Votes).filter_by(vote_id=vote.id))
session.commit()
self.vote_manager.cancel_sent_vote(vote.id)
except Exception as e:
session.execute(update(Votes).filter_by(vote_id=vote.id).values(end_time=time.time() + 86400))
session.commit()
logger.error(f"error in vote loop: {e}")
except Exception as e:
logger.error("Exception in outer vote loop: %s" % e, exc_info=e)
def update_dummy_index_components(ds, **kwargs):
ref_date, this_date = process_date(ds)
flag = check_holiday(this_date)
if not flag:
return
query = select([IndexComponent]).where(
and_(IndexComponent.trade_date == '2018-05-04',
IndexComponent.indexCode.in_([900300, 900905])))
df = pd.read_sql(query, con=engine)
df['trade_date'] = ref_date
query = delete(IndexComponent).where(
and_(IndexComponent.trade_date == ref_date,
IndexComponent.indexCode.in_([900300, 900905])))
engine.execute(query)
df.to_sql(IndexComponent.__table__.name,
engine,
index=False,
if_exists='append')
def test_correlated(self):
table1, table2 = self.tables.mytable, self.tables.myothertable
# test a non-correlated WHERE clause
s = select([table2.c.othername], table2.c.otherid == 7)
self.assert_compile(
delete(table1, table1.c.name == s), 'DELETE FROM mytable '
'WHERE mytable.name = ('
'SELECT myothertable.othername '
'FROM myothertable '
'WHERE myothertable.otherid = :otherid_1'
')')
# test one that is actually correlated...
s = select([table2.c.othername], table2.c.otherid == table1.c.myid)
self.assert_compile(
table1.delete(table1.c.name == s), 'DELETE FROM mytable '
'WHERE mytable.name = ('
'SELECT myothertable.othername '
'FROM myothertable '
'WHERE myothertable.otherid = mytable.myid'
')')
def _drop_suite(trans, name, engine):
"""Drop the suite name.
This patches up the suite description tables for Order Fields,
Machine Fields, Run Fields and Sample Fields.
After than remove the suite directly from the TestSuite table.
"""
test_suite = introspect_table(engine, 'TestSuite')
test_suite_id = trans.execute(
select([test_suite.c.ID]).where(test_suite.c.Name == name)) \
.scalar()
drop_fields(engine, test_suite_id, 'TestSuiteOrderFields', trans)
drop_fields(engine, test_suite_id, 'TestSuiteMachineFields', trans)
drop_fields(engine, test_suite_id, 'TestSuiteRunFields', trans)
drop_samples_fields(engine, test_suite_id, trans)
trans.execute(delete(test_suite).where(test_suite.c.Name == name))
def db_connect_impl(tables_class, url, readonly):
# timeout unused connections
if url.startswith("sqlite://"):
engine_args = {}
else:
engine_args = {
"pool_size": 10,
"max_overflow": 15,
"pool_timeout": 300,
"pool_recycle": 300
}
engine = create_engine(url, **engine_args)
session = scoped_session(sessionmaker(bind=engine))
db = DatabaseConnection()
db.engine = engine
db.metadata = MetaData()
# note: db.session.remove() must be called at the end of a request
# or when a thread context using a session exits
db.session = session
if readonly:
db.session.flush = _abort_flush
db.tables = tables_class(db.metadata)
db.delete_all = lambda: db.tables.drop(db.engine)
db.disconnect = lambda: engine.dispose()
db.delete = lambda table: db.session.execute(delete(table))
db.url = url
db.kind = lambda: url.split(":")[0]
db.health = lambda: db_health(db)
db.conn = db.session.bind.connect()
if url.startswith("sqlite:"):
db.conn.connection.create_function('REGEXP', 2, regexp)
path = url[len("sqlite:///"):]
if path and not os.access(path, os.W_OK):
logging.warning("database at %s not writable" % url)
return db
def setandcancelreminder(self,messege):
if 'label' in messege:
if messege['label']=='cancelreminder':
self.bookname=[]
if 'barcode' in messege:
userid=messege['barcode'].lower()
k=messege['bookname'].lower()
s=select([self.BookDetail.c.Author,self.BookDetail.c.Bookname]).where(and_(self.BookDetail.c.Bookname==k))
result=self.connection.execute(s)
for i in result:
k=select([self.Reminder.c.Bookname]).where(and_(self.Reminder.c.Bookname==k,self.Reminder.c.Author==i.Author,self.Reminder.c.Barcodeno==userid))
res=self.connection.execute(k)
for j in res:
self.bookname.append(j.Bookname)
if len(self.bookname)==0:
s=self.connection.execute(self.Reminder.insert().values(Barcodeno=userid,Bookname=i.Bookname,Author=i.Author))
k=select([self.Reminder.c.Bookname]).where(and_(self.Reminder.c.Bookname==k,self.Reminder.c.Author==i.Author,self.Reminder.c.Barcodeno==userid))
res=self.connection.execute(k)
for j in res:
self.bookname.append(j.Bookname)
self.response={}
self.response['response']='Reminder set'
#self.response['response']=self.bookname
else:
s=delete(self.Reminder,self.Reminder.c.Bookname==k,self.Reminder.c.Barcodeno==userid)
self.connection.execute(s)
self.response={}
self.response['response']='reminder removed'
async def setpermission(msg: Message) -> None:
room = msg.parametrized_room
if len(msg.args) != 3:
return
command = msg.args[0]
if command not in Command.get_rank_editable_commands():
return
rank = msg.args[2]
if rank not in PERMISSION_ROLES:
return
rank = cast(Role | Literal["default"], rank)
db = Database.open()
with db.get_session() as session:
if rank == "default":
stmt = delete(d.CustomPermissions).filter_by(roomid=room.roomid,
command=command)
session.execute(stmt)
else:
session.add(
d.CustomPermissions(roomid=room.roomid,
command=command,
required_rank=rank))
await room.send_modnote("PERMISSIONS", msg.user,
f"set the required rank for {command} to {rank}")
try:
page = int(msg.args[1])
except ValueError:
page = 1
await msg.user.send_htmlpage("permissions",
room,
page,
scroll_to_top=False)
def update_uqer_market(ds, **kwargs):
ref_date, this_date = process_date(ds)
flag = check_holiday(this_date)
if not flag:
return
df = api.MktEqudGet(tradeDate=ref_date)
df.rename(columns={
'tradeDate': 'trade_date',
'ticker': 'code'
},
inplace=True)
df.code = df.code.astype(int)
del df['secID']
query = delete(Market).where(Market.trade_date == this_date)
engine.execute(query)
data_info_log(df, Market)
format_data(df, format='%Y-%m-%d')
df.to_sql(Market.__table__.name, engine, index=False, if_exists='append')
def insert_readiness_data(df_readiness_summary, days_back=7):
start = app.session.query(func.max(ouraReadinessSummary.report_date))
start = '1999-01-01' if start[0][0] is None else datetime.strftime(
start[0][0] - timedelta(days=days_back), '%Y-%m-%d')
# Delete latest dates records from db to ensure values are being overridden from api pull
try:
app.server.logger.debug(
'Deleting >= {} records from oura_readiness_summary'.format(start))
app.session.execute(
delete(ouraReadinessSummary).where(
ouraReadinessSummary.summary_date >= start))
app.session.commit()
except BaseException as e:
app.server.logger.error(e)
app.session.remove()
app.server.logger.debug('Inserting oura readiness summary')
df_readiness_summary.to_sql('oura_readiness_summary',
engine,
if_exists='append',
index=True)
def remove_rfr_message(self, guild_id: int, channel_id: int, message_id: int):
"""
Removes an rfr message from the rfr message database, and also removes all emoji-role combos as part of it.
:param guild_id: Guild ID of the rfr message
:param channel_id: Channel ID of the rfr message
:param message_id: Message ID of the rfr message
:return:
"""
emoji_role_id = self.get_rfr_message(guild_id, channel_id, message_id)
if not emoji_role_id:
return
else:
self.remove_rfr_message_emoji_roles(emoji_role_id[3])
with session_manager() as session:
delete_sql = delete(GuildRFRMessages) \
.where(and_(and_(
GuildRFRMessages.guild_id == guild_id,
GuildRFRMessages.channel_id == channel_id),
GuildRFRMessages.message_id == message_id))
session.execute(delete_sql)
session.commit()
def __delete_expired_tokens_account(account, session=None):
""""
Deletes expired tokens from the database.
:param account: Account to delete expired tokens.
:param session: The database session in use.
"""
stmt_select = select(models.Token) \
.where(and_(models.Token.expired_at < datetime.datetime.utcnow(),
models.Token.account == account)) \
.with_for_update(skip_locked=True)
result_select = session.execute(stmt_select)
tokens = []
for t in result_select.columns('token'):
tokens.append(t.token)
for t in chunks(tokens, 100):
stmt_delete = delete(models.Token) \
.where(models.Token.token.in_(t)) \
.prefix_with("/*+ INDEX(TOKENS_ACCOUNT_EXPIRED_AT_IDX) */")
session.execute(stmt_delete)
def update(
self,
vals: Dict[str, Any] = None,
delete: bool = False,
check_exists: bool = False) -> None:
"""Update (multiple) values in database, based on unique row, field, value, and primary keys(s)
- key must either be passed in manually or exist in current table's df"""
t, keys = self.dbtable, self.keys
if len(keys) == 0:
raise AttributeError('Need to set keys before update!')
session = db.session
cond = [getattr(t, pk) == keys[pk] for pk in keys] # list of multiple key:value pairs for AND clause
if not delete:
if vals is None:
raise AttributeError('No values to update!')
sql = sa.update(t).values(vals).where(and_(*cond))
print(sql)
else:
sql = sa.delete(t).where(and_(*cond)) # kinda sketch to even have this here..
if not check_exists:
db.safe_execute(sql)
else:
# Check if row exists, if not > create new row object, update it, add to session, commit
q = session.query(t).filter(and_(*cond))
func = session.query(literal(True)).filter(q.exists()).scalar
exists = db.safe_func(func)
if not exists:
e = t(**keys, **vals)
session.add(e)
else:
db.safe_execute(sql)
return db.safe_commit() # True if transaction succeeded
def modificaOggetto(mioId_ogg, mioProdotto, miaQuantita):
engine = db.create_engine('sqlite:///easyFindDB.db')
connection = engine.connect()
metadata = db.MetaData()
prodotto = db.Table('prodotto',
metadata,
autoload=True,
autoload_with=engine)
oggetto = db.Table('oggetto',
metadata,
autoload=True,
autoload_with=engine)
idogg = db.select([
prodotto.columns.id
]).where(prodotto.columns.nome_prodotto == mioProdotto.upper())
risultato = connection.execute(idogg).fetchall()
idProd = risultato[0][0]
print('----mioProdotto ' + mioProdotto)
print('----miaQuantita ' + miaQuantita)
print(idProd)
if int(miaQuantita) > 0:
modifica = db.update(oggetto).values(quantita=int(miaQuantita)).where(
db.and_(oggetto.columns.id_prodotto == idProd,
oggetto.columns.id_oggetto == mioId_ogg))
connection.execute(modifica)
print('ho modificato!')
else:
elimina = db.delete(oggetto).where(
db.and_(oggetto.columns.id_prodotto == idProd,
oggetto.columns.id_oggetto == mioId_ogg))
connection.execute(elimina)
print('ho eliminato!')
return redirect("/Home_page")
def update_sw1_adj_industry(ds, **kwargs):
ref_date, this_date = process_date(ds)
flag = check_holiday(this_date)
if not flag:
return
industry = '申万行业分类'
query = select([Industry]).where(
and_(Industry.trade_date == ref_date, Industry.industry == industry))
df = pd.read_sql(query, engine)
df['industry'] = '申万行业分类修订'
df['industryID'] = 10303330102
df['industrySymbol'] = '440102'
ids = df[df.industryName2 == '证券'].index
df.loc[ids, 'industryName1'] = df.loc[ids, 'industryName2']
df.loc[ids, 'industryID1'] = df.loc[ids, 'industryID2']
ids = df[df.industryName2 == '银行'].index
df.loc[ids, 'industryName1'] = df.loc[ids, 'industryName2']
df.loc[ids, 'industryID1'] = df.loc[ids, 'industryID2']
ids = df[df.industryName2 == '保险'].index
df.loc[ids, 'industryName1'] = df.loc[ids, 'industryName2']
df.loc[ids, 'industryID1'] = df.loc[ids, 'industryID2']
ids = df[df.industryName2 == '多元金融'].index
df.loc[ids, 'industryName1'] = df.loc[ids, 'industryName2']
df.loc[ids, 'industryID1'] = df.loc[ids, 'industryID2']
query = delete(Industry).where(
and_(Industry.trade_date == ref_date,
Industry.industry == industry + "修订"))
engine.execute(query)
df.to_sql(Industry.__table__.name, engine, if_exists='append', index=False)
async def remove_procedure(self, ctx):
await ctx.message.delete()
try:
msg1 = await ctx.channel.send(
"Quelle matière voulez-vous supprimer?")
self.traces.append(msg1)
resp1 = await self.bot.wait_for("message",
timeout=60,
check=check_matter)
await msg1.delete()
await resp1.delete()
msg2 = await ctx.channel.send("De quel jour? (Ex: JJ/MM)")
self.traces.append(msg2)
resp2 = await self.bot.wait_for("message",
timeout=120,
check=check_date)
await msg2.delete()
await resp2.delete()
except asyncio.TimeoutError:
await self.author.send(
f"{self.author.mention} Vous avez mis trop de temps à répondre "
f"ou vos réponses n'étaient pas correct. {self.custom_response}"
)
else:
model = Agenda if self.table == 'agenda' else Planning
database.execute(
delete(model).where(model.matter == self.answers["matter"],
model.date == self.answers["date"]))
await self.update_data()
finally:
for msg in self.traces:
try:
await msg.delete()
except discord.errors.NotFound:
pass
def test_load_xml(self):
"""
Common case test of load_xml.
GOAL: Check right of insert rows.
DESCRIPTION:
Test input xml file contains different errors of format in lines.
We push this file in table. Then we check right of insert.
"""
name_db = 'test_db.sqlite'
name_table = 'test_xml'
name_csv = 'test_input.xml'
answer = [('A-120xml', datetime.datetime(2017, 1, 23, 0, 0),
decimal.Decimal('27.0000000000')),
('A-125xml', datetime.datetime(2017, 1, 23, 0, 0),
decimal.Decimal('505.0000000000')),
('B-220xml', datetime.datetime(2017, 1, 26, 0, 0),
decimal.Decimal('150.0000000000')),
('B-220xml', datetime.datetime(2017, 1, 23, 0, 0),
decimal.Decimal('900.0000000000')),
('B-220xml', datetime.datetime(2017, 1, 23, 0, 0),
decimal.Decimal('30.0000000000')),
('B-220xml', datetime.datetime(2017, 1, 23, 0, 0),
decimal.Decimal('50.0000000000'))]
db = csv2db.DataBase(name_db, name_table)
db.load_xml(name_csv)
with db.engine.connect() as conn:
meta = sqlalchemy.MetaData(db.engine)
users_table = sqlalchemy.Table(db.name_tab, meta, autoload=True)
records = conn.execute(
sqlalchemy.select([
users_table.c.customer, users_table.c.posting_date,
users_table.c.amount
])).fetchall()
conn.execute(sqlalchemy.delete(users_table))
self.assertEqual(records, answer, 'Error! Push rows are not correct!')
def test_bulk_delete_future_synchronize_fetch(self):
sess = self._fixture_data()
temps = sess.execute(select(Report)).scalars().all()
eq_(set(t.temperature for t in temps), {80.0, 75.0, 85.0})
sess.execute(
delete(Report)
.filter(Report.temperature >= 80)
.execution_options(synchronize_session="fetch")
)
eq_(
set(
row.temperature
for row in sess.execute(select(Report.temperature))
),
{75.0},
)
# test synchronize session as well
for t in temps:
assert inspect(t).deleted is (t.temperature >= 80)
def delete_messages(messages, session=None):
"""
Delete all messages with the given IDs, and archive them to the history.
:param messages: The messages to delete as a list of dictionaries.
"""
message_condition = []
for message in messages:
message_condition.append(Message.id == message['id'])
if len(message['payload']) > 4000:
message['payload_nolimit'] = message.pop('payload')
try:
if message_condition:
stmt = delete(Message).\
prefix_with("/*+ index(messages MESSAGES_ID_PK) */", dialect='oracle').\
where(or_(*message_condition)).\
execution_options(synchronize_session=False)
session.execute(stmt)
session.bulk_insert_mappings(MessageHistory, messages)
except IntegrityError as e:
raise RucioException(e.args)
def delete_data_5():
"""A function to delete data from a table"""
print(
"You want to delete all data from a table. Here is a list with all the tables in the SocialDatabase: "
)
tables = engine.table_names()
for table in tables:
print(f"* {table}")
"\n"
user_input = input("Please name the table you want to delete: ")
newTable = sqlalchemy.Table(user_input,
metadata,
autoload=True,
autoload_with=engine)
query = sqlalchemy.delete(newTable)
result = connection.execute(query)
question_afterwards()
return
async def test_re_verify():
with session_manager() as session:
test_config = dpytest.get_config()
guild = test_config.guilds[0]
role = dpytest.back.make_role("testRole", guild, id_num=555)
member = test_config.members[0]
await dpytest.add_role(member, role)
test_verified_email = VerifiedEmails(u_id=member.id, email='*****@*****.**')
test_role = Roles(s_id=guild.id, r_id=role.id, email_suffix='egg.com')
session.add(test_verified_email)
session.add(test_role)
session.commit()
await dpytest.message(koalabot.COMMAND_PREFIX + "reVerify <@&555>")
assert role not in member.roles
blacklisted = session.execute(select(ToReVerify).filter_by(u_id=member.id)).all()
assert blacklisted
assert dpytest.verify().message().content(
"That role has now been removed from all users and they will need to re-verify the associated email.")
session.delete(test_verified_email)
session.delete(test_role)
session.execute(delete(ToReVerify).filter_by(u_id=member.id))
session.commit()
async def delete_cluster(
self,
primary_group: GroupID,
standard_groups: List[GroupID],
all_group: GroupID,
cluster_id: PositiveInt,
) -> None:
async with self.engine.acquire() as conn:
clusters_list: List[
Cluster] = await self._clusters_from_cluster_ids(
conn, {cluster_id})
if not clusters_list:
raise ClusterNotFoundError(cluster_id)
the_cluster = clusters_list[0]
this_user_cluster_access_rights = compute_this_user_cluster_access_rights(
the_cluster, primary_group, standard_groups, all_group)
if not this_user_cluster_access_rights.delete:
raise ClusterAccessForbidden(
cluster_id, msg="Administrator rights required.")
await conn.execute(
sa.delete(clusters).where(clusters.c.id == cluster_id))
def limparBanco():
retorno = []
deletar = []
a = select([stats_table])
for row in a.execute():
retorno.append(row)
for i, val in enumerate(retorno):
if len(retorno[i][5]) < 4:
deletar.append(retorno[i][0])
elif re.search(r'[A-z]', retorno[i][5]):
#print(retorno[i][5])
deletar.append(retorno[i][0])
else:
#print(retorno[i][5])
pass
#deletar.append(retorno[i][0])
for d in deletar:
a = delete(stats_table).where(stats_table.c.id == d)
result = conn.execute(a)
print("deletado:", d)
def update_dimension_table(output: DIMENSION_LIST, opts: Namespace, table: Table, table_key: str, key_match: List[str])\
-> bool:
"""
Update the supplied dimension table, removing all existing records
:param output: list of dimension entries
:param opts: input options
:param table: table to be updated
:param table_key: key to use for removing existing entries
:param key_match: list of key roots (delete all keys that start with this)
:return: Success indicator
"""
for km in key_match:
q = delete(table).where(table.c[table_key].startswith(
km.replace('\\', '\\\\')))
ndel = opts.tables.crc_connection.execute(q).rowcount
if ndel > 0:
print("{} {} {} deleted".format(ndel, table,
pluralize(ndel, "record")))
nins = opts.tables.crc_connection.execute(table.insert(),
[e._freeze()
for e in output]).rowcount
print("{} {} {} inserted".format(nins, table, pluralize(nins, "record")))
return True
def test_delete_against_user_textual_cte(self):
"""test #6464.
Test the user's exact arrangement.
"""
q = select(
text("name, date_hour, "
"ROW_NUMBER() OVER(PARTITION BY name, date_hour "
"ORDER BY value DESC)"
" AS RN FROM testtable"))
cte = q.cte("deldup")
stmt = delete(cte, text("RN > 1"))
eq_(stmt.compile().execution_options["autocommit"], True)
self.assert_compile(
stmt,
"WITH deldup AS (SELECT name, date_hour, ROW_NUMBER() "
"OVER(PARTITION BY name, date_hour ORDER BY value DESC) "
"AS RN FROM testtable) DELETE FROM deldup WHERE RN > 1",
)
eq_(stmt.compile().isdelete, True)
def _drop_least_recently_used_item(self) -> None:
with self._sessionmaker.begin() as session:
query_for_least_recently_used_entry = sa.select(self.CacheEntry).order_by(
self.CacheEntry.last_used.asc()
)
oldest_cache_item = (
session.execute(query_for_least_recently_used_entry).scalars().first()
)
if not oldest_cache_item:
self._purge_cache_dir_content()
return
self._delete_cached_result(oldest_cache_item)
delete_query = sa.delete(self.CacheEntry).where(
self.CacheEntry.fingerprint_key == oldest_cache_item.fingerprint_key
)
session.execute(delete_query)
logger.debug(
f"Deleted item with fingerprint "
f"'{oldest_cache_item.fingerprint_key}' to free space."
)
async def isolation_read_sa_transaction(conn, conn2):
await conn.execute(sa.insert(users).values(id=1, name='test1'))
t1 = await conn.begin()
where = users.c.id == 1
q_user = users.select().where(where)
user = await (await conn.execute(q_user)).fetchone()
assert await (await conn2.execute(q_user)).fetchone() == user
await conn.execute(sa.update(users).values({'name': 'name2'}).where(where))
t2 = await conn2.begin()
assert await (await conn2.execute(q_user)).fetchone() == user
await t1.commit()
await conn2.execute(sa.update(users).values(user).where(where))
await t2.commit()
assert await (await conn2.execute(q_user)).fetchone() == user
await conn.execute(sa.delete(users))
assert len(await (await conn.execute(users.select())).fetchall()) == 0
def remove_inactive_hosts(start_time=None):
if start_time is None:
start_time = time.time()
sys.stderr.write("remove_inactive_hosts: starting after " + str(time.time() - start_time) + " seconds\n")
if USING_SUPERVISOR:
from docassemble.base.config import hostname
#from docassemble.webapp.app_object import app
from docassemble.webapp.db_object import db
from docassemble.webapp.core.models import Supervisors
from sqlalchemy import select, delete
to_delete = set()
for host in db.session.execute(select(Supervisors)).scalars():
if host.hostname == hostname:
continue
try:
socket.gethostbyname(host.hostname)
server = xmlrpc.client.Server(host.url + '/RPC2')
server.supervisor.getState()
except:
to_delete.add(host.id)
for id_to_delete in to_delete:
db.session.execute(delete(Supervisors).filter_by(id=id_to_delete))
db.session.commit()
sys.stderr.write("remove_inactive_hosts: ended after " + str(time.time() - start_time) + " seconds\n")
def update_uqer_universe_cyb(ds, **kwargs):
ref_date, this_date = process_date(ds)
flag = check_holiday(this_date)
if not flag:
return
query = delete(Universe).where(
and_(Universe.trade_date == this_date, Universe.universe == 'cyb'))
engine.execute(query)
query = select([IndexComponent.trade_date, IndexComponent.code]).where(
and_(IndexComponent.trade_date == this_date,
IndexComponent.indexCode == 399006))
df = pd.read_sql(query, engine)
if df.empty:
return
df['universe'] = 'cyb'
data_info_log(df, Universe)
format_data(df)
df.to_sql(Universe.__table__.name, engine, index=False, if_exists='append')
