Detailed steps and code for implementing restful interfaces for mysql using python

Recently, when I made a game service layer, I always wanted to separate mysql access into a separate service DBGate for the following reasons:

Requests are collected to DBGate, which changes DBGate to stateless and facilitates horizontal scaling.
When the request volume or storage volume increases, mysql needs to perform database/table sharding. DBGate can directly process the data internally and is imperceptible to the outside world.
Restful restricts the form of data requests. It only supports simple get, post, patch, and put operations for addition, deletion, modification, and query, and does not support complex queries. This is also related to the features of the game business. It is not suitable for websites and other businesses that require complex queries.
DBGate uses the multi-process mode to conveniently control the number of connections between mysql and implement mysql access threshold protection.
Allows you to conveniently perform traffic statistics, slow query statistics, and permission control on DBGate.
Currently, python is used. If you want to perform mysql operations in other languages, you only need to perform standard http requests without incompatibility.

Of course, there are also some disadvantages:

The first is that the response time for a single request is longer. After all, a layer of service is added in the middle and the http format is used.
The deployment is a little more complex than the original one. A lot of thinking about direct mysql operations needs to be changed, and may be uncomfortable at the beginning.

However, in general, the advantage is greater than the disadvantage, so we finally decided to build DBGate.

Of course, it is impossible for us to manually write The restful Service corresponding to each database table. Fortunately, both django and flask provide corresponding solutions. We will introduce them one by one.

Flask

Reference: http://flask-restless.readthedocs.org/en/latest/

Flask-restless is easy to use. Just paste the code:

#-*-Coding: UTF-8 -*-

Import datetime
From flask import Flask
From flask_sqlalchemy import SQLAlchemy
From flask_restless import APIManager


App = Flask (_ name __)
Db = SQLAlchemy (app)
Restless = APIManager (app, flask_sqlalchemy_db = db)


Class User (db. Model ):
"""
User
"""

Id = db. Column (db. Integer, primary_key = True)
Username = db. Column (db. String (255), unique = True, nullable = False)
Password = db. Column (db. String (255), nullable = False)
Create_time = db. Column (db. DateTime, nullable = False, default = datetime. datetime. utcnow)
Login_time = db. Column (db. DateTime)


Restless. create_api (User, methods = ['GET', 'post', 'delete', 'Patch ', 'put'], results_per_page = 100)

Db. create_all ()

If _ name _ = '_ main __':
App. run (port = 25000)

The corresponding restful operations are as follows:

GET user list: GET/user
Add user: POST/user
GET a single user: GET/user/1
Overwrite a single user: PUT/user/1
Modify a single user: PATCH/user/1

Note:

In the http request, remember to add header: Content-Type: application/json
In flask-restless, PUT and PATCH are the same as what fields are passed in. Only the modified fields are not completely overwritten.

Django

Reference link: Django REST framework

Django needs to be more complex to use, and because django comes with a visual operation page, as shown below:

1. Add in settings:

REST_FRAMEWORK = {
# Use hyperlinked styles by default.
# Only used if the 'serializer _ class' attribute is not set on a view.
'Default _ MODEL_SERIALIZER_CLASS ':
'Rest _ framework. serializers. Hypervisor modelserializer ',

# Use Django's standard 'Django. contrib. Auth' permissions,
# Or allow read-only access for unauthenticated users.
'Default _ PERMISSION_CLASSES ':[
# 'Rest _ framework. permissions. Djangomodelpermissionsoranonreadonly ',
'Rest _ framework. permissions. Isadminuser ',
    ]
}

2. Create an app: demo through startapp

3. Modify the models of the demo:

Class User (models. Model ):
# Key is a reserved word
Password = models. IntegerField ()
Nick = models. CharField (max_length = 255)
Create_time = models. DateTimeField (default = datetime. datetime. now)

4. Create serializers. py in demo

From rest_framework import serializers
From models import User


Class UserSerializer (serializers. ModelSerializer ):
Class Meta:
Model = User

5. Modify views. py in demo

From django. shortcuts import render
From rest_framework import viewsets

From serializers import UserSerializer
From models import User


Class UserViewSet (viewsets. ModelViewSet ):
Queryset = User. objects. all ()
Serializer_class = UserSerializer

6. Create urls. py in demo

Import OS. path
From django. conf. urls import patterns, include, url
From django. conf. urls. static import static
From django. conf import settings
Import views

From rest_framework import routers

Appname = OS. path. basename (OS. path. dirname (OS. path. abspath (_ file __)))

Router = routers. DefaultRouter ()
Router. register ('users', views. UserViewSet, appname)

Urlpatterns = patterns ('',
Url (r' ^ ', include (router. urls )),
)

7. Under mysite. urls, include demo. urls and rest_framework.urls

Urlpatterns = patterns ('',
Url (r' ^ demo/', include ('demo. URLs ')),
Url (r '^ admin/', include (admin. site. urls )),
Url (r' ^ api-auth/', include ('Rest _ framework. URLs', namespace = 'rest _ framework '))
)

8. Perform the initialization data operation:

Python manage. py syncdb

Visit http: // 127.0.0.1: 8000/demo to see the following interface:

The test code is as follows:

Import json
Import requests
From urlparse import urljoin

BASE_URL = 'http: // 127.0.0.1: 16500 /'
AUTH = ('admin', 'admin ')


Def test_get_user_list ():
Rsp = requests. get (urljoin (BASE_URL, '/demo/users/'), auth = AUTH, headers = {
'Accept': 'application/json'
})
Assert rsp. OK


Def test_post_user_list ():
Json_data = dict (
Password = 0,
Nick = 'oo ',
Create_time = '2017-03-3T03: 3: 3'
    )
Rsp = requests. post (urljoin (BASE_URL, '/demo/users/'), auth = AUTH, headers = {
'Accept': 'application/json ',
'Content-type': 'application/json ',
}, Data = json. dumps (json_data ))
Assert rsp. OK


Def test_get_user ():
Rsp = requests. get (urljoin (BASE_URL, '/demo/users/1'), auth = AUTH, headers = {
'Accept': 'application/json ',
'Content-type': 'application/json ',
})
Assert rsp. OK


Def test_put_user ():
Json_data = dict (
Password = 100,
Nick = 'XX ',
Create_time = '2017-03-3T03: 3: 3'
    )
# Note the last/
Rsp = requests. put (urljoin (BASE_URL, '/demo/users/1/'), auth = AUTH, headers = {
'Accept': 'application/json ',
'Content-type': 'application/json ',
}, Data = json. dumps (json_data ),
    )
Assert rsp. OK, rsp. status_code


Def test_patch_user ():
Json_data = dict (
Password = 300,
        )
Rsp = requests. patch (urljoin (BASE_URL, '/demo/users/1/'), auth = AUTH, headers = {
'Accept': 'application/json ',
'Content-type': 'application/json ',
}, Data = json. dumps (json_data ),
    )
Assert rsp. OK, rsp. status_code

Django REST framework strictly distinguishes PUT and PATCH, which is different from flask-restless.

OK.