from databases import Database
#from .settings import DB_CONNECTION_STRING
database = Database("mysql://*****:*****@localhost:3306/quartdb")
import logging
import os
import sys
from databases import Database
dir_path = os.path.dirname(os.path.realpath(__file__))
sys.path.insert(0, os.path.split(dir_path)[0])
from app.db.repositories import UserRepository
from app.events import startup_wrapper, shutdown_wrapper
from app.handlers import router
from app.config import VK_API_TOKEN, DB_URL, GROUP_ID
from app.state_manager.storages.memory import MemoryStorage
from app.state_manager.routes.vkwave import VkWaveMainRouter
logging.basicConfig(level=logging.INFO)
main_instance = VkWaveMainRouter(VK_API_TOKEN, GROUP_ID)
database = Database(DB_URL)
main_instance.container.bind_constant(Database, database)
main_instance.container.bind_constant(UserRepository, UserRepository)
main_instance.include_router(router)
if __name__ == '__main__':
main_instance.run(storage=MemoryStorage(),
on_startup=startup_wrapper(database, main_instance.bot),
on_shutdown=shutdown_wrapper(database))
from sqlalchemy import (Column, Integer, MetaData, String, Table,
create_engine, ARRAY)
from databases import Database
import os
DATABASE_URI = os.getenv('DATABASE_URI')
engine = create_engine(DATABASE_URI)
metadata = MetaData()
movies = Table(
'movies',
metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('plot', String(250)),
Column('genres', ARRAY(String)),
Column('casts_id', ARRAY(Integer))
)
database = Database(DATABASE_URI)
import os
from databases import Database
from sqlalchemy import MetaData, create_engine
from .EnumTypes import PermissionLevel, TaskTypes
database = Database(
os.getenv('DATABASE_URL',
'postgresql://<user>:<password>@localhost/<db-name>'))
engine = create_engine(str(database.url))
metadata = MetaData(bind=engine)
# These need to be imported after the engine has been created
from .Users import users
from .Tasks import tasks
metadata.create_all(engine)
async def query(self, query):
async with Database(self.database_url) as database:
return await database.fetch_all(query=q)
from databases import Database
from utils.const import DB_URL
db = Database(DB_URL)
async def execute(query, is_many, values=None):
# if TESTING:
await db.connect()
if is_many:
await db.execute_many(query=query, values=values)
else:
await db.execute(query=query, values=values)
# if TESTING:
await db.disconnect()
async def fetch(query, is_one, values=None):
# if TESTING:
await db.connect()
if is_one:
result = await db.fetch_one(query=query, values=values)
if result is None:
out = None
else:
out = dict(result)
else:
workshop_workshop_collection = sa.Table(
"workshop_workshop_collections", metadata,
sa.Column("workshop_id",
sa.Integer,
sa.ForeignKey("workshops.id"),
nullable=False,
index=True),
sa.Column("workshop_collection_id",
sa.Integer,
sa.ForeignKey("workshop_collections.id"),
nullable=False,
index=True),
sa.UniqueConstraint('workshop_id', 'workshop_collection_id'))
database = Database(config('SQLALCHEMY_URI'))
async def connect():
if database.is_connected:
return
await database.connect()
async def get_outdated_workshops(interval='4 hours'):
query = f"""with s as (select name, status, timestamp, row_number() over (partition by name order by timestamp desc) as rk from instance_events) select s.*, current_timestamp-s.timestamp as age from s whe
re s.rk=1 and current_timestamp-s.timestamp > interval '{interval}' and status!='DELETED'"""
await connect()
res = await database.fetch_all(query)
async def test_queries(database_url):
"""
Test that the basic `execute()`, `execute_many()`, `fetch_all()``, and
`fetch_one()` interfaces are all supported (using SQLAlchemy core).
"""
async with Database(database_url) as database:
async with database.transaction(force_rollback=True):
# execute()
query = notes.insert()
values = {"text": "example1", "completed": True}
await database.execute(query, values)
# execute_many()
query = notes.insert()
values = [
{
"text": "example2",
"completed": False
},
{
"text": "example3",
"completed": True
},
]
await database.execute_many(query, values)
# fetch_all()
query = notes.select()
results = await database.fetch_all(query=query)
assert len(results) == 3
assert results[0]["text"] == "example1"
assert results[0]["completed"] == True
assert results[1]["text"] == "example2"
assert results[1]["completed"] == False
assert results[2]["text"] == "example3"
assert results[2]["completed"] == True
# fetch_one()
query = notes.select()
result = await database.fetch_one(query=query)
assert result["text"] == "example1"
assert result["completed"] == True
# fetch_val()
query = sqlalchemy.sql.select([notes.c.text])
result = await database.fetch_val(query=query)
assert result == "example1"
# fetch_val() with no rows
query = sqlalchemy.sql.select(
[notes.c.text]).where(notes.c.text == "impossible")
result = await database.fetch_val(query=query)
assert result is None
# fetch_val() with a different column
query = sqlalchemy.sql.select([notes.c.id, notes.c.text])
result = await database.fetch_val(query=query, column=1)
assert result == "example1"
# row access (needed to maintain test coverage for Record.__getitem__ in postgres backend)
query = sqlalchemy.sql.select([notes.c.text])
result = await database.fetch_one(query=query)
assert result["text"] == "example1"
assert result[0] == "example1"
# iterate()
query = notes.select()
iterate_results = []
async for result in database.iterate(query=query):
iterate_results.append(result)
assert len(iterate_results) == 3
assert iterate_results[0]["text"] == "example1"
assert iterate_results[0]["completed"] == True
assert iterate_results[1]["text"] == "example2"
assert iterate_results[1]["completed"] == False
assert iterate_results[2]["text"] == "example3"
assert iterate_results[2]["completed"] == True
async def test_queries_raw(database_url):
"""
Test that the basic `execute()`, `execute_many()`, `fetch_all()``, and
`fetch_one()` interfaces are all supported (raw queries).
"""
async with Database(database_url) as database:
async with database.transaction(force_rollback=True):
# execute()
query = "INSERT INTO notes(text, completed) VALUES (:text, :completed)"
values = {"text": "example1", "completed": True}
await database.execute(query, values)
# execute_many()
query = "INSERT INTO notes(text, completed) VALUES (:text, :completed)"
values = [
{
"text": "example2",
"completed": False
},
{
"text": "example3",
"completed": True
},
]
await database.execute_many(query, values)
# fetch_all()
query = "SELECT * FROM notes WHERE completed = :completed"
results = await database.fetch_all(query=query,
values={"completed": True})
assert len(results) == 2
assert results[0]["text"] == "example1"
assert results[0]["completed"] == True
assert results[1]["text"] == "example3"
assert results[1]["completed"] == True
# fetch_one()
query = "SELECT * FROM notes WHERE completed = :completed"
result = await database.fetch_one(query=query,
values={"completed": False})
assert result["text"] == "example2"
assert result["completed"] == False
# fetch_val()
query = "SELECT completed FROM notes WHERE text = :text"
result = await database.fetch_val(query=query,
values={"text": "example1"})
assert result == True
query = "SELECT * FROM notes WHERE text = :text"
result = await database.fetch_val(query=query,
values={"text": "example1"},
column="completed")
assert result == True
# iterate()
query = "SELECT * FROM notes"
iterate_results = []
async for result in database.iterate(query=query):
iterate_results.append(result)
assert len(iterate_results) == 3
assert iterate_results[0]["text"] == "example1"
assert iterate_results[0]["completed"] == True
assert iterate_results[1]["text"] == "example2"
assert iterate_results[1]["completed"] == False
assert iterate_results[2]["text"] == "example3"
assert iterate_results[2]["completed"] == True
async def db_session(test_db, event_loop):
session = Database(test_db_dsn, force_rollback=True)
await session.connect()
yield session
await session.disconnect()
import asyncio
import os
from databases import Database
SHARD_DB_URL = os.getenv("SHARD_DB_URL",
"mysql://*****:*****@localhost:33334/shard")
async def run(shard_db):
await shard_db.connect()
query = """UPDATE MessageData SET Body = 2211 WHERE MessageID = 9781975; UPDATE Attachment SET BlobStorageID = 14 WHERE AttachmentID IN (121373, 121374, 121376, 121377)"""
await shard_db.execute(query)
if __name__ == "__main__":
shard_db = Database(SHARD_DB_URL)
eventloop = asyncio.get_event_loop()
task = eventloop.create_task(run(shard_db=shard_db))
eventloop.run_until_complete(task)
async def _remove_test_db() -> None:
origin_db = Database(settings.DATABASE_URL)
await origin_db.connect()
await origin_db.execute('DROP DATABASE IF EXISTS test_qbot')
await origin_db.disconnect()
async def _create_test_db() -> None:
origin_db = Database(settings.DATABASE_URL)
await origin_db.connect()
await origin_db.execute('CREATE DATABASE test_qbot')
await origin_db.disconnect()
#!/usr/bin/env python3 # -*- coding: utf-8 -*- # # postconn.py # @Author : Gustavo Freitas ([email protected]) # @Link : https://github.com/sharkguto from databases import Database from benchx.config import POSTGRESQL_DATABASE_URI, DB_POOL_SIZE p_database = Database(POSTGRESQL_DATABASE_URI, min_size=1, max_size=DB_POOL_SIZE)
from databases import Database
from ..utils import config
# load the configuration
_config = config.load()
# create the database object
database = Database(_config["connection_string"])
async def connect():
await database.connect()
# create the database tables if they don't exist
await _init()
async def disconnect():
await database.disconnect()
async def _init():
"""Initializes the database and creates tables if they don't exist"""
# check if the models table exists
query = "SELECT name FROM sqlite_master WHERE type='table' AND name='models'"
if not await database.fetch_one(query=query):
query = """CREATE TABLE models (
id INTEGER PRIMARY KEY,
from starlette.staticfiles import StaticFiles
from starlette.templating import Jinja2Templates
from TreeNode import TreeNode
from config import DATABASE_URL
from rpc_server.rpc_server import logger
scheduler_script_name = 'rpc_server/scheduler_script'
script_path = os.path.join(os.getcwd(), scheduler_script_name)
if not os.path.exists(script_path):
os.mkdir(script_path)
app = FastAPI()
dburl = DatabaseURL(DATABASE_URL)
database = Database(dburl)
app.mount("/static", StaticFiles(directory="static"), name="static")
# 创建一个templates（模板）对象，以后可以重用。
templates = Jinja2Templates(directory="templates")
# 下面是使用sqlalchemy连接
metadata = sqlalchemy.MetaData()
sche = sqlalchemy.Table(
"scheduler",
metadata,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True), # int
sqlalchemy.Column('script_name',
sqlalchemy.String(100),
from dateutil.relativedelta import relativedelta
from databases import Database
from functools import wraps
from fund_back_django.settings import DATABASES
def get_uri(name: str = None):
name = name or 'default'
c = DATABASES[name]
uri_ = f"mysql://{c['USER']}:{c['PASSWORD']}@{c['HOST']}:{c['PORT']}/{c['NAME']}?charset=utf8mb4"
return uri_
uri = get_uri()
database = Database(uri)
metadata = sa.MetaData()
engine = sa.create_engine(uri)
time_record = datetime.datetime.now()
def async_database(func):
@wraps(func)
async def inner(*args, **kwargs):
now = datetime.datetime.now()
global time_record
delta = relativedelta(now, time_record)
status = database.is_connected
if delta.hours >= 2:
if status:
print('重启数据库连接')
ForeignKey,
Integer,
MetaData,
String,
create_engine,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy_to_ormar import sqlalchemy_to_ormar
Base = declarative_base()
Database_URL = "sqlite:///test.db"
engine = create_engine(Database_URL)
database = Database(Database_URL)
metadata = MetaData(engine)
class User(Base):
__tablename__ = "user"
USER_ID = Column(Integer(), primary_key=True)
FIRST_NAME = Column(String(255))
LAST_NAME = Column(String(255))
USERNAME = Column(String(255), index=True)
PASSWORD = Column(String(40))
EMAIL = Column(String(255))
CUSTOMER_ID = Column(ForeignKey("customer.CUSTOMER_ID"),
index=True) # type: ignore
user_customer = relationship(
"Customer", primaryjoin="User.CUSTOMER_ID == Customer.CUSTOMER_ID")
def __init__(self, url: str, text_preview_length: int):
self.database = Database(url)
self.metadata = MetaData()
self.engine = create_engine(url)
self._create_tables()
self.text_preview_length = text_preview_length
# coding: utf-8
from fastapi import Depends
import aioredis
from databases import Database
from core.setting import Setting, get_setting
db = Database(get_setting().PSQL_URL)
class get_redis:
def __init__(self, db: int = 0):
self.db = db
async def __call__(self, setting: Setting = Depends(get_setting)):
redis_conn = await aioredis.create_redis(setting.REDIS_URL, db=self.db)
try:
yield redis_conn
finally:
redis_conn.close()
await redis_conn.wait_closed()
from uuid import uuid4
import pytest
from databases import Database
from server.accounts.service import AccountService, Account, AccountID
from server.db.account_store import AccountStore
account_service = AccountService(
account_store=AccountStore(
database=Database("postgresql://*****:*****@localhost:5432/dbalc")
)
)
account = Account(
id=AccountID(uuid4()),
avatar="avatar",
proxy="proxy",
client_id=123
)
@pytest.mark.asyncio
async def test_service():
await account_service.add(account)
res = await account_service.find(account.id)
assert res == account
account.domain = "domain"
await account_service.update(account)
import sqlalchemy
from databases import Database
from app.core.config import DATABASE_URL
database = Database(DATABASE_URL, min_size=2,
max_size=10) # specifying min and max connections
metadata = sqlalchemy.MetaData()
async def query_multiple(self, queries):
async with Database(self.database_url) as database:
queries = map(lambda q: database.fetch_all(query=q), queries)
return await asyncio.gather(*queries)
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, Integer, BigInteger, Boolean, String, ForeignKey, DateTime, BLOB
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from aiopg.sa import create_engine as aio_ce
import asyncio
import datetime
from databases import Database
database = Database('sqlite:///example.db')
""" Контроллер серверной базы данных"""
Base = declarative_base()
class Texts(Base):
"""Модель таблицы юзеров"""
__tablename__ = 'texts'
id = Column(Integer, primary_key=True)
text = Column(String)
def __init__(self, text):
self.text = text
def __repr__(self):
def init_db():
return Database(get_db_url(), force_rollback=TESTING)
def database():
database = Database("sqlite:///test.db")
assert database
yield database
async def _cleanup_stories():
""" Do the actual cleanup... """
async with Database(Settings.dsn) as db:
await story_ops.cleanup_stories(db=db)
async def test_queries_with_expose_backend_connection(database_url):
"""
Replication of `execute()`, `execute_many()`, `fetch_all()``, and
`fetch_one()` using the raw driver interface.
"""
async with Database(database_url) as database:
async with database.connection() as connection:
async with database.transaction(force_rollback=True):
# Get the raw connection
raw_connection = connection.raw_connection
# Insert query
if str(database_url).startswith("mysql"):
insert_query = "INSERT INTO notes (text, completed) VALUES (%s, %s)"
else:
insert_query = "INSERT INTO notes (text, completed) VALUES ($1, $2)"
# execute()
values = ("example1", True)
if str(database_url).startswith("postgresql"):
await raw_connection.execute(insert_query, *values)
elif str(database_url).startswith("mysql"):
cursor = await raw_connection.cursor()
await cursor.execute(insert_query, values)
elif str(database_url).startswith("sqlite"):
await raw_connection.execute(insert_query, values)
# execute_many()
values = [("example2", False), ("example3", True)]
if str(database_url).startswith("mysql"):
cursor = await raw_connection.cursor()
await cursor.executemany(insert_query, values)
else:
await raw_connection.executemany(insert_query, values)
# Select query
select_query = "SELECT notes.id, notes.text, notes.completed FROM notes"
# fetch_all()
if str(database_url).startswith("postgresql"):
results = await raw_connection.fetch(select_query)
elif str(database_url).startswith("mysql"):
cursor = await raw_connection.cursor()
await cursor.execute(select_query)
results = await cursor.fetchall()
elif str(database_url).startswith("sqlite"):
results = await raw_connection.execute_fetchall(select_query)
assert len(results) == 3
# Raw output for the raw request
assert results[0][1] == "example1"
assert results[0][2] == True
assert results[1][1] == "example2"
assert results[1][2] == False
assert results[2][1] == "example3"
assert results[2][2] == True
# fetch_one()
if str(database_url).startswith("postgresql"):
result = await raw_connection.fetchrow(select_query)
else:
cursor = await raw_connection.cursor()
await cursor.execute(select_query)
result = await cursor.fetchone()
# Raw output for the raw request
assert result[1] == "example1"
assert result[2] == True
from databases import Database from . import settings db = Database(settings.DATABASE_URL)
from databases import Database
from utils.const import DB_URL, TESTING, TESTING_DB_URL, IS_LOAD_TEST
if TESTING or IS_LOAD_TEST:
db = Database(TESTING_DB_URL)
else:
db = Database(DB_URL)
