Differences between revisions 4 and 5
⇤ ← Revision 4 as of 2007-03-29 14:07:15 →
Size: 6987
Editor: HuangYi
Comment:
← Revision 5 as of 2007-03-29 14:07:51 → ⇥
Size: 6989
Editor: HuangYi
Comment:
Deletions are marked like this. Additions are marked like this.
Line 162: Line 162:

TableOfContents

Elixir Examples

Simple

simple.py 

   1 from elixir import *
   2 
   3 class User(Entity):
   4     has_field('name', String(50))
   5     has_field('email', String(255))
   6     has_many('articles', of_kind='Article')
   7 
   8 class Article(Entity):
   9     has_field('title', String(200)),
  10     has_field('content', TEXT)
  11     belongs_to('author', of_kind='User')

打开 IPython, 开始试验！ 

In [1]: from simple import *

In [2]: metadata.connect('sqlite:///data.db')

In [3]: metadata.engine.echo=True

In [4]: objectstore.session.echo_uow=True

In [5]: create_all()
2007-03-29 21:28:06,875 INFO sqlalchemy.engine.base.Engine.0x..70 PRAGMA table_i
nfo(simple_user)
2007-03-29 21:28:06,875 INFO sqlalchemy.engine.base.Engine.0x..70 {}
2007-03-29 21:28:06,875 INFO sqlalchemy.engine.base.Engine.0x..70 PRAGMA table_i
nfo(simple_article)
2007-03-29 21:28:06,890 INFO sqlalchemy.engine.base.Engine.0x..70 {}
2007-03-29 21:28:06,890 INFO sqlalchemy.engine.base.Engine.0x..70
CREATE TABLE simple_user (
        name VARCHAR(50),
        email VARCHAR(255),
        id INTEGER NOT NULL,
        PRIMARY KEY (id)
)


2007-03-29 21:28:06,890 INFO sqlalchemy.engine.base.Engine.0x..70 None
2007-03-29 21:28:06,967 INFO sqlalchemy.engine.base.Engine.0x..70 COMMIT
2007-03-29 21:28:06,983 INFO sqlalchemy.engine.base.Engine.0x..70
CREATE TABLE simple_article (
        title VARCHAR(200),
        content TEXT,
        id INTEGER NOT NULL,
        author_id INTEGER,
        PRIMARY KEY (id),
         CONSTRAINT simple_article_author_id_fk FOREIGN KEY(author_id) REFERENCE
S simple_user (id)
)


2007-03-29 21:28:07,000 INFO sqlalchemy.engine.base.Engine.0x..70 None
2007-03-29 21:28:07,078 INFO sqlalchemy.engine.base.Engine.0x..70 COMMIT
2007-03-29 21:28:07,078 INFO sqlalchemy.engine.base.Engine.0x..70 CREATE INDEX i
x_simple_article_author_id ON simple_article (author_id)
2007-03-29 21:28:07,078 INFO sqlalchemy.engine.base.Engine.0x..70 None
2007-03-29 21:28:07,171 INFO sqlalchemy.engine.base.Engine.0x..70 COMMIT

In [6]: u = User(name='guest',email='[email protected]')

In [7]: u.articles.append(Article(title='a article',content='hahaha'))

In [8]: objectstore.session.flush()
2007-03-29 21:29:00,405 INFO sqlalchemy.orm.unitofwork.UOWTransaction.0x..30 Tas
k dump:

 UOWTask(0x108b950, User/simple_user/None) (save/update phase)
   |- Save User(0x10639f0)
   |   |- Process User(0x10639f0).articles
   |   |- Process Article(0x1067c70).author
   |
   |- UOWTask(0x108bab0, Article/simple_article/None) (save/update phase)
   |   |- Save Article(0x1067c70)
   |   |----
   |
   |
   |- UOWTask(0x108bab0, Article/simple_article/None) (delete phase)
   |   |----
   |
   |----


