def get_or_create(self, **restrictions):
sql_base = "INSERT INTO `{table_name}` ({fields}) " \
"SELECT {values} FROM DUAL " \
"WHERE NOT EXISTS (SELECT id FROM `{table_name}` WHERE " \
"{restrictions}) LIMIT 1"
defaults = self.defaults or {}
fields = []
values = []
for f in self.fields:
if f == 'id':
continue
fields.append('`{0}`'.format(f))
v = self.queries_raw.get(f)
v = defaults.get(f) if v is None else v
v = v() if callable(v) else v
values.append(self.to_string(v))
kwargs = {
'table_name': self.db_table,
'fields': ', '.join(fields),
'values': ', '.join(values),
'restrictions': ' AND '.join(["`{0}`={1}".format(k, self.to_string(v)) for k, v in restrictions.items()]) if restrictions else 'id=0'
}
print('SQL:', sql_base.format(**kwargs))
yield db_pool.execute(sql_base.format(**kwargs))
user = yield self.get()
raise tornado.gen.Return(user)
def _filter(self):
sql = "SELECT {0} FROM `{1}` WHERE {2}"
if self.order_by:
order_by = []
for o in self.order_by:
order_by.append("`{0}` DESC".format(o[1:]) if o.startswith('-') else "`{0}` ASC".format(o))
sql += " ORDER BY {0}".format(', '.join(order_by))
self.query = sql.format(', '.join(['`{0}`'.format(f) for f in self.fields]), self.db_table, self.queries)
print(self.query)
cursor = yield db_pool.execute(self.query)
raise tornado.gen.Return(cursor)
def _filter(self):
sql = "SELECT {0} FROM `{1}` WHERE {2}"
if self.order_by:
order_by = []
for o in self.order_by:
order_by.append("`{0}` DESC".format(o[1:]) if o.
startswith('-') else "`{0}` ASC".format(o))
sql += " ORDER BY {0}".format(', '.join(order_by))
self.query = sql.format(
', '.join(['`{0}`'.format(f) for f in self.fields]), self.db_table,
self.queries)
print(self.query)
cursor = yield db_pool.execute(self.query)
raise tornado.gen.Return(cursor)
def check_password(self, raw_password):
if self.data:
hashed_password = self.data.get('password')
else:
sql = "SELECT password FROM `user_user` WHERE {0}"
cursor = yield db_pool.execute(sql.format(self.queries))
res = cursor.fetchall()
if not res:
raise ObjectNotExists
elif len(res) > 1:
raise MultipleObjectsReturned
hashed_password = res[0][0]
hashed_password = hashed_password.encode('utf8')
raise tornado.gen.Return(True if self.gen_password(raw_password, hashed_password) == hashed_password else False)
def get_or_create(self, **restrictions):
sql_base = "INSERT INTO `{table_name}` ({fields}) " \
"SELECT {values} FROM DUAL " \
"WHERE NOT EXISTS (SELECT id FROM `{table_name}` WHERE " \
"{restrictions}) LIMIT 1"
defaults = self.defaults or {}
fields = []
values = []
for f in self.fields:
if f == 'id':
continue
fields.append('`{0}`'.format(f))
v = self.queries_raw.get(f)
v = defaults.get(f) if v is None else v
v = v() if callable(v) else v
values.append(self.to_string(v))
kwargs = {
'table_name':
self.db_table,
'fields':
', '.join(fields),
'values':
', '.join(values),
'restrictions':
' AND '.join([
"`{0}`={1}".format(k, self.to_string(v))
for k, v in restrictions.items()
]) if restrictions else 'id=0'
}
print('SQL:', sql_base.format(**kwargs))
yield db_pool.execute(sql_base.format(**kwargs))
user = yield self.get()
raise tornado.gen.Return(user)
def delete(self):
sql_base = "DELETE FROM `{0}` WHERE {1}"
self.query = sql_base.format(self.db_table, self.queries)
yield db_pool.execute(self.query)
def delete(self):
sql_base = "DELETE FROM `{0}` WHERE {1}"
self.query = sql_base.format(self.db_table, self.queries)
yield db_pool.execute(self.query)