def setUp(self):
self.students_list = (
(1, u"迟宏泽", u"男", 1),
(2, "Jack", u"男", 2),
(3, u"韩梅梅", u"女", 3),
(5, u"李雷", u"男", 3)
)
self.students_table = ListTable(
"students",
(
Title("id", u"编号"),
Title("name", u"姓名"),
Title("gender", u"性别"),
Title("grade", u"年级")
),
self.students_list
)
def even_row(row_index, column_index):
return row_index != 0 and row_index % 2 == 0
self.even_row_style = CellStyle(even_row, {"font_color": "red"})
self.top_three_style = CellStyle(
((1, 3), None), {"bg_color": "yellow"})
self.tmp_path = []
def setUp(self):
self.table = ListTable(
"student",
(Title("id", u"编号"), Title("name", u"姓名"), Title("grade", u"年级")),
((1, u"小王", 1), (2, u"小李", 1), (3, u"小明", 2)))
def data_row_style(row_index, row):
if row.grade == 1:
return "style='color:red;'"
def data_cell_style(row_index, column_index, field_name, value):
if field_name == "id":
return "style='color:orange;'"
def title_row_style(table):
return "style='background-color:gray;'"
def title_cell_style(column_index, title):
if title.name == "name":
return "style='text-decoration:underline;'"
self.properties = {
"table": "border=1",
"title-row": title_row_style,
"title-cell": title_cell_style,
"data-row": data_row_style,
"data-cell": data_cell_style,
}
def test_read_line_with_file(self):
ctx = {}
# 测试一般执行
result = JSONR("line_file.json", "line")(ctx)
self.assertEquals(len(result), 3)
self.assertEquals(result[0], {"id": 1, "name": "Sam", "grade": "A"})
# 测试record_handler
JSONR("line_file.json",
"line",
record_handler=lambda row: {row.pop("name"): row},
variable="test")(ctx)
test_result = ctx["test"]
self.assertEquals(test_result[0], {"Sam": {"id": 1, "grade": "A"}})
# 测试result_wrapper
table = JSONR("line_file.json",
"line",
result_wrapper=TableWrapper(
"students", (Title("id", u"编号"), Title(
"name", u"姓名"), Title("grade", u"等级"))))(ctx)
print "\n", table
row_0 = table[0]
self.assertEquals((row_0.id, row_0.name, row_0.grade), (1, "Sam", "A"))
def test_read_json_array(self):
ctx = {}
# 测试一般执行
result = JSONR("array_file.json", "array")(ctx)
self.assertEquals(len(result), 3)
self.assertEquals(result[0], {"id": 1, "name": "Jack", "grade": "A"})
# URL加载执行
result = JSONR("http://test.gf/array", "array")(ctx)
self.assertEquals(len(result), 3)
self.assertEquals(result[0], {"id": 1, "name": "Jack", "grade": "A"})
# 测试record_handler
result = JSONR("http://test.gf/array",
"array",
record_handler=lambda r: Student(**r))(ctx)
std = result[0]
self.assertEquals((std.id, std.name, std.grade), (1, "Jack", "A"))
table = JSONR("http://test.gf/array",
"array",
record_handler=lambda r: Student(**r),
result_wrapper=TableWrapper(
"student",
(Title("id"), Title("name"), Title("grade"))))(ctx)
print "\n", table
row_0 = table[0]
self.assertEquals((row_0.id, row_0.name, row_0.grade),
(1, "Jack", "A"))
def test_print(self):
table = ListTable(
"test",
(Title("id", u"编号"), Title("name", u"姓名"), Title("grade", u"年级")),
((1, "Sam", "A"), (2, "Tom", "A"), (3, "Jack", "B"),
(4, "James", "C")))
pp = PrintTablePlugin()
pp.execute(None, table, table, table)
def setUp(self):
titles = (Title("id", u"编号"), Title("name",
u"姓名"), Title("grade", u"年级"))
self.table = ObjectTable("student_table", titles)
students = (Student(1, "Sam", 1), Student(2, "Jack",
1), Student(3, "Peter", 2))
self.students = students
self.wrapped_table = ObjectTable("student_table", titles, students)
def setUp(self):
titles = (
Title("id"),
Title("name"),
Title("age"),
Title("grade"),
)
self.table = ListTable("test_table", titles)
self.data = [(1, "Sam", 26, "A"), (2, "Jack", 31, "B"),
(3, "James", 32, "C")]
self.wrapped_table = ListTable("test_table", titles, self.data)
def setUp(self):
self.students_table = ListTable(
"students",
(Title("id", u"编号"), Title("name", u"姓名"), Title("grade", u"年级")),
(
(1, "Sam", 1),
(2, "Jack", 1),
(3, "James", 1),
(4, "Betty", 2),
(5, "Tom", 2),
(6, "Lucy", 2),
(7, "Jason", 3),
))
def test_transform(self):
transformer = TableColumn2TitlePlugin()
transformer.execute(self.ctx,
"students_table",
"changed_table",
"month",
"score",
ZhMonthGenerator,
sum_title=Title("sum", u"总分"),
avg_title=Title("avg", u"平均分"))
print "\nBefore change:"
students_table = self.ctx["students_table"]
print "\n"
print students_table
changed_table = self.ctx["changed_table"]
print "\nAfter change:"
print "\n"
print changed_table
def setUp(self):
self.ctx = {}
titles = (Title("id", u"编号"), Title("name", u"姓名"),
Title("grade", u"年级"), Title("month",
u"月"), Title("score", u"得分"))
students = ((1, "Sam", 1), (2, "James", 1), (3, "Joe", 2), (4, "Jack",
2))
data = []
for i in xrange(0, len(students)):
student = students[i]
for m in xrange(1, 13):
score = random.randint(0, 100)
row = []
row.extend(student)
row.extend([m, score])
data.append(row)
random.shuffle(data)
self.ctx["students_table"] = ListTable("students", titles, data)
def setUp(self):
titles = (Title("id", u"编号"), Title("name",
u"姓名"), Title("grade", u"年级"))
self.table = DictTable("student_table", titles)
students = ({
"id": 1,
"name": "Sam",
"grade": 1
}, {
"id": 2,
"name": "Jack",
"grade": 1
}, {
"id": 3,
"name": "Peter",
"grade": 2
})
self.students = students
self.wrapped_table = DictTable("student_table", titles, students)
def setUp(self):
self.students_table = ListTable(
"students",
(Title("id", u"编号"), Title("name", u"姓名"), Title("grade", u"年级"),
Title("city", u"城市"), Title("class", u"班级")), (
(1, "Sam", 1, 1, 2),
(2, "Jack", 1, 2, 3),
(3, "Lucy", 2, 4, 1),
(4, "James", 2, 3, 2),
(5, "Larry", 2, 10, 3),
(6, "Betty", 2, 10, 5),
))
self.city_table = ListTable("city",
(Title("id", u"编号"), Title("name", u"名称")),
((1, "Pyongyang"), (2, "Beijing"),
(3, "Shanghai"), (4, "Guangzhou"),
(5, "Jinan")))
self.class_scores = ListTable("class_score", (Title(
"grade", u"年级"), Title("class", u"班级"), Title("score", u"分数")),
((1, 1, 10), (1, 2, 20), (1, 3, 30),
(2, 1, 40), (2, 2, 50), (2, 3, 60)))
def test_inner_join(self):
join_table = JoinTablePlugin()
ctx = {}
result = join_table.execute(ctx,
"inner",
self.students_table,
self.city_table,
on="city = id",
fields=("l.id", "l.name", "r.name"),
name=u"学生所在地",
titles=(Title("id",
u"编号"), Title("name", u"姓名"),
Title("city", u"城市")))
self.assertEquals(len(result), 4)
row_0 = result[0]
self.assertEquals(
(row_0.id, row_0.name, row_0.city),
(1, "Sam", "Pyongyang"),
)
print "\n"
print result
# mutli on conditions
result = join_table.execute(ctx,
"inner",
self.students_table,
self.class_scores,
on="grade=grade,class=class",
fields=("l.id", "l.name", "r.grade",
"r.class", "r.score"),
name=u"班级得分",
titles=(Title("id",
u"编号"), Title("name", u"姓名"),
Title("grade", u"年级"),
Title("clazz", u"班级"),
Title("score", u"评分")))
self.assertEquals(len(result), 5)
row_0 = result[0]
self.assertEquals(
(row_0.id, row_0.name, row_0.grade, row_0.clazz, row_0.score),
(1, "Sam", 1, 2, 20))
print "\n"
print result
def test_left_join(self):
join_table = JoinTablePlugin()
ctx = {}
result = join_table.execute(ctx,
"left",
self.students_table,
self.city_table,
on="city=id",
fields=("l.id", "l.name", "r.name"),
name=u"学生所在地",
titles=(Title("id",
u"编号"), Title("name", u"姓名"),
Title("city", u"城市")))
self.assertEquals(len(result), 6)
last_row = result[-1]
self.assertEquals((last_row.id, last_row.name, last_row.city),
(6, "Betty", None))
print "\n"
print result
result = join_table.execute(ctx,
"left",
self.students_table,
self.class_scores,
on="grade=grade,class=class",
fields=("l.id", "l.name", "l.grade",
"l.class", "r.score"),
name=u"班级得分",
titles=(Title("id",
u"编号"), Title("name", u"姓名"),
Title("grade", u"年级"),
Title("clazz", u"班级"),
Title("score", u"分数")))
self.assertEquals(len(result), 6)
last_row = result[-1]
self.assertEquals((last_row.id, last_row.name, last_row.grade,
last_row.clazz, last_row.score),
(6, "Betty", 2, 5, None))
print "\n"
print result
def execute(self,
context,
way,
left,
right,
on,
fields,
name,
titles=None,
variable=None):
"""
:param context 上下文对象
:param way join方式，允许inner、left和right三种join方式
:param on join条件，left_column = right_column，多个条件用逗号隔开
:param fields 结果字段
:param name 结果表格名称
:param titles 结果表格标题
:param variable 用于存储的上下文变量
"""
if way not in ("inner", "left", "right"):
raise InvalidArgumentException(
u"不合法的join方式'{}'，只支持inner、left、right三种join方式".format(way))
conditions = self._parse_conditions(on)
if isinstance(left, types.StringTypes):
left = context[left]
if isinstance(right, types.StringTypes):
right = context[right]
if way == "inner":
result = self._inner_join(left, right, fields, conditions)
elif way == "left":
result = self._side_join("left", left, right, fields, conditions)
elif way == "right":
result = self._side_join("right", right, left, fields, conditions)
if titles is None:
titles = tuple(Title(f.split(".")[1]) for f in fields)
table = ListTable(name, titles, result)
if variable:
context[variable] = table
else:
return table
def setUp(self):
self.students = (
(1, "Sam", 1),
(2, "Jack", 2),
(3, "James", 2),
(4, "Peter", 1)
)
self.students_table = ListTable(
"students",
(
Title("id", u"编号"),
Title("name", u"姓名"),
Title("grade", u"年级")
),
self.students
)
self.scores = (
(1, 100, 98, 88),
(2, 95, 99, 99),
(3, 96, 97, 89),
(4, 98, 97, 99)
)
self.score_table = ListTable(
"students_score",
(
Title("id", u"编号"),
Title("c", u"C语言"),
Title("java", u"Java程序设计"),
Title("ds", u"数据结构")
),
self.scores
)
excel_writer = ExcelWriterPlugin()
self.path = excel_writer.execute({}, None, sheets=(
SheetW(self.students_table),
SheetW(self.score_table)
))
def test_query(self):
ctx = {}
# 执行纯SQL语句的情景
q = Query("test", "test_result", Student,
"select * from student order by id")
q(ctx)
self._check_students_list(ctx["test_result"])
# 进行auto map的情况
q = Query("test", "test_result", "student",
"select * from student order by id")
q(ctx)
self._check_students_list(ctx["test_result"])
# 多个映射类的情况
q = Query("test", "test_result",
(Student.id, Student.name, Grade.name),
("select s.id, s.name, g.name from student s, grade g "
"where s.grade = g.id order by s.id"))
q(ctx)
self.assertTrue(ctx["test_result"][0], (1, "Sam", "Grade One"))
# auto map field 的情况
q = Query("test", "test_result",
(Student.id, "student.name", "grade.name"),
("select s.id, s.name, g.name from student s, grade g "
"where s.grade = g.id order by s.id"))
q(ctx)
self.assertTrue(ctx["test_result"][0], (1, "Sam", "Grade One"))
# 查询字符串表达式
q = Query("test",
"test_result",
Student,
"id > :id and name = :name",
params={
"id": 1,
"name": "Betty"
})
q(ctx)
students = ctx["test_result"]
self.assertEquals(len(students), 1)
s = students[0]
self.assertEquals((s.id, s.name, s.grade), (3, "Betty", 1))
# order by
q = Query("test",
"test_result",
Student,
"id > :id",
order_by="id desc",
params={"id": 1})
q(ctx)
self.assertEquals([std.id for std in ctx["test_result"]], [3, 2])
# group by and count
q = Query("test",
"test_result", ("student.grade", func.count('*')),
order_by="grade desc",
group_by="student.grade")
q(ctx)
grade_counts = ctx["test_result"]
self.assertEquals(len(grade_counts), 2)
self.assertEquals(grade_counts[0], (2, 1))
# 条件查询
q = Query("test", "test_result", Student,
and_(Student.grade == 1, Student.name == "Betty"))
q(ctx)
records = ctx["test_result"]
self.assertEquals(len(records), 1)
# 通过自定义函数来构建查询条件
def my_query(session, ctx, student, grade):
query = session.query(student.id,
grade.name).filter(student.grade == grade.id)
return query.all()
# 只返回查询对象
q_a = Query(
"test", "test_result",
("student", "grade"), lambda s, ctx, std, grade: s.query(
std.id, grade.name).filter(std.grade == grade.id))
# 通过all()返回查询结果
q_b = Query(
"test", "test_result",
("student", "grade"), lambda s, ctx, std, grade: s.query(
std.id, grade.name).filter(std.grade == grade.id).all())
for q in (q_a, q_b):
q(ctx)
records = ctx["test_result"]
self.assertEquals(len(records), 3)
self.assertEquals(records[0], (1, "Grade One"))
class StudentGrade(object):
def __init__(self, row):
student, grade = row
self.id = student.id
self.name = student.name
self.grade = grade.name
# row_handler
q = Query("test",
"test_result", (Student, Grade),
Student.grade == Grade.id,
row_handler=StudentGrade,
order_by=Student.id)
q(ctx)
records = ctx["test_result"]
self.assertEquals(len(records), 3)
self.assertEquals((records[0].id, records[0].name, records[0].grade),
(1, "Sam", "Grade One"))
# Table Wrapper
q = Query("test",
"test_table", (Student.id, Student.name, Student.grade),
result_wrapper=TableWrapper(
"student", (Title("id", u"编号"), Title(
"name", u"姓名"), Title("grade", u"年级"))))
q(ctx)
test_table = ctx["test_table"]
self.assertEquals(test_table.cell(0, 0), 1)
self.assertEquals(tuple(test_table.row(0)), (1, u"Sam", 1))
# Pure SQL Query
s = SQL("test", "sql_1", "select * from student order by id")
s(ctx)
records = ctx["sql_1"]
self.assertEquals(records[0], (1, "Sam", "SamChi", 1))
def my_row_handler(row):
return Student(id=row[0],
name=row[1],
fullname=row[2],
grade=row[3])
s = SQL("test",
"sql_2",
"select * from student where id > :id", {"id": 1},
row_handler=my_row_handler)
s(ctx)
records = ctx["sql_2"]
self.assertEquals((records[0].id, records[0].name), (2, "Jack"))
# Table Wrapper
s = SQL("test",
"sql_3",
"select * from student order by id",
result_wrapper=TableWrapper(
"students",
(Title("id", u"编号"), Title("name", u"名字"),
Title("fullname", u"全名"), Title("grade", u"年级"))))
s(ctx)
table = ctx["sql_3"]
row = table[0]
self.assertEquals((row.id, row.name), (1, "Sam"))
# 测试非查询语句
s = SQL("test", None,
("insert into student (id, name, fullname, grade) "
"values (:id, :name, :fullname, :grade)"), {
"id": 4,
"name": "Jane",
"fullname": "JaneGreen",
"grade": 3
})
s(ctx)
SQL("test", "sql_4", "select * from student where id = 4")(ctx)
self.assertEquals(ctx["sql_4"][0], (4, "Jane", "JaneGreen", 3))
# Test Plugin
plugin_manager.sys_prepare(self.config)
orm_query = plugin_manager.plugin("orm_query")
orm_query.execute(
ctx, Query("test", "result_1", (Student.id, Student.name),
"id = 1"),
Query("test", "result_2", (Student.id, Student.name), "id = 2"))
result_1, result_2 = ctx["result_1"], ctx["result_2"]
self.assertEquals([(1, "Sam")], result_1)
self.assertEquals([(2, "Jack")], result_2)
def ZhMonthGenerator(month):
return Title("month_" + str(month), ZH_MONTHS[month - 1] + u"月")