tell-k

db_session = scoped_session(sessionmaker(bind=engine))
db_session.add(Entry(id=1, text="entrytext"))

# select entry ------
entry1 = Entry.query.filter_by(id=1).first()
print entry1.text # => "entrytext"

db_session.add(Entry(id=2, text="entrytext"))
db_session.flush()

tell-k

INSERT INTO entries (id, text, created_at) VALUES (%s, %s, %s)
(1, 'entrytext', datetime.datetime(2012, 9, 13, 23, 21, 11, 165187))

# Session は ここで selectされる事を知ってるから↑で先にInser文をちゃんとDBに流してくれている。
SELECT entries.id AS entries_id, entries.text AS entries_text, entries.created_at AS entries_created_at 
FROM entries 
WHERE entries.id = %s 
 LIMIT %s

INSERT INTO entries (id, text, created_at) VALUES (%s, %s, %s)

tell-k

# 異なる方法で同じレコードを取り出してみる
entry1 = Entry.query.filter_by(id=1).first()
entry1_another = Entry.query.filter_by(text="entrytext1").first()

# IdentityMapにより同じオブジェクトが返ってくる
if entry1 == entry1_another:
    print "same object"
    print "entry1 => %s" % (entry1)
    print "entry1_another => %s" % (entry1_another)

tell-k

same object
entry1 => <__main__.Entry object at 0x1018408d0>
entry1_another => <__main__.Entry object at 0x1018408d0>

tell-k

class Entry(Base):
    __tablename__ = 'entries'
    id = Column(Integer, primary_key=True)
    text = Column(String(200))
    created_at = Column(DateTime, default=datetime.now, nullable=False)

    comments = relationship('Comment', backref="entry")

class Comment(Base):
    __tablename__ = 'comments'

tell-k

db_session = scoped_session(sessionmaker(bind=engine))

tell-k

# 某フレームワークで OR を表現
Entry.objects.get(Q(id=1) | Q(id=2))
# => SELECT * FROM etnries WHERE id = 1 OR id = 2

tell-k

class Entry(Base):
    __tablename__ = 'entries'
    id = Column(Integer, primary_key=True)
    text = Column(String(200))
    created_at = Column(DateTime, default=datetime.now, nullable=False)

    comments = relationship('Comment', backref="entry") # Commentテーブルへのリレーション

class Comment(Base):
    __tablename__ = 'comments'

tell-k

class Entry(Base):
    __tablename__ = 'entries'
    id = Column(Integer, primary_key=True)
    text = Column(String(200))
    created_at = Column(DateTime, default=datetime.now, nullable=False)

    comments = relationship('Comment', backref="entry",
                    primaryjoin="Entry.id==Comment.entry_id", # <- ここ
                    foreign_keys="Comment.entry_id")          # <- ここ

tell-k

# EngineをDB毎に用意
dsn1 = 'mysql://testuser:testpass@localhost/test'
engine1 = create_engine(dsn1, convert_unicode=True, echo=False)
dsn2 = 'mysql://testuser:testpass@localhost/test2'
engine2 = create_engine(dsn2, convert_unicode=True, echo=False)

Base = declarative_base()
db_session = scoped_session(sessionmaker(twophase=True))
Base.query = db_session.query_property()