ShouQianPingTai/dbsearch.py

#!/usr/bin/python3
# dbsearch.py
#

import pymysql
from properties import Properties
import sys, getopt

class DbSearch:
    # 本类用于提供各类数据库信息搜索服务
    def __init__(self, connect):
        self.connect = connect

    def GetTableList(self, database):
        # 查询某个库的数据表的列表
        cursorTable = self.connect.cursor()
        cursorTable.execute("SELECT table_name FROM INFORMATION_SCHEMA.TABLES where table_schema = '" + database + "'");

        return cursorTable.fetchall()

    def GetColumnList(self, tableName):
        # 查询某张表的数据字段列表
        cursorColumn = self.connect.cursor()
        cursorColumn.execute("SELECT column_name,data_type FROM INFORMATION_SCHEMA.COLUMNS where table_schema='" + database + "' AND table_name='" + 
            tableName + "'");
        return cursorColumn.fetchall()

    def SearchTableByColumnName(self, columnName, database):
        # 查询包含包含searchText的库表
        tableList = self.GetTableList(database)
        findList = list()
        for table in tableList:
            columnList = self.GetColumnList(table[0])
            for column in columnList:
                if column[0].find(columnName) != -1:
                    findList.append(table[0])

        return findList

    def SearchTableByText(self, searchText, database):
        # 查找包含searchText字符串的表，并显示相应的表记录
        tableList = self.GetTableList(database)
        if len(tableList) == 0:
            return False

        found = 0
        findList = list()
        for table in tableList:
            strSql = "SELECT '" + table[0] + "' as table_name, t.* "
            strSql = strSql + "  FROM " + database + "." + table[0] + " as t where " + "("
            
            columnList = self.GetColumnList(table[0])
            i = 0

            count = len(columnList)

            for column in columnList:
                # 如果字段数据类型为非文本型，跳过
                if not column[1] in ('varchar', 'char', 'text'):
                    continue
                i += 1

                if i > 1:
                    strSql += " or "
                strSql += column[0] + " like '%" + searchText + "%' "

            strSql += ")"
            
            cursorColumn = self.connect.cursor()
            try:
                cursorColumn.execute(strSql)
            except Exception as e:
                print('2----------------------------', database, strSql)
                print("-----错误信息：-----\n", e)
                return False

            result = cursorColumn.fetchall()
            if len(result) > 0:
                findList.append(table[0])
                print("==========================================================================")
                print(table[0], result, strSql)
        return findList

if __name__ == '__main__':
    print(
"""
============================================================
|这是数据库全文检索工具，包含两个参数                      |
============================================================
""")

    # 设置运行环境。如果当前是测试环境，则将is_test设置为true
    is_test = True

    if is_test:
        file_path = "/opt/eresource_test/webapp/WEB-INF/classes/prod/jdbc.properties"
        database = 'guoyantest'
    else:
        file_path = "/opt/eresource/webapp/WEB-INF/classes/prod/jdbc.properties"
        database = 'guoyan'

    # 打开jdbc.properties文件，获取数据库的配置信息
    props = Properties(file_path)
    host = 'localhost'
    user = props.get('jdbc.username')
    password = props.get('jdbc.password')

    # 打开数据连接
    db = pymysql.connect(host = host, user = user, password = password, database = database)

    # 获取命令行参数
    keyword = ''
    searchType =''

    keyword = ''
    searchType = ''
    try:
        opts, args = getopt.getopt(sys.argv[1:],"hT:k:",["keyword=","searchType="])
    except getopt.GetoptError:
        print(sys.argv[0] + ' -k <keyword> -T <searchType>')
        sys.exit(2)

    for opt, arg in opts:
        if opt == '-h':
            print('3--------------------', 'test.py -k <keyword> -T <searchType>')
            sys.exit()
        elif opt in ("-k", "--keyword"):
            keyword = arg
        elif opt in ("-T", "--searchType"):
            searchType = arg
    dbSearch = DbSearch(db)
    if searchType == '0':
        print('正在根据您输入的关键词查找表.....................')
        print('found tables: ', dbSearch.SearchTableByText(keyword, database))
    elif searchType == '1':
        print('正在根据您输入的列名查找表.....................')
        print('found tables: ', dbSearch.SearchTableByColumnName(keyword, database))