def query_count(cls, query={}):
_sql = cls._sql_query_count.format(table=cls._table)
_sql += ' %s' % query.get('sql', '')
_params = query.get('args', ())
_cnt, _rs = cls.execute(_sql, _params, True)
logger.info('query count:%s, %s, %s', _sql, _cnt, _rs)
return _cnt, _rs
def query_by_pk(cls, pk):
_sql = cls._sql_query_by_pk.format(columns=','.join(cls._columns),
table=cls._table,
pk=cls._pk)
_cnt, _rs = cls.execute(_sql, (pk, ), True)
logger.info('query pk:%s, %s, %s, %s, %s', _sql, pk, _cnt, _rs)
return cls.create_object(
**dict(zip(cls._columns, _rs[0]))) if _cnt > 0 else None
def login(self):
_query = {}
_query['sql'] = ' AND username=%s AND password=md5(%s) AND status=0 '
_query['args'] = [self.username, self.password]
logger.info('user: %s login', self.username)
_cnt, _ = self.query_all(query=_query)
print _cnt > 0, _query
return _cnt > 0
def index():
logger.info('request /')
if session.get('user') is not None:
__username = session.get('user')
logger.warning('%s request / with valid session' % __username)
return redirect('/user/list')
# 返回templates目录下的login.html模板中的内容
return redirect('/user/login')
def create(self):
_sql = self._sql_insert.format(table=self._table,
columns=','.join(self._columns_add),
values=','.join(['%s'] *
len(self._columns_add)))
_params = [getattr(self, _column, '') for _column in self._columns_add]
print _sql, _params
_cnt, _ = self.execute(_sql, _params)
logger.info('create:%s, %s, %s', _sql, _params, _cnt)
return _cnt > 0
def update(self):
_values = [
'{column}=%s'.format(column=_column)
for _column in self._columns_update if _column != self._pk
]
_params = [
getattr(self, _column) for _column in self._columns_update
if _column != self._pk
]
_sql = self._sql_update.format(table=self._table,
values=','.join(_values),
pk=self._pk)
_params.append(getattr(self, self._pk))
print _sql
print _params
_cnt, _ = self.execute(_sql, _params)
logger.info('update:%s, %s, %s', _sql, _params, _cnt)
print _cnt > 0
return _cnt > 0
def query_all(cls, orderby='', offset=None, limit=None, query={}):
_sql = cls._sql_query_all.format(columns=','.join(cls._columns),
table=cls._table)
_sql += ' %s' % query.get('sql', '')
_params = query.get('args', [])
if orderby != '':
_sql += ' ORDER BY %s' % orderby
if limit is not None:
_sql += ' LIMIT %s'
_params.append(limit)
if offset is not None:
_sql += ' OFFSET %s'
_params.append(offset)
_cnt, _rs = cls.execute(_sql, _params, True)
logger.info('query all:%s, %s, %s', _sql, _cnt, _rs)
return _cnt, [
cls.create_object(**dict(zip(cls._columns, _r))) for _r in _rs
]
def execute_sql(sql, args=(), is_fetch=False):
_conn, _cur = None, None
_rt_cnt, _rt_fetch = 0, ()
try:
# 步骤1
_conn = MySQLdb.connect(host=gconf.DB_HOST,
port=gconf.DB_PORT,
user=gconf.DB_USER,
passwd=gconf.DB_PASSWD,
db=gconf.DB_NAME,
charset=gconf.DB_CHARSET)
_cur = _conn.cursor() # 步骤2
# print sql,args
_rt_cnt = _cur.execute(sql, args) # 步骤3
logger.info('excute %s,%s' % (sql, args))
if is_fetch: # 步骤4 判断是否为查询还是修改动作
_rt_fetch = _cur.fetchall() # 查询操作
else:
_conn.commit()
except BaseException, e:
pass
logger.error('sql excute error %s', str(e), exc_info=True)
def delete(cls, pk):
_sql = cls._sql_delete_by_pk.format(table=cls._table, pk=cls._pk)
_cnt, _ = cls.execute(_sql, (pk, ))
logger.info('delete:%s, %s, %s', _sql, pk, _cnt)
return _cnt > 0, ''