1.这个问题折磨我很久了(用models中定义的class访问其他业务创建的没有主键的table都有这问题)
def testRawSql(uid):
from boosencms.sinaweibo.analysis.models import WomAccount
accounts = WomAccount.objects.raw("select userId as uid, token as access_token, secret as expire_in from account_ProviderUserToken where userId = %s" % uid)
print accounts
for account in accounts:
print account
<RawQuerySet: 'select userId as uid, token as access_token, secret as expire_in from account_ProviderUserToken where userId = 2124561663'>
Traceback (most recent call last):
File "manage.py", line 14, in <module>
execute_manager(settings)
File "/home/dongsong/venv/lib/python2.6/site-packages/Django-1.4-py2.6.egg/django/core/management/__init__.py", line 459, in execute_manager
utility.execute()
File "/home/dongsong/venv/lib/python2.6/site-packages/Django-1.4-py2.6.egg/django/core/management/__init__.py", line 382, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/home/dongsong/venv/lib/python2.6/site-packages/Django-1.4-py2.6.egg/django/core/management/base.py", line 196, in run_from_argv
self.execute(*args, **options.__dict__)
File "/home/dongsong/venv/lib/python2.6/site-packages/Django-1.4-py2.6.egg/django/core/management/base.py", line 232, in execute
output = self.handle(*args, **options)
File "/home/dongsong/boosencms/src/boosencms/../boosencms/sinaweibo/analysis/management/commands/testapi.py", line 31, in handle
testRawSql(2124561663);
File "/home/dongsong/boosencms/src/boosencms/../boosencms/sinaweibo/analysis/management/commands/testapi.py", line 21, in testRawSql
for account in accounts:
File "/home/dongsong/venv/lib/python2.6/site-packages/Django-1.4-py2.6.egg/django/db/models/query.py", line 1480, in __iter__
raise InvalidQuery('Raw query must include the primary key')
django.db.models.query_utils.InvalidQuery: Raw query must include the primary key
原因：
self.model.objects.raw() expects the query result to contain primary keys from the modelself.model, so it can turn these into a list of objects for the function result.
解决办法：
https://docs.djangoproject.com/en/dev/topics/db/sql/#executing-custom-sql-directly
def my_custom_sql():
from django.db import connection, transaction
cursor = connection.cursor()
# Data modifying operation - commit required
cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
transaction.commit_unless_managed()
# Data retrieval operation - no commit required
cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
row = cursor.fetchone()
return row
或者（多数据库）
from django.db import connections
cursor = connections['my_db_alias'].cursor()
# Your code here...
transaction.commit_unless_managed(using='my_db_alias')
list转dict
def dictfetchall(cursor):
"Returns all rows from a cursor as a dict"
desc = cursor.description
return [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
改进后的代码：
def testRawSql(uid):
from boosencms.sinaweibo.analysis.models import WomAccount
from django.db import connections, transaction
cursor = connections['wom'].cursor()
cursor.execute("select userId as uid, token as access_token, secret as expire_in from account_ProviderUserToken where userId = %s" % uid)
transaction.commit_unless_managed(using='wom')
#print cursor.fetchall()
desc = cursor.description
print [dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall()]结果：
[dongsong@bogon boosencms]$ vpython manage.py testapi
/home/dongsong/venv/lib/python2.6/site-packages/Django-1.4-py2.6.egg/django/conf/__init__.py:75: DeprecationWarning: The ADMIN_MEDIA_PREFIX setting has been removed; use STATIC_URL instead.
"use STATIC_URL instead.", DeprecationWarning)
[{'access_token': u'1895748860', 'expire_in': u'79a5a918c3161847ca49e4ff8646904a', 'uid': u'2124561663'}]参考：
http://stackoverflow.com/questions/2909869/error-while-executing-query
2.修改模型层从父类继承来的字段
直接改写报错如下（CustomizeModel没有任何显式的属性，那么默认会有id属性作为主键，子类WomStatusPostQueue定义了id字段则报错）
django.core.exceptions.FieldError: Local field 'id' in class 'WomStatusPostQueue' clashes with field of similar name from base class 'CustomizeModel'
实际代码不方便贴出来，贴一个范例吧
class Place(models.Model):
name = models.CharField(max_length=20)
rating = models.DecimalField()
class LongNamedRestaurant(Place):
def __init__(self, *args, **kwargs):
super(LongNamedRestaurant, self).__init__(*args, **kwargs)
name = models.CharField(max_length=255)
name.contribute_to_class('name', self)
参考：http://stackoverflow.com/questions/2344751/in-django-model-inheritance-does-it-allow-you-to-override-a-parent-models-a
https://docs.djangoproject.com/en/1.1/topics/db/models/#field-name-hiding-is-not-permitted
3.django的mysql重连
a>>>常规用法（save/update/delete）的断线重连
mysql关闭，django在xxRecord.save()时候会抛出与下面类似的一些错误（对于cursor.execute()和xxModel.objects.raw()不管用）
<class 'django.db.utils.DatabaseError'>:(2006, 'MySQL server has gone away')
<class 'django.db.utils.DatabaseError'> : (2013, 'Lost connection to MySQL server during query')
<class '_mysql_exceptions.OperationalError'>,(2003, "Can't connect to MySQL server on '127.0.0.1' (111)")
<class '_mysql_exceptions.OperationalError'>,(2002, "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)")
分析如下：
1>进程启动默认会有数据库连接启动(即使代码里没有数据库操作)
2>save()时如果检测到没有数据库连接（比如在连接闲置期间数据库重启了）则自动重连，业务逻辑感觉不到
3>save()做数据库连接失败则抛出异常，不会重复尝试
结论：
如需要重复尝试数据库连接（一般http服务当然不需要，定制的一个command服务等可能会需要），可以在save()、update()、delete()等方法前后加上异常捕捉并重复的save()/update()/delete()；
这样改动大，更好的方法是编写一个改写了save()/update()/delete()方法的model类，然后其他业务相关的model类从这里继承就ok了
b>>>对于如下使用sql进行操作的情况
from django.db import connections
cursor = connections['my_db_alias'].cursor()
cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
transaction.commit_unless_managed(using='my_db_alias')
每次调用connections['my_db_alias'].cursor()或者connection.cursor()，django底层会自动获取数据库连接并返回，如果没有连接（可能mysql重启过了）则会当场建立连接并返回
如果我们拿着一个获取时有效而现在已经断开连接的cursor进行操作则会报错，需要捕获异常并重新获取cursor
c>>>对于xxModel.objects.raw()的断线重连，暂时不考虑了，不喜欢这种鸟用法
4.django settings.py 中设置DEBUG=True时 leak memory的一个问题，见另一篇文章
5.django多线程、多进程中数据库连接的问题：
多线程下，底层ConnectionHandler会为不同线程向各数据库建立单独的连接；
多进程（os.fork()）下，子进程会继承父进程的连接，所以会有问题（可以在子进程中对所有继承来的数据库连接执行django.db.connections['xxx'].close()，然后该子进程会在需要的时候启动新的连接）；
多进程（popen()）下，子进程会有单独连接，因为子进程是独立的django程序
下面是一些测试代码和结果：
# -*- coding: utf-8 -*-
from django.core.management.base import BaseCommand, CommandError
import time, copy, pprint, urllib, os, logging, threading, multiprocessing
logger = logging.getLogger('main')
def logic_body(sleepTime):
'''''
为test_multi_thread_connection提供线程体
'''
from django.db import connections
debugStr = 'pid(%d) ' % os.getpid()
for dbAlias in connections.databases.keys():
cn = connections[dbAlias]
debugStr += '"%s":%d ' % (dbAlias, id(cn))
logger.debug(debugStr)
time.sleep(sleepTime)
def test_threading_connection():
'''''
测试使用therading模块时数据库连接
'''
from django.db import connections
debugStr = 'pid(%d) ' % os.getpid()
for dbAlias in connections.databases.keys():
cn = connections[dbAlias]
debugStr += '"%s":%d ' % (dbAlias, id(cn))
logger.debug(debugStr)
threadList = list()
for index in range(10):
tmpThread = threading.Thread(target = logic_body, name = "thread-%d" % index, args = (10,))
tmpThread.start()
threadList.append(tmpThread)
time.sleep(5)
threadNum = threading.active_count()
logger.debug('************%d active threads**********' % threadNum)
for tmpThread in threading.enumerate():
logger.debug('*%s %d' % (tmpThread.name, tmpThread.ident))
logger.debug('****************************************')
for tmpThread in threadList:
tmpThread.join()
def test_multiprocessing_connection():
'''''
测试使用multi-process模块时数据库连接
'''
from django.db import connections
debugStr = 'pid(%d) ' % os.getpid()
for dbAlias in connections.databases.keys():
cn = connections[dbAlias]
debugStr += '"%s":%d ' % (dbAlias, id(cn))
logger.debug(debugStr)
processList = list()
for index in range(10):
tmpProcess = multiprocessing.Process(target = logic_body, name = "process-%d" % index, args = (10,))
tmpProcess.start()
processList.append(tmpProcess)
time.sleep(5)
logger.debug('************%d active child processes**********' % len(processList))
for tmpProcess in processList:
logger.debug('*%s %d' % (tmpProcess.name, tmpProcess.ident))
logger.debug('****************************************')
for tmpPorcess in processList:
tmpPorcess.join()
def test_os_fork_connection():
'''''
测试使用os.fork()时数据库连接
'''
from django.db import connections
debugStr = 'pid(%d) ' % os.getpid()
for dbAlias in connections.databases.keys():
cn = connections[dbAlias]
debugStr += '"%s":%d ' % (dbAlias, id(cn))
logger.debug(debugStr)
rt = os.fork()
if rt == 0 : # child process
logic_body(10)
else:
time.sleep(12)
class Command(BaseCommand):
help = "test sina api"
def handle(self, *args, **options):
#test_db_leak_mem()
logger.debug('--------------------------------------')
test_threading_connection()
logger.debug('--------------------------------------')
test_multiprocessing_connection()
logger.debug('--------------------------------------')
test_os_fork_connection()
logger.debug('--------------------------------------')
return
[dongsong@localhost boosencms]$ vpython manage.py testapi
MainThread 2012-08-21 10:54:27,125 DEBUG [testapi.py:142]--------------------------------------
MainThread 2012-08-21 10:54:27,132 DEBUG [testapi.py:76]pid(8572) "default":28730832 "content":33663568 "wom":34623312 "weibo":34627920
thread-0 2012-08-21 10:54:27,155 DEBUG [testapi.py:64]pid(8572) "default":34628880 "content":34629328 "wom":34629776 "weibo":34630224
thread-1 2012-08-21 10:54:27,165 DEBUG [testapi.py:64]pid(8572) "default":34630992 "content":34631504 "wom":34636112 "weibo":34636560
thread-2 2012-08-21 10:54:27,183 DEBUG [testapi.py:64]pid(8572) "default":34637840 "content":34638288 "wom":34638736 "weibo":34639184
thread-3 2012-08-21 10:54:27,207 DEBUG [testapi.py:64]pid(8572) "default":34639632 "content":34640144 "wom":34640592 "weibo":34641040
thread-4 2012-08-21 10:54:27,240 DEBUG [testapi.py:64]pid(8572) "default":34642384 "content":34642832 "wom":34643280 "weibo":34643728
thread-5 2012-08-21 10:54:27,249 DEBUG [testapi.py:64]pid(8572) "default":34648656 "content":34649168 "wom":34649616 "weibo":34650064
thread-7 2012-08-21 10:54:27,267 DEBUG [testapi.py:64]pid(8572) "default":34652880 "content":34653328 "wom":34653776 "weibo":34654224
thread-6 2012-08-21 10:54:27,264 DEBUG [testapi.py:64]pid(8572) "default":34651024 "content":34651472 "wom":34651920 "weibo":34652432
thread-9 2012-08-21 10:54:27,305 DEBUG [testapi.py:64]pid(8572) "default":34661520 "content":34661968 "wom":34662416 "weibo":34662864
thread-8 2012-08-21 10:54:27,300 DEBUG [testapi.py:64]pid(8572) "default":34655568 "content":34656016 "wom":34660624 "weibo":34661072
MainThread 2012-08-21 10:54:32,314 DEBUG [testapi.py:86]************11 active threads**********
MainThread 2012-08-21 10:54:32,314 DEBUG [testapi.py:88]*MainThread 140094652663552
MainThread 2012-08-21 10:54:32,314 DEBUG [testapi.py:88]*thread-0 140094492190464
MainThread 2012-08-21 10:54:32,315 DEBUG [testapi.py:88]*thread-1 140094479546112
MainThread 2012-08-21 10:54:32,315 DEBUG [testapi.py:88]*thread-6 140094352639744
MainThread 2012-08-21 10:54:32,315 DEBUG [testapi.py:88]*thread-5 140094363129600
MainThread 2012-08-21 10:54:32,318 DEBUG [testapi.py:88]*thread-8 140094331660032
MainThread 2012-08-21 10:54:32,318 DEBUG [testapi.py:88]*thread-2 140094384109312
MainThread 2012-08-21 10:54:32,318 DEBUG [testapi.py:88]*thread-3 140094469056256
MainThread 2012-08-21 10:54:32,319 DEBUG [testapi.py:88]*thread-9 140093914347264
MainThread 2012-08-21 10:54:32,319 DEBUG [testapi.py:88]*thread-7 140094342149888
MainThread 2012-08-21 10:54:32,319 DEBUG [testapi.py:88]*thread-4 140094373619456
MainThread 2012-08-21 10:54:32,319 DEBUG [testapi.py:89]****************************************
MainThread 2012-08-21 10:54:37,322 DEBUG [testapi.py:144]--------------------------------------
MainThread 2012-08-21 10:54:37,324 DEBUG [testapi.py:102]pid(8572) "default":28730832 "content":33663568 "wom":34623312 "weibo":34627920
MainThread 2012-08-21 10:54:37,378 DEBUG [testapi.py:64]pid(8585) "default":28730832 "content":33663568 "wom":34623312 "weibo":34627920
MainThread 2012-08-21 10:54:37,423 DEBUG [testapi.py:64]pid(8587) "default":28730832 "content":33663568 "wom":34623312 "weibo":34627920
MainThread 2012-08-21 10:54:37,442 DEBUG [testapi.py:64]pid(8586) "default":28730832 "content":33663568 "wom":34623312 "weibo":34627920
MainThread 2012-08-21 10:54:37,475 DEBUG [testapi.py:64]pid(8588) "default":28730832 "content":33663568 "wom":34623312 "weibo":34627920
MainThread 2012-08-21 10:54:37,511 DEBUG [testapi.py:64]pid(8589) "default":28730832 "content":33663568 "wom":34623312 "weibo":34627920
MainThread 2012-08-21 10:54:37,513 DEBUG [testapi.py:64]pid(8590) "default":28730832 "content":33663568 "wom":34623312 "weibo":34627920
MainThread 2012-08-21 10:54:37,558 DEBUG [testapi.py:64]pid(8591) "default":28730832 "content":33663568 "wom":34623312 "weibo":34627920
MainThread 2012-08-21 10:54:37,591 DEBUG [testapi.py:64]pid(8592) "default":28730832 "content":33663568 "wom":34623312 "weibo":34627920
MainThread 2012-08-21 10:54:37,621 DEBUG [testapi.py:64]pid(8593) "default":28730832 "content":33663568 "wom":34623312 "weibo":34627920
MainThread 2012-08-21 10:54:37,624 DEBUG [testapi.py:64]pid(8594) "default":28730832 "content":33663568 "wom":34623312 "weibo":34627920
MainThread 2012-08-21 10:54:42,632 DEBUG [testapi.py:111]************10 active child processes**********
MainThread 2012-08-21 10:54:42,665 DEBUG [testapi.py:113]*process-0 8585
MainThread 2012-08-21 10:54:42,685 DEBUG [testapi.py:113]*process-1 8586
MainThread 2012-08-21 10:54:42,719 DEBUG [testapi.py:113]*process-2 8587
MainThread 2012-08-21 10:54:42,720 DEBUG [testapi.py:113]*process-3 8588
MainThread 2012-08-21 10:54:42,728 DEBUG [testapi.py:113]*process-4 8589
MainThread 2012-08-21 10:54:42,732 DEBUG [testapi.py:113]*process-5 8590
MainThread 2012-08-21 10:54:42,741 DEBUG [testapi.py:113]*process-6 8591
MainThread 2012-08-21 10:54:42,750 DEBUG [testapi.py:113]*process-7 8592
MainThread 2012-08-21 10:54:42,759 DEBUG [testapi.py:113]*process-8 8593
MainThread 2012-08-21 10:54:42,761 DEBUG [testapi.py:113]*process-9 8594
MainThread 2012-08-21 10:54:42,762 DEBUG [testapi.py:114]****************************************
MainThread 2012-08-21 10:54:47,655 DEBUG [testapi.py:146]--------------------------------------
MainThread 2012-08-21 10:54:47,660 DEBUG [testapi.py:128]pid(8572) "default":28730832 "content":33663568 "wom":34623312 "weibo":34627920
MainThread 2012-08-21 10:54:47,688 DEBUG [testapi.py:64]pid(8597) "default":28730832 "content":33663568 "wom":34623312 "weibo":34627920
MainThread 2012-08-21 10:54:57,708 DEBUG [testapi.py:148]--------------------------------------
MainThread 2012-08-21 10:54:59,719 DEBUG [testapi.py:148]--------------------------------------6.目前为止django(1.4）还不支持组合主键，需要组合主键可能得另辟蹊径，网上找到如下两个实现了composite primary key的扩展包(同一项目，版本不同)
https://github.com/simone/django-compositekey/wiki
http://linux.softpedia.com/get/Internet/HTTP-WWW-/django-compositekey-79245.shtml
前者会对composite primary key建主键的同时在预计组合列上建一个组合唯一索引，然后在组合主键的每个列上还建一个索引。要不要再多建几个呢！？（2012.8.23记，以后的版本可能会改进吧）
后者会对预计composite primary key建立的是组合唯一索引，而不是主键，然后也在“组合主键”的每个列上建了一个索引，主键和索引完全一样吗？no，这也太委曲求全了！（2012.8.23记，以后的版本可能会改进吧）
相比之前，后者更能接受一些，所以选用了后者
最满足需求的方案是修改前者的源码：在compositekey/db/models/fields/multiplekey.py中把对组合主键中各列分别建索引的代码注释掉、把在组合主键上同时建立unique索引的代码也注释掉
61
62 def lazy_init():
63 self.fields = self._get_key_fields()
64 assert isinstance(self.fields, (list, tuple)) and len(self.fields) > 0, \
65 "%s must have a %s with at least 2 fields (%s)" % (cls.__name__, self.__class__.__name__,
66 ",".join([f.name for f in self.fields]))
67 names = [f.name for f in self.fields]
68 cls._meta.ordering = cls._meta.ordering or names
69
70 #import pdb
71 #pdb.set_trace()
72 #if names not in cls._meta.unique_together:
73 # cls._meta.unique_together.append(names)
74 #for field in self.fields: field.db_index=True
75
76 # get/set PK propery
77 setattr(cls, cls._meta.pk.attname, property(get_composite_pk(self.fields), del_composite_pk(), del_composite_pk()))
78
79 # hack db_column for joins see compiler
80 self.column = MultiColumn(self.fields)
81 self.db_type = nope
82 self.db_index = False
83 self.not_in_db = True
84 self.primary_key = True
当用django访问其他程序的数据表时，如果该数据表没有主键（id或其他userId之类的东西）则django访问会失败，而我们又不可以修改该表，这个时候CompositeKey就起作用了，用上述模块在表上指定组合主键就可以访问了
7.django从innodb数据表（至少我遇到的问题只出现在innodb数据表上）取数据时，第一次取到数据data1,那么当数据发生变化以后再次获取时还是data1。解决办法：
>>> MyModel.objects.count()
885
# (Here I added some more data from another process.)
>>> MyModel.objects.count()
885
>>> MyModel.objects.update()
0
>>> MyModel.objects.count()
1025
或者修改mysql的配置，在/etc/my.cnf中加入
transaction-isolation=READ-COMMITTED
参考：http://stackoverflow.com/questions/3346124/how-do-i-force-django-to-ignore-any-caches-and-reload-data
扫一扫
871
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
