def delete(self):
"""delete one record from the table
sql: delete from users where id=2
"""
sql = 'delete from %s %s' %(self.model.table_name, self.where_expr)
logging.info("delete sql: ".format(sql))
db.execute(sql, self.params)
db.commit()
db.cleanup()
def insert(self):
"""insert one record to the table
sql: insert into users (name, password, email) values ()
"""
sql = 'insert into %s(%s) values (%s);' % (
self.model.table_name, ', '.join(self.model.keys()), ', '.join(['%s'] * len(self.model.values())))
logging.info("insert sql: ".format(sql))
db.execute(sql, self.model.values())
db.commit()
db.cleanup()
def update(self, **kwargs):
params = []
equations = []
for field in self.fields:
try:
value = self[field]
equations.append("{}=%s".format(field))
params.append(value)
except:
pass
if equations:
where_expr = "where {}".format(",".join(equations))
else:
raise ValueError("where statement no exist")
update_fields = []
update_params = []
for field, value in kwargs.iteritems():
if field not in self.fields:
raise AttributeError("'Model' object has no field '{}'".format(field))
update_fields.append(field)
update_params.append(value)
# Set sql execute params
update_params.extend(params)
sql = 'update {} set {} {}'.format(self.table_name,
','.join(['{}=%s'.format(field) for field in update_fields]), where_expr)
logging.info("update sql: {}".format(sql))
logging.info("params: {}".format(update_params))
cursor = None
with db:
cursor = db.execute(sql, update_params)
return cursor
def delete(self):
"""delete one record from the table
sql: delete from users where id=2
"""
sql = 'delete from {}'.format(self.table_name)
equations = []
params = []
logging.info("fields:{}".format(self.fields))
for field in self.fields:
try:
value = self[field]
equations.append("{}=%s".format(field))
params.append(value)
except:
pass
if equations:
sql = "{} where {}".format(sql, ",".join(equations))
else:
raise ValueError("where statement no exist")
logging.info("delete sql: {}".format(sql))
logging.info("params: {}".format(params))
cursor = None
with db:
cursor = db.execute(sql, params)
return cursor
def insert(self):
"""insert one record to the table
sql: insert into users (name, password, email) values ()
"""
insert_field = self.fields.copy()
if "id" in insert_field:
del insert_field["id"]
sql = "insert into {} ({}) values ({})".format(
self.table_name, ", ".join(insert_field), ",".join(["%s"]*len(insert_field)))
logging.info("insert sql: {}".format(sql))
params = []
for field in insert_field:
try:
value = self[field]
except KeyError:
# Set field default value is ""
value = ""
params.append(value)
logging.info("params: {}".format(params))
cursor = None
with db:
cursor = db.execute(sql, params)
return cursor
def update(self, **kwargs):
# update students set tel=default where id=5
_keys = []
_params = []
for key, val in kwargs.iteritems():
if val == None or key not in self.model.fields:
continue
_keys.append(key)
_params.append(val)
_params.extend(self.params)
sql = 'update %s set %s %s' %(
self.model.table_name, ','.join([key + '=%s' for key in _keys]), self.where_expr
)
logging.info(sql)
db.execute(sql, _params)
db.commit()
db.cleanup()
def _select(self, need_one=False):
sql = 'select * from %s %s' % (self.model.table_name, self.where_expr)
logging.info("select sql:{}".format(sql))
logging.info("params: {}".format(self.params))
result = {}
with db:
cursor = db.execute(sql, self.params)
if cursor.description:
row_names = [x[0] for x in cursor.description]
if need_one:
values = cursor.fetchone()
if values is not None:
result = Dict(row_names, values)
else:
result = [Dict(row_names, x) for x in cursor.fetchall()]
return result
def select(cls, need_one=False, where=None, args=None, **kwargs):
sql = 'select * from {}'.format(cls.table_name)
params = []
if where is not None:
equations = []
for field in where.keys():
if field not in cls.fields:
raise AttributeError("'Model' object has no field '{}'".format(field))
equations.append("{}=%s".format(field))
if equations:
sql = "{} where {}".format(sql, " and ".join(equations))
params = where.values()
orderbys = kwargs.get("orderbys", None)
if orderbys is not None:
orderby_expr = "order by"
for orderby in orderbys:
if len(orderby) != 2:
raise ValueError("orderby params error")
if orderby[0] not in cls.fields:
raise AttributeError("'Model' object has no field '{}'".format(field))
orderby_expr = "{} {},".format(orderby_expr, " ".join(list(orderby)))
sql = "{} {}".format(sql, orderby_expr[:-1])
limit = kwargs.get("limit", None)
if limit is not None:
sql = "{} limit {}".format(sql, ",".join(str(x) for x in limit))
logging.info("select sql:{}".format(sql))
result = None
with db:
cursor = db.execute(sql, params)
if cursor.description:
row_names = [x[0] for x in cursor.description]
if need_one:
values = cursor.fetchone()
if values is not None:
result = Dict(row_names, values)
else:
result = [Dict(row_names, x) for x in cursor.fetchall()]
return result
def test_init_db():
sql = "insert into user (name, password, email) values (%s, %s, %s)"
values = ("suwen", "123456", "*****@*****.**")
with db:
cursor = db.execute(sql, values)
assert cursor.rowcount == 1
def count(self):
sql = 'select count(*) from %s %s;' % (self.model.table_name, self.where_expr)
print sql
(row_cnt, ) = db.execute(sql, self.params)
return row_cnt
def clear_table():
with db:
sql = "delete from {}".format(TABLE_NAME)
db.execute(sql)