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