def __init__(self, url: DatabaseURL):
url = DatabaseURL(url)
if url.scheme == "postgres":
# The default postgres backend for databases does not return
# RowProxy objects, unlike all the other backends.
# Therefore, we use aiopg so that we have dialect-agnostic results.
url = url.replace(scheme="postgres+aiopg")
self.url = url
self.database = DatabaseBackend(url)
self.metadata = MetaData()
class DatabaseModelMetaclass(orm.models.ModelMetaclass):
def __new__(
cls: type,
name: str,
bases: typing.Sequence[type],
attrs: dict,
) -> type:
attrs["__database__"] = self.database
attrs["__metadata__"] = self.metadata
return super(DatabaseModelMetaclass,
cls).__new__(cls, name, bases, attrs)
class DatabaseModel(orm.Model, metaclass=DatabaseModelMetaclass):
__abstract__ = True
self.Model = DatabaseModel
def test_database_url_constructor():
with pytest.raises(TypeError):
DatabaseURL(
("postgresql", "username", "password", "localhost", "mydatabase"))
u = DatabaseURL(
"postgresql+asyncpg://username:password@localhost:123/mydatabase")
assert DatabaseURL(u) == u
def test_database_url_repr():
u = DatabaseURL("postgresql://localhost/name")
assert repr(u) == "DatabaseURL('postgresql://localhost/name')"
u = DatabaseURL("postgresql://username@localhost/name")
assert repr(u) == "DatabaseURL('postgresql://username@localhost/name')"
u = DatabaseURL("postgresql://*****:*****@localhost/name")
assert repr(u) == "DatabaseURL('postgresql://*****:*****@localhost/name')"
def test_database_url_escape():
u = DatabaseURL(
f"postgresql://*****:*****@localhost/mydatabase")
assert u.username == "username"
assert u.password == "[password"
assert u.userinfo == f"username:{quote('[password')}".encode("utf-8")
u2 = DatabaseURL(u)
assert u2.password == "[password"
u3 = DatabaseURL(str(u))
assert u3.password == "[password"
async def test_iterate_outside_transaction_with_temp_table(database_url):
"""
Same as test_iterate_outside_transaction_with_values but uses a
temporary table instead of a list of values.
"""
database_url = DatabaseURL(database_url)
if database_url.dialect == "sqlite":
pytest.skip("SQLite interface does not work with temporary tables.")
async with Database(database_url) as database:
query = "CREATE TEMPORARY TABLE no_transac(num INTEGER)"
await database.execute(query)
query = "INSERT INTO no_transac(num) VALUES (1), (2), (3), (4), (5)"
await database.execute(query)
query = "SELECT * FROM no_transac"
iterate_results = []
async for result in database.iterate(query=query):
iterate_results.append(result)
assert len(iterate_results) == 5
assert iterate_results == [(1, ), (2, ), (3, ), (4, ), (5, )]
def __init__(self, loggingdb_path):
url = f"sqlite:///{loggingdb_path}"
self.database_url = DatabaseURL(url)
engine = sqlalchemy.create_engine(url)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
self.session = Session()
def test_global_connection_is_initialized_lazily(database_url):
"""
Ensure that global connection is initialized at latest possible time
so it's _query_lock will belong to same event loop that async_adapter has
initialized.
See https://github.com/encode/databases/issues/157 for more context.
"""
database_url = DatabaseURL(database_url)
if database_url.dialect != "postgresql":
pytest.skip("Test requires `pg_sleep()`")
database = Database(database_url, force_rollback=True)
@async_adapter
async def run_database_queries():
async with database:
async def db_lookup():
await database.fetch_one("SELECT pg_sleep(1)")
await asyncio.gather(db_lookup(), db_lookup())
run_database_queries()
def db(pgsql):
from asyncom import OMDatabase
from databases import DatabaseURL
host, port = pgsql
url = f'postgresql://postgres@{host}:{port}/guillotina'
dbins = OMDatabase(DatabaseURL(url), force_rollback=True)
yield dbins
async def database_exists(url: str) -> bool:
url = DatabaseURL(url)
database_name = url.database
if url.dialect in ("postgres", "postgresql"):
url = url.replace(database="postgres")
elif url.dialect == "mysql":
url = url.replace(database="")
if url.dialect in ("postgres", "postgresql"):
statement = "SELECT 1 FROM pg_database WHERE datname='%s'" % database_name
elif url.dialect == "mysql":
statement = ("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA "
"WHERE SCHEMA_NAME = '%s'" % database_name)
elif url.dialect == "sqlite":
if database_name == ":memory:" or not database_name:
return True
if not os.path.isfile(
database_name) or os.path.getsize(database_name) < 100:
return False
with open(database_name, "rb") as file:
header = file.read(100)
return header[:16] == b"SQLite format 3\x00"
async with Database(url) as database:
return bool(await database.fetch_one(statement))
async def create_database(url: str, encoding: str = "utf8") -> None:
url = DatabaseURL(url)
database_name = url.database
if url.dialect in ("postgres", "postgresql"):
url = url.replace(database="postgres")
elif url.dialect == "mysql":
url = url.replace(database="")
if url.dialect in ("postgres", "postgresql"):
statement = "CREATE DATABASE {0} ENCODING '{1}' TEMPLATE template1".format(
database_name,
encoding,
)
statements = [statement]
elif url.dialect == "mysql":
statement = "CREATE DATABASE {0} CHARACTER SET = '{1}'".format(
database_name, encoding)
statements = [statement]
elif url.dialect == "sqlite":
if database_name and database_name != ":memory:":
statements = ["CREATE TABLE DB(id int);", "DROP TABLE DB;"]
else:
statements = []
async with Database(url) as database:
for statement in statements:
await database.execute(statement)
def __init__(self, **values: Any):
super().__init__(**values)
if not self.SECRET_KEY:
self.SECRET_KEY = hexlify(os.urandom(32))
self.MONGODB_URL = DatabaseURL(
f"mongodb://{self.MONGO_USER}:{self.MONGO_PASS}@{self.MONGO_HOST}:"
f"{self.MONGO_PORT}/{self.MONGO_DB}?authSource=admin")
self.DATABASE_NAME = self.MONGO_DB
def test_replace_database_url_components():
u = DatabaseURL("postgresql://localhost/mydatabase")
assert u.database == "mydatabase"
new = u.replace(database="test_" + u.database)
assert new.database == "test_mydatabase"
assert str(new) == "postgresql://localhost/test_mydatabase"
assert u.driver == ""
new = u.replace(driver="asyncpg")
assert new.driver == "asyncpg"
assert str(new) == "postgresql+asyncpg://localhost/mydatabase"
assert u.port is None
new = u.replace(port=123)
assert new.port == 123
assert str(new) == "postgresql://localhost:123/mydatabase"
def __init__(self, loggingdb_path):
url = f"sqlite:///{loggingdb_path}"
self.database_url = DatabaseURL(url)
engine = sqlalchemy.create_engine(url)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
self.session = Session()
self.loop = asyncio.get_event_loop()
self.lock = asyncio.Lock(loop=self.loop)
def test_database_url_properties():
u = DatabaseURL("postgresql+asyncpg://username:password@localhost:123/mydatabase")
assert u.dialect == "postgresql"
assert u.driver == "asyncpg"
assert u.username == "username"
assert u.password == "password"
assert u.hostname == "localhost"
assert u.port == 123
assert u.database == "mydatabase"
async def db_manager():
db_conn_url = DatabaseURL(settings.DATABASE_CONNECTION_URL + '_test')
database_manager = DatabaseManager(db_conn_url, force_rollback=True)
_url = str(db_conn_url).split('/')
_url_str = DatabaseURL('/'.join(_url[:-1] + ['template1']))
try:
conn = await asyncpg.connect(str(db_conn_url))
print('DB exists')
except asyncpg.InvalidCatalogNameError:
print('DB doesn\'t exist. Creating new one.')
# db_conn_url.components.path = 'template1'
conn = await asyncpg.connect(str(_url_str))
await conn.execute(F'create database {db_conn_url.database} '
F'owner "{db_conn_url.username}"')
with patch.object(
settings,
'DATABASE_CONNECTION_URL',
str(db_conn_url),
):
alembicArgs = [
'--raiseerr',
'-c',
'alembic.ini',
'upgrade',
'head',
]
loop = asyncio.get_running_loop()
await loop.run_in_executor(
executor, lambda: alembic.config.main(argv=alembicArgs))
await database_manager.connect()
yield database_manager
await database_manager.disconnect()
await conn.close()
conn = await asyncpg.connect(str(_url_str))
if not os.environ.get('KEEP_TEST_DB'):
try:
await conn.execute(F'drop database {db_conn_url.database}')
print('DB deleted')
except asyncpg.exceptions.ObjectInUseError:
print('DB likely opened in your IDE. Delete manually')
await conn.close()
class client:
config = Config
database_url = DatabaseURL(
"mysql://{}:{}@{}:{}/{}?charset=utf8mb4".format(
config.database["username"],
config.database["password"],
config.database["servername"],
config.database["port"],
config.database["dbname"])
)
database = Database(database_url)
in_memory_cache = InMemoryCache
sessions = Sessions
def __init__(self):
""" This client assumes the developer has taken
the initiative to correctly initialize the needed sessions.
"""
self.routes = Routes(obj=self)
self.middlewares = Middlewares(obj=self)
self.api = Api(obj=self)
self.tables = Tables(obj=self)
def server_init(self):
""" Should be ran within context of the
loop after the aiohttp session is created.
"""
self.server = Server.find_client(obj=self)
def league(self, league_id, region):
return League(obj=self, league_id=league_id, region=region)
async def validate_user(self, user_id):
""" Returns true or false depending if the
user exists, context of region or league
doesn't matter. """
query = """SELECT COUNT(*)
FROM users
WHERE users.user_id = :user_id"""
values = {"user_id": user_id, }
count = await self.database.fetch_val(
query=query,
values=values,
)
return count == 1
async def connect_to_mongo():
if settings.SSH_CONNECTION:
server.start()
db.client = AsyncIOMotorClient('localhost', server.local_bind_port)
else:
db.client = AsyncIOMotorClient(
str(DatabaseURL(settings.MONGODB_URL)),
maxPoolSize=settings.MAX_CONNECTIONS_COUNT,
minPoolSize=settings.MIN_CONNECTIONS_COUNT,
uuidRepresentation="standard")
async def test_concurrent_access_on_single_connection(database_url):
database_url = DatabaseURL(database_url)
if database_url.dialect != "postgresql":
pytest.skip("Test requires `pg_sleep()`")
async with Database(database_url, force_rollback=True) as database:
async def db_lookup():
await database.fetch_one("SELECT pg_sleep(1)")
await asyncio.gather(db_lookup(), db_lookup())
def get_db(url=None, create: bool = False) -> Database:
"""
:param url:
:param create:
:return:
"""
global DB_
if DB_ is None or create is True:
dsn = get_database_dsn()
url = url or DatabaseURL(dsn)
DB_ = Database(url=url)
return DB_
def host_exists(data, resolver=None):
import socket
from databases import DatabaseURL
if resolver == 'database-url':
host = DatabaseURL(data).hostname
elif callable(resolver):
host = resolver(data)
else:
host = data
try:
socket.gethostbyname(host)
return True
except socket.gaierror:
return False
async def setup_db(dsn=None):
global _db
if _db is None:
# Setup db connetion
if dsn is None:
# Get dsn from settings
settings = get_settings()
dsn = settings.dsn
# Create tables
engine = create_engine(dsn)
Base.metadata.create_all(engine)
_db = OMDatabase(DatabaseURL(dsn))
await _db.connect()
async def drop_database(url: str) -> None:
url = DatabaseURL(url)
database_name = url.database
if url.dialect in ("postgres", "postgresql"):
url = url.replace(database="postgres")
elif url.dialect == "mysql":
url = url.replace(database="")
if url.dialect == "sqlite":
if database_name and database_name != ":memory:":
os.remove(database_name)
return
else:
statement = "DROP DATABASE {0}".format(database_name)
async with Database(url) as database:
await database.execute(statement)
def prepare(self) -> None:
def create_dir(filename: Union[Path, str],
parent: bool = True) -> None:
base_dir = filename
if parent:
base_dir = os.path.dirname(filename)
if not os.path.exists(base_dir):
logger.debug(f"Creating directory {base_dir}")
os.makedirs(base_dir)
create_dir(self.pid_file)
create_dir(self.secret_file)
create_dir(self.packages_dir, False)
db_url = DatabaseURL(self.aopi_db_url)
if db_url.dialect == "sqlite" and db_url.hostname is None:
create_dir(db_url.database, True)
if not self.secret_file.exists():
self.secret_file.write_text(secrets.token_urlsafe(128))
self.jwt_secret = self.secret_file.read_text()
async def test_iterate_outside_transaction_with_values(database_url):
"""
Ensure `iterate()` works even without a transaction on all drivers.
The asyncpg driver relies on server-side cursors without hold
for iteration, which requires a transaction to be created.
This is mentionned in both their documentation and their test suite.
"""
database_url = DatabaseURL(database_url)
if database_url.dialect == "mysql":
pytest.skip("MySQL does not support `FROM (VALUES ...)` (F641)")
async with Database(database_url) as database:
query = "SELECT * FROM (VALUES (1), (2), (3), (4), (5)) as t"
iterate_results = []
async for result in database.iterate(query=query):
iterate_results.append(result)
assert len(iterate_results) == 5
def create_test_database():
# Create test databases with tables creation
for url in DATABASE_URLS:
database_url = DatabaseURL(url)
if database_url.scheme == "mysql":
url = str(database_url.replace(driver="pymysql"))
elif database_url.scheme == "postgresql+aiopg":
url = str(database_url.replace(driver=None))
engine = sqlalchemy.create_engine(url)
metadata.create_all(engine)
# Run the test suite
yield
# Drop test databases
for url in DATABASE_URLS:
database_url = DatabaseURL(url)
if database_url.scheme == "mysql":
url = str(database_url.replace(driver="pymysql"))
elif database_url.scheme == "postgresql+aiopg":
url = str(database_url.replace(driver=None))
engine = sqlalchemy.create_engine(url)
metadata.drop_all(engine)
async def test_transaction_commit_serializable(database_url):
"""
Ensure that serializable transaction commit via extra parameters is supported.
"""
database_url = DatabaseURL(database_url)
if database_url.scheme != "postgresql":
pytest.skip("Test (currently) only supports asyncpg")
def insert_independently():
engine = sqlalchemy.create_engine(str(database_url))
conn = engine.connect()
query = notes.insert().values(text="example1", completed=True)
conn.execute(query)
def delete_independently():
engine = sqlalchemy.create_engine(str(database_url))
conn = engine.connect()
query = notes.delete()
conn.execute(query)
async with Database(database_url) as database:
async with database.transaction(force_rollback=True,
isolation="serializable"):
query = notes.select()
results = await database.fetch_all(query=query)
assert len(results) == 0
insert_independently()
query = notes.select()
results = await database.fetch_all(query=query)
assert len(results) == 0
delete_independently()
def create_db_container(db_config: MysqlConfig):
"""
Create namedtuple containing database object (from encode/databases) and sqlalchemy.Table object(s) from DB config.
:param db_config: DB config
:return:
"""
DATABASE_URL = DatabaseURL(
f"mysql://{db_config.user}:{db_config.password}@{db_config.host}/{db_config.db}"
)
database = Database(DATABASE_URL,
min_size=db_config.pool_min_size,
max_size=db_config.pool_max_size)
metadata = sqlalchemy.MetaData()
tasks = sqlalchemy.Table(
"task", metadata,
sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column("title", sqlalchemy.VARCHAR),
sqlalchemy.Column("description", sqlalchemy.VARCHAR),
sqlalchemy.Column("expiry_dt", sqlalchemy.DATETIME))
return DbContainer(database=database, tasks=tasks)
def create_test_database():
# Create test databases
for url in DATABASE_URLS:
database_url = DatabaseURL(url)
if database_url.dialect == "mysql":
url = str(database_url.replace(driver="pymysql"))
engine = sqlalchemy.create_engine(url)
metadata.create_all(engine)
# Run the test suite
yield
# Drop test databases
for url in DATABASE_URLS:
database_url = DatabaseURL(url)
if database_url.dialect == "mysql":
url = str(database_url.replace(driver="pymysql"))
engine = sqlalchemy.create_engine(url)
metadata.drop_all(engine)
MAX_CONNECTIONS_COUNT = int(os.getenv("MAX_CONNECTIONS_COUNT", 10))
MIN_CONNECTIONS_COUNT = int(os.getenv("MIN_CONNECTIONS_COUNT", 10))
SECRET_KEY = Secret(os.getenv("SECRET_KEY", "project secret key"))
PROJECT_NAME = os.getenv("PROJECT_NAME", "Moctor App")
ALLOWED_HOSTS = CommaSeparatedStrings(os.getenv("ALLOWED_HOSTS", ""))
MONGODB_URL = os.getenv("MONGODB_URL", "")
if not MONGODB_URL:
MONGO_HOST = os.getenv("MONGO_HOST", "localhost")
MONGO_PORT = int(os.getenv("MONGO_PORT", 27017))
MONGO_USER = os.getenv("MONGO_USER", "moctor")
MONGO_PASS = os.getenv("MONGO_PASSWORD", "moctor")
MONGO_DB = os.getenv("MONGO_DB", "moctor")
MONGODB_URL = DatabaseURL(
f"mongodb://{MONGO_USER}:{MONGO_PASS}@{MONGO_HOST}:{MONGO_PORT}/{MONGO_DB}"
)
else:
MONGODB_URL = DatabaseURL(MONGODB_URL)
database_name = MONGO_DB
categories_collection_name = "categories"
products_collection_name = "products"
orders_collection_name = "orders"
cart_collection_name = "cart"
tags_collection_name = "tags"
users_collection_name = "users"
inventory_collection_name = "inventory"
comments_collection_name = "commentaries"
def __init__(self, settings: Settings):
models.init(settings.dsn)
self.db = OMDatabase(DatabaseURL(settings.dsn))