def execute_anything(
self,
sql: str,
args: Optional[List[ValidSqlArgumentDescription]] = None,
fetch_rows: bool = True
) -> Tuple[int, List[Dict[str, ValidSqlArgumentDescription]]]:
if args is None:
args = []
try:
return self.execute_with_reconnect(sql, args, fetch_rows)
except MySQLdb.Warning as e:
if e.args[0] in [1050, 1051]:
return (
0, []
) # we don't care if a CREATE IF NOT EXISTS raises an "already exists" warning or DROP TABLE IF NOT EXISTS raises an "unknown table" warning.
if e.args[0] == 1062:
return (
0, []
) # We don't care if an INSERT IGNORE INTO didn't do anything.
raise DatabaseException(
'Failed to execute `{sql}` with `{args}` because of `{e}`'.
format(sql=sql, args=args, e=e)) from e
except MySQLdb.Error as e:
raise DatabaseException(
'Failed to execute `{sql}` with `{args}` because of `{e}`'.
format(sql=sql, args=args, e=e)) from e
def __init__(self, db):
warnings.filterwarnings('error', category=MySQLdb.Warning)
try:
self.name = db
host = configuration.get('mysql_host')
port = configuration.get('mysql_port')
if str(port).startswith('0.0.0.0:'):
# Thanks Docker :/
port = int(port[8:])
user = configuration.get('mysql_user')
passwd = configuration.get('mysql_passwd')
self.connection = MySQLdb.connect(host=host,
port=port,
user=user,
passwd=passwd,
use_unicode=True,
charset='utf8',
autocommit=True)
self.cursor = self.connection.cursor(MySQLdb.cursors.DictCursor)
self.execute('SET NAMES utf8mb4')
try:
self.execute("USE {db}".format(db=db))
except DatabaseException:
print('Creating database {db}'.format(db=db))
self.execute(
'CREATE DATABASE {db} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci'
.format(db=db))
self.execute('USE {db}'.format(db=db))
except MySQLdb.Error as e:
raise DatabaseException(
'Failed to initialize database in `{location}`'.format(
location=db)) from e
def execute_with_reconnect(
self,
sql: str,
args: Optional[List[ValidSqlArgumentDescription]] = None,
fetch_rows: Optional[bool] = False
) -> Tuple[int, List[ValidSqlArgumentDescription]]:
result = None
# Attempt to execute the query and reconnect 3 times, then give up
for _ in range(3):
try:
p = perf.start()
n = self.cursor.execute(sql, args)
perf.check(p, 'slow_query', (f'```{sql}```', f'```{args}```'),
'mysql')
if fetch_rows:
rows = self.cursor.fetchall()
result = (n, rows)
else:
result = (n, [])
break
except OperationalError as e:
if 'MySQL server has gone away' in str(e):
print('MySQL server has gone away: trying to reconnect')
self.connect()
else:
# raise any other exception
raise
else:
# all attempts failed
raise DatabaseException(
'Failed to execute `{sql}` with `{args}`. MySQL has gone away and it was not possible to reconnect in 3 attemps'
.format(sql=sql, args=args))
return result
def close(self) -> None:
if len(self.open_transactions) > 0:
self.execute('ROLLBACK')
self.cursor.close()
self.connection.close()
if len(self.open_transactions) > 0:
raise DatabaseException(f'Closed database connection with open transactions `{self.open_transactions}` (they have been rolled back).')
def connect(self) -> None:
try:
self.connection = MySQLdb.connect(host=self.host,
port=self.port,
user=self.user,
passwd=self.passwd,
use_unicode=True,
charset='utf8',
autocommit=True)
self.cursor = self.connection.cursor(MySQLdb.cursors.DictCursor)
self.execute('SET NAMES utf8mb4')
try:
self.execute('USE {db}'.format(db=self.name))
except DatabaseException:
print('Creating database {db}'.format(db=self.name))
self.execute(
'CREATE DATABASE {db} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci'
.format(db=self.name))
self.execute('USE {db}'.format(db=self.name))
except MySQLdb.Error as c:
msg = 'Failed to initialize database in `{location}`'.format(
location=self.name)
if c.args[0] in [2002, 2003]:
raise DatabaseConnectionRefusedException(msg) from c
raise DatabaseException(msg) from c
def value(self, sql: str, args: Any = None, default: Any = None, fail_on_missing: bool = False) -> Any:
try:
return self.values(sql, args)[0]
except IndexError:
if fail_on_missing:
raise DatabaseException('Failed to get a value from `{sql}`'.format(sql=sql))
else:
return default
def commit(self, label: str) -> None:
print(f'Before COMMIT ({self.open_transactions})')
if len(self.open_transactions) == 1:
self.execute('COMMIT')
committed = self.open_transactions.pop()
if committed != label:
raise DatabaseException(f'Asked to commit `{committed}` to the db but was expecting to commit `{label}`.')
print(f'After COMMIT ({self.open_transactions})')
def execute(self, sql, args=None):
if args is None:
args = []
try:
return self.execute_with_reconnect(sql, args)
except MySQLdb.Warning as e:
if e.args[0] == 1050 or e.args[0] == 1051:
pass # we don't care if a CREATE IF NOT EXISTS raises an "already exists" warning or DROP TABLE IF NOT EXISTS raises an "unknown table" warning.
elif e.args[0] == 1062:
pass # We don't care if an INSERT IGNORE INTO didn't do anything.
else:
raise DatabaseException(
'Failed to execute `{sql}` with `{args}` because of `{e}`'.
format(sql=sql, args=args, e=e))
except MySQLdb.Error as e:
raise DatabaseException(
'Failed to execute `{sql}` with `{args}` because of `{e}`'.
format(sql=sql, args=args, e=e))
def value(self, sql, args=None, default=None, fail_on_missing=False):
try:
return self.values(sql, args)[0]
except IndexError as e:
if fail_on_missing:
raise DatabaseException(
'Failed to get a value from `{sql}`'.format(
sql=sql)) from e
else:
return default
def __init__(self, location):
try:
self.name = location
self.connection = apsw.Connection(location)
self.connection.setrowtrace(row_factory)
self.connection.enableloadextension(True)
self.connection.loadextension(configuration.get('spellfix'))
self.cursor = self.connection.cursor()
except apsw.Error as e:
raise DatabaseException(
'Failed to initialize database in `{location}`'.format(
location=location)) from e
def execute(self, sql, args=None):
sql = sql.replace('MEDIUMINT UNSIGNED',
'INTEGER') # Column type difference.
sql = sql.replace(' SEPARATOR ',
', ') # MySQL/SQLite GROUP_CONCAT syntax difference.
sql = sql.replace('%%', '%') # MySQLDB and apsw escaping difference.
if args is None:
args = []
try:
return self.cursor.execute(sql, args).fetchall()
except apsw.Error as e:
# Quick fix for league bugs
if "cannot start a transaction within a transaction" in str(e):
self.execute("ROLLBACK")
if sql == "BEGIN TRANSACTION":
return self.cursor.execute(sql, args).fetchall()
raise DatabaseException(
'Failed to execute `{sql}` with `{args}` because of `{e}`'.
format(sql=sql, args=args, e=e)) from e
def execute(self, sql, args=None):
sql = sql.replace(
'COLLATE NOCASE', ''
) # Needed for case insensitivity in SQLite which is default in MySQL.
if args is None:
args = []
if args:
# eww
sql = sql.replace('?', '%s')
try:
self.cursor.execute(sql, args)
return self.cursor.fetchall()
except MySQLdb.Warning as e:
if e.args[0] == 1050:
pass # we don't care if a CREATE IF NOT EXISTS raises an "already exists" warning.
else:
raise
except MySQLdb.Error as e:
raise DatabaseException(
'Failed to execute `{sql}` with `{args}` because of `{e}`'.
format(sql=sql, args=args, e=e)) from e
def execute_with_reconnect(self, sql, args):
result = None
# Attempt to excute the query and reconnect 3 times, then give up
for _ in range(3):
try:
p = perf.start()
self.cursor.execute(sql, args)
perf.check(p, 'slow_query', (sql, args), 'mysql')
result = self.cursor.fetchall()
break
except OperationalError as e:
if 'MySQL server has gone away' in str(e):
print("MySQL server has gone away: trying to reconnect")
self.connect()
else:
# raise any other exception
raise e
else:
# all attempts failed
raise DatabaseException(
'Failed to execute `{sql}` with `{args}`. MySQL has gone away and it was not possible to reconnect in 3 attemps'
.format(sql=sql, args=args))
return result
