def updateByCode(self,kwargs):
"update the speicy table recorder"
"""
session.query(User).from_statement(text("SELECT * FROM users WHERE name=:name")).\
params(name='ed').all()
session.query(User).from_statement(
text("SELECT * FROM users WHERE name=:name")).params(name='ed').all()
"""
id = kwargs['id']
if id != "":
tablecls = self.init_table()
sqltext = "SELECT * FROM %s WHERE id=:id" % self.table
try:
recorder = session.query(tablecls).\
from_statement(text(sqltext)).\
params(id=id).one()
updatedattrs = [kw for kw in kwargs.keys() if kw != 'id']
for kw in updatedattrs:
setattr(recorder,kw,kwargs[kw])
session.commit()
except:
session.rollback()
pass
else:
return None
def test_db_mapping_adminlog(self):
from emc.kb.mapping_log_db import AdminLog
from emc.kb.mapping_log_db import UserLog
from emc.kb import log_session as Session
from emc.kb import ora_engine as engine
import os
os.environ['NLS_LANG'] = '.AL32UTF8'
import pdb
pdb.set_trace()
# AdminLog.__table__.drop(engine)
# UserLog.__table__.drop(engine)
# AdminLog.__table__.create(engine)
import pdb
pdb.set_trace()
# AdminLog.create(engine)
adminlog = AdminLog()
# AdminLog.id = 9
adminlog.adminid = u"admin4"
adminlog.userid = u" "
adminlog.datetime = datetime.datetime.now().strftime(fmt)
adminlog.ip = u"192.168.3.101"
adminlog.type = 0
adminlog.operlevel = 5
adminlog.result = 1
adminlog.description = u"admin2 删除 user2"
Session.add(adminlog)
Session.flush()
nums = Session.query(func.count(AdminLog.id)).scalar()
self.assertTrue(AdminLog.id is not None)
# self.assertEqual(int(nums),3)
Session.commit()
Session.rollback()
def getByCode(self, id):
if id != "":
try:
recorder = session.query(UserLog).\
from_statement(text("SELECT * FROM user_logs WHERE id=:id")).\
params(id=id).one()
return recorder
except:
session.rollback()
None
else:
return None
def getByCode(self,id):
import_str = "from %(p)s import %(t)s as tablecls" % dict(p=self.package,t=self.factorycls)
exec import_str
if id != "":
sqltext = "SELECT * FROM %(tbl)s WHERE id=:id" % dict(tbl=self.table)
try:
recorder = session.query(tablecls).\
from_statement(text(sqltext)).\
params(id=id).one()
return recorder
except:
# session.rollback()
return None
else:
return None
def DeleteByCode(self, id):
"delete the specify id recorder"
# xhdm = kwargs['xhdm']
if id != "":
try:
recorder = session.query(UserLog).\
from_statement(text("SELECT * FROM user_logs WHERE id=:id")).\
params(id=id).one()
session.delete(recorder)
session.commit()
except:
session.rollback()
pass
else:
return None
def DeleteByCode(self,id):
"delete the specify id recorder"
tablecls = self.init_table()
if id != "":
sqltext = "SELECT * FROM %(tbl)s WHERE id=:id" % dict(tbl=self.table)
try:
recorder = session.query(tablecls).\
from_statement(text(sqltext)).\
params(id=id).one()
session.delete(recorder)
session.commit()
return True
except:
session.rollback()
return False
else:
return None
def query(self,kwargs):
"""分页查询
columns = request.args.getlist('columns')
stmt = select([column(c) for c in columns]).\
select_from(some_table)
stmt = select([table.c[c] for c in columns])
results = db.session.execute(stmt).fetchall()
session.query.with_entities(SomeModel.col1)
"""
import_str = "from %(p)s import %(t)s as tablecls" % dict(p=self.package,t=self.factorycls)
exec import_str
start = int(kwargs['start'])
size = int(kwargs['size'])
max = size + start + 1
keyword = kwargs['SearchableText']
direction = kwargs['sort_order'].strip()
if size != 0:
if keyword == "":
if direction == "reverse":
sqltext = """select * from
(select a.*,rownum rn from
(select * from %s ORDER BY id DESC) a
where rownum < :max) where rn > :start""" % self.table
selectcon = text(sqltext)
else:
sqltext = """select * from
(select a.*,rownum rn from
(select * from %s ORDER BY id ASC) a
where rownum < :max) where rn > :start""" % self.table
selectcon = text(sqltext)
clmns = self.get_columns()
recorders = session.query(tablecls).with_entities(*clmns).\
from_statement(selectcon.params(start=start,max=max)).all()
else:
keysrchtxt = self.search_clmns2sqltxt(self.fullsearch_clmns)
if direction == "reverse":
sqltxt = """SELECT * FROM
(SELECT a.*,rownum rn FROM
(SELECT * FROM %(tbl)s WHERE %(ktxt)s ORDER BY id DESC ) a
WHERE rownum < :max) WHERE rn > :start
""" % dict(tbl=self.table,ktxt=keysrchtxt)
selectcon = text(sqltxt)
else:
sqltxt = """SELECT * FROM
(SELECT a.*,rownum rn FROM
(SELECT * FROM %(tbl)s WHERE %(ktxt)s ORDER BY id ASC ) a
WHERE rownum < :max) WHERE rn > :start
""" % dict(tbl=self.table,ktxt=keysrchtxt)
selectcon = text(sqltxt)
clmns = self.get_columns()
recorders = session.query(tablecls).with_entities(*clmns).\
from_statement(selectcon.params(x=keyword,start=start,max=max)).all()
else:
if keyword == "":
selectcon = text("SELECT * FROM %s ORDER BY id DESC " % self.table)
clmns = self.get_columns()
recorders = session.query(tablecls).with_entities(*clmns).\
from_statement(selectcon).all()
else:
keysrchtxt = self.search_clmns2sqltxt(self.fullsearch_clmns)
sqltext = """SELECT * FROM %(tbl)s WHERE %(ktxt)s
ORDER BY id DESC """ % dict(tbl=self.table,ktxt=keysrchtxt)
selectcon = text(sqltext)
clmns = self.get_columns()
recorders = session.query(tablecls).with_entities(*clmns).\
from_statement(selectcon.params(x=keyword)).all()
nums = len(recorders)
return nums
try:
return recorders
except:
return []
def query(self, kwargs):
"""以分页方式提取记录，参数：start 游标起始位置；size:每次返回的记录条数;
fields:field list
if size = 0,then不分页，返回所有记录集
order_by(text("id"))
--------------------------------
//page是当前页序数，rows是显示行数
int page=2;
int rows=5;
List<Articles> list=a.select(page*rows+1，(page-1)*rows);
// sql语句：
select * from(select a.*,rownum rn from (select * from t_articles) a where rownum < 11) where rn>5
//第一个参数，对应着第一个rownum<11,第二个参数对应着rn>5
--------------------------------
"""
start = int(kwargs['start'])
size = int(kwargs['size'])
# import pdb
# pdb.set_trace()
max = size + start + 1
keyword = kwargs['SearchableText']
import os
os.environ['NLS_LANG'] = '.AL32UTF8'
direction = kwargs['sort_order'].strip()
if size != 0:
if keyword == "":
if direction == "reverse":
selectcon = text(
"select * from"
"(select a.*,rownum rn from "
"(select * from user_logs ORDER BY id DESC) a "
"where rownum < :max) where rn > :start")
# selectcon = text("select * from admin_logs ORDER BY id DESC")
else:
selectcon = text(
"select * from"
"(select a.*,rownum rn from "
"(select * from user_logs ORDER BY id ASC) a "
"where rownum < :max) where rn > :start")
# selectcon = text("select * from admin_logs ORDER BY id ASC LIMIT :start,:size")
recorders = session.query("userid","datetime",
"ip","type","operlevel","description","result").\
from_statement(selectcon.params(start=start,max=max)).all()
else:
if direction == "reverse":
selectcon = text("select * from"
"(select a.*,rownum rn from "
"(select * from user_logs where"
" description LIKE :x "
" OR userid LIKE :x"
" OR operlevel LIKE :x"
" OR result LIKE :x"
" OR ip LIKE :x"
" ORDER BY id DESC ) a "
"where rownum < :max) where rn > :start")
else:
selectcon = text("select * from"
"(select a.*,rownum rn from "
"(select * from user_logs where"
" description LIKE :x "
" OR userid LIKE :x"
" OR operlevel LIKE :x"
" OR result LIKE :x"
" OR ip LIKE :x"
" ORDER BY id ASC ) a "
"where rownum < :max) where rn > :start")
recorders = session.query("userid","datetime",
"ip","type","operlevel","description","result").\
from_statement(selectcon.params(x=keyword,start=start,max=max)).all()
else:
if keyword == "":
selectcon = text("select * from user_logs order by id desc ")
recorders = session.query("userid","datetime",
"ip","type","operlevel","description","result").\
from_statement(selectcon).all()
else:
selectcon = text("select * from user_logs where"
" description LIKE :x "
" OR userid LIKE :x"
" OR operlevel LIKE :x"
" OR result LIKE :x"
" OR ip LIKE :x"
" order by id DESC ")
recorders = session.query("userid","datetime",
"ip","type","operlevel","description","result").\
from_statement(selectcon.params(x=keyword)).all()
nums = len(recorders)
return nums
try:
# session.commit()
return recorders
except:
session.rollback()
pass