pymysql简单模拟mysql终端

整体代码

#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""pymysql模拟MySQL命令行

use db;
show tables
show databases;
select;
等一些操作实验

并未实现增删改查等操作
"""

import pymysql
import tabulate

conn = pymysql.connect(
    user='root',
    password='',
    host='localhost',
    port=3306,
    charset='utf8mb4',
)

cursors = conn.cursor(cursor=pymysql.cursors.DictCursor)


while 1:
    sql = input('>>> ').strip()  # 输入sql
    if sql == 'exit':
        break
    try:
        rows = cursors.execute(query=sql)  # 执行sql
    except pymysql.err.ProgrammingError as syntax_error:
        print(f'\033[1;31m{syntax_error}\033[0m')
        conn.rollback()
        continue
    except pymysql.err.InternalError as op_error:
        print(f'\033[1;31m{op_error}\033[0m')
        conn.rollback()
        continue
    if rows is 0:  # use db; 这类操作没有返回值那么就是0
        if sql.split()[0].upper() == 'USE':
            print('Database changed')
        else:
            print(f'Query OK，{rows} affected (0.02sec)')
        continue
    ls = list()
    title_flag = False  # 用于标志标题只加入一次
    while 1:
        res = cursors.fetchone()
        if not title_flag:
            title_flag = True
            ls.append(res.keys())
        if res is None:  # 值为None，退出
            break
        ls.append(res.values())  # 加入
    print(tabulate.tabulate(ls, headers='firstrow'))  # 打印
cursors.close()
conn.close()