async def get(self, key):
"""Get data from the database for a given key.
Args:
key (string): The key to lookup in the database.
Returns:
object or None: The data object stored for that key, or None if no
object found for that key.
"""
_LOGGER.debug(_("Getting %s from sqlite"), key)
data = None
async with aiosqlite.connect(self.db_file, **self.conn_args) as _db:
cur = await _db.cursor()
await cur.execute(
"SELECT data FROM {} WHERE key=?".format(self.table),
(key,))
row = await cur.fetchone()
if row:
data = json.loads(row[0], object_hook=JSONDecoder())
self.client = _db
return data
async def connect(self):
"""Connect to the database.
This method will connect to the sqlite database. It will create
the database file named `sqlite.db` in DEFAULT_ROOT_PATH and set
the table name to `opsdroid`. It will create the table if it does
not exist in the database.
Args:
opsdroid (OpsDroid): An instance of opsdroid core.
"""
self.db_file = self.config.get(
"file", os.path.join(DEFAULT_ROOT_PATH, "sqlite.db"))
self.table = self.config.get("table", "opsdroid")
async with aiosqlite.connect(self.db_file, **self.conn_args) as _db:
await _db.execute(
"CREATE TABLE IF NOT EXISTS {}"
"(key text PRIMARY KEY, data text)"
.format(self.table)
)
self.client = _db
_LOGGER.info(_("Connected to sqlite %s"), self.db_file)
async def __aenter__(self) -> "BaseSourceContext":
self.__db = aiosqlite.connect(self.config.filename)
self.db = await self.__db.__aenter__()
self.db.row_factory = aiosqlite.Row
# Create table for feature data
await self.db.execute(
"CREATE TABLE IF NOT EXISTS features ("
"src_url TEXT PRIMARY KEY NOT NULL, "
+ (" REAL, ".join(self.FEATURE_COLS))
+ " REAL"
")"
)
# Create table for predictions
await self.db.execute(
"CREATE TABLE IF NOT EXISTS prediction ("
"src_url TEXT PRIMARY KEY, " + "value TEXT, "
"confidence REAL"
")"
)
return self
async def on_initdb(self, *args):
args = args[0].split(' ')
async with aiosqlite.connect(args[0]) as con:
cur = await con.cursor()
await cur.execute(
'CREATE TABLE IF NOT EXISTS economy (userID INT, money INT)')
for _guild in self.client.guilds:
for _member in _guild.members:
if not _member.bot:
await cur.execute(
'''
INSERT INTO economy SELECT ?, ?
WHERE NOT EXISTS (SELECT * FROM economy WHERE userID=?)
''', (_member.id, 0, _member.id))
await con.commit()
async def youtuber_setup(self, message, args):
args = args[0].split(" ", 1)
if len(args) < 2:
response = "Not enough arguments"
await self.client.send_message(message.channel.id, response)
return
youtubers_channel = int(args[0][2:-1])
youtubers_text = args[1]
try:
async with aiosqlite.connect(self.db.path) as conn:
await conn.execute(
"UPDATE guilds SET youtubers_channel=?, youtubers_text=? "
"WHERE id=?",
(youtubers_channel, youtubers_text, message.guild.id))
await conn.commit()
response = "Update YouTubers annoucement text and channel!"
except Exception as exception: # pylint: disable=W0703
LOG.exception(exception)
response = "Couldn't update YouTubers annoucement text and channel"
await self.client.send_message(message.channel.id, response)
async def auto_cleanup(self):
"""Sceduled auto cleanup task."""
# for now we simply rest the memory cache
self._mem_cache = {}
cur_timestamp = int(time.time())
LOGGER.debug("Running cleanup...")
sql_query = "SELECT id, expires FROM simplecache"
async with aiosqlite.connect(self._dbfile, timeout=600) as db_conn:
db_conn.row_factory = aiosqlite.Row
async with db_conn.execute(sql_query) as cursor:
cache_objects = await cursor.fetchall()
for cache_data in cache_objects:
cache_id = cache_data["id"]
# clean up db cache object only if expired
if cache_data["expires"] < cur_timestamp:
sql_query = "DELETE FROM simplecache WHERE id = ?"
await db_conn.execute(sql_query, (cache_id, ))
# compact db
await db_conn.commit()
LOGGER.debug("Auto cleanup done")
async def test_create_function(self):
"""Assert that after creating a custom function, it can be used"""
def no_arg():
return "no arg"
def one_arg(num):
return num * 2
async with aiosqlite.connect(TEST_DB) as db:
await db.create_function("no_arg", 0, no_arg)
await db.create_function("one_arg", 1, one_arg)
async with db.execute("SELECT no_arg();") as res:
row = await res.fetchone()
self.assertEqual(row[0], "no arg")
async with db.execute("SELECT one_arg(10);") as res:
row = await res.fetchone()
self.assertEqual(row[0], 20)
async def __execute_db(self,
cb: Callable[[Connection], Awaitable[Any]],
check_file: bool = True) -> Any:
"""
Execute something through a callback.
@param cb: Callback which will be invoked in the db.
@param check_file: Whether or not it should check the file, leave this at true unless you are initializing the
database.
@return: The result of the callback.
"""
self.__check_db_file()
if check_file and not isfile("db/exp_system.db"):
await self.__create_db()
async with connect("db/exp_system.db") as db:
db.row_factory = Row
return await cb(db)
async def page(self, ctx):
"ritorna un link per arrivare al messaggio del proprio server"
async with aiosqlite.connect("data/servers.db") as db:
data = await db.execute(
f"select * from servers where id = {ctx.guild.id}")
data = await data.fetchall()
if len(data) == 0 or int(data[0][0]) == 0:
emb = discord.Embed(title="Il tuo server non è in lista!",
colour=discord.Colour.red())
return await ctx.send(embed=emb)
channel = self.bot.get_channel(int(data[0][1]))
message = await channel.fetch_message(int(data[0][2]))
emb = discord.Embed(
description=f"[{ctx.guild.name}]({message.jump_url})",
colour=discord.Colour.green())
await ctx.send(embed=emb)
async def latestscanchartdata(self, domain):
try:
async with aiosqlite.connect(self.db, timeout=30) as conn:
self.latestscandomain["domain"] = domain
cursor = await conn.execute('''SELECT COUNT(*) from results WHERE domain=? AND type="host"''', (domain,))
data = await cursor.fetchone()
self.latestscandomain["host"] = data[0]
cursor = await conn.execute('''SELECT COUNT(*) from results WHERE domain=? AND type="email"''', (domain,))
data = await cursor.fetchone()
self.latestscandomain["email"] = data[0]
cursor = await conn.execute('''SELECT COUNT(*) from results WHERE domain=? AND type="ip"''', (domain,))
data = await cursor.fetchone()
self.latestscandomain["ip"] = data[0]
cursor = await conn.execute('''SELECT COUNT(*) from results WHERE domain=? AND type="vhost"''', (domain,))
data = await cursor.fetchone()
self.latestscandomain["vhost"] = data[0]
cursor = await conn.execute('''SELECT COUNT(*) from results WHERE domain=? AND type="shodan"''', (domain,))
data = await cursor.fetchone()
self.latestscandomain["shodan"] = data[0]
cursor = await conn.execute('''SELECT MAX(find_date) FROM results WHERE domain=?''', (domain,))
data = await cursor.fetchone()
self.latestscandomain["latestdate"] = data[0]
latestdate = data[0]
cursor = await conn.execute('''SELECT * FROM results WHERE domain=? AND find_date=? AND type="host"''', (domain, latestdate,))
scandetailshost = await cursor.fetchall()
self.latestscandomain["scandetailshost"] = scandetailshost
cursor = await conn.execute('''SELECT * FROM results WHERE domain=? AND find_date=? AND type="email"''', (domain, latestdate,))
scandetailsemail = await cursor.fetchall()
self.latestscandomain["scandetailsemail"] = scandetailsemail
cursor = await conn.execute('''SELECT * FROM results WHERE domain=? AND find_date=? AND type="ip"''', (domain, latestdate,))
scandetailsip = await cursor.fetchall()
self.latestscandomain["scandetailsip"] = scandetailsip
cursor = await conn.execute('''SELECT * FROM results WHERE domain=? AND find_date=? AND type="vhost"''', (domain, latestdate,))
scandetailsvhost = await cursor.fetchall()
self.latestscandomain["scandetailsvhost"] = scandetailsvhost
cursor = await conn.execute('''SELECT * FROM results WHERE domain=? AND find_date=? AND type="shodan"''', (domain, latestdate,))
scandetailsshodan = await cursor.fetchall()
self.latestscandomain["scandetailsshodan"] = scandetailsshodan
return self.latestscandomain
except Exception as e:
print(e)
async def verify_existing_custom_event(self, name: str):
"""Verifies whether an event already exists.
Called prior to updating a custom event.
If the event did not exist already, the command fails.
Otherwise, the existing event is destroyed and the command proceeds.
Args:
name (str): the name of the event to check
Returns:
bool: True if successful; False otherwise
"""
async with aiosqlite.connect(self.db_name) as _db:
try:
cursor = await _db.execute(
'select * from events where name = ?', (name, ))
existing = await cursor.fetchone()
if existing:
await _db.execute('delete from events where name = ?',
(name, ))
await _db.commit()
return True
else:
return False
except sqlite3.OperationalError:
await self.create_db('events')
logger.error(
f'Caught {e} in vaivora.db: verify_existing_custom_event; '
f'event: {name}; '
f'guild: {self.db_id}; '
'table recreated')
return False
except Exception as e:
logger.error(
f'Caught {e} in vaivora.db: verify_existing_custom_event; '
f'event: {name}; '
f'guild: {self.db_id}')
return False
async def score(self, author, isCommand, guild, message):
async with aiosqlite.connect("memberScores.db") as conn:
score = -1
cursor = await conn.execute(
'SELECT Score,Name FROM "guild{0}" WHERE ID=?'.format(
str(guild.id)), (author.id, ))
oldScore = await cursor.fetchone()
await cursor.close()
if not author.id in self.MRU:
if oldScore == None:
try:
cursor = await conn.execute(
'INSERT INTO "guild{0}" (ID,Name,Score) VALUES (?,?,1)'
.format(str(guild.id)),
(author.id, str(author)[:-5]))
await conn.commit()
except Exception as e:
print(
f"Error adding user {author.id} to score database")
print(e)
raise
else:
if isCommand:
return oldScore[0]
cursor = await conn.execute(
'UPDATE "guild{0}" SET Score=?,Name=? WHERE ID=?'.
format(str(guild.id)),
(oldScore[0] + 1, str(author)[:-5], author.id))
score = oldScore[0] + 1
await conn.commit()
if guild.id == self.bot.config["nijiCord"] and (
score == 69 or score == 6969):
await message.channel.send("nice")
self.MRU.insert(0, author.id)
if len(self.MRU) > cache:
self.MRU.pop()
else:
score = oldScore[0] if oldScore else -1
if score < 0:
return None
return score
async def migrate_to_latest(db: str):
async with aiosqlite.connect(db) as conn:
# Get the DB Version
cursor = await conn.execute("PRAGMA user_version")
user_version = await cursor.fetchone()
if user_version[0] != db_version:
log.warning(
f"DB Version is behind. Current: {user_version[0]}, Latest: {db_version}!\n Migrating Tables Forward!!!"
)
# The DB is behind. Migrate it to current step by step.
if user_version[0] == 0:
log.warning(f"Migrating Tables to version 1")
await conn.execute('''
alter table systems
add pk_system_tag text default NULL;
''')
await conn.execute('''
alter table systems
add system_tag_override text default NULL;
''')
# Set DB to next version
await conn.execute(f"PRAGMA user_version = 1")
log.warning(f"Tables have been migrated to version 1")
if user_version[0] == 1:
# I messed up.... Now we need tto keep this do nothing migratioon here...
log.warning(f"Migrating Tables to version 2")
await conn.execute(f"PRAGMA user_version = 2")
if user_version[0] == 2:
log.warning(f"Migrating Tables to version 3")
await conn.execute('''
alter table user_settings
add system_tag_override text default NULL;
''')
# # Set DB to next version
await conn.execute(f"PRAGMA user_version = 3")
log.warning(f"Tables have been migrated to version 3")
async def blacklist(self,
ctx,
member: Union[discord.Member, discord.User] = None):
"blacklist a user"
async with ctx.typing():
async with aiosqlite.connect("data/db.db") as db:
if member:
await db.execute(
f"INSERT into blacklist (user) VALUES ({member.id})")
await db.commit()
else:
data = await db.execute(f"SELECT * from blacklist")
data = await data.fetchall()
emb = discord.Embed(
description=f"These users are blacklisted:\n",
colour=self.bot.colour)
for user in data:
if int(user[0]) == 0:
pass
else:
u = self.bot.get_user(int(user[0]))
if not u:
emb.description += f"• **{user[0]}** (I cannot see this user)\n"
else:
emb.description += f"• **{u}**\n"
return await ctx.send(embed=emb)
emb = discord.Embed(
description=
f"<a:check:726040431539912744> | Blacklisted **{member}**",
colour=self.bot.colour)
await ctx.send(embed=emb)
await ctx.message.add_reaction("<a:check:726040431539912744>")
async def add_server(self, server_id: int, owner_discord_id: int,
owner_gw2_name: str, guild_id: str, api: str) -> None:
"""
adds a server to the server table
stores the discord servers id, discord servers name, the discord servers owners client id,
the discord servers owners guild wars 2 account name (with numbers),
the guild wars 2 guild id and the owners guild wars 2 api key
:param server_id: discord server id
:param owner_discord_id: owners discord client id
:param owner_gw2_name: owners guild wars 2 account name
:param guild_id: guild wars 2 guild id
:param api: owners guild wars 2 api key
:return: None
"""
async with aiosqlite.connect(self.db) as connection:
cursor = await connection.cursor()
check_server_command = f"""
select 1 from {self.tables.servers}
where {self.server_keys.server_id} = {server_id}
"""
add_server_command = f"""
insert into {self.tables.servers} (
{", ".join(self.server_keys)}
) values (
{server_id}, {owner_discord_id},
"{owner_gw2_name}", "{guild_id}", "{api}"
);"""
await cursor.execute(check_server_command)
server_excists = await cursor.fetchall()
if not server_excists:
await cursor.execute(add_server_command)
await connection.commit()
else:
raise exceptions.ServerAlreadyExcists(
f"Server: {server_id} already excists.")
async def update_track(self, item_id: int, track: Track):
"""Update a track record in the database."""
async with aiosqlite.connect(self._dbfile, timeout=120) as db_conn:
db_conn.row_factory = aiosqlite.Row
cur_item = await self.get_track(item_id)
# we store a mapping to artists and albums on the track for easier access/listings
track_artists = await self._get_track_artists(
track, cur_item.artists)
track_albums = await self._get_track_albums(track, cur_item.albums)
# merge metadata and provider id's
metadata = merge_dict(cur_item.metadata, track.metadata)
provider_ids = merge_list(cur_item.provider_ids,
track.provider_ids)
sql_query = """UPDATE tracks
SET isrc=?,
metadata=?,
provider_ids=?,
artists=?,
albums=?
WHERE item_id=?;"""
await db_conn.execute(
sql_query,
(
track.isrc or cur_item.isrc,
json_serializer(metadata),
json_serializer(provider_ids),
json_serializer(track_artists),
json_serializer(track_albums),
item_id,
),
)
await self._add_prov_ids(item_id,
MediaType.Track,
track.provider_ids,
db_conn=db_conn)
LOGGER.debug("updated track %s in database: %s", track.name,
item_id)
await db_conn.commit()
# return updated object
return await self.get_track(item_id)
async def insert_in_vrf(vrf_info, h, i):
"""
Function to insert data to the database in the table vrf
:param vrf_info: List with the vrf info gathered from processing the output of the sh ip vrf interfaces
:param h: Hostname where the vrf info data comes from
:param i: Ip address where the vrf ip info data comes from
:return: NULL
"""
print(BColors.HEADER +
'Connecting to the database to insert ip info in DB...' +
BColors.ENDC)
async with aiosqlite.connect('cdp.db') as conn:
for entry in vrf_info:
# Checking that the vlan is not already on the DB
print(
BColors.HEADER +
f"Checking that interface {entry['int']} for host {h} is not in the DB..."
+ BColors.ENDC)
async with conn.execute(
'SELECT COUNT(*) FROM vrf WHERE hostname = ? AND int = ?',
(
h,
entry['int'],
)) as cursor:
n = await cursor.fetchone()
n = n[0]
if n == 0:
# Inserting the vlan in the DB
print(BColors.OKGREEN +
f"Inserting into VRF {entry['int']}..." +
BColors.ENDC)
await conn.execute(
'INSERT INTO vrf(hostname, ip_address, int, ip_int, vrf)\
VALUES(?,?,?,?,?)',
(h, i, entry['int'], entry['ip_int'], entry['vrf']))
await conn.commit()
else:
print(
BColors.OKBLUE +
f"Interface {entry['int']} already exists in the DB for host {h}..."
+ BColors.ENDC)
async def give_start(self, ctx,
channel: typing.Optional[discord.TextChannel], *,
giveaway_data):
"""
Starts a giveaway on a given channel.
giveaway_data should have the following data:
- Ending time
- Platform data w/ giveaway row (Optional),
- Item name,
- Restrictions (Either custom or keywords will be searched),
- Description (Optional)
"""
await ctx.trigger_typing()
# Potentially blocking? Just in case, right?
giveaway: GiveawayEntry = await self.bot.loop.run_in_executor(
None, lambda: GiveawayEntry(ctx, giveaway_data))
async with aiosqlite.connect(configs.DATABASE_NAME) as db:
c = await db.execute(
"SELECT * FROM giveaways WHERE guild = ? AND g_id = ?;",
(ctx.guild.id, giveaway.row))
r = await c.fetchone()
await c.close()
if r is not None:
raise GiveawayError(f"Giveaway {giveaway.row} already exists!")
# TODO: Make this part of config
config_msg = "**__ :tada: Giveaway :tada: __**"
if channel is None:
msg = await ctx.send(config_msg, embed=giveaway)
else:
msg = await channel.send(config_msg, embed=giveaway)
await msg.add_reaction(":tada:")
giveaway.msg = msg
await db.execute(
"INSERT INTO giveaways VALUES (?, ?, ?, ?)",
(ctx.guild.id, giveaway, giveaway.time, giveaway.row))
await db.commit()
async def update_playlist(self, item_id: int, playlist: Playlist):
"""Update a playlist record in the database."""
async with aiosqlite.connect(self._dbfile, timeout=120) as db_conn:
db_conn.row_factory = aiosqlite.Row
cur_item = Playlist.from_db_row(await self.__execute_fetchone(
"SELECT * FROM playlists WHERE item_id=?;", (item_id, ),
db_conn))
metadata = merge_dict(cur_item.metadata, playlist.metadata)
provider_ids = merge_list(cur_item.provider_ids,
playlist.provider_ids)
sql_query = """UPDATE playlists
SET name=?,
sort_name=?,
owner=?,
is_editable=?,
checksum=?,
metadata=?,
provider_ids=?
WHERE item_id=?;"""
await db_conn.execute(
sql_query,
(
playlist.name,
playlist.sort_name,
playlist.owner,
playlist.is_editable,
playlist.checksum,
json_serializer(metadata),
json_serializer(provider_ids),
item_id,
),
)
await self._add_prov_ids(item_id,
MediaType.Playlist,
playlist.provider_ids,
db_conn=db_conn)
LOGGER.debug("updated playlist %s in database: %s", playlist.name,
item_id)
await db_conn.commit()
# return updated object
return await self.get_playlist(item_id)
async def insert_in_vlans(vlans, h, i):
"""
Function to insert data to the database in the table vlans
:param vlans: List with the vlans gathered from processing the output of the sh vlan brief
:param h: Hostname where the vlan data comes from
:param i: Ip address where the vlan data comes from
:return: NULL
"""
print(BColors.HEADER +
'Connecting to the database to insert vlans in DB...' + BColors.ENDC)
async with aiosqlite.connect('cdp.db') as conn:
for entry in vlans:
# Checking that the vlan is not already on the DB
print(
BColors.HEADER +
f"Checking that vlan {entry['vlan']} with name {entry['name']} for host {h} is not in the DB..."
+ BColors.ENDC)
async with conn.execute(
'SELECT COUNT(*) FROM vlans WHERE hostname = ? AND vlan = ?',
(
h,
entry['vlan'],
)) as cursor:
n = await cursor.fetchone()
n = n[0]
if n == 0:
# Inserting the vlan in the DB
print(
BColors.OKGREEN +
f"Inserting into VLANS {entry['vlan']} with name {entry['name']}..."
+ BColors.ENDC)
await conn.execute(
'INSERT INTO vlans(hostname, ip_address, vlan, name)\
VALUES(?,?,?,?)',
(h, i, entry['vlan'], entry['name']))
await conn.commit()
else:
print(
BColors.OKBLUE +
f"Vlan {entry['vlan']} with name {entry['name']} already exists in the DB for host {h}..."
+ BColors.ENDC)
async def insert(cls, **kwargs):
cls.validate(kwargs)
async with aiosqlite.connect(DATABASE_FILE) as db:
if cls.__priamry_key__ is not None and cls.__priamry_key__ in kwargs:
query = "INSERT INTO %s (%s, %s) VALUES (?, %s)" % (
cls.__table_name__,
cls.__priamry_key__,
', '.join("'%s'" % str(x) for x in cls.__fields__),
', '.join(['?'] * len(cls.__fields__)))
data = ()
data = data + (kwargs.get(cls.__priamry_key__),)
for f in cls.__fields__:
if f in cls.__json_fields__:
data = data + (json.dumps(kwargs.get(f)),)
else:
data = data + (kwargs.get(f),)
else:
query = 'INSERT INTO %s (%s) VALUES (%s)' % (
cls.__table_name__,
', '.join("'%s'" % str(x) for x in cls.__fields__),
', '.join(['?'] * len(cls.__fields__)))
data = ()
for f in cls.__fields__:
if f in cls.__json_fields__:
data = data + (json.dumps(kwargs.get(f)),)
else:
data = data + (kwargs.get(f),)
cursor = await db.execute(query, data)
await db.commit()
i = cursor.lastrowid
kwargs["id"] = i
return cls(kwargs)
async def test_connection_properties(self):
async with aiosqlite.connect(TEST_DB) as db:
self.assertEqual(db.total_changes, 0)
async with db.cursor() as cursor:
self.assertFalse(db.in_transaction)
await cursor.execute(
"create table test_properties "
"(i integer primary key asc, k integer, d text)")
await cursor.execute(
"insert into test_properties (k, d) values (1, 'hi')")
self.assertTrue(db.in_transaction)
await db.commit()
self.assertFalse(db.in_transaction)
self.assertEqual(db.total_changes, 1)
self.assertIsNone(db.row_factory)
self.assertEqual(db.text_factory, default_text_factory)
async with db.cursor() as cursor:
await cursor.execute("select * from test_properties")
row = await cursor.fetchone()
self.assertIsInstance(row, tuple)
self.assertEqual(row, (1, 1, "hi"))
with self.assertRaises(TypeError):
_ = row["k"]
db.row_factory = aiosqlite.Row
db.text_factory = bytes
self.assertEqual(db.row_factory, aiosqlite.Row)
self.assertEqual(db.text_factory, bytes)
async with db.cursor() as cursor:
await cursor.execute("select * from test_properties")
row = await cursor.fetchone()
self.assertIsInstance(row, aiosqlite.Row)
self.assertEqual(row[1], 1)
self.assertEqual(row[2], b"hi")
self.assertEqual(row["k"], 1)
self.assertEqual(row["d"], b"hi")
async def add_bets(match_id, user_id):
async with aiosqlite.connect('fantasyDB/fantasy.db') as db:
add_bets_text = '''
INSERT INTO bets
(
match_id,
user_id,
radiant_bet,
dire_bet
)
VALUES
(
'%s',
'%s',
0,
0
)''' % (match_id, user_id)
await db.execute(add_bets_text)
await db.commit()
await db.close()
async def add_message(bot_id, match_id, channel_id, message_id):
async with aiosqlite.connect('fantasyDB/fantasy.db') as db:
add_message_text = '''
INSERT INTO messages
(
bot_id,
match_id,
channel_id,
message_id
)
VALUES
(
'%s',
'%s',
'%s',
'%s'
)''' % (bot_id, match_id, channel_id, message_id)
await db.execute(add_message_text)
await db.commit()
await db.close()
async def taglist(self, ctx):
async with aiosqlite.connect('data.db') as con:
await con.execute(
"CREATE TABLE IF NOT EXISTS tags(title TEXT, content TEXT)")
async with con.execute('SELECT title FROM tags') as cur:
x = []
i = 0
async for result in cur:
i += 1
x.append(f"{i}. {result[0]}")
embed = discord.Embed(title="Tag List",
description="\n".join(x),
colour=0xFF0000)
embed.set_thumbnail(
url=
"https://betanews.com/wp-content/uploads/2013/03/Pin-thumbtack-300x300.jpg"
)
name = ctx.author.display_name
avatar = str(ctx.author.avatar_url)
embed.set_author(name=name, icon_url=avatar)
await ctx.send(embed=embed)
async def list_fc_command(self, ctx):
"""List all friend codes."""
async with aiosqlite.connect('databases/gaming.db') as db:
async with db.execute(
'SELECT * FROM nintendofriendcodes') as cursor:
fcs = await cursor.fetchall()
# Generate a string with all of the friend codes.
msg = 'NINTENDO FRIEND CODES\n------------------------\n'
for fc in fcs:
msg = f'{msg}\n{fc[0]}: {fc[1]}'
# Make a new paste.
paste = Paste_it()
fclist = await paste.new_paste(msg)
# Send the message.
self.bot.logger.info(
f'{ctx.author.name} used {ctx.command.name}.')
await ctx.send(fclist, delete_after=C.DEL_DELAY)
await ctx.message.delete(delay=C.DEL_DELAY)
async def algo_list(request):
userid = request.user
algolist = []
try:
async with aiosqlite.connect(get_main_db()) as db:
async with db.execute("select algoname, tradepair, candlesize, strategyname, parameters from "
"algos where userid = ?", [userid]) as cursor:
async for row in cursor:
algolist += [
{
'algo_name': row[0],
'trade_pair': row[1],
'candle_size': row[2],
'strategy_name': row[3],
'strategy_parameters': json.loads(row[4]),
}
]
except:
return json_response(STATUS_ERR % ERRORS[ERR_INTERNAL_ERROR], status=500)
return json_response(json.dumps(algolist))
async def test_parameterized_record_query(sqlite3_db_path, queries):
async with aiosqlite.connect(sqlite3_db_path) as conn:
conn.row_factory = dict_factory
actual = await queries.blogs.sqlite_get_blogs_published_after(
conn, published="2018-01-01"
)
expected = [
{
"title": "How to make a pie.",
"username": "******",
"published": "2018-11-23 00:00",
},
{
"title": "Testing",
"username": "******",
"published": "2018-01-01 00:00",
},
]
assert actual == expected
async def on_ready(self):
os.makedirs('Data', exist_ok=True)
async with aiosqlite.connect('Data/phantom.db') as db:
await db.execute('''CREATE TABLE IF NOT EXISTS logging(guild INTEGER, enabled BOOL)''')
await db.commit()
await db.execute('''CREATE TABLE IF NOT EXISTS prefix(guild INTEGER, prefix TEXT)''')
await db.commit()
await db.execute('''CREATE TABLE IF NOT EXISTS timer(user INTEGER, is_running TEXT)''')
await db.commit()
await db.execute('''CREATE TABLE IF NOT EXISTS snipe(guild INTEGER, channel INTEGER, message TEXT, member INTEGER)''')
await db.commit()
await db.execute('''CREATE TABLE IF NOT EXISTS uptime(start_time REAL)''')
await db.commit()
await self.bot.change_presence(activity=discord.Game(name=f"Ping me for help! | Terrorizing {len(self.bot.guilds)} server{'s' if len(self.bot.guilds) != 1 else ''}"))
print('Phantom is now online.')
async def database(self, ctx, *, query):
"""Runs arbitrary sql queries on the db in readonly mode and returns the results"""
database_name = BotSecrets.get_instance().database_name
db_path = f'database/{database_name}'
connect_mode = 'ro'
json_params = {'indent': 2, 'separators': (',', ': ')}
async with aiosqlite.connect(f'file:{db_path}?mode={connect_mode}',
uri=True) as db:
async with db.execute(query) as c:
result = await BaseRepository().fetcthall_as_dict(c)
json_res = json.dumps(result, **json_params)
if len(json_res) > DiscordLimits.MessageLength:
await ctx.send(
'Query result greater then discord message length limit')
return
await ctx.send(f'```{json_res}```')
def get_database() -> aiosqlite.Connection:
"""
Gets the discord_chan database
:return: The db context manager
"""
def adapt_set(_set):
return ",".join(map(str, _set))
def convert_set(s):
return {i.decode() for i in s.split(b",")}
import sqlite3
sqlite3.register_adapter(set, adapt_set)
sqlite3.register_converter("pyset", convert_set)
return aiosqlite.connect(
"discord_chan.db", detect_types=1
) # sqlite3.PARSE_DECLTYPES
async def deployed_algo_list(request):
userid = request.user
deployed_list = []
try:
async with aiosqlite.connect(get_main_db()) as db:
async with db.execute("select id, algoname, amount, num_cycles, status "
"from deployed_algos where userid = ?", [userid]) as cursor:
async for row in cursor:
deployed_list += [
{
'id': row[0],
'algo_name': row[1],
'amount': row[2],
'num_cycles': row[3],
'status': row[4],
}
]
except:
return json_response(STATUS_ERR % ERRORS[ERR_INTERNAL_ERROR], status=500)
return json_response(json.dumps(deployed_list))
async def put(self, key, data):
"""Put data into the database.
This method will insert or replace an object into the database for
a given key. The data object is serialised into JSON data using the
JSONEncoder class.
Args:
key (string): The key to store the data object under.
data (object): The data object to store.
"""
_LOGGER.debug(_("Putting %s into sqlite"), key)
json_data = json.dumps(data, cls=JSONEncoder)
async with aiosqlite.connect(self.db_file, **self.conn_args) as _db:
cur = await _db.cursor()
await cur.execute(
"DELETE FROM {} WHERE key=?".format(self.table), (key,))
await cur.execute(
"INSERT INTO {} VALUES (?, ?)".format(self.table),
(key, json_data))
self.client = _db
