def pool(event_loop):
event_loop = asyncio.get_event_loop()
conn_args = dict(database='postgres', loop=event_loop)
conn_args.update(EXTENDED_CONN_ARGS)
adminconn = event_loop.run_until_complete(asyncpg.connect(**conn_args))
event_loop.run_until_complete(
adminconn.execute('CREATE DATABASE sasyncpg_test'))
conn_args = dict(database='sasyncpg_test', loop=event_loop)
conn_args.update(EXTENDED_CONN_ARGS)
conn = event_loop.run_until_complete(asyncpg.connect(**conn_args))
event_loop.run_until_complete(conn.execute('CREATE EXTENSION "hstore"'))
event_loop.run_until_complete(
conn.execute(
"CREATE TABLE users ("
" id serial NOT NULL PRIMARY KEY,"
" name varchar(25) NOT NULL,"
" password varchar(25) NOT NULL,"
" validity daterange,"
" max_renew interval,"
" options hstore,"
" details jsonb,"
" CONSTRAINT uk_users UNIQUE (name)"
");"
"INSERT INTO users (name, password, validity, max_renew) VALUES"
" ('admin', 'nimda', '(-INFINITY,INFINITY)', NULL),"
" ('secretary', 'secret', '[2017-11-30,2018-11-30]', '1 year 2 months 3 days'),"
" ('ceo', 'ultrasecret', '[2017-11-01,INFINITY]', NULL),"
" ('inter', 'retni', '[2017-11-30,2017-12-31]', '1 month')"))
event_loop.run_until_complete(conn.close())
conn_args = dict(
database='sasyncpg_test',
min_size=1,
max_size=10,
init=register_custom_codecs,
loop=event_loop,
)
conn_args.update(EXTENDED_CONN_ARGS)
pool = event_loop.run_until_complete(asyncpg.create_pool(**conn_args))
try:
yield pool
finally:
event_loop.run_until_complete(pool.close())
event_loop.run_until_complete(
adminconn.execute('DROP DATABASE sasyncpg_test'))
event_loop.run_until_complete(adminconn.close())
def _test_connection(self, timeout=60):
self._connection_addr = None
loop = asyncio.new_event_loop()
try:
for i in range(timeout):
if self._connection_addr is None:
self._connection_addr = \
self._connection_addr_from_pidfile()
if self._connection_addr is None:
time.sleep(1)
continue
try:
con = loop.run_until_complete(
asyncpg.connect(database='postgres', timeout=5,
loop=loop, **self._connection_addr))
except (OSError, asyncio.TimeoutError,
asyncpg.CannotConnectNowError,
asyncpg.PostgresConnectionError):
time.sleep(1)
continue
except asyncpg.PostgresError:
# Any other error other than ServerNotReadyError or
# ConnectionError is interpreted to indicate the server is
# up.
break
else:
loop.run_until_complete(con.close())
break
finally:
loop.close()
return 'running'
def _test_connection(self, timeout=60):
self._connection_addr = None
loop = asyncio.new_event_loop()
try:
for i in range(timeout):
if self._connection_addr is None:
conn_spec = self._get_connection_spec()
if conn_spec is None:
time.sleep(1)
continue
try:
con = loop.run_until_complete(
asyncpg.connect(database='postgres',
timeout=5, loop=loop,
**self._connection_addr))
except (OSError, asyncio.TimeoutError,
asyncpg.CannotConnectNowError,
asyncpg.PostgresConnectionError):
time.sleep(1)
continue
except asyncpg.PostgresError:
# Any other error other than ServerNotReadyError or
# ConnectionError is interpreted to indicate the server is
# up.
break
else:
loop.run_until_complete(con.close())
break
finally:
loop.close()
return 'running'
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
# boot time (for uptime)
self.boot_time = datetime.datetime.utcnow()
# hack because __init__ cannot be async
loop = asyncio.get_event_loop()
redis_coroutine = aioredis.create_redis((cfg.redis_url, 6379),
loop=loop)
# aioredis connection
self.redis = loop.run_until_complete(redis_coroutine)
# asyncpg
self.pg = loop.run_until_complete(
asyncpg.connect(**cfg.postgresql_auth))
# aiohttp session used for fetching data
self.session = aiohttp.ClientSession()
# sentry connection for reporting exceptions
self.sentry = raven.Client(cfg.raven_client_url)
# asyncio task that POSTs to bots.discord.pw with the guild count every
# 10 minutes
self.report_task = None
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
)
def db(ctx):
config = get_config()
loop = asyncio.get_event_loop()
connection = loop.run_until_complete(asyncpg.connect(config.settings.dsn))
ctx.obj = types.SimpleNamespace(loop=loop, connection=connection)
ctx.call_on_close(functools.partial(
loop.run_until_complete, connection.close()))
async def get_domains(loop):
async with Closing(asyncpg.connect(**config.DATABASE)) as connection:
hosts = [
row.name for row in await connection.execute(
'SELECT name FROM host WHERE type_id=2')
]
return hosts
def __init__(self, dsn):
try:
self.loop = asyncio.get_event_loop()
self.conn = self.loop.run_until_complete(asyncpg.connect(dsn))
self._modules = set()
except Exception as e:
raise e
def migrate():
"""Migrate from the last version.
To migrate between multiple version you'll need to run each
version's migration in ascending order.
"""
click.echo('Migrating database.')
run = asyncio.get_event_loop().run_until_complete
conn = run(asyncpg.connect(config.postgresql))
queries = [
"""CREATE TABLE IF NOT EXISTS suggestions (
message_id BIGINT PRIMARY KEY,
author_id BIGINT NOT NULL,
sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
content VARCHAR(2000) NOT NULL,
upvotes INT DEFAULT 1,
downvotes INT DEFAULT 1
);""",
"""ALTER TABLE tickets DROP COLUMN type;""",
"""ALTER TABLE reports ADD status_message_id BIGINT;""",
]
for query in queries:
try:
run(conn.execute(query))
except Exception:
click.echo(
'Failed to execute query\n' + traceback.format_exc(),
err=True,
)
def connect():
import asyncpg
return asyncpg.connect(host='localhost',
user='******',
password='******',
database='sunqf',
command_timeout=60)
def __init__(self, loop=None, conn_str=CONN_STR):
self.conn_str = conn_str
self._connection = connect(self.conn_str,
statement_cache_size=0,
max_cached_statement_lifetime=0,
max_cacheable_statement_size=0,
timeout=20,
loop=loop or get_event_loop())
def setup(self):
import asyncio
import asyncpg
self.loop = asyncio.get_event_loop()
self.conn = self.loop.run_until_complete(asyncpg.connect())
# warmup the connection
self.loop.run_until_complete(self.conn.fetch('SELECT 1'))
def conn(event_loop):
conn = event_loop.run_until_complete(
asyncpg.connect(
user=DB_SETTINGS["user"],
password=DB_SETTINGS["password"],
database=DB_SETTINGS["name"],
host=DB_SETTINGS["host"],
port=DB_SETTINGS["port"],
)
)
yield conn
event_loop.run_until_complete(conn.close())
def setUp(self):
super().setUp()
self._tracer = self.tracer_provider.get_tracer(__name__)
AsyncPGInstrumentor().instrument(tracer_provider=self.tracer_provider)
self._connection = async_call(
asyncpg.connect(
database=POSTGRES_DB_NAME,
user=POSTGRES_USER,
password=POSTGRES_PASSWORD,
host=POSTGRES_HOST,
port=POSTGRES_PORT,
))
def postgresql_server(docker, session_id, loop, request):
image = 'postgres:{}'.format('latest')
if sys.platform.startswith('darwin'):
port = unused_port()
else:
port = None
container = docker.containers.run(
image=image,
detach=True,
name='postgresql-test-server-{}-{}'.format('latest', session_id),
ports={
'5432/tcp': port,
},
environment={
'http.host': '0.0.0.0',
'transport.host': '127.0.0.1',
},
)
if sys.platform.startswith('darwin'):
host = '0.0.0.0'
else:
inspection = docker.api.inspect_container(container.id)
host = inspection['NetworkSettings']['IPAddress']
port = 5432
delay = 0.1
for i in range(20):
try:
conn = loop.run_until_complete(
asyncpg.connect(host=host,
port=port,
user='******',
database='postgres',
loop=loop))
loop.run_until_complete(conn.execute('SELECT now()'))
loop.run_until_complete(conn.close())
break
except (ConnectionRefusedError,
asyncpg.exceptions.CannotConnectNowError) as e:
time.sleep(delay)
delay *= 2
else:
pytest.fail("Cannot start postgresql server")
yield {'host': host, 'port': port, 'container': container}
container.kill(signal=9)
container.remove(force=True)
def setUpClass(cls):
super().setUpClass()
cls._connection = None
cls._cursor = None
cls._tracer = cls.tracer_provider.get_tracer(__name__)
AsyncPGInstrumentor().instrument(tracer_provider=cls.tracer_provider)
cls._connection = async_call(
asyncpg.connect(
database=POSTGRES_DB_NAME,
user=POSTGRES_USER,
password=POSTGRES_PASSWORD,
host=POSTGRES_HOST,
port=POSTGRES_PORT,
))
async def setup_database():
conn = await (asyncpg.connect(
user="******",
password="******",
host="localhost",
database="test",
))
await (conn.execute("drop table if exists mytable"))
await (conn.execute("create table if not exists "
"mytable (id serial primary key, data varchar)"))
await conn.close()
def _test_connection(self, timeout=60):
self._connection_addr = None
loop = asyncio.new_event_loop()
connected = False
try:
for n in range(timeout + 1):
# pg usually comes up pretty quickly, but not so
# quickly that we don't hit the wait case. Make our
# first sleep pretty short, to shave almost a second
# off the happy case.
sleep_time = 1 if n else 0.05
if self._connection_addr is None:
conn_addr = self._get_connection_addr()
if conn_addr is None:
time.sleep(sleep_time)
continue
try:
con = loop.run_until_complete(
asyncpg.connect(database='postgres',
user='******',
timeout=5,
loop=loop,
host=self._connection_addr[0],
port=self._connection_addr[1]))
except (OSError, asyncio.TimeoutError,
asyncpg.CannotConnectNowError,
asyncpg.PostgresConnectionError):
time.sleep(sleep_time)
continue
except asyncpg.PostgresError:
# Any other error other than ServerNotReadyError or
# ConnectionError is interpreted to indicate the server is
# up.
break
else:
connected = True
loop.run_until_complete(con.close())
break
finally:
loop.close()
if connected:
return 'running'
else:
return 'not-initialized'
async def run_request():
conn = await (asyncpg.connect(
user="******",
password="******",
host="localhost",
database="test",
))
for i in range(num_recs):
random_data = "random %d" % (random.randint(1, 1000000))
retval = await add_and_select_data(conn, random_data)
assert retval == random_data, "%s != %s" % (retval, random_data)
await (conn.close())
async def _fetch_domains():
url = 'https://isc.sans.edu/feeds/suspiciousdomains_High.txt'
async with aiohttp.ClientSession() as session:
async with session.get(url) as response:
response.raise_for_status()
domains = [
line for line in (await response.text()).split('\n')
if not line.startswith('#') and '.' in line
]
async with Closing(asyncpg.connect(**config.DATABASE)) as connection:
await connection.executemany(
'INSERT INTO host(name, type_id) VALUES ($1, $2)'
' ON CONFLICT (name) DO NOTHING;',
[(domain, 2) for domain in domains])
def __init__(self, *args, **kwargs):
super().__init__(get_prefix, *args, **kwargs)
self.loop = asyncio.get_event_loop()
self.session = aiohttp.ClientSession()
self.start_time = dt.datetime.now()
self.config = toml.load('config.toml')
self.db = self.loop.run_until_complete(
asyncpg.connect(user=self.config['default']['db_user'],
password=self.config['default']['db_password'],
database=self.config['default']['db_name'],
host='127.0.0.1'))
# Cache stuff
self.stats = {}
self.prefixes = {}
self.cache = {}
self.disabledCommands = []
self.blacklistedUsers = []
self.reactionRoleDict = self.loop.run_until_complete(
self.cache_reactionroles())
records = self.loop.run_until_complete(
self.db.fetch("SELECT * FROM blacklist"))
for i in records:
self.blacklistedUsers.append(i["id"])
records = self.loop.run_until_complete(
self.db.fetch("SELECT * FROM guild_config"))
self.prefixes = dict(
self.loop.run_until_complete(
self.db.fetch("SELECT id, prefix FROM guild_config")))
for record in records:
d = {
record["id"]: {
"prefix": record["prefix"],
"autorole": record["autorole"],
"welcomeMessage": record["welcomemessage"],
"welcomeEnabled": record["welcomeenabled"],
"welcomeId": record["welcomeid"],
"logId": record['log_id']
}
}
self.cache.update(d)
def setup_work(config, days=1):
query = 'SELECT account_id, console FROM total_battles_{0} UNION SELECT account_id, console FROM diff_battles_{0}'
start_day = datetime.utcnow() - timedelta(days=1)
conn = ioloop.IOLoop.current().run_sync(
lambda: connect(**config['database']))
result = set()
for d in range(days):
day = start_day - timedelta(days=d)
try:
result.update(ioloop.IOLoop.current().run_sync(
lambda: conn.fetch(query.format(day.strftime('%Y_%m_%d')))))
except UndefinedTableError:
continue
del conn
return ((i, row['account_id'], row['console'])
for i, row in enumerate(result)), len(result)
def __init__(self):
data_connection = [
'DATABASE_USER', 'DATABASE_PASSWORD', 'DATABASE_HOST',
'DATABASE_PORT', 'DATABASE_NAME'
]
for key in data_connection:
setattr(self, key, os.environ.get(key, None))
if getattr(self, key) == None:
raise Exception("Environment variable {} is None".format(key))
taskConn = asyncpg.connect(user=self.DATABASE_USER,
password=self.DATABASE_PASSWORD,
database=self.DATABASE_NAME,
host=self.DATABASE_HOST)
self.__conn = asyncio.get_event_loop().run_until_complete(taskConn)
def setUpClass(cls):
os.system('createdb dqo_test')
cls.db = dqo.Database(
sync_src=lambda: psycopg2.connect("dbname='dqo_test'"),
async_src=lambda: asyncpg.connect(database='dqo_test'))
super().setUpClass()
def asyncpg_connect():
return asyncpg.connect(host=_pghost,
database=_pgdb,
user=_pguser,
password=_pgpassword)
def connect(*args, **kwargs):
loop = kwargs.pop('loop') or asyncio.new_event_loop()
return Connection(loop.run_until_complete(asyncpg.connect(*args,
**kwargs)),
loop=loop)
import discord
from discord.ext import commands
import asyncpg
conn = asyncpg.connect('messages.db')
c = conn.cursor()
# try:
# c.execute("""CREATE TABLE logs (
# message_id integer unique,
# author_id integer,
# content text,
# channel_id integer,
# created_at text)""")
# conn.commit()
# # c.execute("""CREATE TABLE users (author_id integer unique)""")
# # conn.commit()
# print("tables created")
# except:
# print("tables already created")
class logCog(commands.Cog, name="logs"):
def __init__(self, bot):
self.bot = bot
@commands.command()
@commands.has_permissions(manage_roles=True)
async def getlogs(self, ctx):
def get_postgres_connection():
return asyncpg.connect(POSTGRES_CONN_STR, ssl=True)
def test_unix_socket_connect(event_loop):
with pytest.raises(OSError):
event_loop.run_until_complete(asyncpg.connect("postgres://?host=/.s.PGSQL.THIS_FILE_BETTER_NOT_EXIST"))
def __init__(self, conn_str=CONN_STR):
self.conn_str = conn_str
self._connection = connect(self.conn_str, statement_cache_size=0)
def __init__(self, conn_str=CONN_STR):
self.conn_str = conn_str
self._connection = connect(self.conn_str, statement_cache_size=0)
self._account_name = DB_NAME
