搭建目標(biāo)如下：
圖：系統(tǒng)架構(gòu)圖
這個(gè)系統(tǒng)可以提供web服務(wù)及其它查詢應(yīng)用服務(wù)，我用其做一個(gè)二手房信息搜集、處理及分發(fā)的系統(tǒng)，可以通過(guò)瀏覽器訪問(wèn)，也可以通過(guò)定制的客戶端進(jìn)行訪問(wèn)。
一、安裝篇
1、下載安裝python
# wget http://www.python.org/ftp/python/2.7.3/Python-2.7.3.tgz## tar xvfz Python-2.7.3.tgz# cd Python-2.7.3#./configure# make# sudo make install
下面是一些python安裝工具，可以方便的安裝所缺模塊
python的包管理setuptools安裝
# wget http://peak.telecommunity.com/dist/ez_setup.py# python ez_setup.py
python的包管理pip安裝（需要先安裝setuptools）
# wget http://python-distribute.org/distribute_setup.py# python distribute_setup.py# wget https://github.com/pypa/pip/raw/master/contrib/get-pip.py# python get-pip.py
下面使用pip 安裝readline
# sudo pip install readline
2、下載安裝Django
# wget https://www.djangoproject.com/download/1.4.3/tarball/## tar xvfz Django-1.4.3.tar.gz# cd Django-1.4.3# sudo python setup.py install
3、下載安裝MongoDB
l 先下載安裝scons
# wget http://sourceforge.net/projects/scons/files/scons/2.1.0.alpha.20101125/scons-2.1.0.alpha.20101125.tar.gz/download## tar xvfz scons-2.1.0.alpha.20101125.tar.gz# cd scons-2.1.0.alpha.20101125# sudo python setup.py install
l 下載安裝MongoDB
# wget http://downloads.mongodb.org/src/mongodb-src-r2.2.2.tar.gz## tar xvfz mongodb-src-r2.2.2.tar.gz# cd mongodb-src-r2.2.2# scons all# sudo scons --prefix=/usr/local/mongodb --full install
l 下載安裝pyMongo
# wget wget http://pypi.python.org/packages/source/p/pymongo/pymongo-2.4.2.tar.gz# # tar xvfz pymongo-2.4.2.tar.gz# cd pymondo-2.4.2# sudo python setup.py install
測(cè)試pyMongo是否安裝成功
# python> import pymongo
如果沒有返回錯(cuò)誤，則表明安裝成功。
l 下載安裝mongoengine【暫時(shí)沒有用到】
# wget http://github.com/mongoengine/mongoengine/tarball/v0.6.20 --no-check-certificate# # tar xvfz v0.6.20# cd MongoEngine-mongoengine-9cc6164# sudo python setup.py install
測(cè)試mongoengine是否安裝成功
# python> from mongoengine import connect
如果沒有返回錯(cuò)誤，則表明安裝成功。
4、下載安裝MySQL
l 先下載安裝cmake：
# wget http://www.cmake.org/files/v2.8/cmake-2.8.8.tar.gz## tar xvfz cmake-2.8.8.tar.gz# cd cmake-2.8.8#./configure# make# sudo make install
l 下載安裝mysql
# wget http://cdn.mysql.com/Downloads/MySQL-5.5/mysql-5.5.29.tar.gz## tar xvfz mysql-5.5.29.tar.gz# cd mysql-5.5.29# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/localmysql/data/ -DMYSQL_UNIX_ADDR=/usr/localmysql/data/mysqld.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DSYSCONFDIR=/etc -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_unicode_ci -DWITH_DEBUG=0# make# sudo make install
l 下載安裝mysql-python
# wget http://sourceforge.net/projects/mysql-python/files/mysql-python/1.2.3/MySQL-python-1.2.3.tar.gz# # tar xvfz MySQL-python-1.2.3.tar.gz# cd MySQL-python-1.2.3
在安裝前，需要修改site.py中mysql_config的路徑（為mysql安裝路徑下的/bin/mysql_config），
# site.pymysql_config = /usr/local/mysql/bin/mysql_config
更改完后，可以進(jìn)行編譯和安裝了
# python setup.py build# sudo python setup.py install
通過(guò)測(cè)試import MySQLdb來(lái)判斷是否安裝成功，這里還需要將mysql安裝路徑下的lib加入到環(huán)境變量LD_LIBRARY_PATH中。
# export LD_LIBRARY_PATH=/usr/local/mysql/lib/:$LD_LIBRARY_PATH
注：cmake選項(xiàng)說(shuō)明
選項(xiàng)
說(shuō)明
-DCMAKE_INSTALL_PREFIX
mysql安裝的主目錄。默認(rèn)為/usr/local/mysql
-DMYSQL_DATADIR
mysql數(shù)據(jù)保存的路徑自定義
-DMYSQL_UNIX_ADDR
系統(tǒng)Socket文件(.sock)設(shè)置基于該文件路徑進(jìn)行Socket連接必要為絕對(duì)路徑
-DWITH_INNOBASE_STORAGE_ENGINE
存儲(chǔ)引擎設(shè)置
-DSYSCONFDIR
mysql配置文件my.cnf地址默認(rèn)/etc下
-DMYSQL_TCP_PORT
數(shù)據(jù)庫(kù)服務(wù)器TCP/IP連接的監(jiān)聽端口默認(rèn)為3306
-DEXTRA_CHARSETS
-DDEFAULT_CHARSET
-DDEFAULT_COLLATION
數(shù)據(jù)庫(kù)編碼設(shè)置
-DENABLED_LOCAL_INFILE
默認(rèn)為關(guān)閉這里開啟
-DWITH_DEBUG
DEBUG開關(guān)，默認(rèn)為關(guān)
5、下載安裝uWsgi
# wget http://projects.unbit.it/downloads/uwsgi-1.2.3.tar.gz## tar xvfz uwsgi-1.2.3.tar.gz# cd uwsgi-1.2.3# python uwsgiconfig.py --build
二、配置篇
1、配置nginx(配置nginx.conf)
server { listen 8080; server_name django; location / { root /data/htdocs/django; include uwsgi_params; uwsgi_pass 127.0.0.1:8000; } access_log /data/htdocs/django/access.log; }
2、配置uWsgi
可以將uwsgi的配置文件做成ini格式的，也可以直接在命令行進(jìn)行輸入，下面給出了ini文件形式的配置
#uwsgi.ini[uwsgi]socket = 127.0.0.1:8000file=/data/htdocs/django/django_uwsgi.pypidfile = /data/htdocs/django/django_uwsgi.pidmaster = trueworkers = 4daemonize = /data/htdocs/django/django_uwsgi.log
其中django.py是我們需要自己定義的，它是用來(lái)將uwsgi與django進(jìn)行連接的。
#django_uwsgi.py#!/usr/bin/pythonimport os, sysfrom django.core.handlers.wsgi import WSGIHandlerif not os.path.dirname(__file__) in sys.path[:1]: sys.path.insert(0, os.path.dirname(__file__))os.environ['DJANGO_SETTINGS_MODULE'] = 'mysites.settings' #設(shè)置配置文件application = WSGIHandler() #調(diào)用django的處理函數(shù)WSGIHandler
3、配置mySQL
在安裝完成后，創(chuàng)建mysql用戶，并將mysql的目錄擁有者換成mysql和mysql所屬的group，并設(shè)置數(shù)據(jù)庫(kù)的用戶名和data的路徑。
# groupadd mysql # useradd -g mysql mysql # chown mysql.mysql -R /service/mysql/# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
將配置文件拷貝到/etc/下，并重命名為my.conf
# cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf
4、配置Django連接MySQL
在安裝完成后，需要?jiǎng)?chuàng)建運(yùn)行環(huán)境
# python manage.py startproject
執(zhí)行后，會(huì)在創(chuàng)建一個(gè)文件manage.py和一個(gè)目錄mysite，mysite目錄中有urls.py，__init__.py，settings.py和wsgi.py文件。我們通過(guò)修改settings.py文件中的部分配置來(lái)連接mysql數(shù)據(jù)庫(kù)。
假設(shè)在mysql中，創(chuàng)建了一個(gè)數(shù)據(jù)庫(kù)test_python，并添加了一個(gè)用戶名python_user且密碼為python_user，而我們連接地址為192.168.1.2的mysql服務(wù)器，端口為3306（默認(rèn)），則更改settings.py如下：
...DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'. 'NAME': 'test_python', # Or path to database file if using sqlite3. 'USER': 'python_user', # Not used with sqlite3. 'PASSWORD': 'python_user', # Not used with sqlite3. 'HOST': '192.168.1.2', # Set to empty string for localhost. Not used with sqlite3. 'PORT': '3306', # Set to empty string for default. Not used with sqlite3. }}...
通過(guò)django中的manage.py進(jìn)行驗(yàn)證
# python manage.py shell>> from django.db import connection>> cursor = connection.cursor()
如果成功，則表明連接數(shù)據(jù)庫(kù)成功，其余的關(guān)于django的使用在此不多介紹。
5、配置Django連接MongoDB
這里可以直接使用PyMongo模塊，也可以使用第三方的中間件mongoengine，PyMongo使用方法的介紹有很多，可以直接查看官方文檔
http://api.mongodb.org/python/current/api/pymongo/connection.html。
這里主要介紹mongoengine的配置方法
首先，要在settings中設(shè)置一個(gè)包含數(shù)據(jù)庫(kù)信息的別名，在連接時(shí)會(huì)用到
DATABASES = {...'MongoDB': { 'ENGINE': 'django_mongodb_engine', 'NAME':'test', }}...
其中NAME指的是database的名字。
如果你想使用 django 的 session 和 authentication 這兩個(gè)框架， 還要加入
# add sessionSESSION_ENGINE = 'mongoengine.django.sessions'# add authenticationAUTHENTICATION_BACKENDS = ('mongoengine.django.auth.MongoEngineBackend', )
然后就可以使用mongoengine了。
from mongoengine import *from mysite.settings import DATABASESconn = connect('MongoDB', ip="127.0.0.1", port=27017)
這里使用了settings中定義的別名'MongoDB'。
三、啟動(dòng)篇
1、啟動(dòng)Django服務(wù)
啟動(dòng)Django服務(wù)進(jìn)程
# python manage.py runserver 0.0.0.0:8000
2、啟動(dòng)mongoDB服務(wù)進(jìn)程
# /usr/local/mongodb/bin/mongod --port=27000 --dbpath=$HOME/data/ --logpath=$HOME/data/mongo.log
3、啟動(dòng)mysql服務(wù)
# /etc/init.d/mysqld start
四、實(shí)例篇
1、通過(guò)django的模板和mysql數(shù)據(jù)庫(kù)中的數(shù)據(jù)，生成一個(gè)包含人名及信息表格的html頁(yè)面
首先，我們先在數(shù)據(jù)庫(kù)中建立一個(gè)表peoples，并插入三條數(shù)據(jù)
mysql> create table peoples (id int auto_increment primary key, name char(30), age int, birth date);mysql> mysql> insert into peoples(name, age, birth) values('zhangsan', 30,' 1983-1-1'),('lisi', 29, '1984-1-1'), ('wangwu', 28, '1985-1-1');
然后做一個(gè)html頁(yè)面模板，名為peoples_list.html，內(nèi)容如下：
<html><meta http-equiv="Content-Type" content="text/html; charset=utf-8"/><head>Peoples List</head><body><br><br><table border="1"><tr><th>Name</th><th>Age</th><th>Birth</th></tr>{% for people in peoples_list %}<tr> <td>{{ people.0 }}</td> <td>{{ people.1 }}</td> <td>{{ people.2 }}</td></tr>{% endfor %}</table></body></html>
接下來(lái)是完成業(yè)務(wù)邏輯，保存在文件peoples.py中（使用了django自帶的數(shù)據(jù)庫(kù)管理模塊）
#!/bin/python#!/bin/python2# -*- coding: utf-8 -*- from django.db import connectionfrom django.shortcuts import render_to_responsedef peoples_list(request): cursor = connection.cursor() cursor.execute('select name,age,birth from peoples') peoples = cursor.fetchall()return render_to_response('peoples_list.html', {'peoples_list':peoples})
最后修改urls.py中的配置，標(biāo)紅的就是修改的內(nèi)容
from django.conf.urls import patterns, include, urlfrom peoples import peoples_list# Uncomment the next two lines to enable the admin:# from django.contrib import admin# admin.autodiscover()urlpatterns = patterns('', # Examples: # url(r'^$', 'mysite.views.home', name='home'), # url(r'^mysite/', include('mysite.foo.urls')), # Uncomment the admin/doc line below to enable admin documentation: # url(r'^admin/doc/', include('django.contrib.admindocs.urls')), # Uncomment the next line to enable the admin: # url(r'^admin/', include(admin.site.urls)), url(r'peoples_list/$', peoples_list),)
通過(guò)瀏覽器訪問(wèn)對(duì)應(yīng)的地址就能看到最終的結(jié)果
2、使用MySQLdb來(lái)完成上面的業(yè)務(wù)邏輯
業(yè)務(wù)邏輯保存在peoples_mysqldb.py中
#!/bin/python# -*- coding: utf8 -*-from django.shortcuts import render_to_responseimport MySQLdbdef peoples_list_mysqldb(request): conn = MySQLdb.connect(host='127.0.0.1', port=3306, user='python_user', passwd='python_user', db='test_python', charset='utf8') cursor = conn.cursor() sqlComm = "select name, age, birth from peoples" cursor.execute(sqlComm) peoples = cursor.fetchall() cursor.close() conn.close() return render_to_response('peoples_list.html', {'peoples_list':peoples})
修改urls.py
from django.conf.urls import patterns, include, url#from view import current_datetimefrom peoples_mysqldb import peoples_list_mysqldb# Uncomment the next two lines to enable the admin:# from django.contrib import admin# admin.autodiscover()urlpatterns = patterns('', # Examples: # url(r'^$', 'mysite.views.home', name='home'), # url(r'^mysite/', include('mysite.foo.urls')), # Uncomment the admin/doc line below to enable admin documentation: # url(r'^admin/doc/', include('django.contrib.admindocs.urls')), # Uncomment the next line to enable the admin: # url(r'^admin/', include(admin.site.urls)), url(r'peoples_list_mysqldb/$', peoples_list_mysqldb))
最終的結(jié)果為：
3、將數(shù)據(jù)庫(kù)數(shù)據(jù)以json形式返回
主要是業(yè)務(wù)邏輯代碼的編寫：test_json.py
# coding: utf-8#!/bin/pythonfrom django.utils import simplejsonfrom django.http import HttpResponsefrom django.db import connectiondef json_peoples(request): cursor = connection.cursor() cursor.execute('select name, age, birth from peoples') peoples = cursor.fetchall() i = 0 json_peoples = {} names = locals() for people in peoples: tag = 'person%s' % i names[tag] = {'name':people[0], 'age':people[1], 'birth':str(people[2])} json_peoples[tag] = names[tag] i = ((i+1)) json = {'person':i} json['person_info'] = json_peoples cursor.close() return HttpResponse(simplejson.dumps(json, ensure_ascii=False, sort_keys=True))
向urls中添加該對(duì)應(yīng)關(guān)系
from django.conf.urls import patterns, include, url#from view import current_datetimefrom json_test import json_peoples# Uncomment the next two lines to enable the admin:# from django.contrib import admin# admin.autodiscover()urlpatterns = patterns('', # Examples: # url(r'^$', 'mysite.views.home', name='home'), # url(r'^mysite/', include('mysite.foo.urls')), # Uncomment the admin/doc line below to enable admin documentation: # url(r'^admin/doc/', include('django.contrib.admindocs.urls')), # Uncomment the next line to enable the admin: # url(r'^admin/', include(admin.site.urls)), url(r'peoples_json/$', json_peoples))
最終效果為：
4、通過(guò)pymongo模塊訪問(wèn)mongodb，將結(jié)果返回成一個(gè)頁(yè)面
模板還是使用第一個(gè)例子的，只要重新寫一個(gè)業(yè)務(wù)邏輯即可mongodb_test.py
#!/bin/python2# -*- coding: utf-8 -*-from django.db import connectionfrom django.shortcuts import render_to_responsedef peoples_list(request): cursor = connection.cursor() cursor.execute('select name,age,birth from peoples') peoples = cursor.fetchall() print peoplesreturn render_to_response('peoples_list.html', {'peoples_list':peoples})
向urls.py中添加對(duì)應(yīng)關(guān)系
from django.conf.urls import patterns, include, url#from view import current_datetimefrom mongodb_test import mongodb_peoples# Uncomment the next two lines to enable the admin:# from django.contrib import admin# admin.autodiscover()urlpatterns = patterns('', # Examples: # url(r'^$', 'mysite.views.home', name='home'), # url(r'^mysite/', include('mysite.foo.urls')), # Uncomment the admin/doc line below to enable admin documentation: # url(r'^admin/doc/', include('django.contrib.admindocs.urls')), # Uncomment the next line to enable the admin: # url(r'^admin/', include(admin.site.urls)), url(r'peoples_mongo/$', mongodb_peoples))
最終結(jié)果為
五、性能
由于系統(tǒng)中有nginx，uwsgi，django，mysql和mongodb模塊，所以分別對(duì)幾種情況下做了一下簡(jiǎn)單的性能測(cè)試。
測(cè)試工具使用了SuperWebBench，具體介紹可以查看
http://www.oschina.net/p/superwebbench上的介紹。
測(cè)試環(huán)境：2核Intel(R) Xeon(R) CPU E5645，4G內(nèi)存，上述所有模塊在一臺(tái)服務(wù)器上運(yùn)行。
采用了并發(fā)500，持續(xù)30秒的測(cè)試壓力。
測(cè)試nginx：
./superwebbench -c 500 -t 30 http://127.0.0.1:8000/SuperWebBench - Advanced Simple Web Benchmark 0.1Copyright (c) Radim Kolar 1997-2004, GPL Open Source Software.Modified By Davelv 2011-11-03Benchmarking:GET http://127.0.0.1:8000/ (using HTTP/1.1)500 clients, running 30 sec.Speed=6080 pages/sec, 4998280 bytes/sec.Requests: 182419 ok, 0 http error, 0 failed.
測(cè)試nginx+uwsgi：（將uwsgi的文件指向一個(gè)直接返回http響應(yīng)的python腳本）
用于返回包含當(dāng)前時(shí)間的HTML頁(yè)面的Python腳本：
# coding: utf-8#!/usr/local/bin/pythonimport datetimedef application(environ, start_response): cur = datetime.datetime.now() response_body = """<html><meta http-equiv="Content-Type" content="text/html; charset=utf-8"/><head>Current Datetime</head><body>It is now %s</body></html>""" % cur status = '200 OK' response_headers = [('Content-Type', 'text/plain'), ('Content-Length', str(len(response_body)))] start_response(status, response_headers)return [response_body]
結(jié)果：
./superwebbench -c 500 -t 30 http://127.0.0.1:8000/SuperWebBench - Advanced Simple Web Benchmark 0.1Copyright (c) Radim Kolar 1997-2004, GPL Open Source Software.Modified By Davelv 2011-11-03Benchmarking:GET http://127.0.0.1:8000/ (using HTTP/1.1)500 clients, running 30 sec.Speed=4417 pages/sec, 1351734 bytes/sec.Requests: 132523 ok, 0 http error, 0 failed.
測(cè)試nginx+uwsgi+mysql：
用于返回包含mysql數(shù)據(jù)的HTML頁(yè)面的Python腳本：
# coding: utf-8#!/usr/local/bin/pythonimport datetimeimport MySQLdbdef application(environ, start_response): conn = MySQLdb.connect(host='127.0.0.1', port=3306, user='python_user', passwd='python_user', db='test_python', charset='utf8') cursor = conn.cursor() sqlComm = "select name, age, birth from peoples" cursor.execute(sqlComm) peoples = cursor.fetchall() cursor.close() conn.close() body = "<table border=\"1\"><tr><th>Name</th><th>Age</th><th>Birth</th></tr>" for people in peoples: person = "<tr><td>%s</td><td>%s</td><td>%s</td></tr>" % (str(people[0]), str(people[1]), str(people[2])) body = body + person body = body +"</table>" response_body = """<html><meta http-equiv="Content-Type" content="text/html; charset=utf-8"/><head>People List</head><body>%s</body></html>""" % body status = '200 OK' print response_body response_headers = [('Content-Type', 'text/plain'), ('Content-Length', str(len(response_body)))] print response_headers start_response(status, response_headers) return [response_body]
結(jié)果
./superwebbench -c 500 -t 30 http://127.0.0.1:8000/SuperWebBench - Advanced Simple Web Benchmark 0.1Copyright (c) Radim Kolar 1997-2004, GPL Open Source Software.Modified By Davelv 2011-11-03Benchmarking:GET http://127.0.0.1:8000/ (using HTTP/1.1)500 clients, running 30 sec.Speed=1078 pages/sec, 539381 bytes/sec.Requests: 32345 ok, 13 http error, 0 failed.
測(cè)試nginx+uwsgi+django：
./superwebbench -c 500 -t 30 http://127.0.0.1:8000/time/SuperWebBench - Advanced Simple Web Benchmark 0.1Copyright (c) Radim Kolar 1997-2004, GPL Open Source Software.Modified By Davelv 2011-11-03Benchmarking:GET http://127.0.0.1:8000/time/ (using HTTP/1.1)500 clients, running 30 sec.Speed=652 pages/sec, 176182 bytes/sec.Requests: 19558 ok, 7 http error, 0 failed.
測(cè)試nginx+uwsgi+django+mysql：
./superwebbench -c 500 -t 30 http://127.0.0.1:8000/peoples_list/SuperWebBench - Advanced Simple Web Benchmark 0.1Copyright (c) Radim Kolar 1997-2004, GPL Open Source Software.Modified By Davelv 2011-11-03Benchmarking:GET http://127.0.0.1:8000/peoples_list/ (using HTTP/1.1)500 clients, running 30 sec.Speed=321 pages/sec, 204044 bytes/sec.Requests: 9615 ok, 23 http error, 0 failed.
測(cè)試nginx+uwsgi+django+mongodb:
./superwebbench -c 500 -t 30 http://127.0.0.1:8000/peoples_mongo/SuperWebBench - Advanced Simple Web Benchmark 0.1Copyright (c) Radim Kolar 1997-2004, GPL Open Source Software.Modified By Davelv 2011-11-03Benchmarking:GET http://127.0.0.1:8000/peoples_mongo/ (using HTTP/1.1)500 clients, running 30 sec.Speed=355 pages/sec, 221449 bytes/sec.Requests: 10648 ok, 15 http error, 0 failed.
總結(jié)一下，可以看出nginx的處理速度極快，而uwsgi同樣也不慢，最大的瓶頸在于django，效率大概下降了70%多，而數(shù)據(jù)庫(kù)查詢（無(wú)論是mysql還是mongodb）也對(duì)效率有一定影響。
當(dāng)然，這只是把所有服務(wù)都部署在一臺(tái)服務(wù)器上，對(duì)資源的搶占也影響了系統(tǒng)的效率。
六、其它介紹
1、編碼問(wèn)題
需要注意編碼問(wèn)題，否則會(huì)出現(xiàn)亂碼或者執(zhí)行錯(cuò)誤。
有四個(gè)部分需要統(tǒng)一編碼格式（以u(píng)tf8為例）：
（1） mysql數(shù)據(jù)庫(kù)的編碼設(shè)置(charset = ‘utf8’)
（2） python文件的編碼設(shè)置（# -*- coding:utf8 -*-）
（3） 連接mysql數(shù)據(jù)庫(kù)時(shí)要加上參數(shù)charset=’utf8’
（4） 如果使用django，則需要在settings.py中添加DEFAULT_CHARSET = 'utf8'。
2、Python通過(guò)MySQLdb對(duì)MySQL的操作
導(dǎo)入MySQLdb模塊
import MySQLdb
與數(shù)據(jù)庫(kù)建立連接
conn=MySQLdb.connect([host="localhost",][port=3306,] user="root", passwd="passwd",db="database_name"[, charset=’utf8’])
其中host為mysql主機(jī)名，port為端口號(hào)，user為用戶名，passwd為密碼，db為數(shù)據(jù)庫(kù)名，charset為編碼類型
獲取游標(biāo)
cursor = conn.cursor()
數(shù)據(jù)庫(kù)命令
插入命令
insertComm = ‘insert into table_name(...) values(...)’cursor.execute(insertComm,...)
如：（注意最后要調(diào)用commit來(lái)提交這次命令）
insertComm = 'insert into peoples(name, age, birth) values(%s, %s, %s)'param = ('zhengliu', 27, '1986-1-1')cursor.execute(insertComm, param)conn.commit()
更新命令
updateComm = ‘update table_name set column1=value1[,...] where column=value[,...]’cursor.execute(updateComm)
如：（注意最后要調(diào)用commit來(lái)提交這次命令）
updateComm = "update peoples set age=%s,birth=%s where name='zhengliu'"param = (26, '1987-1-1')cursor.execute(updateComm, param)conn.commit()
刪除命令
deleteComm = ‘delete from table_name where column1=value1[,...]’cursor.execute(deleteComm)
如：（注意最后要調(diào)用commit來(lái)提交這次命令）
deleteComm = "delete from peoples where name=%s"param=('zhengliu')cursor.execute(deleteComm, param)conn.commit()
查詢命令
selectComm = ‘select name, age, birth from peoples [where column1=values1,...]’cursor.execute(selectComm)result = cursor.fetchall()
如：
queryComm = 'select name, age, birth from peoples'cursor.execute(queryComm)peoples = cursor.fetchall()
提交和回滾
在對(duì)數(shù)據(jù)庫(kù)進(jìn)行修改操作時(shí)，需要進(jìn)行commit命令來(lái)最終提交數(shù)據(jù)庫(kù)，如果想要取消這次操作，則要在commit前先調(diào)用rollback進(jìn)行回滾操作。
conn.commit()conn.rollback()
關(guān)閉命令
關(guān)閉游標(biāo)
cursor.close()
關(guān)閉連接
conn.close()
cursor游標(biāo)對(duì)象屬性及方法
屬性方法
描述
arraysize
使用fetchmany()方法時(shí)一次取出的記錄數(shù)，默認(rèn)為1
connection
創(chuàng)建此游標(biāo)的連接（可選）
discription
返回游標(biāo)的活動(dòng)狀態(tài)，包括（7元素）：(name,type_code, display_size,internal_size,precision,scale,null_ok) 其中name,type_code是必須的。
lastrowid
返回最后更新行的ID（可選），如果數(shù)據(jù)庫(kù)不支持，返回None
rowcount
最后一次execute()返回或影響的行數(shù)
callproc(func[,args])
調(diào)用一個(gè)存儲(chǔ)過(guò)程
close()
關(guān)閉游標(biāo)
execute(op[,args])
執(zhí)行sql語(yǔ)句或數(shù)據(jù)庫(kù)命令
executemany(op,args)
一次執(zhí)行多條sql語(yǔ)句，執(zhí)行的條數(shù)由arraysize給出
fetchone()
匹配結(jié)果的下一行
fetchall()
匹配所有剩余結(jié)果
fetchmany(size-cursor,arraysize)
匹配結(jié)果的下幾行
__iter__()
創(chuàng)建迭代對(duì)象(可選，參考next())
messages
游標(biāo)執(zhí)行好數(shù)據(jù)庫(kù)返回的信息列表（元組集合）
next()
使用迭代對(duì)象得到結(jié)果的下一行
nextset()
移動(dòng)到下一個(gè)結(jié)果集（如果支持的話）
rownumber
當(dāng)前結(jié)果集中游標(biāo)的索引（從0行開始）
setinput-size(sizes)
設(shè)置輸入最大值
setoutput-size(sizes[,col])
設(shè)置列輸出的緩沖值