2007-03-29 21:29:00,421 INFO sqlalchemy.engine.base.Engine.0x..70 BEGIN
2007-03-29 21:29:00,437 INFO sqlalchemy.engine.base.Engine.0x..70 INSERT INTO si
mple_user (name, email) VALUES (?, ?)
2007-03-29 21:29:00,437 INFO sqlalchemy.engine.base.Engine.0x..70 ['guest', 'tes
[email protected]']
2007-03-29 21:29:00,453 INFO sqlalchemy.engine.base.Engine.0x..70 INSERT INTO si
mple_article (title, content, author_id) VALUES (?, ?, ?)
2007-03-29 21:29:00,453 INFO sqlalchemy.engine.base.Engine.0x..70 ['a article',
'hahaha', 1]
2007-03-29 21:29:00,453 INFO sqlalchemy.orm.unitofwork.UOWTransaction.0x..30 Exe
cute Complete
2007-03-29 21:29:00,467 INFO sqlalchemy.engine.base.Engine.0x..70 COMMIT

In [9]: a = Article.select_by(title='a article')[0]
2007-03-29 21:34:58,500 INFO sqlalchemy.engine.base.Engine.0x..10 SELECT simple_
article.content AS simple_article_content, simple_article.author_id AS simple_ar
ticle_author_id, simple_article.title AS simple_article_title, simple_article.id
 AS simple_article_id
FROM simple_article
WHERE simple_article.title = ? ORDER BY simple_article.oid
2007-03-29 21:34:58,500 INFO sqlalchemy.engine.base.Engine.0x..10 ['a article']

In [10]: a.auth
a.author    a.author_id

In [10]: a.author.name
2007-03-29 21:35:09,515 INFO sqlalchemy.engine.base.Engine.0x..10 SELECT simple_
user.id AS simple_user_id, simple_user.name AS simple_user_name, simple_user.ema
il AS simple_user_email
FROM simple_user
WHERE ? = simple_user.id ORDER BY simple_user.oid
2007-03-29 21:35:09,515 INFO sqlalchemy.engine.base.Engine.0x..10 [1]
Out[10]: u'guest'

Eager Loading

修改上面的 simple.py，第6行加上 lazy=False 的选项： 

   1     ...
   2     has_many('articles', of_kind='Article', lazy=False)
   3     ...

打开 IPython： 

In [1]: from simple import *

In [2]: metadata.connect('sqlite:///data.db')

In [3]: metadata.engine.echo=True

In [4]: objectstore.session.echo_uow=True

In [5]: User.sele
User.select    User.select_by User.selectone

In [5]: user = User.select_by(name='guest')[0]
2007-03-29 21:38:46,171 INFO sqlalchemy.engine.base.Engine.0x..70 SELECT simple_
user.id AS simple_user_id, simple_article_2bba.content AS simple_article_2bba_co
ntent, simple_article_2bba.author_id AS simple_article_2bba_author_id, simple_ar
ticle_2bba.title AS simple_article_2bba_title, simple_article_2bba.id AS simple_
article_2bba_id, simple_user.name AS simple_user_name, simple_user.email AS simp
le_user_email
FROM simple_user LEFT OUTER JOIN simple_article AS simple_article_2bba ON simple
_article_2bba.author_id = simple_user.id
WHERE simple_user.name = ? ORDER BY simple_user.oid, simple_article_2bba.oid
2007-03-29 21:38:46,187 INFO sqlalchemy.engine.base.Engine.0x..70 ['guest']

In [6]: user.articles
Out[6]: [<simple.Article object at 0x010A8E90>]

注意到查询 user 的时候进行了连接，访问 user.articles 的时候不需要另外的查询。

Lazy Column

在上面的 simple.py 基础上修改第 10 行，加上 deferred=True 选项： 

   1     ...
   2     has_field('content', TEXT, deferred=True)
   3     ...

继续在 IPython 中干活： 

In [1]: from simple import *

In [2]: metadata.connect('sqlite:///data.db')

In [3]: metadata.engine.echo = objectstore.session.echo_uow = True

In [4]: article = Article.select_by(title='a article')[0]
2007-03-29 21:45:28,015 INFO sqlalchemy.engine.base.Engine.0x..10 SELECT simple_
article.author_id AS simple_article_author_id, simple_article.title AS simple_ar
ticle_title, simple_article.id AS simple_article_id
FROM simple_article
WHERE simple_article.title = ? ORDER BY simple_article.oid
2007-03-29 21:45:28,030 INFO sqlalchemy.engine.base.Engine.0x..10 ['a article']

In [5]: print article.content
2007-03-29 21:45:41,671 INFO sqlalchemy.engine.base.Engine.0x..10 SELECT simple_
article.content AS simple_article_content
FROM simple_article
WHERE simple_article.id = ?
2007-03-29 21:45:41,671 INFO sqlalchemy.engine.base.Engine.0x..10 [1]
hahaha

注意到默认取 Article 的时候不会取 content 字段，当访问到 article.content 才会去取。

SQLAlchemy/ElixirExamples (last edited 2009-12-25 07:15:41 by localhost)