在进行Django项目开发的过程中,Web API的开发是一个研发工程师必备的技能。本文主要介绍接口开发中,如何对数据库的连接访问,使用模块进行封装,用户可以通过调用封装好的接口,只需要传入sql语句,便可以获得查询结果。
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': 'xxx', // 根据自己的环境进行配置
'USER': 'xxx', // 自行配置
'PASSWORD': 'xxx', // 自行配置
'HOST': '127.0.0.1',
'PORT': '5432',
}
}
import psycopg2
DATABASE = "自己的数据库名"
USER = "自己的用户"
PASSWORD = "自己的数据库的密码"
HOST = "自己的ip,一般是本地127.0.0.1"
PORT = "端口,默认是5432"
def pg_conn():
database = DATABASE
user = USER
password = PASSWORD
host = HOST
port = PORT
conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
return conn
def pg_model(op):
"""
最终的返回结果是一个list,list中的每一个元素是一个元组
"""
conn = pg_conn()
cur = conn.cursor()
cur.execute(op)
rows = cur.fetchall()
conn.commit()
cur.close()
return rows
if __name__ == "__main__":
search_sql = """
你的sql代码
"""
search = pg_model(search_sql)
print(search)
es_model.py代码如下:
"""
本模块是关于Elasticsearch的相关配置信息,有关的
"""
from elasticsearch import Elasticsearch
# 初始化连接
def init_conn(protocol, host, port):
if protocol == 'http':
es = Elasticsearch(hosts="http://"+host+":"+port+"/")
else:
es = Elasticsearch(hosts="https://"+host+":"+port+"/")
return es
# 获取查询结果
def get_query(es, query_json, source_index):
query = es.search(index=source_index, body=query_json)
return query
# 常数配置
PROTOCOL = "自己的服务器的http协议" # 服务器的协议:http或者https
HOST = "自己服务器ip" # 服务器IP
PORT = "es的端口,默认9200" # 服务器上Elasticsearch的端口
ES_SERVER = init_conn(protocol=PROTOCOL, host=HOST, port=PORT)
ES_INDEX = "你的数据源索引" # ES数据源的索引
# 查询语言
QUERY_JSON = {你的dsl查询语言}
这样utils下的代码编写完成,注意:请在代码中填入你自己的配置!!!
在models.py开始编写第一个查询pg库的信息查询接口的核心业务逻辑代码company_info_model.py。
代码如下:
from test_api_app.utils.pg_model import pg_model
import logging
class CompanyInfoModel(object):
def __init__(self):
pass
def get_company_info(self):
try:
sql_count = """
select count(*) from api_test.company
"""
count = pg_model(sql_count)[0][0]
sql_query_info = """
SELECT name, age, address, salary FROM api_test.company
"""
query = pg_model(sql_query_info)
data_list = []
for i in range(len(query)):
tmp = {
"name": query[i][0],
"age": query[i][1],
"address": query[i][2],
"salary": query[i][3]
}
data_list.append(tmp)
res = {"count": count, "data": data_list}
return res
except Exception as e:
logging.info(e)
return False
if __name__ == '__main__':
test_model = CompanyInfoModel()
res = test_model.get_company_info()
print(res)
在services包下编写models包下对应模块company_info_model.py的服务模块company_info_service.py。
代码如下:
from test_api_app.models.company_info_model import CompanyInfoModel
class CompanyInfoService(object):
def __init__(self):
pass
@staticmethod
def company_query_info():
obj = CompanyInfoModel()
query_result = obj.get_company_info()
return query_result
在views包下编写models包下对应模块company_info_model.py的views模块company_info_view.py。
代码如下:
from test_api_app.services.company_info_service import CompanyInfoService
from django.http import HttpResponse
import json
def company_query_info(request):
"""
查询公司信息
url: api/v1/company_query_info,严格遵循restful api规范
:param request:
:return:
"""
result = dict()
if request.method == 'GET':
request_params = request.GET
query_result = CompanyInfoService.company_query_info()
result = query_result
result['status'] = 200
else:
result['status'] = 500
result['msg'] = '不支持的方法'
return HttpResponse(json.dumps(result, ensure_ascii=False), status=200)
在主应用test_web_api的urls.py中定义路由,即前端需要访问的接口,代码如下:
"""test_web_api URL Configuration
The `urlpatterns` list routes URLs to views. For more information please see:
https://docs.djangoproject.com/en/3.1/topics/http/urls/
Examples:
Function views
1. Add an import: from my_app import views
2. Add a URL to urlpatterns: path('', views.home, name='home')
Class-based views
1. Add an import: from other_app.views import Home
2. Add a URL to urlpatterns: path('', Home.as_view(), name='home')
Including another URLconf
1. Import the include() function: from django.urls import include, path
2. Add a URL to urlpatterns: path('blog/', include('blog.urls'))
"""
from django.contrib import admin
from django.conf.urls import url
from test_api_app.views import company_info_view
urlpatterns = [
url(r'^admin/', admin.site.urls),
url(r'^api/v1/company_query_info', company_info_view.company_query_info),
]
这样,便完成了接口api/v1/company_query_info接口的开发,请求方式为"GET",接下来启动服务,进行接口的访问。
在终端运行:python manage.py runserver 127.0.0.1:7777开启服务:
在postman中或者浏览器中输入:http://127.0.0.1:7777/api/v1/company_query_info/
便可查看到结果: