打开flower的监控页面,发现monitor页的succeeded tasks图表始终为空,打印日志发现有下面的警告:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[2016-02-24 12:00:21,799: WARNING/MainProcess] celery@localhost.localdomain ready.
[2016-02-24 12:00:22,297: WARNING/MainProcess] Substantial drift from celery@centos7-181 may mean clocks are out of sync. Current drift is
70 seconds. [orig: 2016-02-24 12:00:22.297798 recv: 2016-02-24 11:59:12.438481]
[2016-02-24 12:00:22,300: WARNING/MainProcess] Substantial drift from celery@centos7-xiaoqiao may mean clocks are out of sync. Current drift is
764 seconds. [orig: 2016-02-24 12:00:22.300171 recv: 2016-02-24 11:47:38.863792]
[2016-02-24 12:00:22,302: WARNING/MainProcess] Substantial drift from celery@centos7-186 may mean clocks are out of sync. Current drift is
65 seconds. [orig: 2016-02-24 12:00:22.302378 recv: 2016-02-24 11:59:17.157844]
[2016-02-24 12:00:22,303: WARNING/MainProcess] Substantial drift from celery@centos7-182 may mean clocks are out of sync. Current drift is
70 seconds. [orig: 2016-02-24 12:00:22.303616 recv: 2016-02-24 11:59:12.633749]
[2016-02-24 12:00:22,306: WARNING/MainProcess] Substantial drift from celery@centos7-189 may mean clocks are out of sync. Current drift is
64 seconds. [orig: 2016-02-24 12:00:22.306499 recv: 2016-02-24 11:59:18.100351]
[2016-02-24 12:00:22,307: WARNING/MainProcess] Substantial drift from celery@centos7-188 may mean clocks are out of sync. Current drift is
65 seconds. [orig: 2016-02-24 12:00:22.307066 recv: 2016-02-24 11:59:17.197479]
[2016-02-24 12:00:22,310: WARNING/MainProcess] Substantial drift from celery@centos7-184 may mean clocks are out of sync. Current drift is
64 seconds. [orig: 2016-02-24 12:00:22.309941 recv: 2016-02-24 11:59:18.490792]
[2016-02-24 12:00:22,524: WARNING/MainProcess] Substantial drift from celery@centos7-183 may mean clocks are out of sync. Current drift is
64 seconds. [orig: 2016-02-24 12:00:22.524618 recv: 2016-02-24 11:59:18.005931]
[2016-02-24 12:00:22,525: WARNING/MainProcess] Substantial drift from celery@centos7-187 may mean clocks are out of sync. Current drift is
70 seconds. [orig: 2016-02-24 12:00:22.525161 recv: 2016-02-24 11:59:12.294425]
[2016-02-24 12:00:22,525: WARNING/MainProcess] Substantial drift from celery@centos7-185 may mean clocks are out of sync. Current drift is
65 seconds. [orig: 2016-02-24 12:00:22.525489 recv: 2016-02-24 11:59:17.309409]

字面看是由于时间不同步造成的,可是在settings里已经设置了USE_TZ = True以及相应的时区选项,经过一番费力的寻找发现造成这个问题的原因居然是每台机器的时间不同!至于为什么机器设置为同一时区却有时间差就不探究了,解决起来就是把各个机器的时间同步就可以了:

1
2
yum install ntpdate -y
ntpdate 0.asia.pool.ntp.org

网上很多Ntp服务器都失效了,可以使用下面的:

1
2
3
4
0.asia.pool.ntp.org
1.asia.pool.ntp.org
2.asia.pool.ntp.org
3.asia.pool.ntp.org

另外最好设置个计划任务定时去同步。

评论和分享

之前的博客中简单的介绍了celery的安装配置以及如何在python程序中使用,这里记录一下我使用django结合celery以及rabbitmq提供web服务,同时使用flower进行监控的过程。至于这几样东西是什么、怎么安装这里就不再细说了。

阅读全文

django性能分析

发布在 Django

