async def reminderloop(self):
async with aiosqlite3.connect(databasedir) as db:
data = await db.execute('SELECT timeExecuted, event FROM reminders'
)
rows = await data.fetchall()
high = []
medium = []
low = []
task_queue_data = [low, medium, high]
for item in rows:
importance, data = time_check(
item[1], datetime.timedelta(minutes=1),
datetime.datetime.now(),
datetime.datetime.strptime(item[0], '%Y-%m-%d %H:%M:%S.%f'))
if data == None:
continue
else:
task_queue_data[importance].append(data)
task_queue = high + medium + low
async with aiosqlite3.connect('idiotbot.db') as db:
for task in task_queue:
data = await db.execute(
'SELECT author, event, channel, timeExecuted FROM reminders WHERE event=?',
(task, ))
rows = await data.fetchall()
rows = rows[0]
await self.send_message(
rows[0], rows[1], rows[2],
datetime.datetime.strptime(rows[3],
'%Y-%m-%d %H:%M:%S.%f'))
await db.execute(
'DELETE FROM reminders WHERE event = ? AND timeExecuted = ?',
(rows[1], rows[3]))
await db.commit()
async def delete_one(req,name):
async with aiosqlite3.connect(app.config.DB_NAME) as db:
async with db.execute("delete from user where username=?", (name,)):
await db.commit()
log_text=f'finish delete from table: name: {name}'
logger.info(log_text)
return response.text(log_text)
async def test_connect_context_manager(loop, db):
"""
上下文支持
"""
async with aiosqlite3.connect(db, loop=loop) as conn:
assert not conn.closed
assert conn.closed
async def update_rate(req,name,rate):
async with aiosqlite3.connect(app.config.DB_NAME) as db:
async with db.execute("update user set rate=? where username=?", (rate, name)):
await db.commit()
log_text=f'finish update table: name: {name}, rate: {rate}'
logger.info(log_text)
return response.text(log_text)
async def update_pwd(req,name,password):
async with aiosqlite3.connect(app.config.DB_NAME) as db:
async with db.execute("update user set password=? where username=?", (password, name)):
await db.commit()
log_text=f'finish update table: name: {name}, password: {password}'
logger.info(log_text)
return response.text(log_text)
async def add_user(req,name,password,rate):
async with aiosqlite3.connect(app.config.DB_NAME) as db:
async with db.execute("insert into user values(?,?,?)", (name, password, rate)):
await db.commit()
log_text=f'finish insert into table: name: {name}, password: {password}, rate: {rate}'
logger.info(log_text)
return response.text(log_text)
async def main():
global rate,capacity
if sys.argv[1]=='-d':
rate = 1000000
else:
rate = int(sys.argv[1])
capacity = rate
async with aiosqlite3.connect("user.db") as db:
print("清除数据")
await db.execute("drop table if exists user")
await db.commit()
await db.execute('''create table user
(usrname test primary key not null,
usrpassword text not null,
cur_amount integer not null,
last_time integer not null);''')
await db.execute(f"insert into user (usrname,usrpassword,cur_amount,last_time) \
values ('abc','123',{capacity!r},{int(time.time())!r})")
await db.execute(f"insert into user (usrname,usrpassword,cur_amount,last_time) \
values ('efg','321',{capacity!r},{int(time.time())!r})")
await db.commit()
while True:
confirm = await asyncio.start_server(handle_confirm,'127.0.0.1',6666)
async with confirm:
await confirm.serve_forever()
def test_connect_execute_error(db, loop):
"""
测试错误
"""
conn = yield from aiosqlite3.connect(db, loop=loop, check_same_thread=True)
with pytest.raises(aiosqlite3.OperationalError):
yield from conn.execute('sdfd')
def test_connect_sync_setter(db):
"""
测试connect的setter
"""
conn = yield from aiosqlite3.connect(database=db,
echo=True,
check_same_thread=True)
def dict_factory(cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
def unicode_str(x):
return unicode(x, "utf-8", "ignore")
assert not conn.autocommit
conn.isolation_level = None
assert conn.autocommit
assert conn.row_factory is None
assert conn.text_factory is str
conn.row_factory = dict_factory
assert conn.row_factory is dict_factory
conn.text_factory = unicode_str
assert conn.text_factory is unicode_str
yield from conn.close()
def conn(loop, db):
"""
生成一个连接
"""
coro = aiosqlite3.connect(database=db, echo=True)
connection = loop.run_until_complete(coro)
yield connection
loop.run_until_complete(connection.close())
def test_default_loop(loop, db):
"""
测试连接对象的loop设置
"""
asyncio.set_event_loop(loop)
conn = yield from aiosqlite3.connect(db)
assert conn.loop is loop
yield from conn.close()
async def save(self):
async with aiosqlite3.connect(databasedir) as db:
await db.execute(
'INSERT INTO queues VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
(self.channel_id, self.guild_id, self.author_id,
self.song_name, self.url, self.song_author, self.song_likes,
self.song_length, self.queue_id))
await db.commit()
async def tag(self, ctx, *, tag=None):
async with aiosqlite3.connect('tags.db') as db:
c = await db.execute('SELECT * FROM tags WHERE name=? AND guild_id=?', (tag, ctx.guild.id))
data = await c.fetchone()
if data is None:
await ctx.send(f'No tag named **{tag}**.')
else:
await ctx.send(data[1])
def test_custom_executor(loop, db, executor):
conn = yield from aiosqlite3.connect(db, executor=executor, loop=loop)
assert conn._executor is executor
cur = yield from conn.execute('SELECT 10;')
(resp, ) = yield from cur.fetchone()
yield from conn.close()
assert resp == 10
assert conn.closed
def go(**kwargs):
if 'database' in kwargs:
new_db = kwargs['database']
del kwargs['database']
else:
new_db = db
conn = yield from aiosqlite3.connect(database=new_db, loop=loop, echo=True, **kwargs)
conns.append(conn)
return conn
async def tag_info(self, ctx, *, name:str):
async with aiosqlite3.connect('tags.db') as db:
c = await db.execute('SELECT * FROM tags WHERE guild_id=? AND name=?',
(ctx.guild.id, name))
data = c.fetchone()
e = discord.Embed(title=data[0],
description=f'**Name:** {data[0]} \n **Author**: {self.bot.get_user(data[2]).mention}',
color=green)
await ctx.send(embed=e)
async def note_get(self, ctx, user: discord.Member):
async with aiosqlite3.connect('idiotbot.db') as db:
cursor = await db.execute('SELECT * FROM notes')
e = discord.Embed(title=f"Notes for {user.name}",
description=f"Notes for {user.name}",
color=0x7ae19e)
for row in cursor:
e.add_field(name=f"Note for {user.name}", value=row[1])
await ctx.send(embed=e)
async def mute(self, ctx, member: discord.Member):
async with aiosqlite3.connect('idiotbot.db') as db:
c = await db.execute('SELECT * FROM mutes WHERE guild_id=?',
(ctx.guild.id, ))
try:
guild_id, role_id = await c.fetchone()
except TypeError:
return await ctx.send('No mute role in this server.')
role = self.bot.get_guild(guild_id).get_role(role_id)
if role is None:
return await ctx.send('You deleted the mute role you idiot!')
await member.add_roles(role)
async with aiosqlite3.connect('idiotbot.db') as db:
await db.execute('INSERT INTO Muted VALUES (?, ?, ?)',
(member.id, None, ctx.guild.id))
await db.commit()
e = discord.Embed(title='Muted',
description=f'Muted {member.mention}.',
color=green)
await ctx.send(embed=e)
async def get_user(req):
userlist = list()
async with aiosqlite3.connect(app.config.DB_NAME) as db:
async with db.execute("select username, password, rate from user") as cursor:
logger.info('get all user info:')
row = await cursor.fetchone()
while row:
user = {'name': row[0], 'password': row[1], 'rate': row[2]}
logger.info(f'{user}')
userlist.append(user)
row = await cursor.fetchone()
return response.json(userlist)
async def mute_role_remove(self, ctx):
async with aiosqlite3.connect('idiotbot.db') as db:
c = await db.execute('SELECT role_id FROM mutes WHERE guild_id=?',
(ctx.guild.id, ))
role_id, = await c.fetchone()
role = ctx.guild.get_role(role_id)
await db.execute('DELETE FROM mutes WHERE role_id=?', (role_id, ))
await db.commit()
e = discord.Embed(title='Removed Role',
description=f'Removed Mute Role: {role.mention}',
color=green)
await ctx.send(embed=e)
async def mute_role_add(self, ctx, role: discord.Role):
async with aiosqlite3.connect('idiotbot.db') as db:
await db.execute('DELETE FROM mutes WHERE guild_id=?',
(ctx.guild.id, ))
await db.execute('INSERT INTO mutes VALUES (?, ?)',
(ctx.guild.id, role.id))
await db.commit()
e = discord.Embed(
title='Set Mute Role',
description=
f'Set mute role for **{ctx.guild.name}** to {role.mention}.',
color=green)
await ctx.send(embed=e)
async def starboard_add(
self,
ctx,
emoji: idiotlibrary.StarboardEmojiConverter = star_emoji,
limit: int = None):
if limit is None:
members = await get_non_bot_members(ctx.guild)
limit = 10 if members > 20 else math.ceil(members / 2)
msg = await ctx.send(embed=discord.Embed(
title='Confirm',
description=
f'Are you sure that you want to add a starboard to **{ctx.guild.name}**, channel **{ctx.channel.name}**, with the emoji {emoji} and the limit {limit}?',
color=green))
await msg.add_reaction(idiotlibrary.check_mark_emoji)
await msg.add_reaction(idiotlibrary.red_x_emoji)
def check(reaction, user):
return user == ctx.author
try:
reaction, _ = await self.bot.wait_for('reaction_add',
timeout=60.0,
check=check)
except asyncio.TimeoutError:
await ctx.send('No response found. Cancelling staboard.',
delete_after=10)
else:
if str(reaction) == idiotlibrary.red_x_emoji:
await msg.edit(embed=discord.Embed(
title='Starboard Cancelled',
description=
'Starboard has been cancelled. You can use `starboard add <emoji> <limit>` to add a new one. These will be determined automatically if left out.',
colour=red))
await msg.clear_reactions()
return
elif str(reaction) == idiotlibrary.check_mark_emoji:
await msg.edit(embed=discord.Embed(
title='Okay!',
description='Adding Starboard to your server...',
colour=green))
async with aiosqlite3.connect('idiotbot.db') as db:
await db.execute(
'INSERT INTO starboards VALUES (?, ?, ?, ?)',
(ctx.channel.id, emoji, limit, ctx.guild.id))
await db.commit()
await msg.edit(embed=discord.Embed(
title='Starboard Added!',
description=
f'A starboard has been added to: ```\nserver: {ctx.guild.name},\nchannel: {ctx.channel.name},\nemoji: {emoji},\nlimit: {limit}```',
colour=green))
async def mute_role_add(self, ctx, role: discord.Role):
async with aiosqlite3.connect('idiotbot.db') as db:
c = await db.execute('SELECT role_id FROM mutes WHERE guild_id=?',
(ctx.guild.id, ))
row = await c.fetchone()
if row == None:
await db.execute('INSERT INTO mutes VALUES (?, ?)',
(ctx.guild.id, role.id))
await db.commit()
e = discord.Embed(
title='Success',
description=f'Added Mute Role **{role.name}**.',
color=green)
await ctx.send(embed=e)
async def myRead(reader, _addr,lock):
cur_amount = 0
last_time = int(time.time())
async with aiosqlite3.connect("user.db") as db:
async with db.execute(f"select cur_amount,last_time from user where usrname={_addr!r}") as cursor:
for row in cursor:
cur_amount = row[0]
last_time = row[1]
print(f'cur_amount of client {_addr!r}: ',cur_amount)
increment = (int(time.time())-last_time) * rate
#lock
await lock.acquire()
cur_amount = min(cur_amount + increment, capacity)
#unlock
lock.release()
async with aiosqlite3.connect("user.db") as db:
await db.execute(f"update user set cur_amount={cur_amount!r} where usrname={_addr!r}")
await db.commit()
increment = (int(time.time())-last_time) * rate
#lock
await lock.acquire()
cur_amount = min(cur_amount + increment, capacity)
async with aiosqlite3.connect("user.db") as db:
await db.execute(f"update user set cur_amount={cur_amount!r} where usrname={_addr!r}")
await db.commit()
#unlock
lock.release()
while cur_amount < 4096:
increment = (int(time.time())-last_time) * rate
#lock
await lock.acquire()
cur_amount = min(cur_amount + increment, capacity)
async with aiosqlite3.connect("user.db") as db:
await db.execute(f"update user set cur_amount={cur_amount!r} where usrname={_addr!r}")
await db.commit()
#unlock
lock.release()
#lock
await lock.acquire()
last_time = int(time.time())
async with aiosqlite3.connect("user.db") as db:
await db.execute(f"update user set last_time={last_time!r} where usrname={_addr!r}")
await db.commit()
#unlock
lock.release()
data = await reader.read(4096)
#lock
await lock.acquire()
cur_amount -= len(data)
async with aiosqlite3.connect("user.db") as db:
await db.execute(f"update user set cur_amount={cur_amount!r} where usrname={_addr!r}")
await db.commit()
#unlock
lock.release()
return data
async def test_connect_check_same_thread(loop, db):
"""
测试同步
"""
async with aiosqlite3.connect(db, loop=loop,
check_same_thread=True) as conn:
async with conn.cursor() as cursor:
await cursor.execute('SELECT 42;')
res = await cursor.fetchone()
assert res == (42, )
conn = await aiosqlite3.connect(db, loop=loop, check_same_thread=True)
assert conn._thread
await conn.close()
assert conn._thread is None
with pytest.raises(TypeError):
cursor = await conn.execute('SELECT 42;')
async def handle_confirm(reader, writer):
name = ''
flag = 1
data = await reader.read(50)
option = struct.unpack("!B",data[0:1])[0]
name_len = struct.unpack("!B",data[1:2])[0]
name = data[2:2+name_len].decode()
password_len = struct.unpack("!B", data[2+name_len:3+name_len])[0]
password = data[3+name_len:3+name_len+password_len].decode()
async with aiosqlite3.connect("user.db") as db:
async with db.execute(f"select usrname,usrpassword,cur_amount,last_time from user") as cursor:
print("all info:")
for row in cursor:
print(row[0],row[1],row[2],row[3])
if option == 0:
await db.execute(f"insert into user (usrname,usrpassword,cur_amount,last_time) \
values ({name!r},{password!r},{capacity!r},{int(time.time())!r})")
await db.commit()
print('after insert:')
async with db.execute(f"select usrname,usrpassword,cur_amount,last_time from user") as cursor:
print("all info:")
for row in cursor:
print(row[0],row[1],row[2],row[3])
flag = 0
elif option == 1:
find = 1
async with db.execute(f'select usrpassword from user where usrname="{name}"') as cursor:
if len(list(cursor))==0:
flag = 2
find = 0
if find == 1:
async with db.execute(f'select usrpassword from user where usrname="{name}"') as cursor:
for row in cursor:
if row[0] != password:
print(row[0],password)
flag = 10
print('wrong pwd')
db.close()
data0 = struct.pack("!B",flag)
writer.write(data0)
await writer.drain()
if flag == 1:
await handle_tcp_echo(reader, writer)
async def tag_search(self, ctx, query:str):
if query == '':
return await ctx.send('Query cannot be None. Please enter a search value.')
async with aiosqlite3.connect('tags.db') as db:
cursor = await db.execute('SELECT name FROM tags WHERE guild_id=?', (ctx.guild.id,))
data = await cursor.fetchall()
tagnames = []
for row in data:
tagnames.append(row[0])
tagnames = process.extract(query, tagnames, limit=5)
sorted_ = []
for item in tagnames:
sorted_.append(item[0])
sorted_ = '\n'.join(sorted_)
embed = discord.Embed(title=f'Tags matching query: **{query}**', description=f'```\n{sorted_}```',
color=green)
await ctx.send(embed=embed)
async def tag_delete(self, ctx, *, tag:str):
async with aiosqlite3.connect('tags.db') as db:
c = await db.execute(
'SELECT * FROM tags WHERE name=? AND guild_id = ?', (tag, ctx.guild.id))
data = c.fetchone()
try:
if ctx.author.id == data[2]:
c.execute(
'DELETE FROM tags WHERE name=? AND guild_id=?', (tag, ctx.guild.id))
await db.commit()
await ctx.send(f'Successfully deleted tag **{tag}**')
else:
e = discord.Embed(
title='Error', description='You do not own this tag.', color=red)
await ctx.send(embed=e)
except TypeError:
await ctx.send('Could not find that tag.')
async def mute_role(self, ctx):
async with aiosqlite3.connect('idiotbot.db') as db:
c = await db.execute('SELECT * FROM mutes WHERE guild_id=?',
(ctx.guild.id, ))
try:
guild_id, role_id, = await c.fetchone()
except TypeError:
return await ctx.send('No mute roles in this server.')
if role_id == None:
return await ctx.send('No mute roles in this server.')
role = self.bot.get_guild(guild_id).get_role(role_id)
if role is None:
return await ctx.send('You idiot! You deleted the mute role!')
e = discord.Embed(title=f'Mute Role for {ctx.guild.name}',
description=f'Muted role: {role.mention}',
color=green)
await ctx.send(embed=e)
async def unmute(self, ctx, member: discord.Member):
async with aiosqlite3.connect('idiotbot.db') as db:
c = await db.execute('SELECT * FROM mutes WHERE guild_id=?',
(ctx.guild.id, ))
try:
guild_id, role_id = await c.fetchone()
except TypeError:
return await ctx.send('No mute role in this server.')
role = self.bot.get_guild(guild_id).get_role(role_id)
await member.remove_roles(role)
await db.execute('DELETE FROM Muted WHERE MutedID=?',
(member.id, ))
await db.commit()
e = discord.Embed(title='Unmuted',
description=f'Unmuted {member.mention}.',
color=green)
await ctx.send(embed=e)
