Tutorial on implementing restful interfaces for MySQL In the Python framework, pythonrestful

Tutorial on implementing restful interfaces for MySQL In the Python framework, pythonrestful

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

1. Requests are collected to DBGate, which changes DBGate to stateless and facilitates horizontal scaling.
2. 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.
3. 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.
4. DBGate uses the multi-process mode to conveniently control the number of connections between mysql and implement mysql access threshold protection.
5. Allows you to conveniently perform traffic statistics, slow query statistics, and permission control on DBGate.
6. 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:

1. 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.
2. 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 link: flask-restless

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)
 
#-*-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

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.HyperlinkedModelSerializer',  # 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',  ]} 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.HyperlinkedModelSerializer',   # 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): # the key is the reserved WORD password = models. integerField () nick = models. charField (max_length = 255) create_time = models. dateTimeField (default = datetime. datetime. now) class User (models. model): # the key is the 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 from rest_framework import serializersfrom models import User  class UserSerializer(serializers.ModelSerializer):  class Meta:    model = User

5. Modify views. py in demo

from django.shortcuts import renderfrom rest_framework import viewsetsfrom serializers import UserSerializerfrom models import Userclass UserViewSet(viewsets.ModelViewSet):  queryset = User.objects.all()  serializer_class = UserSerializer from django.shortcuts import renderfrom rest_framework import viewsets from serializers import UserSerializerfrom models import User  class UserViewSet(viewsets.ModelViewSet):  queryset = User.objects.all()  serializer_class = UserSerializer

6. Create urls. py in demo

import os.pathfrom django.conf.urls import patterns, include, urlfrom django.conf.urls.static import staticfrom django.conf import settingsimport viewsfrom rest_framework import routersappname = 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)),) import os.pathfrom django.conf.urls import patterns, include, urlfrom django.conf.urls.static import staticfrom django.conf import settingsimport 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'))) 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 python manage.py syncdb

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

The test code is as follows:

Import jsonimport requestsfrom urlparse import urljoinBASE_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 = {'access': 'application/json'}) assert rsp. okdef 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 = {'access': 'application/json', 'content-type ': 'application/json',}, data = json. dumps (json_data) assert rsp. okdef test_get_user (): rsp = requests. get (urljoin (BASE_URL, '/demo/users/1'), auth = AUTH, headers = {'access': 'application/json', 'content-type ': 'application/json',}) assert rsp. okdef test_put_user (): json_data = dict (password = 100, nick = 'XX', create_time = '2017-03-3T03: 3: 3') # Pay attention to the final/rsp = requests. put (urljoin (BASE_URL, '/demo/users/1/'), auth = AUTH, headers = {'access': '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.