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)