一般情况我们使用django-debug-toolbar就能够看到每个步骤的耗时等信息,不过如果需要调试某个接口就不那么直观了,这种情况下我们可以使用下面的中间件来解决问题:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
# Orignal version taken from http://www.djangosnippets.org/snippets/186/
# Original author: udfalkso
# Modified by: Shwagroo Team and Gun.io
import sys
import os
import re
import hotshot, hotshot.stats
import tempfile
import StringIO
from django.conf import settings
words_re = re.compile( r'\s+' )
group_prefix_re = [
re.compile( "^.*/django/[^/]+" ),
re.compile( "^(.*)/[^/]+$" ), # extract module path
re.compile( ".*" ), # catch strange entries
]
class ProfileMiddleware(object):
"""
Displays hotshot profiling for any view.
http://yoursite.com/yourview/?prof
Add the "prof" key to query string by appending ?prof (or &prof=)
and you'll see the profiling results in your browser.
It's set up to only be available in django's debug mode, is available for superuser otherwise,
but you really shouldn't add this middleware to any production configuration.
WARNING: It uses hotshot profiler which is not thread safe.
"""
def process_request(self, request):
if (settings.DEBUG or request.user.is_superuser) and 'prof' in request.GET:
self.tmpfile = tempfile.mktemp()
self.prof = hotshot.Profile(self.tmpfile)
def process_view(self, request, callback, callback_args, callback_kwargs):
if (settings.DEBUG or request.user.is_superuser) and 'prof' in request.GET:
return self.prof.runcall(callback, request, *callback_args, **callback_kwargs)
def get_group(self, file):
for g in group_prefix_re:
name = g.findall( file )
if name:
return name[0]
def get_summary(self, results_dict, sum):
list = [ (item[1], item[0]) for item in results_dict.items() ]
list.sort( reverse = True )
list = list[:40]
res = " tottime\n"
for item in list:
res += "%4.1f%% %7.3f %s\n" % ( 100*item[0]/sum if sum else 0, item[0], item[1] )
return res
def summary_for_files(self, stats_str):
stats_str = stats_str.split("\n")[5:]
mystats = {}
mygroups = {}
sum = 0
for s in stats_str:
fields = words_re.split(s);
if len(fields) == 7:
time = float(fields[2])
sum += time
file = fields[6].split(":")[0]
if not file in mystats:
mystats[file] = 0
mystats[file] += time
group = self.get_group(file)
if not group in mygroups:
mygroups[ group ] = 0
mygroups[ group ] += time
return "<pre>" + \
" ---- By file ----\n\n" + self.get_summary(mystats,sum) + "\n" + \
" ---- By group ---\n\n" + self.get_summary(mygroups,sum) + \
"</pre>"
def process_response(self, request, response):
if (settings.DEBUG or request.user.is_superuser) and 'prof' in request.GET:
self.prof.close()
out = StringIO.StringIO()
old_stdout = sys.stdout
sys.stdout = out
stats = hotshot.stats.load(self.tmpfile)
stats.sort_stats('time', 'calls')
stats.print_stats()
sys.stdout = old_stdout
stats_str = out.getvalue()
if response and response.content and stats_str:
response.content = "<pre>" + stats_str + "</pre>"
response.content = "\n".join(response.content.split("\n")[:40])
response.content += self.summary_for_files(stats_str)
os.unlink(self.tmpfile)
return response

