def create_postgresql_adapter(username: str,
password: str,
server: str,
db_name: str,
port: int = 5432) -> Adapter:
"""
Creates a SQL Alchemy database engine for PostgreSQL, using database credentials.
Parameters
----------
username: str
Database username.
password: str
Database password.
server: str
Database server name or IP Address.
db_name: str
Database name.
port: int
PostgreSQL's TCP port. Defaults to 5432
Returns
-------
An instance of Adapter
"""
return Adapter(
sa_create_engine(
f"postgresql://{username}:{password}@{server}:{port}/{db_name}"))
def db():
settings_ = Settings(pg_dsn=os.getenv('DATABASE_URL', DB_DSN))
prepare_database(True, settings_)
engine = sa_create_engine(settings_.pg_dsn)
populate_db(engine)
yield engine
engine.dispose()
def _connect(self,db_type, user, password, host, database):
engine = sa_create_engine("%s://%s:%s@%s/%s" % (db_type,
user,
password,
host,
database
))
self.connection = engine.connect()
def create_engine():
setting = DATABASES['default']
PROTOCOL = 'mysql'
SERVER_ID = setting['USER']
SERVER_PW = setting['PASSWORD']
DOMAIN = '{host}:{port}'.format(host=setting['HOST'], port=setting['PORT'])
SCHEMA = setting['NAME']
connection_info = '{protocol}://{server_id}:{server_pw}@{domain}/{schema}?charset=utf8&use_unicode=0'.format(protocol=PROTOCOL, server_id=SERVER_ID, server_pw=SERVER_PW, domain=DOMAIN, schema=SCHEMA)
return sa_create_engine(connection_info, pool_size=5, encoding='utf8', echo=False)
def db(dsn):
conn = psycopg2.connect(**pg_connect_kwargs(settings))
conn.autocommit = True
cur = conn.cursor()
cur.execute('DROP DATABASE IF EXISTS {}'.format(settings.PG_DATABASE))
cur.execute('CREATE DATABASE {}'.format(settings.PG_DATABASE))
engine = sa_create_engine(dsn)
Base.metadata.create_all(engine)
yield engine
engine.dispose()
cur.execute('DROP DATABASE {}'.format(settings.PG_DATABASE))
cur.close()
conn.close()
def create_mssql_adapter(
username: str,
password: str,
server: str,
db_name: str,
port: int = 1433,
encrypt: bool = False,
trust_certificate: bool = False,
) -> Adapter:
"""
Creates a SQL Alchemy database engine for Microsoft SQL Server, using SQL
credentials.
Parameters
----------
username: str
Database username.
password: str
Database password.
server: str
Database server name or IP Address.
db_name: str
Database name.
port: int
SQL Server's TCP port. Defaults to 1433.
encrypt: bool
Encrypt database connections. Defaults to false.
trust_certificate: bool
When encrypting the database connection, trust the server certificate.
Helpful for localhost development. USE WITH CAUTION.
Returns
-------
An instance of Adapter
"""
url = f"mssql+pyodbc://{username}:{password}@{server},{port}/{db_name}?driver=ODBC+Driver+17+for+SQL+Server"
if encrypt:
url += "&Encrypt=yes"
if trust_certificate:
url += "&TrustServerCertificate=yes"
return Adapter(sa_create_engine(url))
def create_engine():
setting = DATABASES['default']
PROTOCOL = 'mysql'
SERVER_ID = setting['USER']
SERVER_PW = setting['PASSWORD']
DOMAIN = '{host}:{port}'.format(host=setting['HOST'], port=setting['PORT'])
SCHEMA = setting['NAME']
connection_info = '{protocol}://{server_id}:{server_pw}@{domain}/{schema}?charset=utf8&use_unicode=0'.format(
protocol=PROTOCOL,
server_id=SERVER_ID,
server_pw=SERVER_PW,
domain=DOMAIN,
schema=SCHEMA)
return sa_create_engine(connection_info,
pool_size=5,
encoding='utf8',
echo=False)
def create_mssql_adapter_with_integrated_security(
server: str,
db_name: str,
port: int = 1433,
encrypt: bool = False,
trust_certificate: bool = False,
) -> Adapter:
"""
Creates a SQL Alchemy database engine for Microsoft SQL Server, using
integrated security (aka SSPI, aka Windows domain authentication).
Parameters
----------
server: str
Database server name or IP Address.
db_name: str
Database name.
port: int
SQL Server's TCP port. Defaults to 1433.
encrypt: bool
Encrypt database connections. Defaults to false.
trust_certificate: bool
When encrypting the database connection, trust the server certificate.
Helpful for localhost development. USE WITH CAUTION.
Returns
-------
An instance of Adapter
"""
url = (
f"mssql+pyodbc://{server},{port}/{db_name}?driver=ODBC+Driver+17+for+SQL+Server"
)
if encrypt:
url += "&Encrypt=yes"
if trust_certificate:
url += "&TrustServerCertificate=yes"
return Adapter(sa_create_engine(url))
def get_db_engine(config: Dict = {}) -> db_Engine:
__DRIVERNAME = config.get("database_driver", "redshift+psycopg2")
__PORT = config.get("PGPORT", 5555)
__USER = config.get("PGUSER", None)
__DATABASE = config.get("PGDATABASE", "dev")
__HOST = config.get("PGHOST", "xxxxxx.redshift.amazonaws.com")
__PASSWORD = config.get("PGPASSWORD", None)
if not __USER or not __PASSWORD:
raise ValueError(
"PGUSER/PGPASSWORD is not defined in config and environment.")
db_url = sa_url.URL(
drivername=__DRIVERNAME,
username=__USER,
password=__PASSWORD,
host=__HOST,
port=__PORT,
database=__DATABASE,
)
db_engine = sa_create_engine(db_url,
connect_args={"sslmode": "verify-full"})
return db_engine
def _connect(self, db_type, user, password, host, database):
engine = sa_create_engine("%s://%s:%s@%s/%s" % (db_type, user, password, host, database))
self.connection = engine.connect()
def create_engine(url=None, echo=None):
return sa_create_engine(url or settings.db.url, echo=echo or settings.db.echo)
def get_db_engine(self) -> sa_Engine:
if self.engine == DbEngine.MSSQL:
return sa_create_engine(self.connection_string)
raise NotImplementedError(
f"Support for '{self.engine}' has not yet been implemented.")
return await conn.execute(*args, **kwargs)
async def proxy_engine_init(self, *args, **kwargs):
global real_engine
loop = asyncio.get_event_loop()
real_engine = await create_engine(**MYSQL_CONFIG, loop=loop)
self.__dict__['execute'] = async_execute
return await async_execute(*args, **kwargs)
real_engine = None
# 新建自动识别 reflect 的 engine class，并替换掉模块中的引用
CustomEngine = type('CustomEngine', (Engine, ),
dict(__getattr__=lambda _, name: meta.tables[name]))
mod = sys.modules.get('aiomysql.sa.engine')
mod.__dict__['Engine'] = CustomEngine
# 使用 sa 提供的反射机制
mysql_conn = 'mysql://{user}:{password}@{host}:{port}/{db}?charset=utf8mb4'.format(
**MYSQL_CONFIG)
_ = sa_create_engine(mysql_conn)
meta = MetaData()
meta.reflect(_)
ProxyEngine = type(
'ProxyEngine', (object, ),
dict(execute=proxy_engine_init,
__getattr__=lambda _, name: meta.tables[name]))
db = engine = ProxyEngine()
