Django 数据库查询

Django 数据库查询

birthday__year=2006 
headline__startswith='What' 等价于 headline__startswith like 'What%'
headline__endswith='What' 等价于 headline__startswith like '%What'
birthday__gte=datetime.now() 等价于 birthday__gte >= datetime.now()
birthday__lte=datetime.now() 等价于 birthday__gte <= datetime.now()
pk__gt=14 等价于 pk>14
name__icontains="food" 等价于 name like "%food%"
headline__exact="Man bites dog" 等价于 headline = 'Man bites dog';
name__iexact="beatles blog" 查找name="beatles blog"的对象，不区分大小写
name__isnull=True 查询的是name为null的值
pk__in=[1,4,7] 等价于 id in{1,4,7} 

一对多
many端
e = Entry.objects.get(id=2) 
print e.blog

one端
b = Blog.objects.get(id=1)
b.entry_set.all()
b.entry_set.count() 

#!/usr/bin/python
#coding:utf-8

from django.shortcuts import render;
from django.shortcuts import render_to_response;
from django.http import HttpResponse;
from django.template import loader,Context, Template;
from django.http import HttpResponseRedirect;
from django.views.decorators.csrf import csrf_exempt;
import time, datetime;
from django.db import connection,transaction;

from blog.models import Blog;
from blog.models import Entry;
from blog.models import Author;
from blog.models import AuthorBlog;


def saveBlog(request):
    try:
        blog=Blog();
        blog.name="python";
        blog.tagline="Python是一种解释型、面向对象、动态数据类型的高级程序设计语言。自从20世纪90年代初Python语言诞生至今，它逐渐被广泛应用于处理系统管理任务和Web编程。";
        blog.save();
        return HttpResponse("save blog success");
    except BaseException, e:
        return HttpResponse("save blog failure:"+e);
        
def updateBlog(request):
    try:
        blog=Blog.objects.get(id=4);
        blog.tagline="Python是一种解释型、面向对象、动态数据类型的高级程序设计语言。";
        blog.save();
        return HttpResponse("update blog success");
    except BaseException, e:
        return HttpResponse("update blog failure:"+e);

def delBlog(request):
    try:
        blog=Blog.objects.get(id=4);
        #blog.delete();
        return HttpResponse("delete blog success");
    except BaseException, e:
        return HttpResponse("delete blog failure:"+e);

###一对多添加###
def saveEntry(request):
    try:
        blog=Blog.objects.get(id=4); #python
        entry=Entry();   
        entry.headline="django";
        entry.body_text="Django是一个开放源代码的Web应用框架，由Python写成。";
        entry.pub_date=datetime.date.today();
        entry.blog=blog;
        entry.save();
        return HttpResponse("save entry success");
    except BaseException, e:
        return HttpResponse("save entry failure:"+e);

###多对多添加###
def saveAuthorBlog(request):
    try:
        author=Author.objects.get(id=1);
        blog=Blog.objects.get(id=4);
        ab=AuthorBlog();
        ab.author=author;
        ab.blog=blog;
        ab.created_at=datetime.date.today();
        ab.save();
        return HttpResponse("save AuthorBlog success");
    except BaseException, e:
        return HttpResponse("save AuthorBlog failure:"+e);
      
def queryBlog(request):
    #检索所有的对象
    blogs = Blog.objects.all();
    for blog in blogs:
        print blog.name;
    print "=======";
    
    #多对一查询
    entrys=Entry.objects.all();
    for entry in entrys:
        print entry.headline+", "+entry.blog.id+", "+entry.blog.name;
    print "=======";
    
    return HttpResponse("queryBlog");
    
###执行原生查询并返回模型实例###
def rawBlog(request):
    raw_sql = 'select * from blog_Blog';
    blogs=Blog.objects.raw(raw_sql); #xx.objects.raw()执行原始sql
    print blogs;
    for blog in blogs:
        print blog.name;
    print "======";
    
    raw_sql = 'select * from blog_Blog o where o.name=%s'; #带参数
    blogs=Blog.objects.raw(raw_sql, ["j2ee"]); #xx.objects.raw()执行原始sql
    print blogs;
    for blog in blogs:
        print blog.name;
    return HttpResponse("rawBlog");
    
def sqlBlog(request):
    cursor=connection.cursor(); #获得一个游标(cursor)对象
    
    #更新操作
    cursor.execute('update blog_Blog set name="hadoop168" where id=%s', [1]); #执行sql语句
    transaction.commit_unless_managed(); #提交到数据库
    
    #查询操作
    cursor.execute('select * from blog_Blog where id=%s', [1]);
    blogs = cursor.fetchone(); #或使用 #raw = cursor.fetchall()；返回的结果集是个元组
    for blog in blogs:
        print blog;
    print "======";
    
    cursor.execute('select * from blog_Blog');
    blogs = cursor.fetchall(); #返回的结果集是个元组
    for blog in list(blogs):
        print str(blog[0])+", "+blog[1]+", "+blog[2];
    print "======";
    
    return HttpResponse("sqlBlog");

def querySqlBlog(request):
    sql="select b.name as blog_name, e.headline from blog_Blog b, blog_Entry e where b.id=e.blog_id";
    cursor=connection.cursor(); #获得一个游标(cursor)对象
    cursor.execute(sql);
    blogs = cursor.fetchall(); #返回的结果集是个元组
    
    records=[]
    for blog in blogs:
        dic={}
        dic['blog_name']=blog[0]
        dic['headline']=blog[1]
        records.append(dic);
    
    for record in records:
        print record["blog_name"]+", "+record["headline"];
    return HttpResponse("sqlBlog");
    
def searchBlog(request):
    #检索所有的对象
    #blogs = Blog.objects.all(); #使用all()方法返回数据库中的所有对象
    
    #检索特定的对象，使用以下两种方法： 
    #fileter()  返回一个与参数匹配的QuerySet,相当于等于(=)
    #exclude()  返回一个与参数不匹配的QuerySet,相当于不等于(!=)
    
    #blogs=Blog.objects.filter(name='python'); 等同于：slect * from blog_Blog where name='python' 
    #不使用Blog.objects.all().filter(name='python')，虽然也能运行，all()最好再获取所有的对象时使用。 
    blogs=Blog.objects.filter(name='python');
    for blog in list(blogs):
        print str(blog.id)+", "+blog.name+", "+blog.tagline;
    print "======";
    
    return HttpResponse("searchBlog");