python写数据库脚本_如何基于Python实现MySQL查询的API设计,附上完整脚本

这是学习笔记的第 2134 篇文章

我们在平时的工作中不可避免会有连接到数据库的操作,通常来说我们会使用基于Shell的方式,或者基于数据库驱动的连接方式,比如JDBC,ODBC,PyMySQL,MySQLdb等。

如果是基于Shell的方式,很容易出现一个使用瓶颈,那就是如果通过shell去查看一个表的数据,那么输出是没有规范的格式的,Shell执行是最简单最基本的调用模式,我们也可以利用数据库服务端的特性来输出相应的数据格式,但是基于数据库版本的差异,有些低版本是不支持输出一些格式的,所以使用Shell来输出SQL查询的结果显然不是一个通用而且优雅的实现方式。

如果使用数据库启动,基于Python的模式就是一种很不错的选择,我们可以开发一个Python脚本,然后把这个Python脚本使用RESTful API的模式包装起来,这样对外的服务就是API而不是单一的脚本,而且可移植性和扩展性也要好很多。

大概的设计方式如下:

我们因为版本的选型在这里使用的是基于MySQLdb的实现方式,我们来逐个分析一下。

首先对于SQL查询来说,输出结果,执行时长,结果集行数等这些是我们关注的一些数据,要实现这个功能,实际上要实现一揽子细小的功能。

1)使用MySQLdb或者其他驱动的默认情况下,输出的结果都是只有数据,而没有相应的列名等信息,我们需要做一些补充操作,输出为字段名和字段值的映射,比如{"id":100,"name":"aaa"}这样的形式,

2)基于Python驱动的输出时间类型是映射datetime,对于前端处理来说也不够友好,所以我们要一并处理。

3)对于数据返回行数,默认是long行,我们需要转换为整型

4)对于返回结果,默认是unicode,字符'abc'会显示类似为 u'abc'这种,我们也需要做一层转换。

5)为了前端程序方便解析,我们需要补充一个字段列表,比如有字段id,name,我们就返回一个数组或者列表,包含这些字段。

#! /usr/bin/env python

# _#_ coding:utf-8 _*_

import MySQLdb

import MySQLdb.cursors

import datetime

import json

import sys

def query(db_port,db_name,sql):

try:

return_dict = {}

starttime = datetime.datetime.now()

conn = MySQLdb.connect(host='127.0.0.1', user='jeanron', passwd='xxxx', db=db_name, port=int(db_port), charset='utf8')

cur = conn.cursor()

cur.execute(sql)

rows = cur.fetchall()

endtime = datetime.datetime.now()

return_dict["execution_seconds"] = (endtime - starttime).seconds

index = cur.description

result = []

for res in rows:

row = {}

for i in range(len(index)):

if type(res[i]) == datetime.datetime:

row[index[i][0]] = res[i].strftime('%Y-%m-%d %H:%M:%S')

elif type(res[i]) == int or type(res[i]) == long:

row[index[i][0]] = int(res[i])

elif type(res[i]) == unicode:

row[index[i][0]] = res[i].encode('utf8')

else:

print type(res[i])

row[index[i][0]] = res[i]

result.append(row)

if len(result) > 0:

column_list = result[0].keys()

return_dict["column_list"] = column_list

return_dict["data_rows"] = int(cur.rowcount)

return_dict["table_data"] = result

cur.close()

conn.close()

return json.dumps(return_dict)

except MySQLdb.Error as e:

print(e)

调用可以使用类似下面的代码形式:

sql_text="explain select * from test_data ;"

db_name='test'

db_port=int('3306')

测试没有问题止呕,很自然的可以使用参数化的形式:

sql_text=sys.argv[3]

db_port=sys.argv[1]

db_name=sys.argv[2]

为了封装为一个API,逻辑的部分实现有以下几个要点:

1)调用Python脚本,这里我们是通过Ansible的adhoc来实现的,当然也可以基于原生的paramiko等实现方式。

2)对于数据结果的返回,因为是一个调用-返回的逻辑关系,通过驱动有时候无法得到一些明细的信息,比如错误信息,我们就需要刻意处理几个场景,比如表不存在,SQL语法错误等。

实现的代码如下:

@api_view(['POST'])

def mysql_db_query(request):

try:

request_data = request.data.get("data")

sql_text_raw = request_data.get("sql_content")

ip_addr = request_data.get("ip_addr")

db_port = request_data.get("db_port")

db_name = request_data.get("db_name")

sql_text = sql_text_raw.replace("=","\=")

command = "/usr/local/DBA_SCRIPTS/mysql/mysql_db_query.py %s %s %s " % (db_port, db_name,"\""+sql_text+"\"")

result = ansible_adhoc("dba_mysql", ip_addr, "script", command, True)

print result

if result['success'][ip_addr][0].find("doesn\'t exist")!= -1:

return MyJsonResponse(data={}, code=str(status.HTTP_500_INTERNAL_SERVER_ERROR),

message='query failed,table doesn\'t exists')

elif result['success'][ip_addr][0].find("error in your SQL syntax")!= -1:

return MyJsonResponse(data={}, code=str(status.HTTP_500_INTERNAL_SERVER_ERROR),

message='query failed,there is an error in your SQL syntax')

else:

return_data=json.loads(result['success'][ip_addr][0])

#print return_data

return MyJsonResponse(data=return_data, code=str(status.HTTP_200_OK), message='success')

except MySQLdb.Error as e:

print(e)

API的输入参数样例为为:

{

"data": {

"ip_addr": "xxxx",

"db_port": "4306",

"db_name": "test",

"sql_content":"select * from test_data where id>0"

}

}

一个比较理想的输出样例如下:

{

"message": "success",

"code": "200",

"data": {

"data_rows": 10,

"execution_seconds": 0,

"column_list": [

"cdate",

"id",

"name"

],

"table_data": [

{

"cdate": "2019-10-14 18:49:16",

"id": 1,

"name": "aa"

},

{

"cdate": "2019-10-14 18:49:16",

"id": 2,

"name": "bb"

}

]

},

"pagenation": null

}

大家在使用中有问题和意见也欢迎随时反馈。

相关链接:

个人新书 《MySQL DBA工作笔记》

QQ群号:763628645

QQ群二维码如下,个人微信号:jeanron100, 添加请注明:姓名+地区+职位,否则不予通过

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值