Python实现mysql命令行

一、源码

import os
import pymysql


def sql_shell():
    password = input("Enter Password: ")
    # 访问密码
    if password.strip() != "yyds":
        print("Bye")
        return
    # 清空控制台输出
    os.system("cls" if os.name == "nt" else "clear")
    try:
        # 连接到MySQL数据库
        conn = pymysql.connect(
        host="127.0.0.1",
        port=3306,
        user="root",
        password="123456",
        database="test",
        )

        # 创建游标对象
        cursor = conn.cursor()
        mysql_logo = """
         ____    ____  ____  ____   ______     ___      _____     
        |_   \  /   _||_  _||_  _|.' ____ \  .'   `.   |_   _|    
          |   \/   |    \ \  / /  | (___ \_|/  .-.  \    | |      
          | |\  /| |     \ \/ /    _.____`. | |   | |    | |   _  
         _| |_\/_| |_    _|  |_   | \____) |\  `-'  \_  _| |__/ | 
        |_____||_____|  |______|   \______.' `.___.\__||________| 
                                                                
        """
        print(mysql_logo)
        print("Welcome to the MySQL shell.  Commands end with ';' 输入quit退出.")
        # 第一次提示用户输入SQL命令
        sql_input = input("mysql> ")
        while sql_input.strip() != "quit":
            while not sql_input.endswith(";"):
                sql_input += " " + input()  # 继续输入
            # 去除分号和额外空格
            sql_query = sql_input.rstrip("; ")
            try:
                # 执行SQL查询
                cursor.execute(sql_query)
                result = cursor.fetchall()
                # 打印查询结果
                if result:
                    column_names = [desc[0] for desc in cursor.description]
                    # 计算每列的最大宽度
                    column_widths = [
                        max(
                            custom_length(column),
                            max(custom_length(str(row[i])) for row in result),
                        )
                        for i, column in enumerate(column_names)
                    ]
                    # 打印列名
                    header = " | ".join(
                        column.ljust(width + 3)
                        for column, width in zip(column_names, column_widths)
                    )
                    print(header)
                    print(
                        "-" * sum(column_widths + [len(column_widths) * 3])
                    )  # 3 为每列之间的分隔符 " | " 的宽度
                    # 打印查询结果
                    for row in result:
                        # 使用自定义的填充函数确保按字符个数填充
                        row_str = " | ".join(
                            custom_ljust(str(value), width + 3)
                            for value, width in zip(row, column_widths)
                        )
                        print(row_str)
                print(f"\n{cursor.rowcount} rows in set\n")
            except Exception as e:
                print("发生错误:", str(e))
            # 继续提示输入
            sql_input = input("mysql>")

    except Exception as e:
        print("发生连接错误:", str(e))

    finally:
        # 关闭游标和连接
        cursor.close()
        conn.close()
    print("Bye")

def custom_length(s):
    length = 0
    for char in s:
        if ord(char) < 128:
            length += 1
        else:
            length += 2
    return length

def custom_ljust(s, width):
    spaces_to_fill = width - custom_length(s)
    return s + " " * spaces_to_fill

# 示例用法
if __name__=='__main__':
    sql_shell()

二、运行效果