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)