sql查询后台可以自己做吗?

Img

公司主营短信,工作中数据库查询占了很大比例。

我们的操作是,通过navicat连接十多台机器的库,然后连接数据库-输入sql-修改查询条件(如一长串的日期)进行各种查询,

遇上高峰期,客服频频转发问题给我们,例如查下客户为什么没收到短信啦,查询发送记录啦,某个短信通道的发送量,签名统计等等 Img 最让我郁闷的地方,每次手机号、日期、短信通道等条件挨个修改一遍,键盘敲得噼里啪啦,鼠标点的不亦乐乎,别人还以为你有多忙,结果一顿操作猛于虎就查了条数据 Img

都说打工人苦打工人累 Img

我们就不要把工作耗费在这无意义的机械重复中了,是的,即使快一点也好 Img

思路

我想到总结常用的sql,写入配置文件 Img 通过网页点击执行这些语句。

在前端,好不容易靠试错发现,可以通过JavaScript的splice函数来对接红框内的查询条件: Img

利用splice实现搜索条件动态添加,这个用法是我蒙的,不知道专业的前端MM怎么做的。

上述内容就是我最初的想法。

虽然研发已经给客服MM做了查询后台,不过不适合我们这种“查询工程师”。

手工DIY一个? Img

想法挺好,怎么实现呢。

这个不急,骑着毛驴看唱本——走着瞧

摸到一条鱼

日月轮转,上班闲敲棋子,忙改文字,敲敲打打的,在没有其它技术人员支持的情况下,前后端居然调通了。

忽然间,大部分的查询工作,我都可以通过自己的笔记本轻描淡写的“指点江山”了: Img

不管多少个数据库,多少条语句,我都可以写到配置文件中。

前端呢,可以用element-ui的“Cascader 级联选择器”分门别类来添加要执行的sql Img

下图红框内就是“Cascader 级联选择器”效果,选中后会动态出现相关的搜索选项: Img

我们看下展示效果: Img

大体情况就是这样。

怎么实现的,主要介绍2点:

  • 如何连接不同的数据库
  • 如何添加一条sql查询

    文末上传了所有源码到gitee,有兴趣的同学可以看看

如何连接不同的数据库

1. 怎么在前端看到数据库

如图,怎么让数据库信息在下拉框中显示呢? Img 首先,每个数据库取个英文名字,写在配置文件里,通过configparser模块读取 Img

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
from rest_framework.views import APIView
import configparser

def read_cfg(name):
    cfg = configparser.RawConfigParser()
    cfg.read(settings.CONFIG_PATH, encoding='utf-8')
    return cfg.items(name)

class get_dbs(APIView):
    def get(self, request, *args, **kwargs):
        dbs = read_cfg('db')
        dbs_list = []
        d = {}
        for k,v in dbs:
            d['label'] = k
            d['value'] = v
            dbs_list.append(d)
            d = {}
        return JsonResponse({'code': 1, 'data': dbs_list})

然后配置前端,一打开页面就获取数据库信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
export default {
  created() {
    this.get_dbs()
  },
}

async get_dbs() {
    //this.$http.get('dbs'),dbs为获取数据库信息的接口地址
    const { data: res } = await this.$http.get('dbs')
    if (res.code !== 1) {
    return this.$message.error('获取dbs失败')
    }
    this.operateFormLabel[0].children = res.data
},

2. 让django处理前端传来的数据库信息

公司数据库为oracle,并且经过堡垒机,所以用到cx_Oracle和sshtunnel模块

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
from cx_Oracle import Connection
from sshtunnel import SSHTunnelForwarder
from rest_framework.views import APIView

