async def run():
credentials = {
"user": '******',
'password': '******',
'database': 'server_data',
'host': 'localhost'
}
out = {}
async with asyncpg.create_pool(**credentials, min_size=1,
max_size=1) as pool:
async with pool.acquire() as conn:
for codex in codexList:
easydata[codex] = {}
for categ in categList:
print(categ)
if categ == 'pokemon':
entries = {
i['id']: [i['alias_assoc'], i['forms_id_list']]
for i in await conn.fetch(request(codex, categ))
}
else:
entries = {
i['id']: json.loads(i['embed'])
for i in await conn.fetch(request(codex, categ))
}
easydata[codex][categ] = entries
"""for name in pages[codex][categ]:
def init():
"""Creates the database."""
loop = asyncio.get_event_loop()
pool = loop.run_until_complete(
asyncpg.create_pool(config.dsn, command_timeout=60))
loop.run_until_complete(create_db(pool))
async def save_to_database(data):
"""Save fundamental data in a database"""
df = data.unstack().reset_index()
df.columns = ['source', 'variable', 'symbol', 'value']
df = df[['symbol', 'source', 'variable', 'value']]
df['value'] = df['value'].astype(str)
values = df.to_records(index=False)
sql = """
INSERT INTO fundamentals (symbol_id, source, var, val)
VALUES ((SELECT id FROM symbols WHERE symbol=$1), $2, $3, $4)
ON CONFLICT (symbol_id, source, var)
DO UPDATE
SET symbol_id=excluded.symbol_id,
source=excluded.source,
var=excluded.var,
val=excluded.val;
"""
# using asynciopf as the Database connection manager for Async
creds = {
'host': S.DB_HOST,
'database': S.DB_NAME,
'user': S.DB_USER,
'password': S.DB_PASSWORD
}
async with asyncpg.create_pool(**creds) as pool:
async with pool.acquire() as db:
async with db.transaction():
await db.executemany(sql, values)
Database().timestamp('fundamentals', close=True)
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs, formatter=DogbotHelpFormatter())
# configuration dict
self.cfg = kwargs.get('cfg', {})
# aiohttp session used for fetching data
self.session = aiohttp.ClientSession(loop=self.loop)
# boot time (for uptime)
self.boot_time = datetime.datetime.utcnow()
# aioredis connection
redis_coroutine = aioredis.create_redis(
(self.cfg['db']['redis'], 6379), loop=self.loop)
self.redis = self.loop.run_until_complete(redis_coroutine)
# asyncpg
pg = self.cfg['db']['postgres']
self.database = pg['database']
self.pgpool = self.loop.run_until_complete(asyncpg.create_pool(**pg))
# load core extensions
self._exts_to_load = []
self.load_extensions('dog/core/ext', 'Core recursive load')
class BotBase(_BotBase[CT]):
pool: Pool[Record]
context_cls: ClassVar = cast(type[CT], Context)
def __init__(self, config: Config, /, *args: Any, **kwargs: Any) -> None:
if not has_asyncpg:
raise RuntimeError(
'asyncpg library needed in order to use a database')
super().__init__(config, *args, **kwargs)
pool_kwargs: dict[str, Any] = {}
if hasattr(self,
'__init_connection__') and asyncio.iscoroutinefunction(
cast(Any, self).__init_connection__):
pool_kwargs['init'] = cast(Any, self).__init_connection__
if hasattr(self,
'__setup_connection__') and asyncio.iscoroutinefunction(
cast(Any, self).__setup_connection__):
pool_kwargs['setup'] = cast(Any, self).__setup_connection__
self.pool = cast(
Pool[Record],
self.loop.run_until_complete(
create_pool(
self.config.get('db_url', ''),
min_size=1,
max_size=10,
**pool_kwargs,
)),
)
def __init__(self, loop: asyncio.AbstractEventLoop):
self.pool: asyncio.pool.Pool = loop.run_until_complete(
asyncpg.create_pool(
user=config.PGUSER,
password=config.PGPASSWORD,
host=config.ip,
))
async def _get_place_records(top, right, bottom, left):
# TODO: not this!! need a global pool somewhere!
async with asyncpg.create_pool(**_CONN_KWARGS) as conn_pool:
async with conn_pool.acquire() as conn:
# NOTE THE ORDER CHANGE!! compared to the signature above. We're
# using CSS ordering in our corosignature, but the postgis
# function uses left, bottom, right, top instead
records = await conn.fetch('''
SELECT
place_id,
osm_id,
identity_name,
identity_display_class,
status,
locator_website,
locator_phone,
ST_AsGeoJSON(ST_Transform(locator_point, 4326))
AS locator_point_json,
locator_address
FROM app_placedata.places placedata
WHERE ST_Contains(
ST_Transform(
ST_MakeEnvelope($1, $2, $3, $4, 4326),
3857
),
placedata.locator_point)
ORDER BY identity_name;
''', left, bottom, right, top)
return [dict(record) for record in records]
def run_bot():
loop = asyncio.get_event_loop()
async def init_connection(conn):
await conn.set_type_codec("jsonb",
encoder=json.dumps,
decoder=json.loads,
schema="pg_catalog")
try:
pool = loop.run_until_complete(
asyncpg.create_pool(config.postgresql, init=init_connection))
except Exception:
print(f"Could not connect not Postgres database. Exiting")
traceback.print_exc()
return
bot = ProLog(command_prefix=None)
bot.modules = data.modules
bot.db = pool
bot.DatabaseFunctions = DatabaseFunctions(bot)
bot.command_prefix = lambda b, m: bot.DatabaseFunctions.get_prefixes(m)
bot.run(config.token)
async def run():
credentials = {"user": '******', 'password': '******', 'database':'server_data', 'host':'localhost'}
out = {}
async with asyncpg.create_pool(**credentials, min_size=1, max_size=1) as pool:
async with pool.acquire() as conn:
for num, emb in d.items():
await conn.execute(request, num, emb)
async def main(loop):
async with asyncpg.create_pool(dsn=CONNECTION_STRING, loop=loop) as pool:
await update_sql_functions(pool)
min_id, max_id = await get_min_max_ids(pool)
to_process_queue = await create_chunk_queue(min_id, max_id, loop)
processed_queue = asyncio.Queue(loop=loop)
await run_pipelines(to_process_queue, to_process_queue.qsize() - 1, processed_queue, loop, pool)
def __init__(self):
self.pool = asyncpg.create_pool(host=warnings_config.PG_Host,
database=warnings_config.PG_Database,
user=warnings_config.PG_User,
password=warnings_config.PG_Password)
asyncio.get_event_loop().run_until_complete(self.create_tables())
def __init__(self, dsn, processes=1):
self.dsn = dsn
self.processes = processes
self.loop = asyncio.get_event_loop()
future = asyncio.ensure_future(
asyncpg.create_pool(dsn, init=DBConnectInit))
future.add_done_callback(functools.partial(self.pool_made, self))
async def write_data():
accounts, copy = tee(_load_accounts_data())
async with asyncpg.create_pool(**PG_CREDS) as pool:
await asyncio.gather(
_write_data(pool, 'accounts', accounts, _accounts_mapper),
_write_data(pool, 'likes', copy, _likes_mapper),
)
def setup(bot: commands.Bot):
if bot.config is None:
bot.log.warn("Can't connect to Postgres, reason: no external config file.")
config = bot.config.get("postgres", {})
try:
if config.get("as_pool", False):
bot.postgres = bot.loop.run_until_complete(create_pool(
host=config.get("host", "127.0.0.1"),
port=config.get("port", "5432"),
user=config.get("user", "postgres"),
password=config.get("password"),
database=config.get("database", "postgres")
))
else:
bot.postgres = bot.loop.run_until_complete(connect(
host=config.get("host", "127.0.0.1"),
port=config.get("port", "5432"),
user=config.get("user", "postgres"),
password=config.get("password"),
database=config.get("database", "postgres")
))
bot.log.info("Successfully connected to Postgres server.")
bot.dispatch("postgres_connect")
bot.add_cog(Plugin(bot))
except:
bot.log.fatal(
msg="Can't connect to Postgres, reason: error occured when making connection.",
exc_info=True
)
async def main(recipe_file, workers, source):
"""
adds recipes to database created by create_tables.py
input => recipe file in the form of a dictionary
with the following keys:
- "ingredients" => List of dictionaries
each one contains
ingredient text as a single
key and the text for the
ingredient as a value
- "title" => name of the recipe
- "url" => url of the recipe
- "instructions" => steps to complete the recipe
=> workers is for concurrency
Output <= None
"""
recipes = await load_file(recipe_file)
picture_dict = await load_file('recipes/layer2.json')
pic_dic = {i['id']: i['images'][0] for i in picture_dict}
database_address_string = ('postgres://localhost:5435/recipes_dev'
'?user=postgres&password=postgres')
async with asyncpg.create_pool(database_address_string) as pool:
q = Queue()
for recipe in recipes:
q.put_nowait(recipe)
async with aiohttp.ClientSession() as sess:
tasks = [worker(sess, q, i, pool, pic_dic) for i in range(workers)]
await asyncio.gather(*tasks)
async def get_spawnpoints_async():
async with create_pool(**conf.DB) as pool:
async with pool.acquire() as conn:
async with conn.transaction():
return await conn.fetch(
'SELECT spawn_id, despawn_time, lat, lon, duration FROM spawnpoints'
)
async def get_gyms_async(names=POKEMON):
async with create_pool(**conf.DB) as pool:
async with pool.acquire() as conn:
async with conn.transaction():
results = await conn.fetch('''
SELECT
fs.fort_id,
fs.id,
fs.team,
fs.prestige,
fs.guard_pokemon_id,
fs.last_modified,
f.lat,
f.lon
FROM fort_sightings fs
JOIN forts f ON f.id=fs.fort_id
WHERE (fs.fort_id, fs.last_modified) IN (
SELECT fort_id, MAX(last_modified)
FROM fort_sightings
GROUP BY fort_id
)
''')
return [{
'id': 'fort-' + str(fort['fort_id']),
'sighting_id': fort['id'],
'prestige': fort['prestige'],
'pokemon_id': fort['guard_pokemon_id'],
'pokemon_name': names[fort['guard_pokemon_id']],
'team': fort['team'],
'lat': fort['lat'],
'lon': fort['lon']
} for fort in results]
def process_posts(posts,
pg_username,
pg_password,
pg_database,
pg_host,
batch_size=10):
from itertools import islice, count, chain
log = logging.getLogger('process_worker')
log.info("creating connection to the database...")
# Make a connection for the database:
loop = asyncio.get_event_loop()
pg_pool = loop.run_until_complete(
asyncpg.create_pool(user=pg_username,
password=pg_password,
database=pg_database,
host=pg_host))
log.info("success! {}".format(pg_pool))
def co(p):
return process_post(p, pg_pool)
# batch the processing... otherwise we never return to the database connections
for i in count():
try:
nv = next(posts)
log.info("finding posts {} - {}".format(i * batch_size,
(i + 1) * batch_size))
async_map(co, chain([nv], islice(posts, batch_size)))
except StopIteration:
return
def result_handler(dbconf,
res_queue,
work_done,
par,
use_temp=False,
pool_size=3):
logger = logging.getLogger('WoTServer')
# Not availabile until Python 3.7. Use 3.6-compatible syntax for now
# asyncio.run(create_helpers(db_pool, res_queue, work_done))
logger.debug('Creating event loop')
loop = asyncio.new_event_loop()
asyncio.set_event_loop(loop)
db_pool = loop.run_until_complete(
create_pool(min_size=pool_size, max_size=pool_size, **dbconf))
logger.debug('Event loop created for Process-%i', par)
try:
loop.run_until_complete(
asyncio.gather(*[
send_results_to_database(
db_pool, res_queue, work_done, par, c,
'player_tanks' if not use_temp else 'temp_player_tanks')
for c in range(pool_size)
]))
finally:
loop.close()
def start_up(self):
'''Command that starts AdamBot, is run in AdamBot.__init__'''
self.load_cogs()
self.loop.create_task(self.execute_todos())
self.pool: asyncpg.pool.Pool = self.loop.run_until_complete(
asyncpg.create_pool(self.DB + "?sslmode=require", max_size=20))
self.run(os.environ.get('TOKEN'))
def __init__(self):
intents = discord.Intents(guilds=True, messages=True, reactions=True,
bans=True, members=True)
allowed_mentions = discord.AllowedMentions(everyone=False, users=True, roles=False)
super().__init__(command_prefix='?', fetch_offline_members=False,
help_command=meta.PWBotHelp(command_attrs={
'brief': 'Display all commands available',
'help': 'Display all commands available,\
will display additional info if a command is specified'
}), allowed_mentions=allowed_mentions, intents=intents
)
self.client_id = config.client_id
self.settings = utils.Settings()
self.weather_key = config.weather_key
loop = asyncio.get_event_loop()
try:
self.pool = loop.run_until_complete(asyncpg.create_pool(config.postgresql))
except Exception:
print('Failed set up PostgreSQL pool, exiting')
raise
for extension in initial_extensions:
try:
self.load_extension(extension)
except commands.ExtensionError:
print(f'Failed to load extension {extension}', file=sys.stderr)
traceback.print_exc()
def __init__(self, command_prefix, **options):
super().__init__(command_prefix, **options)
pgloop = asyncio.get_event_loop()
f = pickle.load(open('credentials.pkl', 'rb'))
self.pool = pgloop.run_until_complete(
asyncpg.create_pool(dsn=f["postgres_uri"],
host=f["postgres_host"],
user=f["postgres_user"],
port=f["postgres_port"],
password=f["postgres_password"],
database=f["postgres_database"]))
self.reddit = apraw.Reddit(client_id=f['reddit_id'],
client_secret=f['reddit_secret'],
user_agent="Eclipse")
self.memes = []
self.brain_id = f['brain_id']
self.brain_api = f['brain_api']
self.token = f["discord"]
self.sra_api = f['some_random_api']
self.launch_time = datetime.datetime.utcnow()
self.gameboy = False
self.color = discord.Color.from_rgb(156, 7, 241)
with open("config/config.json", "r") as read_file:
data = json.load(read_file)
self.config = data
self.flask_instance: Flask = None
self.flask_thread: multiprocessing.Process = None
async def start(self, token: str, **kwargs: Any):
"Get everything ready in async env"
cache_info = self.config["Redis Info"]
db_info = self.config["PostgreSQL Info"]
self.cache_db = aioredis.from_url(**cache_info)
self.pool, self.gtts = await asyncio.gather(
cast(Awaitable[Pool], asyncpg.create_pool(**db_info)),
asyncgTTS.setup(premium=False, session=self.session),
)
# Fill up bot.channels, as a load of webhooks
for channel_name, webhook_url in self.config["Webhook URLs"].items():
self.channels[channel_name] = discord.Webhook.from_url(
webhook_url, session=self.session, bot_token=self.http.token
)
# Load all of /cogs and /extensions
self.load_extensions("cogs")
self.load_extensions("extensions")
# Send starting message and actually start the bot
if self.shard_ids is not None:
prefix = f"`[Cluster] [ID {self.cluster_id}] [Shards {len(self.shard_ids)}]`: "
self.websocket = await self.create_websocket()
kwargs["reconnect"] = True
else:
prefix = ""
self.websocket = None
self.logger = utils.setup_logging(config["Main"]["log_level"], prefix, self.session)
self.logger.info("Starting TTS Bot!")
await automatic_update.do_normal_updates(self)
await super().start(token, **kwargs)
async def save_to_database(data):
"""Save price data in a database"""
rows = data.itertuples(index=False)
values = [list(row) for row in rows]
sql = """
INSERT INTO price_history (date, symbol_id, open, high, low,
close, adj_close, volume)
VALUES ($1, (SELECT id FROM symbols WHERE symbol=$2),
$3, $4, $5, $6, $7, $8)
ON CONFLICT (symbol_id, date)
DO UPDATE
SET symbol_id=excluded.symbol_id,
date=excluded.date, open=excluded.open,
high=excluded.high, low=excluded.low,
close=excluded.close,
adj_close=excluded.adj_close,
volume=excluded.volume;
"""
# using asynciopg as the Database connection manager for Async
creds = {
'host': S.DB_HOST,
'database': S.DB_NAME,
'user': S.DB_USER,
'password': S.DB_PASSWORD
}
async with asyncpg.create_pool(**creds) as pool:
async with pool.acquire() as db:
async with db.transaction():
await db.executemany(sql, values)
Database().timestamp('price_history', close=True)
def main():
loop = asyncio.get_event_loop()
pool = loop.run_until_complete(
asyncpg.create_pool(config.dsn, command_timeout=60))
loop.run_until_complete(create_db(pool))
bot = Bot(pool=pool, loop=loop)
bot.run(config.token)
def client(loop, aiohttp_client):
app = web.Application()
app.add_routes([
web.get('/api/v1/system/is_alive', is_alive),
web.get('/api/v1/producer/send', send_to_kafka_topic),
web.get('/api/v1/consumer/start', start_consumer),
web.get('/api/v1/consumer/stop', stop_consumer),
web.get('/api/v1/postgres/events', get_events_from_pg)
])
app.pool = loop.run_until_complete(
asyncpg.create_pool(dsn=settings.POSTGRES_URL,
min_size=2,
max_size=20,
loop=loop))
if settings.ENVIRONMENT == "aiven":
app.ssl_context = create_ssl_context(
cafile=settings.SSL_CAFILE_KAFKA,
certfile=settings.SSL_CERTFILE_KAFKA,
keyfile=settings.SSL_KEYFILE)
else:
app.ssl_context = None
yield loop.run_until_complete(aiohttp_client(app))
if hasattr(app, "consumer"):
loop.run_until_complete(app.consumer.stop())
loop.run_until_complete(app.pool.close())
def __init__(self,
*,
settings_file: typing.Union[str, os.PathLike],
pokeapi_file: typing.Union[str, os.PathLike] = None,
**kwargs):
# Load settings
self.settings = Settings(settings_file)
super().__init__(activity=discord.Game(self.settings.game), **kwargs)
self._ctx_cache: dict[tuple[int, int], list[MyContext, set[int]]] = {}
self.log_info('Connecting database')
self._pool = asyncpg.create_pool(
'postgres://{username}:{password}@{host}/{dbname}'.format(
**self.settings.database))
self.loop.run_until_complete(self._pool)
# Reboot handler
self.reboot_after = True
# Uptime
self._alive_since: typing.Optional[datetime.datetime] = None
# PokeAPI
self._pokeapi: typing.Optional[PokeApi]
if pokeapi_file:
self._pokeapi = PokeApi(pokeapi_file,
factory=PokeApiConnection,
uri=True)
else:
self._pokeapi = None
def __init__(self, *args, **kwargs):
"""Initial function that runs when the class has been created."""
# Declare memory and load config.
self.memory = {}
with open('config.json', encoding='utf8') as data:
self.config = json.load(data, object_hook=lambda d: namedtuple('X', d.keys())(*d.values()))
# Declare intents.
intents = discord.Intents.default()
intents.members = True
intents.presences = True
# Call the initialize of the bot itself.
super().__init__(
command_prefix=commands.when_mentioned_or(self.config.prefix),
intents=intents,
*args,
**kwargs
)
# Configure database.
self.db = asyncio.get_event_loop().run_until_complete(
asyncpg.create_pool(self.config.postgre)
)
async def main():
tty.setcbreak(0)
os.system('clear')
rows = move_to_bottom_of_screen()
async def redraw_output(items: deque):
save_cursor_position()
move_to_top_of_screen()
for item in items:
delete_line()
print(item)
restore_cursor_position()
messages = MessageStore(redraw_output, rows - 1)
stdin_reader = await create_stdin_reader()
async with asyncpg.create_pool(host='127.0.0.1',
port=5432,
user='******',
password='******',
database='products',
min_size=6,
max_size=6) as pool:
while True:
query = await read_line(stdin_reader)
asyncio.create_task(run_query(query, pool, messages))
async def get_gyms_async(names=POKEMON):
async with create_pool(**conf.DB) as pool:
async with pool.acquire() as conn:
async with conn.transaction():
results = await conn.fetch('''
SELECT
fs.fort_id,
fs.id,
fs.team,
fs.prestige,
fs.guard_pokemon_id,
fs.last_modified,
f.lat,
f.lon
FROM fort_sightings fs
JOIN forts f ON f.id=fs.fort_id
WHERE (fs.fort_id, fs.last_modified) IN (
SELECT fort_id, MAX(last_modified)
FROM fort_sightings
GROUP BY fort_id
)
''')
return [{
'id': 'fort-' + str(fort['fort_id']),
'sighting_id': fort['id'],
'prestige': fort['prestige'],
'pokemon_id': fort['guard_pokemon_id'],
'pokemon_name': names[fort['guard_pokemon_id']],
'team': fort['team'],
'lat': fort['lat'],
'lon': fort['lon']
} for fort in results]
def starter(self):
"""Runs the bot"""
try:
# dsn = os.environ['dsn'] or self.get_config('DSN')
print("Connecting to database ...")
pool_pg = self.loop.run_until_complete(
asyncpg.create_pool(dsn=self.get_config('DSN')))
print("Connected to PostgreSQL server!")
except Exception as e:
print("Could not connect to database:", e)
else:
print("Connecting to Discord ...")
self.uptime = datetime.datetime.utcnow()
self.db = pool_pg
extensions = [
'jishaku',
'cogs.useful',
'cogs.owner',
'cogs.prefixes',
'cogs.economy',
'cogs.errorhandler',
'cogs.fun',
'cogs.utilities',
'cogs.polaroid_manipulation',
'cogs.music',
]
for extension in extensions:
self.load_extension(extension)
self.create_command_list()
self.run(self.get_config('token'))
async def get_pokemarkers_async(after_id):
async with create_pool(**conf.DB) as pool:
async with pool.acquire() as conn:
async with conn.transaction():
results = await conn.fetch('''
SELECT id, pokemon_id, expire_timestamp, lat, lon, atk_iv, def_iv, sta_iv, move_1, move_2
FROM sightings
WHERE expire_timestamp > {ts} AND id > {poke_id}
'''.format(ts=time(), poke_id=after_id))
return tuple(map(sighting_to_marker, results))
async def test_pool_02(self):
for n in {1, 3, 5, 10, 20, 100}:
with self.subTest(tasksnum=n):
addr = self.cluster.get_connection_addr()
async with asyncpg.create_pool(host=addr[0], port=addr[1],
database='postgres',
loop=self.loop, min_size=5,
max_size=5) as pool:
async def worker():
con = await pool.acquire(timeout=1)
self.assertEqual(await con.fetchval('SELECT 1'), 1)
await pool.release(con)
tasks = [worker() for _ in range(n)]
await asyncio.gather(*tasks, loop=self.loop)
def create_pool(*args,
dialect=None,
connection_class=_SAConnection,
**connect_kwargs):
class SAConnection(connection_class):
def __init__(self, *args, dialect=dialect, **kwargs):
super().__init__(*args, dialect=dialect, **kwargs)
connection_class = SAConnection
# dict is fine on the pool object as there is usually only one of them
# asyncpg.pool.Pool.__slots__ += ('__dict__',)
# monkey patch pool to have some extra methods
def transaction(self, **kwargs):
return ConnectionTransactionContextManager(self, **kwargs)
asyncpg.pool.Pool.transaction = transaction
asyncpg.pool.Pool.begin = transaction
pool = asyncpg.create_pool(*args, connection_class=connection_class,
**connect_kwargs)
return pool
async def get_spawnpoints_async():
async with create_pool(**conf.DB) as pool:
async with pool.acquire() as conn:
async with conn.transaction():
return await conn.fetch('SELECT spawn_id, despawn_time, lat, lon, duration FROM spawnpoints')
def init_db_pool(loop):
coro = asyncpg.create_pool(host=DB_HOST, user=DB_USER, password=DB_PASSWORD, database=DATABASE_NAME)
pool = loop.run_until_complete(coro)
return pool
async def get_pokestops_async():
async with create_pool(**conf.DB) as pool:
async with pool.acquire() as conn:
async with conn.transaction():
return await conn.fetch('SELECT external_id, lat, lon FROM pokestops')