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

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

1. Request to Dbgate, can make the dbgate into a stateless, convenient lateral expansion
2. When the amount of request or storage is large, MySQL needs to do the sub-Library, Dbgate can be directly handled internally, no outside perception
3. By restful restrictions on the form of data requests, only a simple get/post/patch/put is supported for incremental deletions, 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 inquiries business, this is not suitable for
4. Dbgate use multi-process mode to control the number of links between MySQL and MySQL access threshold protection
5. Convenient on the dbgate to carry on the traffic statistics, the slow query statistics, the Authority control and so on a series of logic
6. The current use of Python, in the future to use other languages for MySQL operations, as long as the standard HTTP request can not appear incompatible

Of course, there are disadvantages:

1. First of all, the response time for a single request to become longer, after all, the middle added a layer of services, and still HTTP format
2. Deployment is more complex than the original, many of the direct operation of MySQL need to change the thinking, at first may be a little uncomfortable

In general, however, the advantages outweigh the disadvantages, so the final decision is to build dbgate

Of course, we can not manually write each of the library table corresponding to the restful service, thankfully, Django and flask have provided the corresponding solution, we introduced each.
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
Overwrite individual users: PUT/USER/1
To modify an individual user: PATCH/USER/1

Get user list: Get/user
Add User: Post/user
Get a single User: GET/USER/1
Overwrite individual users: PUT/USER/1
To modify an individual user: PATCH/USER/1

Attention:

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

Django

Reference Link: Django REST framework

Django is a bit more complicated, and because the Django version has a Visual action page, it looks like this:

1. Add in Settings:

Rest_framework = {# Use hyperlinked styles by default.
  # only used if the ' Serializer_class ' was 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_framewo
  Rk.permissions.IsAdminUser ',]} rest_framework = {# Use hyperlinked styles by default.
  # only used if the ' Serializer_class ' was 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_framewo Rk.permissions.IsAdminUSer ',]}

 

2. Establishment of a App:demo through Startapp
3. Modify the Demo models:

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

4. Under the demo new serializers.py

From rest_framework import serializers
From models import User
Class Userserializer (serializers. Modelserializer):
  class Meta:
    model = User from
 
rest_framework import serializers from
models Import User
 
 
class Userserializer (serializers. Modelserializer):
  class Meta:
    model = User

5. Modify the views.py under Demo

From django.shortcuts import render to
rest_framework import viewsets from

serializers import userserializer< C4/>from Models Import User


class Userviewset (viewsets. Modelviewset):
  queryset = User.objects.all ()
  serializer_class = Userserializer from
 
django.shortcuts Import render from
rest_framework import viewsets to
 
serializers import Userserializer from
models Import User
 
 
class Userviewset (viewsets. Modelviewset):
  queryset = User.objects.all ()
  Serializer_class = Userserializer

6. Under the demo new urls.py

Import Os.path
from Django.conf.urls import patterns, include, url from
django.conf.urls.static import static< C3/>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)),"
 
Import Os.path
from Django.conf.urls import patterns, include, url from
django.conf.urls.static import static
From django.conf Import settings
import
 
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 initialization data operations:

Python manage.py syncdb
 
python manage.py syncdb

Then visit: Http://127.0.0.1:8000/demo can see the following interface:

The corresponding 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 ': ' AP Plication/json '}) assert Rsp.ok def test_post_user_list (): Json_data = Dict (password=0, nick= ' oo ', c Reate_time= ' 2014-03-3t03:3:3 ') RSP = Requests.post (Urljoin (base_url, '/demo/users/'), Auth=auth, headers={' Accep T ': ' Application/json ', ' content-type ': ' Application/json ',}, Data=json.dumps (Json_data)) Assert Rsp.ok def tes T_get_user (): RSP = Requests.get (Urljoin (base_url, '/demo/users/1 '), Auth=auth, headers={' Accept ': ' Application/jso n ', ' content-type ': ' Application/json ',}) assert Rsp.ok def test_put_user (): Json_data = Dict (password=1 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 ': ' Application/json ', ' content-type ': ' Application/json ',}, Data=json.dumps (Json_data), Asse

 RT Rsp.ok, Rsp.status_code

The Django REST Framework is a strict distinction between put and patch, which, unlike flask-restless, requires attention.

OK, that's it.