class get_messages(APIView):
    def post(self, request, *args, **kwargs):
        data = json.loads(request.body.decode('utf-8'))
        db = data['db']
        #根据下拉框中选择的数据库名字,配置要连接的数据库
        if not db:
            return JsonResponse({'code':0,'msg':"请选择数据库"})
        if db == 'lt1':
            conn = ('192.168.2.1','ms','sgate;Normal;sms')
        elif db == 'lt2':
            conn = ('192.168.2.6','ms2','sgate;Normal;sms2')
        
        #sshtunnel建立客户端与堡垒机的隧道
        with SSHTunnelForwarder(
                ('堡垒机地址', port),
                ssh_username="ssw",
                ssh_password="1223456",
                remote_bind_address=(conn, 1521)) as server:
            conn = (conn[1], '123456', '127.0.0.1:%d/%s' % (server.local_bind_port,conn[2]))
            xconn = Connection(*conn)
            cursor = xconn.cursor()  # 新建游标
            cursor.execute(sql)  # 执行sql语句
            ret = cursor.fetchall()
            cursor.close()
            xconn.close()

这样就可以在页面下拉框中,自由的进行数据库的连接啦。接下来就是如何添加sql了,请看下面的例子。

如何添加一条sql查询

前端操作

1. 怎么在前端看到它

如图,怎么让这条sql在前端显示呢? Img 只需一步, 在messages.vue中添加级联菜单的一级菜单巡检和二级菜单服务器即可

1
2
3
4
5
6
7
8
9
10
11
12
      casecadeFormLabel: [
        {
          model: 'weekly_check',
          label: '巡检',
          children: [
            {
              label: '服务器',
              value: 'inspect'
            },
          ]
        }
      ],

只是显示还不够,每条语句要显示的字段不一样,我们需要单独定义它们 Img

2. 单独定义要显示的表头字段

如上所述,在messages.vue中添加inspect的表头字段,并且设置this.tableLabel = this.inspectLabel。为每条sql语句设置不同的表头字段,赋值给this.tableLabel,这样可以让不同的sql显示不同的字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
      inspectLabel: [
        { prop: 'ip', label: '服务器', width: 120},
        { prop: 'cpu', label: 'CPU占用率', width: 70},
        { prop: 'mem', label: '内存占用率', width: 70},
        { prop: 'disk', label: '磁盘使用情况', width: 230 },
        { prop: 'vda1', label: '/dev/vda1使用率', width: 100},
        { prop: 'vdb1', label: '/dev/vdb1使用率', width: 100},
        { prop: 'network', label: '网络连接', width: 60},
        { prop: 'service', label: '服务检查', width: 165 },  
        { prop: 'url', label: '站点检查', width: 165 },       
        { prop: 'create_time', label: '日期', width: 70}      
      ], 

      ...中间略
      else if (this.operateForm.sql === 'inspect') {
        this.tableLabel = this.inspectLabel
      }        
      
3. 动态添加搜索条件

如图,框内的3个搜索条件是通过JavaScript的splice函数生成的。通过它,我们可以为每条语句定义不同的搜索条件。 Img 在commonForm.vue中编辑:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
// 如果点击的的sql是“inspect”,就在页面上添加3个搜索条件,分别是网络连接、服务检查、时间范围
export default {
  data() {
    return {
      network: {
        model: 'network',
        label: '网络连接'
      },
      service: {
        model: 'service',
        label: '服务检查'
      },
      timerange: {
        model: 'timerange',
        label: '时段',
        type: 'date'
      },
    }
  }

    methods: {
    handleChange() {

        if (this.selectedKeys === 'inspect') {
            this.formLabel.splice(1, 1, this.network)
            this.formLabel.splice(2, 1, this.service)
            this.formLabel.splice(3, 1, this.timerange)
        }
    }
    }
}

接下来是后端操作

后端操作

1. 读取sql

首先,sql取名为“inspect”。 config.cfg-[sql] 填写要执行的sql语句 Img

old_views.py-read_cfg函数get_sql函数 读取config.cfg中的sql语句,比如读到名为“inspect”的语句,返回的结果是这样的 [‘select project,ip,cpu,mem,disk,vda1,vdb1,network,service,url,create_time\nfrom weekly_check\nwhere create_time BETWEEN {1} AND {2}’]

