def run(request, param):
state, dbinfo = tools.getDBConf("DB")
if not state:
return tools.response(-1, dbinfo)
db = tools.database(**dbinfo)
db.printing = True
# 执行方式
settings.LOCK.acquire()
try:
# ############################ 结构化操作数据库 ############################ #
# 结构化查询select
entries1 = db.select("test",
what="*",
where="a='hehe'",
order="d desc",
limit=3)
# 结构化单行插入insert
entries2 = db.insert("test", a="sigal", b="n", c=0, d=10.1)
# 结构化多行插入multiple_insert
db.supports_multiple_insert = True
values = [{
"a": "muti1",
"b": "p",
"c": 6,
"d": 11.1
}, {
"a": "muti2",
"b": "q",
"c": 6,
"d": 11.2
}, {
"a": "muti3",
"b": "r",
"c": 6,
"d": 11.3
}]
entries3 = db.multiple_insert("test", values=values)
# 结构化更新update
entries4 = db.update("test", where="a='fad'", b='mn', c=2)
# 结构化删除delete
entries5 = db.delete("test", where="a='ferry'")
# ############################ 非结构化操作数据库, 可以执行复杂操作, 查询语句返回storage结果集, 其他返回影响行数 ############################ #
sql = """select m.a, m.b, m.c, m.d, n.f, n.g from test m, test2 n where m.a = n.e"""
entries6 = db.exec(sql)
except Exception as e:
print('执行sql failed! [%s]' % str(e))
settings.LOCK.release()
raise
settings.LOCK.release()
# 结果打包成json
jsonData = tools.storage2Json(entries1)
return tools.response(0, '', jsonData)
def run(request, param):
state, dbinfo = tools.getDBConf("DB")
if not state:
return tools.response(-1, dbinfo)
db = tools.database(**dbinfo)
db.printing = True
# 执行方式
settings.LOCK.acquire()
try:
# ############################ 存储过程 ############################ #
# 返回固定个数值
parmout = [tools.INT, tools.FLOAT]
results = db.callproc("select_test1", ["fad"], parmout)
print("parmout: ", parmout)
# 返回结果集, 可通过parmout[i].fetchall()获取
parmout = [tools.CURSOR]
results = db.callproc("select_test2", ["fad"], parmout)
print("results: ", parmout)
except Exception as e:
print('执行sql failed! [%s]' % str(e))
settings.LOCK.release()
raise
settings.LOCK.release()
print("return: ", results)
# storage结果打包成json
jsonData = tools.storage2Json(None)
return tools.response(0, '', jsonData)
def run(request, param):
state, dbinfo = tools.getDBConf("DB")
if not state:
return tools.response(-1, dbinfo)
db = tools.database(**dbinfo)
db.printing = True
# 执行方式
settings.LOCK.acquire()
try:
cursor = db._db_cursor()
# ############################ 存储过程 ############################ #
declare = "declare @p1 INT declare @p2 DECIMAL(10,2) declare @ret INT"
exec = "exec @ret = select_test2 'fad',@p1 output,@p2 output"
select = "select @p1,@p2,@ret"
cursor.execute(f"%s %s %s" % (declare, exec, select))
result = cursor.fetchall() # 得到结果集
for i in result:
print(i)
while cursor.nextset():
result = cursor.fetchall()
for i in result:
print(i)
except Exception as e:
print('执行sql failed! [%s]' % str(e))
settings.LOCK.release()
raise
settings.LOCK.release()
# storage结果打包成json
jsonData = tools.storage2Json(None)
return tools.response(0, '', jsonData)
def run(request, param):
state, dbinfo = tools.getDBConf("DB")
if not state:
return tools.response(-1, dbinfo)
db = tools.database(**dbinfo)
db.printing = True
# 执行方式
settings.LOCK.acquire()
try:
# ############################ 结构化操作数据库 ############################ #
# 结构化查询select
entries1 = db.select("test", what="a,c,d", where="a='fad'", order="d desc", limit=2)
# 结构化单行插入insert
entries2 = db.insert("test", a="sigal", b="n", c=0, d=10.1)
# print("entries2: ", entries2)
# 结构化多行插入multiple_insert
db.supports_multiple_insert = True
values = [{"A": "muti1", "B": "p", "C": 6, "D": 11.1}, {"A": "muti2", "B": "q", "C": 6, "D": 11.2},
{"A": "muti3", "B": "r", "C": 6, "D": 11.3}]
entries3 = db.multiple_insert("test", values=values)
# 结构化更新update
entries4 = db.update("test", where="A='fad'", B='mn', C=2)
# 结构化删除delete
entries5 = db.delete("test", where="A='ferry'")
# ############################ 非结构化操作数据库, 可以执行复杂操作, 查询语句返回storage结果集, 其他返回影响行数 ############################ #
sql = """select m.A, m.B, m.C, m.D, n.F, n.G from test m, test1 n where m.A = n.E """
sql = """INSERT INTO test(a, b, c, d) VALUES ('abc', 'n', 0, 10.1)"""
entries6 = db.exec(sql)
except Exception as e:
print('执行sql failed! [%s]' % str(e))
settings.LOCK.release()
raise
settings.LOCK.release()
# 结果打包成json
jsonData = tools.storage2Json(entries1)
return tools.response(0, '', jsonData)