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)
