def make_engine(self, use_loop=True, **kwargs): if use_loop: return (yield from sa.create_engine(db=self.db, user=self.user, password=self.password, host=self.host, loop=self.loop, **kwargs)) else: return (yield from sa.create_engine(db=self.db, user=self.user, password=self.password, host=self.host, **kwargs))
async def test_create_engine(loop, mysql_params, table): async with sa.create_engine(loop=loop, **mysql_params) as engine: async with engine.acquire() as conn: ret = [] async for i in conn.execute(tbl.select()): ret.append(i) assert [(1, 'a'), (2, 'b'), (3, 'c')] == ret
def create_df(): engine = create_engine(f'mysql+pymysql://{user}:{password}@{host}:3306/{database}') connection = engine.connect() user_df = pd.read_sql(f"SELECT * FROM {user_prayer_times_table_name}", connection) server_df = pd.read_sql(f"SELECT * FROM {server_prayer_times_table_name}", connection) connection.close() return user_df, server_df
async def go(): async with sa.create_engine(**kw) as engine: async with engine.acquire() as conn: await self._prepare(conn.connection) ret = [] async for i in conn.execute(tbl.select()): ret.append(i) assert [(1, 'a'), (2, 'b'), (3, 'c')] == ret
def Database(future): ''' data is from the http response in main module. ''' global engine engine = yield from create_engine(user='******',db='hot',port=3306,\ host='127.0.0.1', password='******',\ echo=True, charset='utf8') future.set_result(engine)
def _create_all_tables(metadata): """Blocking function that sets up the tables that do not exist.""" from sqlalchemy import create_engine import pymysql global tables pymysql.install_as_MySQLdb() engine = create_engine(_url()) metadata.create_all(engine) engine.dispose()
async def test_sa_connection(table, mysql_params, loop): async with sa.create_engine(loop=loop, **mysql_params) as engine: connection = await engine.acquire() assert not connection.closed async with connection: ret = [] async for i in connection.execute(tbl.select()): ret.append(i) assert [(1, 'a'), (2, 'b'), (3, 'c')] == ret assert connection.closed
async def test_transaction_context_manager_error(loop, mysql_params, table): async with sa.create_engine(loop=loop, **mysql_params) as engine: async with engine.acquire() as conn: with pytest.raises(RuntimeError) as ctx: async with conn.begin() as tr: assert tr.is_active raise RuntimeError('boom') assert str(ctx.value) == 'boom' assert not tr.is_active assert conn.closed
async def save_dataframes(self): engine = create_engine(f'mysql+pymysql://{user}:{password}@{host}:3306/{database}') connection = engine.connect() user_df_truncated = PrayerTimesHandler.user_df[['user', 'location', 'timezone', 'calculation_method']].copy() user_df_truncated.to_sql(f"{user_prayer_times_table_name}", engine, if_exists="replace", index=False) server_df_truncated = PrayerTimesHandler.server_df[['server', 'channel', 'location', 'timezone', 'calculation_method']].copy() server_df_truncated.to_sql(f"{server_prayer_times_table_name}", engine, if_exists="replace", index=False) connection.close()
async def test_compatible_cursor_correct(loop, mysql_params): class SubCursor(Cursor): pass mysql_params['cursorclass'] = SubCursor async with sa.create_engine(loop=loop, **mysql_params) as engine: async with engine.acquire() as conn: # check not raise sa.ArgumentError exception pass assert conn.closed
async def status_ok(self): try: async with create_engine(**self.connection_dict) as engine: async with engine.acquire() as connection: resultProxy = await connection.execute('SELECT 1') result = await resultProxy.first() return result and result[0] == 1 except: raise return False
def init_db(app): conf = app['config']['mysql'] print(conf) engine = yield from create_engine( db=conf['db'], user=conf['user'], password=conf['password'], host=conf['host'], port=conf['port'], ) app['db'] = engine
async def go(): kw = self._conn_kw() async with sa.create_engine(**kw) as engine: async with engine.acquire() as conn: with pytest.raises(RuntimeError) as ctx: async with conn.begin() as tr: assert tr.is_active raise RuntimeError('boom') assert str(ctx.value) == 'boom' assert not tr.is_active assert conn.closed
async def db(event_loop): if DATABASE_URL.startswith("sqlite"): m = re.match(r"(.+)://(.+)", DATABASE_URL) assert m, "DATABASE_URL is an invalid format" schema, db = m.groups() params = dict(database=db) else: m = re.match(r"(.+)://(.+):(.*)@(.+):(\d+)/(.+)", DATABASE_URL) assert m, "DATABASE_URL is an invalid format" schema, user, pwd, host, port, db = m.groups() params = dict(host=host, port=int(port), user=user, password=pwd) if schema == "mysql": from aiomysql import connect from aiomysql.sa import create_engine elif schema == "postgres": from aiopg import connect from aiopg.sa import create_engine elif schema == "sqlite": from aiosqlite import connect create_engine = connect else: raise ValueError("Unsupported database schema: %s" % schema) if schema == "mysql": params["autocommit"] = True params["loop"] = event_loop if schema == "sqlite": if os.path.exists(db): os.remove(db) else: if schema == "postgres": params["database"] = "postgres" async with connect(**params) as conn: async with conn.cursor() as c: # WARNING: Not safe await c.execute("DROP DATABASE IF EXISTS %s;" % db) await c.execute("CREATE DATABASE %s;" % db) if schema == "mysql": params["db"] = db elif schema == "postgres": params["database"] = db async with create_engine(**params) as engine: mgr = SQLModelManager.instance() mgr.database = {"default": engine} yield engine
def mysql_create_engine(self): return create_engine( minsize=int(self.config.get('pool_size', 10)), maxsize=int(self.config.get('pool_size', 10)), host=self.host, port=self.port, user=self.username, password=self.password, db=self.config['db'], charset='utf8mb4', loop=self.loop, autocommit=True)
def create_sa_engine(self, loop): logger.info('Create database engine ...') self.engine = yield from create_engine(user='******', password='******', host='127.0.0.1', db='starry', minsize=60, maxsize=120, loop=loop) return self.engine
def __initData(): if procVariable.debug: singletonDefine.g_obj_dbMysqlMgr = yield from create_engine( host=oss_config.mysqlAddress_debug, port=3306, user='******', password=oss_config.mysqlPwd_debug, db='probet_oss', loop=g_obj_loop, charset="utf8") else: singletonDefine.g_obj_dbMysqlMgr = yield from create_engine( host=oss_config.mysqlAddress, port=3306, user='******', password=oss_config.mysqlPwd, db='probet_oss', loop=g_obj_loop, charset="utf8") singletonDefine.g_obj_LogicOssFileMgr.init_file( str(procVariable.rescanFile), 0)
async def test_sa_transaction(table, mysql_params, loop): async with sa.create_engine(loop=loop, **mysql_params) as engine: async with engine.acquire() as connection: cnt = await connection.scalar(tbl.count()) assert 3 == cnt async with (await connection.begin()) as tr: assert tr.is_active await connection.execute(tbl.delete()) assert not tr.is_active cnt = await connection.scalar(tbl.count()) assert 0 == cnt
async def go(): async with sa.create_engine(**kw) as engine: async with engine.acquire() as conn: await self._prepare(conn.connection) cnt = await conn.scalar(tbl.count()) assert 3 == cnt async with (await conn.begin()) as tr: assert tr.is_active await conn.execute(tbl.delete()) assert not tr.is_active cnt = await conn.scalar(tbl.count()) assert 0 == cnt
async def test_sa_transaction_rollback(loop, mysql_params, table): async with sa.create_engine(loop=loop, **mysql_params) as engine: async with engine.acquire() as conn: cnt = await conn.scalar(tbl.count()) assert 3 == cnt with pytest.raises(RuntimeError) as ctx: async with (await conn.begin()) as tr: assert tr.is_active await conn.execute(tbl.delete()) raise RuntimeError("Exit") assert str(ctx.value) == "Exit" assert not tr.is_active cnt = await conn.scalar(tbl.count()) assert 3 == cnt
def go(): engine = yield from create_engine(user='******', db='test_pymysql', host='127.0.0.1', password='') yield from create_table(engine) with (yield from engine) as conn: yield from conn.execute(tbl.insert().values(val='abc')) res = yield from conn.execute(tbl.select()) for row in res: print(row.id, row.val) engine.close() yield from engine.wait_closed()
async def go(): async with sa.create_engine(**kw) as engine: async with engine.acquire() as conn: await self._prepare(conn.connection) cnt = await conn.scalar(tbl.count()) assert 3 == cnt with pytest.raises(RuntimeError) as ctx: async with (await conn.begin()) as tr: assert tr.is_active await conn.execute(tbl.delete()) raise RuntimeError("Exit") assert str(ctx.value) == "Exit" assert not tr.is_active cnt = await conn.scalar(tbl.count()) assert 3 == cnt
def go(): engine = yield from create_engine(user='******', db='test_pymysql', host='127.0.0.1', password='******', autocommit=True) with (yield from engine) as conn: yield from conn.execute(tbl.insert().values(val='abc')) # yield from conn.execute( # tbl.insert(), # {"val": "v1", "id": 1} # ) # yield from conn.execute('commit') res = yield from conn.execute(tbl.select()) for row in res: print(row.id, row.val)
def fixture_db_pool(request, loop): mysql_config = { 'db': 'test_jenkins_integrator', 'host': '127.0.0.1', 'user': '******', 'port': 3306, 'minsize': 2, 'maxsize': 2, } engine = loop.run_until_complete(create_engine(loop=loop, **mysql_config)) def resource_teardown(): engine.close() loop.run_until_complete(engine.wait_closed()) request.addfinalizer(resource_teardown) return engine
def Database(future): ''' data is from the http response in main module. ''' global engine engine = yield from create_engine(user='******',db='Currency',port=3306, host='127.0.0.1', password='******') # yield from create_table(engine) # with (yield from engine) as conn: # yield from conn.execute(tbl.insert().values(val='sag34')) # yield from conn.execute('commit') # res = yield from conn.execute(tbl.select(tbl.c.id)) # print('ok') # for row in res: # print(row.id) future.set_result(engine)
async def go(): kw = self._conn_kw() async with sa.create_engine(**kw) as engine: async with engine.acquire() as conn: async with conn.begin() as tr: # check that in context manager we do not execute # commit for second time. Two commits in row causes # InvalidRequestError exception await tr.commit() assert not tr.is_active tr2 = await conn.begin() async with tr2: assert tr2.is_active # check for double commit one more time await tr2.commit() assert not tr2.is_active assert conn.closed
async def test_transaction_context_manager_commit_once(loop, mysql_params, table): async with sa.create_engine(loop=loop, **mysql_params) as engine: async with engine.acquire() as conn: async with conn.begin() as tr: # check that in context manager we do not execute # commit for second time. Two commits in row causes # InvalidRequestError exception await tr.commit() assert not tr.is_active tr2 = await conn.begin() async with tr2: assert tr2.is_active # check for double commit one more time await tr2.commit() assert not tr2.is_active assert conn.closed
async def test_transaction_context_manager(loop, mysql_params, table): async with sa.create_engine(loop=loop, **mysql_params) as engine: async with engine.acquire() as conn: async with conn.begin() as tr: async with conn.execute(tbl.select()) as cursor: ret = [] async for i in cursor: ret.append(i) assert [(1, 'a'), (2, 'b'), (3, 'c')] == ret assert cursor.closed assert not tr.is_active tr2 = await conn.begin() async with tr2: assert tr2.is_active async with conn.execute('SELECT 1;') as cursor: rec = await cursor.scalar() assert rec == 1 await cursor.close() assert not tr2.is_active
async def test_transaction_context_manager(loop, mysql_params, table): async with sa.create_engine(loop=loop, **mysql_params) as engine: async with engine.acquire() as conn: async with conn.begin() as tr: async with conn.execute(tbl.select()) as cursor: ret = [] async for i in conn.execute(tbl.select()): ret.append(i) assert [(1, 'a'), (2, 'b'), (3, 'c')] == ret assert cursor.closed assert not tr.is_active tr2 = await conn.begin() async with tr2: assert tr2.is_active async with conn.execute('SELECT 1;') as cursor: rec = await cursor.scalar() assert rec == 1 await cursor.close() assert not tr2.is_active
def Database(future): ''' data is from the http response in main module. ''' global engine engine = yield from create_engine(user='******', db='Currency', port=3306, host='127.0.0.1', password='******') # yield from create_table(engine) # with (yield from engine) as conn: # yield from conn.execute(tbl.insert().values(val='sag34')) # yield from conn.execute('commit') # res = yield from conn.execute(tbl.select(tbl.c.id)) # print('ok') # for row in res: # print(row.id) future.set_result(engine)
def go(): engine = yield from create_engine(minsize=1, maxsize=30, user='******', db='learning', host='localhost', password='******', loop=loop) with (yield from engine) as conn: trans = yield from conn.begin() try: yield from conn.execute(users.insert().values(name='abc', age=33)) res = yield from conn.execute(users.select()) except Exception: yield from trans.rollback() else: yield from trans.commit() if res: for row in res: print(row.id, row.name, row.age) print(engine.freesize)
async def go(): kw = self._conn_kw() async with sa.create_engine(**kw) as engine: async with engine.acquire() as conn: await self._prepare(conn.connection) async with conn.begin() as tr: async with conn.execute(tbl.select()) as cursor: ret = [] async for i in conn.execute(tbl.select()): ret.append(i) assert [(1, 'a'), (2, 'b'), (3, 'c')] == ret assert cursor.closed assert not tr.is_active tr2 = await conn.begin() async with tr2: assert tr2.is_active async with conn.execute('SELECT 1;') as cursor: rec = await cursor.scalar() assert rec == 1 await cursor.close() assert not tr2.is_active assert conn.closed
def __initData(): classSqlBaseMgr() if procVariable.debug: yield from classSqlBaseMgr.getInstance().connetSql( oss_config.mysqlAddress_debug, 3306, 'root', oss_config.mysqlPwd_debug, 'probet_oss', g_obj_loop) singletonDefine.g_obj_dbMysqlMgr = yield from create_engine( host=oss_config.mysqlAddress_debug, port=3306, user='******', password=oss_config.mysqlPwd_debug, db='probet_oss', loop=g_obj_loop) else: yield from classSqlBaseMgr.getInstance().connetSql( oss_config.mysqlAddress, 3306, 'root', oss_config.mysqlPwd, 'probet_oss', g_obj_loop) singletonDefine.g_obj_dbMysqlMgr = yield from create_engine( host=oss_config.mysqlAddress, port=3306, user='******', password=oss_config.mysqlPwd, db='probet_oss', loop=g_obj_loop) #TODO check the filepos selectSql = FilePos.getSelectSqlObj(procVariable.host) engine = classSqlBaseMgr.getInstance().getEngine() with (yield from engine) as conn: result = yield from conn.execute(selectSql) if result.rowcount <= 0: #初始化 logging.info("init the file pos mysql table") objInsertSql = FilePos.getInsertSqlObj(procVariable.host, oss_config.billFilaPath, 0, 0) trans = yield from conn.begin() try: yield from conn.execute(objInsertSql) except Exception as e: # TODO log error logging.exception(e) yield from trans.rollback() else: yield from trans.commit() singletonDefine.g_obj_LogicOssFileMgr.init_file( oss_config.billFilaPath, 0) else: for var_row in result: logging.info("oss row is -->{}".format(var_row)) if not timeHelp.isSameDay(var_row.lastLogTime.timestamp(), timeHelp.getNow()): logging.error("bill log is between two days") #TODO 把这个间隔时间段内的读取完毕 singletonDefine.g_obj_LogicOssFileMgr.init_file( str(var_row.fileName), 0) else: singletonDefine.g_obj_LogicOssFileMgr.init_file( str(var_row.fileName), var_row.seekPos, var_row.lastLogTime.timestamp() ) #int(var_row.seekPos))
from aiomysql.sa import create_engine patch_all() patch_psycopg() app = Flask(__name__) app.config.from_pyfile('config.py') bootstrap = Bootstrap(app) login_manager = LoginManager(app) db = SQLAlchemy(app) db.engine.pool._use_threadlocal = True engine = create_engine(user='******', db='sakila', host='127.0.0.1', password=os.environ.get('PWD_OF_MYSQL', '')) async def query(username): with (await engine) as conn: _sql = 'select * from users_for_flask where username = "******"' % username res = await conn.execute(_sql) return res.fetchone() class LoginForm(FlaskForm): username = StringField('Username', validators=[Required(), Length(1, 64)]) password = PasswordField('Password', validators=[Required()]) submit = SubmitField('Log In')