1
2
3
4
5
6
7
8
9
10
11
12
import configparser
def read_cfg(name):
    cfg = configparser.RawConfigParser()
    cfg.read(settings.CONFIG_PATH, encoding='utf-8')
    return cfg.items(name)

#读取config.cfg中的sql语句,比如读到名为“inspect”的语句,返回的结果是这样的
#['select project,ip,cpu,mem,disk,vda1,vdb1,network,service,url,create_time\nfrom weekly_check\nwhere create_time BETWEEN {1} AND {2}']
def get_sql(sqlname):
    data = read_cfg('sql')
    sql = [item[1] for item in data if sqlname == item[0]]
    return sql[0]
2. 对页面提交过来的sql语句进行处理

然后,对页面提交过来的sql语句进行处理: 如修改日期,修改where条件,加上搜索条件等

old_views.py-get_messages()

1
2
3
4
5
6
7
8
9
10
11
12
13
if sqlname in ('inspect'):
    inputLabel = []
    field = []
    condition = []
    field_dict = {'network': network, 'service': service}
    for key, value in field_dict.items():
        print('len(value)',len(value))
        field.append(key)
        condition.append("{0} like '%{1}%' and".format(key, value))
    if sqlname == 'inspect':
        print('get_sql(sqlname)',get_sql(sqlname).format(','.join(field), start, end))
        print('condition',condition)
        sql = get_sql(sqlname).format(','.join(field), start, end).replace('where','where {0}').format(' '.join(condition))
3. 数据转成字典

(('农林牧渔', '172.16.1.6', '2.05', '24.59', '/dev/vda1 used: 7.9G nouse: 30G', '19.75', '74.11', '异常', 'Bootlog: OK'),)

这是从数据库返回的数据,类型为元组,需要转成字典并给value加上key,方便前端识别。如{‘项目名称’: ‘农林牧渔’, ‘ip’: ‘172.16.1.6’}

old_views.py-foo函数bar函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#把参数转成字典
def foo(**kwargs):
    #对字典中的datetime时间格式数据转为字符串
    if isinstance(kwargs['create_time'],datetime.datetime):
        kwargs['create_time'] = kwargs['create_time'].strftime(('%Y-%m-%d %X'))
    return kwargs

#field是选取哪些字段的数据返回给前端
def bar(ret,sqlname,field=None,user_id=None):
    for item in ret:
        if sqlname == 'inspect':
            obj = foo(project=item[0], ip=item[1], cpu=item[2],mem=item[3],disk=item[4],
                      vda1=item[5],
                      vdb1=item[6],
                      network=item[7],
                      service=item[8],
                      url=item[9],
                      create_time=item[10])
        yield obj        

上一步bar()函数主要作用是把从数据库查到的数据转成字典,并给value加上一个key,大概过程:

1
2
3
4
5
6
7
8
9
10
11
import datetime
ret = (('农林牧渔', '172.16.1.6', '2.05', '24.59', '/dev/vda1 used: 7.9G nouse: 30G', '19.75', '74.11', '异常', 'Bootlog: OK', '', datetime.datetime(2022, 8, 19, 16, 17, 12)),)
obj = {}
field = ['network', 'service']
for item in ret:
    for i, v in enumerate(field):
        obj[v] = item[i]

print(obj)
输出为
{'network': '农林牧渔', 'service': '172.16.1.6'}

到这里,一条sql查询就添加完了。

源码下载和安装

vue

1
2
3
4
git clone https://gitee.com/sswfit/vue-morning-shift.git
cd vue-morning-shift
npm install --registry=https://registry.npm.taobao.org
npm run serve

django

1
2
3
4
git clone https://gitee.com/sswfit/morning_shift.git
cd morning_shift
pip install -r requirements.txt
python manage.py runserver localhost:8887
Python Geek Tech wechat
欢迎订阅 Python 技术,这里分享关于 Python 的一切。