在Python的框架中为MySQL实现restful接口的教程
最近在做游戏服务分层的时候，一直想把mysql的访问独立成一个单独的服务DBGate，原因如下:
- 请求收拢到DBGate，可以使DBGate变为无状态的，方便横向扩展
- 当请求量或者存储量变大时，mysql需要做分库分表，DBGate可以内部直接处理，外界无感知
- 通过restful限制对数据请求的形式，仅支持简单的get/post/patch/put 进行增删改查，并不支持复杂查询。这个也是和游戏业务的特性有关，如果网站等需要复杂查询的业务，对此并不适合
- DBGate使用多进程模式，方便控制与mysql之间的链接数，进行mysql访问量阀值保护
- 方便在DBGate上进行访问量统计，慢查询统计、权限控制等等一系列逻辑
- 目前是使用python，以后要使用其他语言进行mysql操作时，只要进行标准的http请求即可，不会出现不兼容的情况
当然坏处也是有的：
- 首当其冲就是单次请求的响应时间变长，毕竟中间加了一层服务，并且还是http格式
- 部署上比原来复杂了一些，很多对mysql直接操作的思维需要进行转变，一开始可能会有些不适
不过总的来说，还是利大于弊，所以最终还是决定搭建DBGate
当然，我们不可能去手工挨个写每个库表对应的restful服务，值得庆幸的是django和flask都提供了对应的解决方案，我们一个个介绍.
Flask
参考链接: flask-restless
flask-restless使用方法比较简单，我直接贴一下代码即可:
# -*- 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)
其对应的restful操作如下:
获取用户列表: GET /user
添加用户: POST /user
获取单个用户: GET /user/1
覆盖单个用户: PUT /user/1
修改单个用户: PATCH /user/1
获取用户列表: GET /user
添加用户: POST /user
获取单个用户: GET /user/1
覆盖单个用户: PUT /user/1
修改单个用户: PATCH /user/1
注意:
- 在http请求中，记得加入header: Content-Type: application/json
- flask-restless中，PUT和PATCH一样，都是传入什么字段，只修改什么字段，不会完全覆盖
Django
参考链接: Django REST framework
Django用起来要更复杂一些，也因为django版自带了一个可视化的操作页面，如下:
1. 在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. 通过startapp建立一个app: demo
3. 修改demo的models:
class User(models.Model): # key是保留字 password = models.IntegerField() nick = models.CharField(max_length=255) create_time = models.DateTimeField(default=datetime.datetime.now) class User(models.Model): # key是保留字 password = models.IntegerField() nick = models.CharField(max_length=255) create_time = models.DateTimeField(default=datetime.datetime.now)
4. 在demo下新建serializers.py
<p>from rest_framework import serializers<br />from models import User</p>
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. 在demo下修改views.py
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 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. 在demo下新建urls.py
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)),
)
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. 在mysite.urls下include demo.urls和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. 执行初始化数据操作:
python manage.py syncdb python manage.py syncdb
之后访问: http://127.0.0.1:8000/demo 即可看到如下界面了:
对应的测试代码如下:
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='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.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='2014-03-3T03:3:3'
)
# 注意最后的 /
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
Django REST framework 是严格区分PUT和PATCH的，这一点和flask-restless 不一样，需要注意。
OK，就这样。
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
Is the conversion speed fast when converting XML to PDF on mobile phone?
Apr 02, 2025 pm 10:09 PM
The speed of mobile XML to PDF depends on the following factors: the complexity of XML structure. Mobile hardware configuration conversion method (library, algorithm) code quality optimization methods (select efficient libraries, optimize algorithms, cache data, and utilize multi-threading). Overall, there is no absolute answer and it needs to be optimized according to the specific situation.
Is there any mobile app that can convert XML into PDF?
Apr 02, 2025 pm 08:54 PM
An application that converts XML directly to PDF cannot be found because they are two fundamentally different formats. XML is used to store data, while PDF is used to display documents. To complete the transformation, you can use programming languages and libraries such as Python and ReportLab to parse XML data and generate PDF documents.
How to convert XML files to PDF on your phone?
Apr 02, 2025 pm 10:12 PM
It is impossible to complete XML to PDF conversion directly on your phone with a single application. It is necessary to use cloud services, which can be achieved through two steps: 1. Convert XML to PDF in the cloud, 2. Access or download the converted PDF file on the mobile phone.
What is the function of C language sum?
Apr 03, 2025 pm 02:21 PM
There is no built-in sum function in C language, so it needs to be written by yourself. Sum can be achieved by traversing the array and accumulating elements: Loop version: Sum is calculated using for loop and array length. Pointer version: Use pointers to point to array elements, and efficient summing is achieved through self-increment pointers. Dynamically allocate array version: Dynamically allocate arrays and manage memory yourself, ensuring that allocated memory is freed to prevent memory leaks.
How to control the size of XML converted to images?
Apr 02, 2025 pm 07:24 PM
To generate images through XML, you need to use graph libraries (such as Pillow and JFreeChart) as bridges to generate images based on metadata (size, color) in XML. The key to controlling the size of the image is to adjust the values of the <width> and <height> tags in XML. However, in practical applications, the complexity of XML structure, the fineness of graph drawing, the speed of image generation and memory consumption, and the selection of image formats all have an impact on the generated image size. Therefore, it is necessary to have a deep understanding of XML structure, proficient in the graphics library, and consider factors such as optimization algorithms and image format selection.
How to convert xml into pictures
Apr 03, 2025 am 07:39 AM
XML can be converted to images by using an XSLT converter or image library. XSLT Converter: Use an XSLT processor and stylesheet to convert XML to images. Image Library: Use libraries such as PIL or ImageMagick to create images from XML data, such as drawing shapes and text.
How to open xml format
Apr 02, 2025 pm 09:00 PM
Use most text editors to open XML files; if you need a more intuitive tree display, you can use an XML editor, such as Oxygen XML Editor or XMLSpy; if you process XML data in a program, you need to use a programming language (such as Python) and XML libraries (such as xml.etree.ElementTree) to parse.
Recommended XML formatting tool
Apr 02, 2025 pm 09:03 PM
XML formatting tools can type code according to rules to improve readability and understanding. When selecting a tool, pay attention to customization capabilities, handling of special circumstances, performance and ease of use. Commonly used tool types include online tools, IDE plug-ins, and command-line tools.