把这个文件保存为middleware.py,然后在settings.py中的MIDDLEWARE_CLASSES添加刚才写好的中间件
'yourproject.yourapp.middleware.ProfileMiddleware',
添加完成后,在原本要访问的url后添加’?prof’就可以看到性能分析结果了:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<pre>         202188 function calls (182154 primitive calls) in 2.948 seconds
Ordered by: internal time, call count
ncalls tottime percall cumtime percall filename:lineno(function)
302 2.444 0.008 2.454 0.008 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/MySQLdb/cursors.py:277(_do_query)
2 0.086 0.043 0.104 0.052 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/MySQLdb/connections.py:62(__init__)
2 0.017 0.009 0.017 0.009 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/MySQLdb/connections.py:287(set_character_set)
2 0.016 0.008 0.016 0.008 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/django/db/backends/mysql/base.py:299(_set_autocommit)
302 0.008 0.000 2.488 0.008 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/django/db/backends/utils.py:76(execute)
19410 0.008 0.000 0.008 0.000 /usr/lib64/python2.7/copy.py:267(_keep_alive)
10237 0.008 0.000 0.008 0.000 /usr/lib64/python2.7/json/encoder.py:33(encode_basestring)
600 0.008 0.000 0.012 0.000 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/django/db/models/sql/query.py:246(clone)
6 0.007 0.001 0.007 0.001 /usr/lib64/python2.7/urlparse.py:336(unquote)
500 0.007 0.000 2.712 0.005 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/django/db/models/query.py:229(iterator)
1152 0.005 0.000 0.029 0.000 /usr/lib64/python2.7/copy.py:253(_deepcopy_dict)
1 0.005 0.005 0.014 0.014 /usr/lib64/python2.7/json/encoder.py:212(iterencode)
302 0.005 0.000 0.010 0.000 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/MySQLdb/cursors.py:117(_do_get_result)
300 0.005 0.000 0.121 0.000 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/django/db/models/sql/compiler.py:351(as_sql)
300 0.004 0.000 0.033 0.000 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/django/db/models/sql/query.py:1114(build_filter)
302 0.004 0.000 0.004 0.000 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/MySQLdb/cursors.py:313(_get_result)
1 0.003 0.003 0.017 0.017 /usr/lib64/python2.7/json/encoder.py:186(encode)
1300 0.003 0.000 0.004 0.000 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/django/db/backends/mysql/operations.py:176(get_db_converters)
600 0.003 0.000 0.006 0.000 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/django/db/models/sql/query.py:1334(names_to_path)
300 0.003 0.000 0.004 0.000 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/django/db/models/sql/query.py:110(__init__)
302 0.003 0.000 0.003 0.000 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/MySQLdb/cursors.py:82(_warning_check)
200 0.003 0.000 0.004 0.000 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/django/db/models/base.py:388(__init__)
302 0.003 0.000 2.469 0.008 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/MySQLdb/cursors.py:139(execute)
300 0.003 0.000 0.022 0.000 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/django/db/models/sql/compiler.py:155(get_select)
300 0.003 0.000 0.007 0.000 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/django/db/models/sql/compiler.py:472(get_default_columns)
908 0.003 0.000 0.003 0.000 /home/xsy/.virtualenvs/calc/lib64/python2.7/encodings/utf_8.py:15(decode)
3500 0.003 0.000 0.005 0.000 /home/xsy/.virtualenvs/calc/lib/python2.7/site-packages/django/db/models/sql/compiler.py:325(quote_name_unless_alias)
<pre> ---- By file ----

简单解释下主要的信息,ncalls是函数调用的次数,percall指调用一次所需时间,cumtime是运行时占用的总时间。比如percall时间很短而cumtime时间很长,则代表循环次数过多了,试着从算法角度进行优化;如果从输出中看到大量时间消耗和“sql”有关的,则试着从数据库相关代码优化;如果看见大量时间消耗和”template”有关,则尝试从模板渲染处着手优化。

评论和分享

django-userena使用记录

发布在 Django

django-userena扩展了django原生的用户系统,提供了注册、登录、修改密码、邮件验证等一系列常用功能。直接使用pip安装即可:pip install django-userena

会自动安装其所需的依赖包,不过个人建议为了更好的定制模板或相关功能,把这个包放到项目目录下当作一个app更方便一些。安装完成后修改settings.py,首先来创建一个app用于扩展用户系统
python manage.py startapp accounts
然后修改Models.py来扩展原生用户字段,我这里以添加用户等级为例:

