A tutorial on implementing a RESTful interface for MySQL in the Python framework

Recently, when doing the game service layering, I always wanted to separate MySQL access into a separate service dbgate for the following reasons:

1. Request collapse to Dbgate, can make dbgate stateless, convenient scale-out
2. When the volume of requests or storage is large, MySQL needs to do a sub-database sub-table, Dbgate can be processed internally directly, no outside perception
3. In the form of data requests through restful restrictions, only simple get/post/patch/put are supported for incremental pruning and retrieval, and complex queries are not supported. This is also related to the characteristics of the game business, if the site and other needs of complex query business, it is not suitable for
4. Dbgate uses multi-process mode to conveniently control the number of links to and from MySQL for MySQL traffic threshold protection
5. Convenient to dbgate on the traffic statistics, slow query statistics, permission control and a series of logic
6. Currently using Python, in the future to use other languages for MySQL operations, as long as the standard HTTP request can be, there is no incompatibility situation

Of course there are disadvantages:

1. The first is the response time of a single request is longer, after all, a layer of service in the middle, and also the HTTP format
2. Deployment is a bit more complicated than the original, many of the direct operation of MySQL thinking needs to change, at first may be a bit uncomfortable

But overall, it is more than the pros and cons, so ultimately decided to build Dbgate

Of course, it's not possible to hand-write each of the corresponding restful services for each library table, and fortunately Django and flask provide the corresponding solutions, which we introduce.
Flask

Reference Link: flask-restless

Flask-restless use method is relatively simple, I directly paste the code can:

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

Its corresponding restful operation is as follows:

Get user list: Get/user
Add User: Post/user
Get a single User: GET/USER/1
Overriding a single User: PUT/USER/1
To modify a single user: PATCH/USER/1

Get user list: Get/user
Add User: Post/user
Get a single User: GET/USER/1
Overriding a single User: PUT/USER/1
To modify a single user: PATCH/USER/1

Attention:

• In the HTTP request, remember to join Header:content-type:application/json
• In flask-restless, put and patch are all fields that are passed in, only what fields are modified, and not completely overwritten

Django

Reference Link: Django REST framework

Django is more complicated to use, and because the Django version comes with a visual Operation page, as follows:

1. Add in Settings:

Rest_framework = {# Use hyperlinked styles by default.  # only used if the ' Serializer_class ' attribute are 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 are 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. Establish a App:demo through Startapp
3. Modify the models of the demo:

Class User (models. Model):  # key is reserved word  password = models. Integerfield ()  Nick = models. Charfield (max_length=255)  create_time = models. Datetimefield (Default=datetime.datetime.now) class User (models. Model):  # key is reserved word  password = models. Integerfield ()  Nick = models. Charfield (max_length=255)  create_time = models. Datetimefield (Default=datetime.datetime.now)

4. Create a new serializers.py under 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 the views.py under Demo

From django.shortcuts import renderfrom rest_framework import viewsetsfrom serializers import Userserializerfrom Models I Mport 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 Uservie WSet (viewsets. Modelviewset):  queryset = User.objects.all ()  Serializer_class = Userserializer

6. Create a new urls.py under 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

After access: Http://127.0.0.1:8000/demo can see the following interface:

The corresponding test code is as follows:

Import jsonimport requestsfrom urlparse Import urljoinbase_url = ' http://127.0.0.1:16500/' AUTH = (' admin ', ' admin ') def te St_get_user_list (): RSP = Requests.get (Urljoin (base_url, '/demo/users/'), Auth=auth, headers={' Accept ': ' application/ JSON '}) assert Rsp.okdef test_post_user_list (): Json_data = Dict (password=0, nick= ' oo ', create_time= ' 2014-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.okdef Test_get_user (): RSP = Request S.get (Urljoin (base_url, '/demo/users/1 '), Auth=auth, headers={' Accept ': ' Application/json ', ' content-type ': ' Applic Ation/json ',}) assert Rsp.okdef Test_put_user (): Json_data = Dict (password=100, nick= ' xx ', Create_time= ' 2014 -03-3t03:3:3 ') # Note the last/RSP = Requests.put (Urljoin (base_url, '/demo/users/1/'), Auth=auth, headers={' Accept ': ' AP Plication/json ', ' ContEnt-type ': ' Application/json ',}, Data=json.dumps (Json_data),) assert Rsp.ok, Rsp.status_code 

The Django REST Framework is a strict distinction between put and patch, which is different from flask-restless and needs attention.

OK, that's it.

