class MySql(Database): @gen.coroutine def connect(self, callback=None): if not self.is_connected: self.db = Pool( dict(user=self.connection.user, passwd=self.connection.password, db=self.connection.database, cursorclass=cursor_type), max_idle_connections=1, max_open_connections=1) self.is_connected = True if callback is None: raise gen.Return(True) callback(True) @gen.coroutine def close(self, callback=None): if self.is_connected: yield self.db.close() self.is_connected = False if callback is None: raise gen.Return(True) callback(True) @staticmethod def _quote(value): if value is None: return 'null' if isinstance(value, datetime.datetime): return "'%s'" % str(value) if value == 'NOW()': return value # convert python3 byte strings if sys.version_info >= (3,0,0) and isinstance(value, bytes): value = value.decode('utf-8') if isinstance(value, float): return str(value) try: value = str(int(value)) except: value = "'%s'" % tornado_mysql.converters.escape_string(value) return value @gen.coroutine def select_one(self, table, **kwargs): yield self.connect() where_bits = [] for key in kwargs: where_bits.append("`%s` = %s" % (key, MySql._quote(kwargs[key]))) sql = "SELECT * FROM `%s` WHERE BINARY %s" % (table, ' AND BINARY '.join(where_bits)) cur = yield self.db.execute(sql) result = cur.fetchone() if result is None: raise error.StormNotFoundError("Object of type: %s not found with args: %s" % (table, kwargs)) callback = kwargs.get('callback') if callback is None: raise gen.Return(result) callback(result) @gen.coroutine def select_multiple(self, table, query, **kwargs): yield self.connect() query.bind(':table', table) page = kwargs.get('page') if page: page_size = kwargs.get('page_size', 10) query.limit = page_size query.offset = (page - 1) * page_size raw_sql = query.sql total_count = 0 tasks = [self.db.execute(raw_sql)] if page: tasks.append(self.db.execute(query.count_sql)) cursors = yield tasks results = [cursors[0].fetchall()] if len(cursors) > 1: results.append(cursors[1].fetchall()) data = results[0] total_count = len(data) if len(results) == 2: total_count = results[1][0]['count'] data, filtered_out_count = query.apply_filters(data) total_count -= filtered_out_count callback = kwargs.get('callback', None) if callback is None: raise gen.Return([data, total_count]) callback([data, total_count]) @gen.coroutine def insert(self, table, data, callback=None): yield self.connect() fields = [] values = [] for key in data: fields.append(key) value = MySql._quote(data[key]) values.append(value) sql = "INSERT INTO `%s` (`%s`) VALUES (%s)" % (table, '`, `'.join(fields), ', '.join(values)) # double escape % sign so we don't get an error if one of the fields # we are trying to insert has a % in it sql = sql.replace('%', '%%') cur = yield self.db.execute(sql) insert_id = cur.lastrowid if callback is None: raise gen.Return(insert_id) callback(insert_id) @gen.coroutine def update(self, table, data, changes, primary_key, callback=None): if len(changes) == 0: raise gen.Return(False) yield self.connect() if 'modified_on' in data: changes.append('modified_on') data['modified_on'] = 'NOW()' pairs = [] compound_primary_key = isinstance(primary_key, list) for key in changes: if compound_primary_key and key in primary_key: continue if key == primary_key: continue pairs.append("`%s` = %s" % (key, MySql._quote(data[key]))) if not compound_primary_key: primary_key = [primary_key] where_bits = [] for key in primary_key: where_bits.append("`%s` = %s" % (key, MySql._quote(data[key]))) sql = "UPDATE `%s` SET %s WHERE %s" % (table, ', '.join(pairs), ' AND '.join(where_bits)) result = yield self.db.execute(sql) if callback is None: raise gen.Return(result) callback(result)
class DB(object): def __init__(self, host='127.0.0.1', port=3306, user='******', passwd='', db='mysql'): self.pool = Pool(dict(host=host, port=port, user=user, passwd=passwd, db=db), max_idle_connections=5, max_recycle_sec=3, max_open_connections=20) logger.debug('Created database connection pool') @gen.coroutine def _query(self, query, fetchone=False, fetchall=False): logger.debug('QUERY: {}'.format(query)) try: cur = yield self.pool.execute(query) except Exception as e: logger.error(e) logger.debug('DETAILS', exc_info=traceback.format_exc()) raise gen.Return(None) if cur: if fetchone: if cur.rowcount != 1: logger.debug('RESULT: None') raise gen.Return(None) result = cur.fetchone() logger.debug('RESULT: {}'.format(result)) raise gen.Return(result) elif fetchall: result = cur.fetchall() logger.debug('RESULT: {}'.format(result)) raise gen.Return(cur.fetchall()) raise gen.Return(None) @gen.coroutine def get_user_code(self, mac): logger.info('Trying to find user code for MAC {}'.format(mac)) res = yield self._query( "SELECT code FROM device_code WHERE mac='{}';".format( adopt_mac(mac)), fetchone=True) if res and not res.exception(): (code, ) = res logger.info('Found code {}'.format(code)) raise gen.Return(code) logger.info('Code not found') raise gen.Return(None) @gen.coroutine def add_user(self, mac, code): logger.info('Adding new user. MAC: {}, CODE: {}'.format(mac, code)) res = yield self._query( "INSERT INTO device_code (mac, code) VALUES ('{}', '{}')".format( adopt_mac(mac), code)) if res: logger.debug(res) raise gen.Return(False) logger.info('Successfully added user MAC: {}, CODE: {}'.format( mac, code)) raise gen.Return(True)