1
2
3
4
5
6
7
8
9
10
11
# coding=utf-8
from django.db import models
from django.contrib.auth.models import User
from django.utils.translation import ugettext as _
from userena.models import UserenaBaseProfile
class CustomerProfile(UserenaBaseProfile):
user = models.OneToOneField(User,
unique=True,
verbose_name=_('user'),
related_name='customer_profile')
level = models.IntegerField(_(u"用户等级"), default=0)

接下来修改settings.py,首先把

1
2
3
4
5
"django.contrib.sites",
"accounts",
'userena',
'guardian',
'easy_thumbnails'

添加到INSTALLED_APPS中,然后把'django.contrib.sites.middleware.CurrentSiteMiddleware',
添加到MIDDLEWARE_CLASSES中,在django1.7.7中如果不添加和site有关的东西时,userena注册用户会抛出”Site matching query does not exist“异常。最后添加下面的配置:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
AUTHENTICATION_BACKENDS = (
'userena.backends.UserenaAuthenticationBackend',
'guardian.backends.ObjectPermissionBackend',
'django.contrib.auth.backends.ModelBackend',
)
EMAIL_HOST = 'xxxx'
EMAIL_HOST_USER = 'xxxx@xxxx'
EMAIL_HOST_PASSWORD = 'xxxx'
EMAIL_PORT = 25
DEFAULT_FROM_EMAIL = 'xxxx@xxxx'
ANONYMOUS_USER_ID = -1
AUTH_PROFILE_MODULE = 'accounts.CustomerProfile'
USERENA_SIGNIN_REDIRECT_URL = '/accounts/%(username)s/'
LOGIN_URL = '/accounts/signin/'
LOGOUT_URL = '/accounts/signout/'

xxxx根据实际情况修改,和发送验证邮件有关。这些都添加完毕后,执行
./manage.py migrate
创建数据库,接下来执行
./manage.py check_permissions
否则报错”Permission matching query does not exist“。最后记得添加url

1
url(r'^accounts/', include('userena.urls')),

都完成后,运行程序,访问http://127.0.0.1:8000/accounts/signup/就可以看见注册页面了。

所有html文件以及email模板都在userena/templates/userena路径下,进行相应定制即可。

评论和分享

最近有个需求就是当执行save时需要把数据写入2个数据库,查看文档后发现直接重写save方法比较简单。

首先建立2个测试数据库testa和testb,然后在settings中配置数据库:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'testa',
'USER': 'root',
'PASSWORD': 'asdasd',
'HOST': '192.168.0.17',
},
'testdb': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'testb',
'USER': 'root',
'PASSWORD': 'asdasd',
'HOST': '192.168.0.18',
},
}

接下来定义model:

1
2
3
4
5
6
7
8
9
10
11
from django.db import models
# Create your models here.
class ABCD(models.Model):
name = models.CharField(max_length=200, unique=False)
def __unicode__(self):
return "%s" % (self.name)
def save(self, *args, **kwargs):
print "save testa"
super(ABCD, self).save(*args, **kwargs)
print "save testb"
super(ABCD, self).save(using="testdb", *args, **kwargs)

注意最后一行的using指定了写入哪个库中,接下来执行syncdb命令创建表,这里只会在default中创建,所以我们手动在testdb中创建表。

然后进入shell中测试一下:

1
2
3
4
5
6
7
8
9
10
11
12
13
% python manage.py shell
Python 2.7.8 (default, Nov 10 2014, 08:19:18)
Type "copyright", "credits" or "license" for more information.
IPython 2.3.0 -- An enhanced Interactive Python.
? -> Introduction and overview of IPython's features.
%quickref -> Quick reference.
help -> Python's own help system.
object? -> Details about 'object', use 'object??' for extra details.
In [1]: from abcd.models import ABCD
In [2]: c = ABCD(name="testc")
In [3]: c.save()
save testa
save testb

再看数据库中,插入都成功了。这里可能产生的问题就是大量并发请求时造成主键的不一致,但对于写操作不频繁的情况下也是一种可以接受的选择。

评论和分享

Nginx+Uwsgi部署Django程序

发布在 Linux

