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)
