django 执行原始SQL[转]

声明

文章转载于

参考文档和资料

Django 1.2

https://docs.djangoproject.com/en/1.2/topics/db/sql/

知识点总结

Django提供两种方式执行(performing)原始的SQL查询:

  • Manager.raw():执行原始查询并返回模型实例
  • Executing custom SQL directly:直接执行自定义SQL,这种方式可以完全避免数据模型,而是直接执行原始的SQL语句。

raw()方法

Manager.raw(raw_query, params=None, translations=None)

这种方法通过执行原始的SQL查询并返回一个RawQuerySet实例。返回的RawQuerySet实例就如QuerySet一样能够迭代。

1
2
3
4
5
6
7
8
9
10
11
>>> from account.models import person #导入person类
>>> raw_sql = 'select * from Person' #原始sql语句
>>> raw_querySet = person.objects.raw(raw_sql) #xx.objects.raw()执行原始sql
>>> print raw_querySet
<RawQuerySet: 'select * from Person'> #RawQuerySet对象
>>> for obj in raw_querySet: #迭代循环
... print obj
...
张三
李四
王五

注意,原始SQL里的model,如果在db_table没有定义,则使用app的名称,后面下划线 后面接模型类的名称,如“Myblog_New”;上面的例子,在定义类的时候已经这样处理了:

1
2
3
4
5
6
Class New(models.Model):
......
......
#自定义表名
class Meta:
db_table = 'New'

查询字段映射到模型字段(Mapping query fields to model fields)

raw() 方法自动通过查询字段映射到model字段,并且是通过名称来匹配,这意味着我们可以使用SQL子句(clause)

注意当输入查询字段的时候,不要漏了主键(id),否则会出错:InvalidQuery: Raw query must include the primary key

当有其他表(如表1)的时候,如果sql查询中对应名字(xx as yy)与表2中的字段匹配的话(yy)则model实例将会被创建:

1
2
3
4
5
>>> <span style="color: #3366ff;">table1</span>.objects.raw('''SELECT first AS first_name,
... last AS last_name,
... bd AS birth_date,
... pk as id,
... FROM <span style="color: #888888;">some_other_table</span>''')

可以使用raw()方法中的translations参数进行query字段到model字段的映射。

1
2
>>> name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'}
>>> table1.objects.raw('SELECT * FROM some_other_table', translations=name_map)

some_other_table 字段(first,last,bd,pk) 通过字典映射table1字段(first_name,last_name,birth_date,id)

索引查找(Index lookups)

1
2
3
first_person = Person.objects.raw('SELECT * from myapp_person')[0]
first_person = Person.objects.raw('SELECT * from myapp_person LIMIT 1')[0]
#然而,索引和切片不是在数据库级别上执行(除LIMIT外)

延迟模型字段(Deferring model fields)

Fields may also be left out(left out:忽视,不考虑;被遗忘),这意味着该字段的查询将会被排除在根据需要时的加载。

1
2
3
4
5
6
>>> for p in Person.objects.raw('SELECT id, first_name FROM myapp_person'):
... print p.first_name, # 这将检索到原始查询
... print p.last_name # 这将检索需求
...
John Smith
Jane Jones

传递参数(Passing parameters into raw())

如果需要执行参数化查询,您可以使用params参数原始()

1
2
>>> name = 'Doe'
>>> Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', [name])

注意,参数必须是一系列列表类型的。

不能在raw()方法中使用格式化字符串:

1
2
#错误
query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname
1
Person.objects.raw(query)

直接执行自定义SQL

Manager.raw()远远不够,可直接执行自定义SQL,直接执行UPDATE, INSERT, DELETE,而这些都是manager.raw()不能实现的。所以要直接执行自定义SQL

操作流程如下:

导入 form django.db import connection,transaction

django.db.connection:代表默认的数据库连接
django.db.transaction:代表默认数据库事务(transaction)
connection.cursor(): 获得一个游标(cursor)对象
cursor.execute(sql, [params]):执行SQL
cursor.fetchone() 或者 cursor.fetchall():返回结果行

如果执行修改操作,则调用transaction.commit_unless_managed()来保证你的更改提交到数据库。