原来使用apache进行部署,感觉内存占用大以及速度比较慢就换成了nginx+uwsgi的方式,结果完爆apache啊!

首先安装nginx以及uwsgi:

1
2
yum install nginx
pip install uwsgi

然后编辑uwsgi的配置文件,这里我使用ini文件格式,示例如下:

1
2
3
4
5
6
7
8
9
[uwsgi]
socket = 127.0.0.1:9000
chdir = /var/www/html/test
pidfile = /var/run/db_uwsgi.pid
daemonize = /var/log/db_uwsgi.log
wsgi-file = /var/www/html/test/wsgi.py
processes = 4
threads = 2
stats = 127.0.0.1:9191

根据实际情况修改程序目录、进程、线程,还有更高级的配置选项细节看uwsgi的文档。

启动:uwsgi ini路径

停止:uwsgi –stop pidfile路径

重起:uwsgi –reload pidfile路径

nginx配置示例如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
server {
listen 800;
server_name 192.168.2.42 ;
access_log /var/log/nginx-dc-access_log;
error_log /var/log/nginx-dc-error.log;
charset utf-8;
default_type text/html;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $remote_addr;
set_real_ip_from 192.168.2.0/24;
set_real_ip_from 192.168.2.42;
real_ip_header X-Real-IP;
location / {
uwsgi_pass 127.0.0.1:9000;
include uwsgi_params;
}
location /static {
root /var/www/html/test;
}
}

更多高级配置请看nginx文档。

配置完成后启动nginx以及uwsgi即可:

1
2
3
service nginx start
uwsgi test.ini
[uWSGI] getting INI configuration from test.ini

如果你需要使用脚本方式进行uwsgi的重启,记得stop后sleep3秒钟左右再启动,否则重启不一定成功。

1
2
3
4
5
6
echo "stop uwsgi"
uwsgi --stop /var/run/db_uwsgi.pid
echo "wait 5's"
sleep 5
uwsgi /var/www/html/test/test.ini
echo "start uwsgi"

评论和分享

django自带的评论模块

发布在 Django

某个设计本来采用了一个比较不错的在线评论模块,不过答辩的时候丫居然不给网!!想偷个懒还是挺难啊….

那就用自带的评论模块吧,django版本是1.5.3。

首先把'django.contrib.comments'添加到INSTALLED_APPS中,然后添加url:

1
2
3
4
5
urlpatterns = patterns('',
......
url(r'^comments/', include('django.contrib.comments.urls')),
......
)

在需要显示评论的HTML中:

1
2
3
{% load comments %}
{% render_comment_list for post %} <!--显示所有属于这个文章的评论-->
{% render_comment_form for post %} <!--显示自带的添加评论的form-->

不过自带的添加评论实在太丑了,我们可以自己改造一下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
{% get_comment_form for post as form %}
<form action='{%comment_form_target%}' method='post'>
{% csrf_token %}
{{form.object_pk}}
{{form.content_type}}
{{form.timestamp}}
{{form.security_hash}}
<p><label for="id_name">姓名(必填):</label><input name="name" id="id_name"></p>
<p><label for="id_email">邮箱(必填):</label><input name="email" id="id_email"></p>
<p><label for="id_url">网站(可选):</label><input name="url" id="id_url"></p>
<p><label for="id_comment">评论(必填):</label></p>
<p><textarea id="id_comment" rows="10" cols="40" name="comment"></textarea></p>
<p style="display:none;"><label for="id_honeypot">垃圾评论。</label>
<input type="text" name="honeypot" id="id_honeypot"></p>
<p><input name="post" value="发表" type="submit" /></p>
<input type='hidden' name='next' value="/blog/post/{{post.id}}"/> <!--这个是提交后的重定向,使用软编码总报错...-->
</form>

然后再进行CSS或者JS一类的特效美化就OK了。

评论和分享

