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))
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 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
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 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
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_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 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 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 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
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
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 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_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 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(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
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')