实例如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
def sql(request):
"""
----------------------------------------------
Function: 执行原始的SQL
DateTime: 2013/x/xx
----------------------------------------------
"""
from django.db import connection,transaction
cursor = connection.cursor() #获得一个游标(cursor)对象
#更新操作
cursor.execute('update other_other2 set name ="李四" where id=%s',[3]) #执行sql语句
transaction.commit_unless_managed() #提交到数据库
#查询操作
cursor.execute('select * from other_other2 where id>%s' ,[1])
raw = cursor.fetchone() #返回结果行 或使用 #raw = cursor.fetchall()
#如果连接多个数据库则使用django.db.connections
from django.db import connections
_cursor = connections['other_database'].cursor()
#如果执行了更新、删除等操作
transaction.commit_unless_managed(using='other_databases')
return render_to_response('other/sql.html',{'raw':raw})

django.db.connections:针对使用多个数据库

1
2
3
4
from django.db import connections
cursor = connections['my_db_alias'].cursor()
# Your code here...
transaction.commit_unless_managed(using='my_db_alias')

通常我们不需要手动调用transaction.commit_unless_managed(),我们可以这样做:

调用set_dirty()自动映射到操作的字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<span style="color: #ff0000;">@commit_on_success</span>
def my_custom_sql_view(request, value):
from django.db import connection, transaction
cursor = connection.cursor()
# Data modifying operation
cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [value])
# Since we modified data, mark the transaction as dirty
<span style="color: #ff0000;">transaction.set_dirty()</span>
# Data retrieval operation. This doesn't dirty the transaction,
# so no call to set_dirty() is required.
cursor.execute("SELECT foo FROM bar WHERE baz = %s", [value])
<span style="color: #ff0000;"> row = cursor.fetchone()</span>
<span style="color: #ff0000;">return render_to_response('template.html', {'row': row})
</span>

个人常用

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
def Message(request,msg_id):
.........
where=msg_id
sql='''
select t.id, t.real_name, t2.* from auth_user t join (
select max(is_red) as is_red,add_user_id,task_id from card <br>       where msg_id=%s GROUP BY task_id,add_user_id)
t2 ON t2.add_user_id=t.id
''' %where
cursor = connection.cursor()
cursor.execute(sql)
fetchall = cursor.fetchall()
card=[]
for obj in fetchall:
dic={}
dic['user_id']=obj[0]
dic['real_name']=obj[1]
dic['is_red']=obj[2]
dic['add_user']=obj[3]
dic['task_id']=obj[4]
card.append(dic)
context['card']=card

整体实例演示

搜索数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
if request.method == 'POST':
type = int(request.POST.get('type',0))
s_title = request.POST.get('s_title')
if not type:
type = 'type>0'
else:
type = 'type=%s' %type
if s_title:
s_title = "title LIKE '%%%%%s%%%%'" %s_title
else:
s_title = 'id>0'
sql = u'''
SELECT @row := @row + 1 as row,t.* FROM(SELECT * FROM version WHERE %s AND %s) t,(SELECT @row := 0) r ORDER BY release_date''' %(type,s_title)
print sql
cursor = connection.cursor()
cursor.execute(sql)
fetchall = cursor.fetchall()

添加、更新数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
now = datetime.datetime.now()
if request.method == 'POST':
id = int(request.POST.get('id',0)) #编辑对应id,如果新添加的则id=0
version = request.POST.get('no')
type = int(request.POST.get('type'))
title = request.POST.get('title')
content = request.POST.get('content')
if not id: #添加
sql = 'insert into version(type,version,title,content,release_date) values(%s,%s,%s,%s,%s)'
cursor = connection.cursor()
cursor.execute(sql,[type,version,title,content,now])
transaction.commit_unless_managed()
return HttpResponse('ok')
else: #编辑
sql = 'update version set type=%s,version=%s,title=%s,content=%s where id=%s'
cursor = connection.cursor()
cursor.execute(sql,[type,version,title,content,id])
transaction.commit_unless_managed()
return HttpResponse('ok')

异步获取数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
...
if request.method == 'POST':
id = int(request.POST.get('id',0))
cursor = connection.cursor()
cursor.execute('select * from version where id =%s',[id])
raw = cursor.fetchone()
lis =[]
dic = {}
dic['type'] =raw[1]
dic['version']=raw[2]
dic['title'] = raw[3]
dic['content'] = raw[4]
lis.append(dic)
return ajax.ajax_ok(lis)

删除数据

1
2
3
4
5
6
if request.method == 'POST':
id = int(request.POST.get('id',0))
cursor = connection.cursor()
cursor.execute('delete from version where id=%s',[id])
transaction.commit_unless_managed()
return HttpResponse('ok')

热评文章