Python3 programming actual Tetris robot (database operation)

Series entry

"Python3 Programming Actual Tetris Robot"

Design ideas

Record the user's manual play and AI automatic play history and store it in the database for later analysis. In order not to rely on a particular data system, a general database operation interface is designed to facilitate switching between different databases at the application level.

Interface design

class BaseDao(object):
    def select(self, tablename, params={}, fields=None):     # 查询接口，参数：数据表名；查询参数（ORM规则融入字典中，请参看下一篇日志）；返回数据字段
        fields = [] if fields == None else fields            # Python的默认参数行为很是不同，会记录上一次调用的结果，有点象其它语言中的静态变量
        return dbhelper.select(tablename, params, fields)    # 真正的查询实现，dbhelper是针对特定数据库的接口实现

    def insert(self, tablename, params={}, fields=[]):       # 新增接口，CURD函数的参数形式一致，这种设计方便作rest微服务时，由http的不同访问方式直接选择后台操作方法
        if '_id_' in params and len(params) < 2 or '_id_' not in params and len(params) < 1:     # 要求提供_id_，约定_id_为所有表的主键
            return {"code": 301, "err": "The params is error."}
        return dbhelper.insert(tablename, params)

    def update(self, tablename, params={}, fields=[]):
        if '_id_' not in params or len(params) < 2:
            return {"code": 301, "err": "The params is error."}
        return dbhelper.update(tablename, params)

    def delete(self, tablename, params={}, fields=[]):
        if '_id_' not in params:
            return {"code": 301, "err": "The params is error."}
        return dbhelper.delete(tablename, params)

    def querySql(self, sql, values = [], params = {}, fields = []):  # 手写查询接口
        return dbhelper.querySql(sql, values, params, fields)

    def execSql(self, sql, values = []):      # 手写非查询接口
        return dbhelper.exec_sql(sql, values)

    def insertBatch(self, tablename, elements : List):     # 批量写入接口
        return dbhelper.insertBatch(tablename,elements)

    def transGo(elements = [], isAsync = False):    # 事务接口，待实现
        pass

Specific implementation (Sqlit3)

First realized the operation interface of Sqlit3.

Directly face the function of Sqlit3

def exec_sql(sql, values, opType = 0): # opType : 0 - 单条SQL语句； 1 - 批量操作语句；2 - 查询返回数据集；
    try:    # 所有与数据操作都通过这个函数，需要用异常处理封装
        flag = False    # 是否出错标识变量
        error = {}
        if not os.path.exists("./dist"):  # 存储位置目录存在判断
            os.mkdir("dist")
        conn = dbHandle.connect("./dist/log.db")  # 连接数据库或新建
        cur = conn.cursor()
        if opType == 1:          # 批量操作
            num = cur.executemany(sql, values)
        else:                    # 单条语句
            num = cur.execute(sql, values)
        if opType == 2:          # 有结果集返回
            result = cur.fetchall()
        else:
            conn.commit()
        # print('Sql: ', sql, ' Values: ', values)
    except Exception as err:     # 出错
        flag = True
        error = err
        print('Error: ', err)
    finally:
        conn.close()            # 结束处理，并格式化返回结果
        if flag:
            return False, error, num if 'num' in dir() else 0
    return True, result if 'result' in dir() else [], len(result) if opType == 2 else num.rowcount if 'num' in dir() else 0

Query function

Here is an explanation of the main function of the function. For the analysis of integrating ORM into the dictionary, please refer to the next log.

def select(tablename, params={}, fields=None, sql = None, values = None):
    where = ""
    AndJoinStr = ' and '
    reserveKeys = {}
    for rk in ["sort", "search", "page", "size", "sum", "count", "group"]:
        # 提取保留关键字
    for k, v in params.items():
        whereExtra = ""
        if k == "ins":
            # 保留关键字ins,lks,ors处理
        else:
            flag = False
            if type(v) == "str":
                # 不等查询操作处理
            elif reserveKeys.get('search'):
                # 精确查询与模糊查询处理
            else:
                whereExtra += k + " =? "
                values.append(v)
        where += whereExtra
    # 排序、统计、分组和分页等操作处理
    rs = exec_sql(sql, values, 2)
    return {"code": 200, "rows": rs[1], "total": rs[2]}

Insert function

Delete and update are similar to insert, here is an explanation of the insert function

def insert(tablename, params={}):
    sql = "insert into %s ( " % tablename     # 主干
    ks = params.keys()
    vs = []
    ps = ""
    for al in ks:           # 解析参数
        sql += al + ","     # 按插入语句拼接每一个参数
        ps += "?,"          # python的sqlit3封装没法送入list对象来实现元组数据的写入，只能拆开
        vs.append(params[al])
    sql = sql[:-1] + ") values (" + ps[:-1] + ")"  # 去掉最后的逗号，并完成sql语句
    rs = exec_sql(sql, vs)                         # 执行，vs参数中不能嵌套list，比起C++版本的实现，这里有些别扭
    if rs[0]:                                      # 返回结果
        return {"code": 200, "info": "create success.", "total": rs[2]}
    else:
        return {"code": 701, "error": rs[1].args[0], "total": rs[2]}

Bulk insert

Facts have proved that the efficiency of inserting one by one is too low, and data writing cannot keep up with the rhythm when AI is running.

def insertBatch(tablename, elements : List):
    if len(elements) == 0:    # 无输入元素，直接退出
        return {"code": 201, "info": "There is no elements exist.", "total": 0}
    elif len(elements) == 1:  # 只有一个元素，调用insert实现来完成
        return insert(tablename, elements[0])

    sql = "insert into %s ( " % tablename
    isFirst = True      # 在循环的第一次，要处理操作字段
    vs = []
    ps = ""
    for ele in elements:
        if isFirst:
            isFirst = False
            ks = ele.keys()
            for al in ks:   # 操作字段，只需处理一次
                sql += al + ","
                ps += "?,"  # 参数批占位符
        items = []
        for bl in ks:  # 按key的顺序逐个添加写入参数值，字典的访问顺序是不一定的
            items.append(ele[bl])
        vs.append(items)
    sql = sql[:-1] + ") values (" + ps[:-1] + ")"  # 最后的拼接
    rs = exec_sql(sql, vs, 1)    # 执行
    if rs[0]:                    # 返回结果
        return {"code": 200, "info": "create success.", "total": rs[2]}
    else:
        return {"code": 701, "error": rs[1].args[0], "total": rs[2]}

Content preview

The next log explains the design and usage of ORM rules integrated into the dictionary. With this set of rules, there is no need to write SQL statements. Please continue to pay attention to what is going on, thank you!

project address

https://gitee.com/zhoutk/ptetris
或
https://github.com/zhoutk/ptetris

Operation method

1. install python3, git
2. git clone https://gitee.com/zhoutk/ptetris (or download and unzip source code)
3. cd ptetris
4. python3 tetris

This project surpport windows, linux, macOs

on linux, you must install tkinter first, use this command:  
sudo apt install python3-tk

Related items

C++ version has been implemented, project address:

https://gitee.com/zhoutk/qtetris