自己封装了一个logging在django中使用,结果发现输出的时候总是重复输出,比如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DEBUG---- 2014/03/31 10:38:13:
!!
DEBUG---- 2014/03/31 10:38:13:
!!
DEBUG---- 2014/03/31 10:38:13:
!!
DEBUG---- 2014/03/31 10:38:13:
!!
DEBUG---- 2014/03/31 10:38:13:
140091903388560
DEBUG---- 2014/03/31 10:38:13:
140091903388560
DEBUG---- 2014/03/31 10:38:13:
140091903388560
DEBUG---- 2014/03/31 10:38:13:
140091903388560

可以看出,id都是一样的。而且我查了一下,正好4个地方引入了mylog。mylog的完整代码见以前日志,这里给出导致错误的原因:
self.logger = logging.getLogger(__name__)

简单测试:

1
2
3
4
5
6
mlog = MyLog()
print id(mlog)
print id(mlog.logger)
xmlog = MyLog()
print id(xmlog)
print id(xmlog.logger)

结果如下:

1
2
3
4
123456666
123456789
123457777
123456789

可以看出虽然类id不同,不过核心的logger却是一个。因为我们执行runserver后,__name__就确定为mylog了,所以不管我们引用的时候如何写,__name__不变则都指向了同一个logger。更简单的例子如下:

1
2
3
4
5
6
In [1]: x = 1
In [2]: y = 1
In [3]: id(x)
Out[3]: 11279464
In [4]: id(y)
Out[4]: 11279464

更细节的原因可以看《python源码分析》。

改起来就让__name__不一样就解决了。比如:

self.logger = logging.getLogger(str(uuid.uuid4()))

评论和分享

今天把django从1.5.5升级到了1.6.2,结果使用mysql-python查询数据库时候就报了这个错误:“not all arguments converted during string formatting”

貌似这个问题应该和django没什么关系,正好今天就看看mysql-python的源码吧。想看如何解决的请直接跳到最后。

查询部分简化后如下:

1
2
3
posistion = "top"
order = r"select id,p_name,p_explain,p_href from pictures where p_position = %s and p_show = 1 order by -id"
self.cur.execute(order, position)

mysql-python中execute函数:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
def execute(self, query, args=None):
"""Execute a query.
query -- string, query to execute on server
args -- optional sequence or mapping, parameters to use with query.
Note: If args is a sequence, then %s must be used as the
parameter placeholder in the query. If a mapping is used,
%(key)s must be used as the placeholder.
Returns long integer rows affected, if any
"""
del self.messages[:]
db = self._get_db()
if isinstance(query, unicode):
query = query.encode(db.unicode_literal.charset)
if args is not None:
if isinstance(args, dict):
query = query % dict((key, db.literal(item))
for key, item in args.iteritems())
else:
query = query % tuple([db.literal(item) for item in args]) ##########################这里!!
try:
r = None
r = self._query(query)
except TypeError, m:
if m.args[0] in ("not enough arguments for format string",
"not all arguments converted"):
self.messages.append((ProgrammingError, m.args[0]))
self.errorhandler(self, ProgrammingError, m.args[0])
else:
self.messages.append((TypeError, m))
self.errorhandler(self, TypeError, m)
except (SystemExit, KeyboardInterrupt):
raise
except:
exc, value, tb = sys.exc_info()
del tb
self.messages.append((exc, value))
self.errorhandler(self, exc, value)
self._executed = query
if not self._defer_warnings: self._warning_check()
return r

错误地方就是后面加了#的语句。

可以看到,因为字符串也是可序列化的,所以一个字符串被拆分成了单个的字符进行db.literal()处理。这个db是什么呢?

