本篇主要介绍我们在使用django框架开发的时候使用的操作数据库的语句,这在动态网站部分是很重要的,因为我们需要连接数据库进行操作,然后把数据处理的结果显示在前端页面上,在其他地方我们都是直接用sql语句来手动输入,这样容易造成错误,还影响效率,在django中,内置了很多处理数据库操作的函数,能更便利的帮我们操作数据库
models.py
class Blog(models.Model):
name = models.CharField(max_length=100)
tagline = models.TextField()
def __unicode__(self):
return self.name
class Author(models.Model):
name = models.CharField(max_length=50)
email = models.EmailField()
#python3中用__str__
def __unicode__(self):
return self.name
class Entry(models.Model):
blog = models.ForeignKey(Blog)
headline = models.CharField(max_length=255)
body_text = models.TextField()
pub_date = models.DateTimeField()
authors = models.ManyToManyField(Author)
#python3中用__str__
def __unicode__(self):
return self.headline
from mysite.blog.models import Blog #导入blog这个app
b = Blog(name='Beatles Blog', tagline='All the latest Beatles news.')
b.save()
b5.name = 'New name
b5.save()
joe = Author.objects.create(name="Joe")
entry.authors.add(joe)
#fileter(**kwargs)
Entry.objects.filter(pub_date__year=2006)
exclude(**kwargs)
q1 = Entry.objects.filter(headline__startswith="What")
q2 = q1.exclude(pub_date__gte=datetime.now())
q3 = q1.filter(pub_date__gte=datetime.now())
q = Entry.objects.filter(headline__startswith="What")
q = q.filter(pub_date__lte=datetime.now())
q = q.exclude(body_text__icontains="food")
print q
Entry.objects.all()[:5]
Entry.objects.all()[5:10]
Entry.objects.all()[:10:2]
Entry.objects.order_by('headline')[0]
#或Entry.objects.order_by('headline')[0:1].get()
Blog.objects.get(id__exact=14) # Explicit form
Blog.objects.get(id=14) # __exact is implied
Entry.objects.filter(pub_date__lte='2006-01-01')
Entry.objects.get(headline__exact="Man bites dog")
Entry.objects.get(headline__contains='Lennon')
Entry.objects.filter(blog__name__exact='Beatles Blog')
Blog.objects.filter(entry__headline__contains='Lennon')
Blog.objects.filter(entry__author__name='Lennon')
Blog.objects.filter(entry__author__name__isnull=True)
Blog.objects.filter(entry__author__isnull=False,entry__author__name__isnull=True)
Blog.objects.get(id__exact=14) # Explicit form
Blog.objects.get(id=14) # __exact is implied
Blog.objects.get(pk=14) # pk implies id__exact
Blog.objects.filter(pk__in=[1,4,7])
Blog.objects.filter(pk__gt=14)
Entry.objects.filter(blog__id__exact=3) # Explicit form
Entry.objects.filter(blog__id=3) # __exact is implied
Entry.objects.filter(blog__pk=3) # __pk implies __id__exact
Q(question__startswith='Who') | Q(question__startswith='What')
Poll.objects.get(
Q(question__startswith='Who'),
Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
)
Poll.objects.get(
Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),
question__startswith='Who')
比较
some_entry == other_entry
some_entry.id == other_entry.id
删除部分
Entry.objects.filter(pub_date__year=2005).delete()
b = Blog.objects.get(pk=1)
# This will delete the Blog and all of its Entry objects.
b.delete()
删除所有
Entry.objects.all().delete()
Update all the headlines with pub_date in 2007
Entry.objects.filter(pub_date__year=2007).update(headline='Everything is the same')
Change every Entry so that it belongs to this Blog
b = Blog.objects.get(pk=1)
Entry.objects.all().update(blog=b)
关联对象
one-to-many e = Entry.objects.get(id=2) e.blog # Returns the related Blog object.
e = Entry.objects.get(id=2) e.blog = some_blog e.save()
e = Entry.objects.get(id=2) e.blog = None e.save() # “UPDATE blog_entry SET blog_id = NULL …;”
e = Entry.objects.get(id=2) print e.blog # Hits the database to retrieve the associated Blog. print e.blog # Doesn’t hit the database; uses cached version.
e = Entry.objects.select_related().get(id=2) print e.blog # Doesn’t hit the database; uses cached version. print e.blog # Doesn’t hit the database; uses cached version
b = Blog.objects.get(id=1) b.entry_set.all() # Returns all Entry objects related to Blog.
b.entry_set is a Manager that returns QuerySets. b.entry_set.filter(headline__contains=’Lennon’) b.entry_set.count()
b = Blog.objects.get(id=1) b.entries.all() # Returns all Entry objects related to Blog. b.entries is a Manager that returns QuerySets. b.entries.filter(headline__contains=’Lennon’) b.entries.count()
You cannot access a reverse ForeignKey Manager from the class; it must be accessed from an instance: Blog.entry_set
add(obj1, obj2, …) Adds the specified model objects to the related object set. create(**kwargs) Creates a new object, saves it and puts it in the related object set. Returns the newly created object. remove(obj1, obj2, …) Removes the specified model objects from the related object set. clear() Removes all objects from the related object set.
many-to-many类型: e = Entry.objects.get(id=3) e.authors.all() # Returns all Author objects for this Entry. e.authors.count() e.authors.filter(name__contains=’John’) a = Author.objects.get(id=5) a.entry_set.all() # Returns all Entry objects for this Author.
one-to-one 类型: class EntryDetail(models.Model): entry = models.OneToOneField(Entry) details = models.TextField()
ed = EntryDetail.objects.get(id=2) ed.entry # Returns the related Entry object
使用sql语句进行查询:
def my_custom_sql(self): from django.db import connection cursor = connection.cursor() cursor.execute(“SELECT foo FROM bar WHERE baz = %s”, [self.baz]) row = cursor.fetchone() return row