import os, sqlite3

db_file = os.path.join(os.path.dirname(__file__), 'test.db')
if os.path.isfile(db_file):
    os.remove(db_file)

# 初始数据:
conn = sqlite3.connect(db_file)
cursor = conn.cursor()
cursor.execute('create table user(id varchar(20) primary key, name varchar(20), score int)')

for t in [('A-001', 'Adam', 95),
    ('A-002', 'Bart', 62),
    ('A-003', 'Lisa', 78)]:
    cursor.execute('insert into user values (?,?,?)', t)

cursor.close()
conn.commit()
conn.close()

def get_score_in(low, high):
    '''返回指定分数区间的名字，按分数从低到高排序 '''
    try:
        # 初始数据:
        conn = sqlite3.connect(db_file)
        cursor = conn.cursor()
        cursor.execute('select name from user where score between ? and ? order by score',(low, high))
        # 获得查询结果集:
        values = cursor.fetchall()

        Li = []
        for v in values:
            Li.append(v[0])
        print('返回数组：', Li)
    except Exception as e:
        print('查询错误：', e)
    finally:
        cursor.close()
        conn.close()

    return Li


# 测试:
assert get_score_in(80, 95) == ['Adam'], get_score_in(80, 95)
assert get_score_in(60, 80) == ['Bart', 'Lisa'], get_score_in(60, 80)
assert get_score_in(60, 100) == ['Bart', 'Lisa', 'Adam'], get_score_in(60, 100)

print('Pass')