往上找:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
def __init__(self, connection):
from weakref import proxy
self.connection = proxy(connection)
self.description = None
self.description_flags = None
self.rowcount = -1
self.arraysize = 1
self._executed = None
self.lastrowid = None
self.messages = []
self.errorhandler = connection.errorhandler
self._result = None
self._warnings = 0
self._info = None
self.rownumber = None
def _get_db(self):
if not self.connection:
self.errorhandler(self, ProgrammingError, "cursor closed")
return self.connection
```
可以看出,这个db就是connection的一个弱引用。再进一步,这个connnection哪来的呢?

```python
def open(self):
""" 返回一个默认的数据库连接游标 """
try:
self.conn = MySQLdb.Connection(
self.db_ip,
self.db_un,
self.db_pd,
self.db_db,
charset=self.db_ch)
self.cur = self.conn.cursor()
except MySQLdb.Error as e:
print e
`

mysqldb-python的connection.py中:

1
2
3
class Connection(_mysql.connection):
"""MySQL Database Connection Object"""
default_cursor = cursors.Cursor

那这个Cursor又是什么呢?

1
2
3
4
class Cursor(CursorStoreResultMixIn, CursorTupleRowsMixIn,
BaseCursor):
"""This is the standard Cursor class that returns rows as tuples
and stores the result set in the client."""

这个Cursor这些类的子类,其中最上面出错函数就是BaseCursor的一个方法。

有点跑偏,先看看那literal函数吧:

1
2
3
4
5
6
7
8
9
10
11
def literal(self, o):
print o,type(o)
print self.encoders
"""
If o is a single object, returns an SQL literal as a string.
If o is a non-string sequence, the items of the sequence are
converted and returned as a sequence.
Non-standard. For internal use; do not use this in your
applications.
"""
return self.escape(o, self.encoders)

print是我添加的,输出如下:

1
2
3
4
5
6
t <type 'str'>
{<type 'instance'>: <function Instance2Str at 0x17b3aa0>, <type 'set'>: <function Set2Str at 0x17b3cf8>, <type 'long'>: <function Thing2Str at 0x17b3de8>, <type 'bool'>: <function Bool2Str at 0x17b3ed8>, <type 'unicode'>: <function unicode_literal at 0x7f12e42c4230>, <type 'str'>: <function string_literal at 0x7f12e42be2a8>, <type 'float'>: <function Float2Str at 0x17b3c08>, <type 'list'>: <function quote_tuple at 0x17b3938>, <type 'tuple'>: <function quote_tuple at 0x17b3938>, <type 'array.array'>: <function array2Str at 0x17b39b0>, <type 'NoneType'>: <function None2NULL at 0x17b3b90>, <type 'datetime.datetime'>: <function DateTime2literal at 0x17b30c8>, <type 'dict'>: <built-in function escape_dict>, <type 'object'>: <function Instance2Str at 0x17b3aa0>, <type 'int'>: <function Thing2Str at 0x17b3de8>, <type 'datetime.timedelta'>: <function DateTimeDelta2literal at 0x17b3140>}
o <type 'str'>
{<type 'instance'>: <function Instance2Str at 0x17b3aa0>, <type 'set'>: <function Set2Str at 0x17b3cf8>, <type 'long'>: <function Thing2Str at 0x17b3de8>, <type 'bool'>: <function Bool2Str at 0x17b3ed8>, <type 'unicode'>: <function unicode_literal at 0x7f12e42c4230>, <type 'str'>: <function string_literal at 0x7f12e42be2a8>, <type 'float'>: <function Float2Str at 0x17b3c08>, <type 'list'>: <function quote_tuple at 0x17b3938>, <type 'tuple'>: <function quote_tuple at 0x17b3938>, <type 'array.array'>: <function array2Str at 0x17b39b0>, <type 'NoneType'>: <function None2NULL at 0x17b3b90>, <type 'datetime.datetime'>: <function DateTime2literal at 0x17b30c8>, <type 'dict'>: <built-in function escape_dict>, <type 'object'>: <function Instance2Str at 0x17b3aa0>, <type 'int'>: <function Thing2Str at 0x17b3de8>, <type 'datetime.timedelta'>: <function DateTimeDelta2literal at 0x17b3140>}
p <type 'str'>
{<type 'instance'>: <function Instance2Str at 0x17b3aa0>, <type 'set'>: <function Set2Str at 0x17b3cf8>, <type 'long'>: <function Thing2Str at 0x17b3de8>, <type 'bool'>: <function Bool2Str at 0x17b3ed8>, <type 'unicode'>: <function unicode_literal at 0x7f12e42c4230>, <type 'str'>: <function string_literal at 0x7f12e42be2a8>, <type 'float'>: <function Float2Str at 0x17b3c08>, <type 'list'>: <function quote_tuple at 0x17b3938>, <type 'tuple'>: <function quote_tuple at 0x17b3938>, <type 'array.array'>: <function array2Str at 0x17b39b0>, <type 'NoneType'>: <function None2NULL at 0x17b3b90>, <type 'datetime.datetime'>: <function DateTime2literal at 0x17b30c8>, <type 'dict'>: <built-in function escape_dict>, <type 'object'>: <function Instance2Str at 0x17b3aa0>, <type 'int'>: <function Thing2Str at 0x17b3de8>, <type 'datetime.timedelta'>: <function DateTimeDelta2literal at 0x17b3140>}

就像上面说的,”top”被拆分成了3个,可是我们语句中的占位符%s只有一个!所以报错 not all arguments converted during string formatting。最初这里我理解成了是哪里转化出了错误,最后醒悟这意思是因为占位符个数和传入参数个数不一样导致!英语不过关阿~不过以前怎么没报错呢??奇怪……

解决办法有2个,简单的方法就是执行语句修改如下:self.cur.execute(order, (position,)) #注意逗号。

更推荐的方法就是使用字典传值:

1
2
3
4
5
if show:
order = r"select id,p_name,p_explain,p_href from pictures where p_position = %(position)s and p_show = 1 order by -id"
else:
order = r"select id,p_name,p_explain,p_href,p_uploaddate,p_show from pictures where p_position = %(position)s"
self.cur.execute(order, {'position': position})

这样虽然多打了几个字母,不过却是一种最稳妥的方式,不用担心由于参数顺序造成错误了。

mysql-python源码写的狠帅阿!

评论和分享

django数据库分库

发布在 Django

有些时候我们需要项目中的app访问不同的数据库,这时就要进行分库操作。

首先建立一个db_router.py,内容示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
#coding=utf-8
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql', # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
'NAME': 'xxxx', # Or path to database file if using sqlite3.
'USER': 'root', # Not used with sqlite3.
'PASSWORD': '123456', # Not used with sqlite3.
'HOST': '', # Set to empty string for localhost. Not used with sqlite3.
'PORT': '3306', # Set to empty string for default. Not used with sqlite3.
'OPTIONS': {
'init_command': 'SET storage_engine=MyISAM',
},
},
'analysis_db':{
'ENGINE': 'django.db.backends.mysql',
'NAME': 'analysis_qq', #分析相关的操作用这个库
'HOST': '',
'USER': 'root',
'PASSWORD': '123456',
'PORT':'3306',
'OPTIONS':{
'init_command': 'SET storage_engine=MyISAM',
},
},
}
class MasterSlaveRouter(object):
def db_for_read(self, model, **hints):
if model._meta.app_label == 'analysisqq':
return 'analysis_db'
else:
return 'default'
def db_for_write(self, model, **hints):
if model._meta.app_label == 'analysisqq':
return 'analysis_db'
else:
return 'default'
def allow_relation(self, obj1, obj2, **hints):
db_list = ('default','analysis_db',)
if obj1._state.db in db_list and obj2._state.db in db_list:
return True
return None
def allow_syncdb(self, db, model):
if db == 'analysis_db':
print " in analysis db"
return True
elif db == 'default':
return False
else:
return None
DATABASE_ROUTERS = ['db_router.MasterSlaveRouter']

然后在settings.py中删除和数据库相关的代码,添加from db_router import *,这样之后指定的app就会使用特定的库了。

注意,这里分库是针对于读写操作,以及default已经存在后的syncdb,如果说你想执行syncdb同时就在不同数据库建表,这样操作是无效的。知道怎么做的请告知!

评论和分享

作者的图片

Roy

君以国士待我,我必以国士报君。


野生程序猿


China