python 交互式命令行数据库连接助手

目前支持mysql 、mssql 、redis

完整代码已上传至github https://github.com/b4zinga/PythonTools/blob/master/dbcli.py

0. 操作示例

python 连接mysql:

连接mysql

python连接mssql:

连接mssql

python连接redis

连接redis

帮助

image

1. python 连接mssql

    def conn(self):
        db = pymssql.connect(server=self.host, port=int(self.port), user=self.user, password=self.passwd)
        return db

2. python 连接mysql

    def conn(self):
        db = pymysql.connect(host=self.host, port=int(self.port), user=self.user, password=self.passwd, charset='utf8')
        return db

3. python 连接redis

def conn(self):
        sock = socket.socket()
        try:
            sock.connect((self.host, int(self.port)))
        except Exception as err:
            print(err)
            sys.exit(0)
        return sock

    def exec(self, sql):
        if sql=='':
            return
        sql = self.makeCmd(sql)
        try:
            self.db.send(sql.encode())
            while True:
                recv = self.db.recv(1024)
                print(self.handleRecv(recv))
                if len(recv)<1024:  # 循环接收1024, 如果长度小于1024则默认后面已经无内容,break
                    break
        except Exception as err:
            print(err)

    @staticmethod
    def makeCmd(cmd):
        command = "*"
        cmd = cmd.split()
        command = command + str(len(cmd)) + '\r\n'
        for c in cmd:
            command = command + '$' + str(len(c)) + '\r\n' + c + '\r\n'
        return command

    @staticmethod
    def handleRecv(recvdate):
        recvdate = recvdate.decode()
        if recvdate.startswith('*'):
            recvdate=recvdate[2:].strip('\r\n')
        recvdate = re.sub('\$\d+\\r\\n', '', recvdate)
        return recvdate

n. Tips

1. mssql命令

    查询所有数据库:  SELECT Name FROM Master..SysDatabases ORDER BY Name
    查询某数据库内所有表   : SELECT Name FROM DatabaseName..SysObjects Where XType='U' ORDER BY Name 
          XType='U'   : 表示所有用户表; 
          XType='S'   : 表示所有系统表;