def mk_query(i, parameters, prt_gen_all=0):
"""从模板和参数表生成query，并输出结果
第一步：
定义所有的参数
第二步：
记录所有可能用到的模板
第三部：
执行查询，并按照三种模式中的一种，输出数据
"""
table_name, device_id, DATE, DATE1, DATE2 = (parameters["table_name"],
parameters["device_id"],
parameters["DATE"],
parameters["DATE1"],
parameters["DATE2"])
# 根据device_id获取全部信息
query0 = \
"""
SELECT * FROM %s WHERE device_id = '%s'
""" % (table_name, device_id)
# 查看所有的device_id
query1 = \
"""
SELECT DISTINCT device_id FROM %s
""" % table_name
# 根据device_id查看某一天的power总和
query2 = \
"""
SELECT SUM(power) FROM
%s WHERE device_id = '%s' AND
datetime_interval >= '%s 00:00:00' AND
datetime_interval <= '%s 23:59:59'
""" % (table_name, device_id, DATE, DATE)
# 根据device_id查看某个时间区间的power总和
query3 = \
"""
SELECT SUM(power) FROM
%s WHERE device_id = '%s' AND
datetime_interval >= '%s 00:00:00' AND
datetime_interval <= '%s 23:59:59'
""" % (table_name, device_id, DATE1, DATE2)
c.execute(eval("query%s" % i))
if prt_gen_all == 0: # 打印ascii二维表
print(from_db_cursor(c))
elif prt_gen_all == 1: # 生成器模式生成行
return iterC(c)
elif prt_gen_all == 2: # 返回整个二维表数据
return c.fetchall()
else:
prt_all(c)
def mk_query(i, parameters, prt_gen_all = 0):
"""从模板和参数表生成query，并输出结果
第一步：
定义所有的参数
第二步：
记录所有可能用到的模板
第三部：
执行查询，并按照三种模式中的一种，输出数据
"""
table_name, device_id, DATE, DATE1, DATE2 = (parameters["table_name"],
parameters["device_id"],
parameters["DATE"],
parameters["DATE1"],
parameters["DATE2"])
# 根据device_id获取全部信息
query0 = \
"""
SELECT * FROM %s WHERE device_id = '%s'
""" % (table_name, device_id)
# 查看所有的device_id
query1 = \
"""
SELECT DISTINCT device_id FROM %s
""" % table_name
# 根据device_id查看某一天的power总和
query2 = \
"""
SELECT SUM(power) FROM
%s WHERE device_id = '%s' AND
datetime_interval >= '%s 00:00:00' AND
datetime_interval <= '%s 23:59:59'
""" % (table_name, device_id, DATE, DATE)
# 根据device_id查看某个时间区间的power总和
query3 = \
"""
SELECT SUM(power) FROM
%s WHERE device_id = '%s' AND
datetime_interval >= '%s 00:00:00' AND
datetime_interval <= '%s 23:59:59'
""" % (table_name, device_id, DATE1, DATE2)
c.execute(eval("query%s" % i))
if prt_gen_all == 0: # 打印ascii二维表
print(from_db_cursor(c))
elif prt_gen_all == 1: # 生成器模式生成行
return iterC(c)
elif prt_gen_all == 2: # 返回整个二维表数据
return c.fetchall()
else:
prt_all(c)
def unit_test2():
"""测试stable_insertmany的功能
"""
conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute("CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL, number INTEGER)")
records = [(1, 10), (3, 10), (5, 10)] # insert some records at begin
c.executemany("INSERT INTO test VALUES (?, ?)", records)
records = [(2, 10), (3, 10), (4, 10)]
stable_insertmany(conn, c, "INSERT INTO test VALUES (?, ?)", records)
c.execute("SELECT * FROM test")
prt_all(c)
def unit_test2():
"""测试stable_insertmany的功能
"""
conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute(
"CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL, number INTEGER)")
records = [(1, 10), (3, 10), (5, 10)] # insert some records at begin
c.executemany("INSERT INTO test VALUES (?, ?)", records)
records = [(2, 10), (3, 10), (4, 10)]
stable_insertmany(conn, c, "INSERT INTO test VALUES (?, ?)", records)
c.execute("SELECT * FROM test")
prt_all(c)
