之前接触过的orm框架/标准有hibernate、jpa,但觉得都不好用,不如spring jdbc template来得方便可控。可偏偏各种各种web框架都喜欢整合orm,是对开发人员没信心吗?本来只需要学会sql就好了,这一来换个框架都还得需要重新学习各自的接口定义用法,非常麻烦。不过还是尝试进一步了解下,毕竟要用。django自带了一份orm实现,直接举例说明好了。

定义

class Author(models.Model):
    name = models.CharField(max_length=150)
    email = models.EmailField(max_length=150)

class Tag(models.Model):
	name = models.CharField(max_length=20)

class Post(models.Model):
  author = models.ForeignKey(Author)
  title = models.CharField(max_length=150)
  content = models.CharField(max_length=3000)
  tags = models.ManyToManyField(Tag)
  timestamp = models.DateTimeField(auto_now=True)

上面对应了Author、Tag、Post三个model,三者之间用到了OneToMany、ManyToMany对应关系。在命令行下执行python manager.py sqlall可以看到对应生成的sql代码,

BEGIN;
CREATE TABLE `blog_author` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` varchar(150) NOT NULL,
    `email` varchar(150) NOT NULL
)
;
CREATE TABLE `blog_tag` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` varchar(20) NOT NULL
)
;
CREATE TABLE `blog_post_tags` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `post_id` integer NOT NULL,
    `tag_id` integer NOT NULL,
    UNIQUE (`post_id`, `tag_id`)
)
;
ALTER TABLE `blog_post_tags` ADD CONSTRAINT `tag_id_refs_id_2dd3b201` FOREIGN KEY (`tag_id`) REFERENCES `blog_tag` (`id`);
CREATE TABLE `blog_post` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `author_id` integer NOT NULL,
    `title` varchar(150) NOT NULL,
    `content` varchar(3000) NOT NULL,
    `timestamp` datetime NOT NULL
)
;
ALTER TABLE `blog_post` ADD CONSTRAINT `author_id_refs_id_d6ffd74` FOREIGN KEY (`author_id`) REFERENCES `blog_author` (`id`);
ALTER TABLE `blog_post_tags` ADD CONSTRAINT `post_id_refs_id_e6ee571e` FOREIGN KEY (`post_id`) REFERENCES `blog_post` (`id`);
CREATE INDEX `blog_post_cc846901` ON `blog_post` (`author_id`);
COMMIT;

三个model却有四张表,这是因为Post、Tag间的多对多关系,所以多了一张blog_post_tags表存储对应关系。django默认为每一个model增加了一个自增的id域作为主键,当然主动设定主键的话这域就没了。model field的具体详情参见这里,meta信息参见那里,这里具体关注在python代码中进行的crud操作。

查看生成的SQL

首先看下怎么查看自动生成的SQL,这样可以更清楚的知道django究竟做了什么,也可以在某些性能问题下给予一定的参考提示。从connection.queries里面可以查看执行过的语句,

from django.db import connection
connection.queries[:-10]

创建

创建model对象就和创建普通python对象一样,many-to-many field稍有特殊,

##
## INSERT INTO `blog_author` (`name`, `email`) VALUES (solilo, )
##
author = Author(name='solilo')
author.save()

##
## INSERT INTO `blog_tag` (`name`) VALUES (django)
##
django = Tag(name='django')
django.save()

##
## INSERT INTO `blog_tag` (`name`) VALUES (python)
##
python = Tag(name='python')
python.save()

## post保存之后才会生成id,many-to-many属性在有id后才能使用
##
## INSERT INTO `blog_post` (`author_id`, `title`, `content`, `timestamp`)
## VALUES (20, django notes, django orm blabla.., 2012-11-10 08:03:55)
##
post = Post(author=author, title='django notes', content='django orm blabla..')
post.save()

##
## INSERT INTO `blog_post_tags` (`post_id`, `tag_id`) VALUES (14, 29)' ...
##
post.tags.add(python)
post.tags.add(django)
post.save()

## 或者用create方法
##
## INSERT INTO `blog_author` (`name`, `email`) VALUES (leisure, )
##
author = Author.objects.create(name='solilo')

删除

删除操作也非常容易,

##
## DELETE FROM `blog_author` WHERE `id` IN (31)
##
...
author.delete()

or

Author.objects.filter(id=author.id).delete()

更更复杂的查询操作就留待下篇好了..