def _connect_all(self):
conn1 = yield from aiomysql.connect(loop=self.loop,
host=self.host,
port=self.port,
user=self.user,
db=self.db,
password=self.password,
use_unicode=True,
echo=True)
conn2 = yield from aiomysql.connect(loop=self.loop,
host=self.host,
port=self.port,
user=self.user,
db=self.other_db,
password=self.password,
use_unicode=False,
echo=True)
conn3 = yield from aiomysql.connect(loop=self.loop,
host=self.host,
port=self.port,
user=self.user,
db=self.db,
password=self.password,
use_unicode=True,
echo=True,
local_infile=True)
self.connections = [conn1, conn2, conn3]
def test_issue_114(self):
""" autocommit is not set after reconnecting with ping() """
conn = yield from aiomysql.connect(charset="utf8", loop=self.loop)
yield from conn.autocommit(False)
c = conn.cursor()
yield from c.execute("""select @@autocommit;""")
r = yield from c.fetchone()
self.assertFalse(r[0])
yield from conn.wait_closed()
yield from conn.ping()
yield from c.execute("""select @@autocommit;""")
r = yield from c.fetchone()
self.assertFalse(r[0])
yield from conn.wait_closed()
# Ensure autocommit() is still working
conn = yield from aiomysql.connect(charset="utf8", loop=self.loop)
c = conn.cursor()
yield from c.execute("""select @@autocommit;""")
r = yield from c.fetchone()
self.assertFalse(r[0])
yield from conn.wait_closed()
yield from conn.ping()
yield from conn.autocommit(True)
yield from c.execute("""select @@autocommit;""")
r = yield from c.fetchone()
self.assertTrue(r[0])
yield from conn.wait_closed()
def _connect_all(self):
conn1 = yield from aiomysql.connect(loop=self.loop, host=self.host,
port=self.port, user=self.user,
db=self.db,
password=self.password,
use_unicode=True, echo=True)
conn2 = yield from aiomysql.connect(loop=self.loop, host=self.host,
port=self.port, user=self.user,
db=self.other_db,
password=self.password,
use_unicode=False, echo=True)
self.connections = [conn1, conn2]
def f(**kw):
conn_kw = mysql_params.copy()
conn_kw.update(kw)
_loop = conn_kw.pop('loop', loop)
conn = yield from aiomysql.connect(loop=_loop, **conn_kw)
connections.append(conn)
return conn
async def execute(sql, **db):
async with aiomysql.connect(**db) as conn:
async with conn.cursor() as curor:
await curor.execute(sql)
value = await curor.fetchmany(size=20)
for k in value:
print(k)
async def check_status():
"""Checking work_parser status and demand of the re-authetication"""
status = None
try:
async with aiomysql.connect(host=settings.settings.abb_db_host, port=settings.settings.abb_db_port, db=settings.abb_db, user=settings.settings.abb_db_user, password=settings.settings.abb_db_password) as conn:
cur = await conn.cursor()
await cur.execute('select status from work_parser;')
row = await cur.fetchone()
if row:
status = row[0]
else:
await cur.execute("insert into work_parser(date_work, status_work, status) values(now(), '%s', %d); commit;"% (work_statuses['working']['description'], work_statuses['working']['status']))
status = work_statuses['working']['status']
#check credentials
await cur.execute('select settings.login, pass, proxy from settings.login;')
r = await cur.fetchone()
if r:
#if settings.login, password or proxy have been changed then we must reauthenticate
if r[0] != settings.login['user'] or r[1] != settings.login['pass'] or r[2] != settings.login['proxy']:
settings.login['user']=r[0]
settings.login['pass']=r[1]
settings.login['proxy']=r[2]
#status = 3 - parst must reauthenticate
await cur.execute('update work_parser set = 3; commit;')
status = 3
else:
await cur.execute("insert into settings.login(settings.login, pass, proxy) values('%s', '%s', '%s'); commit;" % (settings.login['user'], settings.login['pass'], settings.login['proxy']))
await cur.close()
except Exception as exc:
print(exc)
return status
def connect(self,
host=None,
user=None,
password=None,
db=None,
use_unicode=True,
no_delay=None,
port=None,
**kwargs):
if host is None:
host = self.host
if user is None:
user = self.user
if password is None:
password = self.password
if db is None:
db = self.db
if port is None:
port = self.port
conn = yield from aiomysql.connect(loop=self.loop,
host=host,
user=user,
password=password,
db=db,
use_unicode=use_unicode,
no_delay=no_delay,
port=port,
**kwargs)
self.addCleanup(conn.close)
return conn
def test___del__(self):
conn = yield from aiomysql.connect(
loop=self.loop, host=self.host, port=self.port, db=self.db, user=self.user, password=self.password
)
with self.assertWarns(ResourceWarning):
del conn
gc.collect()
def __new__(cls, clsname, superclasses, attributedict):
try:
loop = asyncio.get_event_loop()
coro = asyncio.start_server(
client_connected_cb=attributedict['handler'],
host=attributedict['host_server'],
port=attributedict['port_server'],
loop=loop)
conn = aiomysql.connect(host=attributedict['host_db'],
port=attributedict['port_db'],
user=attributedict['user_db'],
password=attributedict['password_db'],
db=attributedict['db'],
loop=loop,
cursorclass=aiomysql.cursors.DictCursor)
conn = loop.run_until_complete(conn)
server = loop.run_until_complete(coro)
attributedict['conn'] = conn
attributedict['loop'] = loop
attributedict['server'] = server
return type.__new__(cls, clsname, superclasses, attributedict)
except Exception as e:
raise Exception(e)
def f(**kw):
conn_kw = mysql_params.copy()
conn_kw.update(kw)
_loop = conn_kw.pop('loop', loop)
conn = yield from aiomysql.connect(loop=_loop, **conn_kw)
connections.append(conn)
return conn
def tomysql(queue):
while 1:
dict = yield from queue.get()
if dict is None:
break
conn = yield from aiomysql.connect(host='127.0.0.1',
port=3306,
charset='utf8',
autocommit=True,
user='******',
password='******',
db='testdb',
loop=loop)
cur = yield from conn.cursor()
yield from cur.execute(
"create table if not exists testdb.spider_ips(ID int unsigned not null auto_increment primary key,\
ip varchar(244) not null,\
port varchar(244) not null)")
for i in range(len(dict['ip'])):
yield from cur.execute(
'insert into testdb.spider_ips(ip,port) values(%s,%s)',
(dict['ip'][i], dict['port'][i]))
log([dict['ip'][i], dict['port'][i]])
yield from cur.close()
conn.close()
def test_issue_17(self):
""" could not connect mysql use passwod """
conn = self.connections[0]
host = self.host
db = self.db
c = yield from conn.cursor()
# grant access to a table to a user with a password
try:
yield from c.execute("drop table if exists issue17")
yield from c.execute(
"create table issue17 (x varchar(32) primary key)")
yield from c.execute(
"insert into issue17 (x) values ('hello, world!')")
yield from c.execute("grant all privileges on %s.issue17 to "
"'issue17user'@'%%' identified by '1234'"
% db)
yield from conn.commit()
conn2 = yield from aiomysql.connect(host=host, user="******",
passwd="1234", db=db,
loop=self.loop)
c2 = yield from conn2.cursor()
yield from c2.execute("select x from issue17")
r = yield from c2.fetchone()
self.assertEqual("hello, world!", r[0])
finally:
yield from c.execute("drop table issue17")
def test_example_executemany():
conn = yield from aiomysql.connect(host='127.0.0.1', port=3306,
user='******', password='',
db='test_pymysql', loop=loop)
cur = yield from conn.cursor()
yield from cur.execute("DROP TABLE IF EXISTS music_style;")
yield from cur.execute("""CREATE TABLE music_style
(id INT,
name VARCHAR(255),
PRIMARY KEY (id));""")
yield from conn.commit()
# insert 3 rows one by one
yield from cur.execute("INSERT INTO music_style VALUES(1,'heavy metal')")
yield from cur.execute("INSERT INTO music_style VALUES(2,'death metal');")
yield from cur.execute("INSERT INTO music_style VALUES(3,'power metal');")
yield from conn.commit()
# insert 3 row by one long query using *executemane* method
data = [(4, 'gothic metal'), (5, 'doom metal'), (6, 'post metal')]
yield from cur.executemany(
"INSERT INTO music_style (id, name)"
"values (%s,%s)", data)
yield from conn.commit()
# fetch all insert row from table music_style
yield from cur.execute("SELECT * FROM music_style;")
result = yield from cur.fetchall()
print(result)
yield from cur.close()
conn.close()
async def get_db():
"""Database connection used for db: Sessions. Creates a cursor to execute
queries, cursors are added to the event pool,
yielded to use for execution and then the queries are commited to the database.
Raises:
HTTPException: If the connection to the database fails then the exception is raised.
Yields:
DictCursor: That allows the connection to be queried.
"""
# loop = asyncio.get_event_loop()
async with aiomysql.connect(host=f'{CONNECTION}',
port=3306,
user=f'{USERNAME}',
password=f'{DATABASE_PASSWORD}',
db=f"{DATABASE}",
autocommit=False) as conn:
cursor = await conn.cursor(aiomysql.DictCursor)
if not cursor:
raise HTTPException(
status_code=status.HTTP_503_SERVICE_UNAVAILABLE,
detail=f"Could not connect to the database")
yield cursor
await conn.commit()
async def lists(self, ctx):
async with aiomysql.connect(host="localhost",
user="******",
db="role",
password="") as conn:
async with conn.cursor() as cur:
server_id = ctx.message.guild.id
if not await cur.execute(
'SELECT 1 FROM roles WHERE server_id=%s;',
(server_id, )):
embed = discord.Embed(description="この鯖にはレベル役職が登録されてません。")
return await ctx.send(embed=embed)
await cur.execute(
'SELECT lower,upper,role_id FROM roles WHERE server_id=%s ORDER BY lower;',
(server_id, ))
all_list = await cur.fetchall()
list_roles = "".join([
f"`[{k+1}]: Lv{member[0]}~{member[1]}:『{discord.utils.get(ctx.message.guild.roles,id=member[2]).name}』`\n"
for member, k in zip(all_list, range(0, len(all_list)))
])
ranking_msgs = [
"\n".join(list_roles.split("\n")[i:i + 25])
for i in range(0, len(all_list), 25)
]
for row in ranking_msgs:
await ctx.send(embed=discord.Embed(
description=row).set_author(name="現在の役職リストはこちら"))
def test_issue_17(self):
""" could not connect mysql use passwod """
conn = self.connections[0]
host = self.host
db = self.db
c = conn.cursor()
# grant access to a table to a user with a password
try:
yield from c.execute("drop table if exists issue17")
yield from c.execute(
"create table issue17 (x varchar(32) primary key)")
yield from c.execute(
"insert into issue17 (x) values ('hello, world!')")
yield from c.execute("grant all privileges on %s.issue17 to "
"'issue17user'@'%%' identified by '1234'" %
db)
yield from conn.commit()
conn2 = yield from aiomysql.connect(host=host,
user="******",
passwd="1234",
db=db,
loop=self.loop)
c2 = conn2.cursor()
yield from c2.execute("select x from issue17")
r = yield from c2.fetchone()
self.assertEqual("hello, world!", r[0])
finally:
yield from c.execute("drop table issue17")
async def get_one_row(sql, *args):
logging.debug(f'{sql}')
logging.debug(f'{args}')
async with aiomysql.connect(**mysql_connection_details()) as conn:
async with conn.cursor() as cur:
await cur.execute(sql, *args)
return await cur.fetchone()
async def db_connection():
async with aiomysql.connect(host=db['host'],
db=db['database'],
user=db['user'],
password=db['password'],
autocommit=db['autocommit'],
charset=db['charset']) as conn:
yield conn
async def go():
with pytest.raises(RuntimeError) as ctx:
async with aiomysql.connect(**kw) as conn:
assert not conn.closed
raise RuntimeError('boom')
assert str(ctx.value) == 'boom'
assert conn.closed
def test___del__(self):
conn = yield from aiomysql.connect(loop=self.loop, host=self.host,
port=self.port, db=self.db,
user=self.user,
password=self.password)
with self.assertWarns(ResourceWarning):
del conn
gc.collect()
async def execute_many(sql, rows) -> int:
logging.debug(f'{sql}')
logging.debug(f'{rows}')
async with aiomysql.connect(**mysql_connection_details()) as conn:
async with conn.cursor() as cur:
await cur.executemany(sql, rows)
await conn.commit()
return cur.rowcount
async def get_rows_as_dict_array(sql, *args):
logging.debug(f'{sql}')
logging.debug(f'{args}')
async with aiomysql.connect(**mysql_connection_details()) as conn:
async with conn.cursor() as cur:
await cur.execute(sql, args)
columns = [column[0] for column in cur.description]
return [dict(zip(columns, r)) for r in await cur.fetchall()]
def __init__(self, bot):
self.bot = bot
self.timers = {'casts': 0, 'streams': 0, 'links': 0}
self._rage = {}
self.conn = asyncio.async(aiomysql.connect(host=DB_SERVER, port=DB_PORT,
user=DB_LOGIN, password=DB_PASSWORD,
db=DB_TABLE))
self.conn = asyncio.get_event_loop().run_until_complete(self.conn)
async def go():
with pytest.raises(RuntimeError) as ctx:
async with aiomysql.connect(**kw) as conn:
assert not conn.closed
raise RuntimeError('boom')
assert str(ctx.value) == 'boom'
assert conn.closed
async def test_connect_method_exception(mysql_params, loop):
with pytest.raises(RuntimeError) as ctx:
async with aiomysql.connect(loop=loop, **mysql_params) as connection:
assert not connection.closed
raise RuntimeError('boom')
assert str(ctx.value) == 'boom'
assert connection.closed
def connect(self, db_config):
return aiomysql.connect(host=db_config['host'],
user=db_config['user'],
password=db_config['password'],
db=db_config['db'],
charset=db_config['charset'],
cursorclass=aiomysql.cursors.DictCursor,
autocommit=True)
async def test_connect_method_exception(mysql_params, loop):
with pytest.raises(RuntimeError) as ctx:
async with aiomysql.connect(loop=loop, **mysql_params) as connection:
assert not connection.closed
raise RuntimeError('boom')
assert str(ctx.value) == 'boom'
assert connection.closed
async def main():
# 等待mysql连接好
pool = aiomysql.connect(host='localhost', port=3306, user='******',
password='******', db='my_aio', loop=loop, charset='utf8', autocommit=True)
async with aiohttp.CLientSession() as session:
html = await fetch(start_url, session)
seen_urls.add(start_url)
extract_urls(html)
asyncio.ensure_future(consumer(pool))
def test():
conn = yield from aiomysql.connect(**db_config)
cur = yield from conn.cursor()
yield from cur.execute("select host,user from mysql.user")
print(cur.description)
r = yield from cur.fetchall()
print(r)
yield from cur.close()
conn.close()
def koneksi():
loop = asyncio.get_event_loop()
conn = aiomysql.connect(host='127.0.0.1',
port=3306,
user='******',
password='',
db='bajigur',
loop=loop)
return conn
def test_issue_34(self):
try:
yield from aiomysql.connect(host="localhost", port=1237,
user="******", loop=self.loop)
self.fail()
except aiomysql.OperationalError as e:
self.assertEqual(2003, e.args[0])
except Exception:
self.fail()
async def test_connect_method(mysql_params, loop):
async with aiomysql.connect(loop=loop, **mysql_params) as connection:
async with connection.cursor() as cursor:
await cursor.execute("SELECT 42")
value = await cursor.fetchone()
assert value, (42,)
assert cursor.closed
assert connection.closed
def _connect_to_ctl(self):
self._ctl_connection_settings = dict(self._connection_settings)
self._ctl_connection_settings["db"] = "information_schema"
self._ctl_connection_settings["cursorclass"] = aiomysql.DictCursor
self._ctl_connection = yield from aiomysql.connect(
**self._ctl_connection_settings)
self._ctl_connection._get_table_information = \
self._get_table_information
self._connected_ctl = True
async def test_connect_method(mysql_params, loop):
async with aiomysql.connect(loop=loop, **mysql_params) as connection:
async with connection.cursor() as cursor:
await cursor.execute("SELECT 42")
value = await cursor.fetchone()
assert value, (42, )
assert cursor.closed
assert connection.closed
def test_issue_34(self):
try:
yield from aiomysql.connect(host="localhost", port=1237,
user="******", loop=self.loop)
self.fail()
except aiomysql.OperationalError as e:
self.assertEqual(2003, e.args[0])
except Exception:
self.fail()
def connect(self):
self.connection = yield from aiomysql.connect(host=self.host,
port=self.port,
user=self.user,
password=self.password,
db=self.db,
charset='utf8',
autocommit=True)
self.cursor = yield from self.connection.cursor(aiomysql.DictCursor)
return self.cursor
def connect(self, **kwargs):
conn = yield from connect(db=self.db,
user=self.user,
password=self.password,
host=self.host,
loop=self.loop,
**kwargs)
# TODO: fix this, should autocommit be enabled by default?
yield from conn.autocommit(True)
ret = sa.SAConnection(conn, sa.engine._dialect)
return ret
def connect(self, **kwargs):
conn = yield from connect(db=self.db,
user=self.user,
password=self.password,
host=self.host,
loop=self.loop,
**kwargs)
# TODO: fix this, should autocommit be enabled by default?
yield from conn.autocommit(True)
ret = sa.SAConnection(conn, sa.engine._dialect)
return ret
def connect(self, **kwargs):
conn = yield from connect(db='test_pymysql',
user='******',
password='',
host='127.0.0.1',
loop=self.loop,
**kwargs)
# TODO: fix this, should autocommit be enabled by default?
yield from conn.autocommit(True)
ret = sa.SAConnection(conn, sa.engine._dialect)
return ret
def get_connection(self, loop, db=None):
self._conn = yield from aiomysql.connect(host=self._host,
port=self._port,
user=self._user,
password=self._password,
loop=loop)
yield from self._get_db_list()
if not self._check_db(self._db):
yield from self._create_db(self._db)
yield from self._conn.select_db(self._db)
return self._conn
async def go():
async with aiomysql.connect(loop=self.loop, host=self.host,
port=self.port, user=self.user,
db=self.db, password=self.password,
use_unicode=True, echo=True) as conn:
async with conn.cursor() as cur:
await cur.execute("SELECT 42")
value = await cur.fetchone()
assert value, (42,)
assert cur.closed
assert conn.closed
def test_example():
conn = yield from aiomysql.connect(host='127.0.0.1', port=3306,
user='******', password='******', db='mysql',
loop=loop, autocommit=True)
cur = yield from conn.cursor()
yield from cur.execute("SELECT Host,User FROM user")
print(cur.description)
r = yield from cur.fetchall()
print(r)
yield from cur.close()
conn.close()
def setUp(self):
self.root_conn = yield from aiomysql.connect(host='127.0.0.1', port=3306,
user='******', password='******', db='test',
loop=self.loop)
cur = yield from self.root_conn.cursor()
yield from cur.execute("SET GLOBAL wait_timeout=1")
yield from cur.close()
self.pool = yield from aiomysql.create_pool(minsize=2, host='127.0.0.1', port=3306,
user='******', password='******', db='test',
loop=self.loop)
self.keep_alive = KeepAliveService(self.pool, self.loop, 1).start()
def test_example_transaction():
conn = yield from aiomysql.connect(host='127.0.0.1', port=3306,
user='******', password='',
db='test_pymysql', autocommit=False,
loop=loop)
cursor = yield from conn.cursor()
stmt_drop = "DROP TABLE IF EXISTS names"
yield from cursor.execute(stmt_drop)
yield from cursor.execute("""
CREATE TABLE names (
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(30) DEFAULT '' NOT NULL,
cnt TINYINT UNSIGNED DEFAULT 0,
PRIMARY KEY (id))""")
yield from conn.commit()
# Insert 3 records
names = (('Geert',), ('Jan',), ('Michel',))
stmt_insert = "INSERT INTO names (name) VALUES (%s)"
yield from cursor.executemany(stmt_insert, names)
# Roll back!!!!
yield from conn.rollback()
# There should be no data!
stmt_select = "SELECT id, name FROM names ORDER BY id"
yield from cursor.execute(stmt_select)
resp = yield from cursor.fetchall()
# Check there is no data
assert not resp
# Do the insert again.
yield from cursor.executemany(stmt_insert, names)
# Data should be already there
yield from cursor.execute(stmt_select)
resp = yield from cursor.fetchall()
print(resp)
# Do a commit
yield from conn.commit()
yield from cursor.execute(stmt_select)
print(resp)
# Cleaning up, dropping the table again
yield from cursor.execute(stmt_drop)
yield from cursor.close()
conn.close()
def connect(self, host=None, user=None, password=None,
db=None, use_unicode=True, no_delay=None, **kwargs):
if host is None:
host = self.host
if user is None:
user = self.user
if password is None:
password = self.password
if db is None:
db = self.db
conn = yield from aiomysql.connect(loop=self.loop, host=host,
user=user, password=password,
db=db, use_unicode=use_unicode,
no_delay=no_delay, **kwargs)
return conn
def connect(self, **kwargs):
conn = yield from connect(db=self.db,
user=self.user,
password=self.password,
host=self.host,
loop=self.loop,
**kwargs)
# TODO: fix this, should autocommit be enabled by default?
yield from conn.autocommit(True)
engine = mock.Mock()
engine.dialect = sa.engine._dialect
def release(*args):
return
engine.release = release
ret = sa.SAConnection(conn, engine)
return ret
def connect(self, **kwargs):
conn = yield from connect(db=self.db,
user=self.user,
password=self.password,
host=self.host,
loop=self.loop,
**kwargs)
yield from conn.autocommit(True)
cur = yield from conn.cursor()
yield from cur.execute("DROP TABLE IF EXISTS sa_tbl")
yield from cur.execute("CREATE TABLE sa_tbl "
"(id serial, name varchar(255))")
yield from cur.execute("INSERT INTO sa_tbl (name)"
"VALUES ('first')")
yield from cur._connection.commit()
# yield from cur.close()
return sa.SAConnection(conn, sa.engine._dialect)
def test_example():
conn = yield from aiomysql.connect(host='127.0.0.1', port=3306,
user='******', password='',
db='test_pymysql', loop=loop)
cur = yield from conn.cursor()
yield from cur.execute("DROP PROCEDURE IF EXISTS myinc;")
yield from cur.execute("""CREATE PROCEDURE myinc(p1 INT)
BEGIN
SELECT p1 + 1;
END
""")
yield from cur.callproc('myinc', [1])
(ret, ) = yield from cur.fetchone()
assert 2, ret
print(ret)
yield from cur.close()
conn.close()
def connection(mysql_params, loop):
coro = aiomysql.connect(loop=loop, **mysql_params)
conn = loop.run_until_complete(coro)
yield conn
loop.run_until_complete(conn.ensure_closed())
def connect_conn_control(self, db):
if self.conn_control is not None:
self.conn_control.close()
self.conn_control = yield from aiomysql.connect(**db)
