用pymysql实现了上一章节中的需求

# -*- coding: utf-8 -*-


import pymysql.cursors


def executeSql(sql, params=None, isQuery=False):
    result = []
    conn = pymysql.connect(host="localhost", user="root", password="admin",
                           db="test", port=3306)
    with conn.cursor() as cursor:
        cursor.execute(sql, params)
        if isQuery:
            result = cursor.fetchall()
        conn.commit()
    conn.close()
    return result


# executeSql("CREATE DATABASE test default character set utf8 collate utf8_general_ci;")
executeSql("CREATE TABLE IF NOT EXISTS user(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30), score INT);")


def insert(name, score):
    executeSql("INSERT INTO user(name, score) VALUES (%s, %s);", (name, score))


insert("Adam", 95)
insert("Bart", 62)
insert("Lisa", 78)


def get_score_in(low, high):
    return [value[0] for value in
            executeSql("SELECT name FROM user WHERE score >= %s AND score <= %s ORDER BY score ASC;", (low, high),
                       True)]


# 测试:
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')

与SQLite不同的是，SQL语句中不支持?占位符，这让我找了很长时间的问题。