On Tue, Mar 16, 2010 at 8:08 AM, Michael Bayer <[email protected]> wrote:
>
> we definitely should because I am amazed at how often this question gets
> asked. But the more puzzling thing is that it means the central tenet of
> declarative isn't getting across to people, which is that nothing changes
> at all when using declarative, its strictly a syntactical shortcut -
> everything you do when not using declarative is still 100% doable, in
> exactly the same way. I'm not sure why I see so many questions from
> others of the form "but I'm using declarative!"
My advice to you is to embrace the feedback and use it to make the
project better. People want to *start* with declarative, whereas you
as the architect started with the core parts of the framework and
recently added declarative. So you know all the underpinnings, but
most users don't, and they want to remain on as high a level
(declarative) as possible without having to get sidetracked by being
forced to master the lower-level parts in order to simply create an
index for their declarative tables. Make sense? How to tie the two
together is not always documented clearly. Think from the new user's
perspective and try to accommodate them via the docs.
I'm including a bunch of my sample code below, which you are welcome
to use in the docs or SQLAlchemy itself. This stuff deals with table
creation - you also need more examples for queries, but I don't have
enough useful examples stored up for those yet.
Here's one thing that was tricky to figure out - a self-referencing
table using declarative. Tricky because of the "remote_side" reference
using a string:
# The following code implements a self-referencing, heirarchical
table, and is tricky code
# to figure out for SQLAlchemy. You can append children to .children
or choose to create the
# child first and set its parent. Commit one and the parent/children
should be committed
# too.
class Location(Base):
__tablename__ = 'location'
parent_id = Column(Integer, ForeignKey('location.id'))
parent = relation('Location', backref=backref('children'),
remote_side='location.c.id')
name = UniqueString(25)
desc = Column(String(80))
Below, there are some examples of "bridging the divide" between
non-declarative and declarative use, to show people how to do it. It's
much easier to understand how to do this when you can actually see
code that does it. A lot of the examples in the docs are somewhat
trivial and don't really show you how the pieces fit together, such as
this example below, which shows how to reference __table__:
class Endpoint(Base):
__tablename__ = 'endp'
__table_args__ = [
UniqueConstraint( 'samplegroup_id', 'name' ),
]
samplegroup_id, samplegroup = ManyToOne(SampleGroup, nullable=False)
name = Column(String(80), nullable=False)
os_id, os = ManyToOne(EndpointOSType, nullable=False)
Index('endp_grp_ix', Endpoint.__table__.c.samplegroup_id,
Endpoint.__table__.c.name)
Here is another much-needed example:
# this table has a "peer_id" which can reference another
HostInterface. myhostinterface.peer will reference this peer, or
# None if no peer is set in peer_id. There is a backref to from the
peer back to myhostinferface using the .peered backref.
#
class HostInterface(Base):
__tablename__ = 'host_i'
name = Column(String(20), nullable=False)
host_id, host = ManyToOne(Host, nullable=False )
hostmac_id, hostmac = ManyToOne(HostMAC, nullable=False)
peer_id = Column(Integer, ForeignKey('host_i.id'), index=True)
peer = relation('HostInterface', backref=backref('peered'),
remote_side='host_i.c.id')
ip_id, ip = ManyToOne(IP)
class Host(Base):
__tablename__ = 'host'
owner_id, owner = ManyToOne(User, nullable=False, index=True)
type = Column(String(1), nullable=False)
hostid = UniqueString()
#Our host can have many HostInterfaces. By default, SQLAlchemy would
allow us to reference them as a list, such as:
# for int in myhost.interfaces:
# print int
# But it would be nice to access them as a dictionary, so we could
grab a particular interface by typing:
# myhost.interfaces["eth0"]. Here's how we create an "interfaces"
reference in dictionary mode. This must be done
# outside of the class after both tables have been defined:
Host.interfaces = relation("HostInterface",
collection_class=column_mapped_collection(HostInterface.name))
I am working on a pretty simple DB project, with only about 12 tables,
but here is my supporting/helper code, which is an order of magnitude
more complex than the samples in the docs, but got declarative to the
point where 1) I could actually use it with Oracle by adding
auto-sequences to the declarative model (a BIG hurdle for new users
who just want to dive in and are using it with a db that doesn't have
auto-increment sequences) and 2) where I could significantly reduce
duplicated code, which is generally one of the benefits of using a
class heirarchy.
It took me quite a bit of time to piece this all together, where more
complex examples in the docs would have helped me along:
# Easy unique string creation:
def UniqueString(length=80,index=True):
return Column(String(length), unique=True, index=index, nullable=False)
# Easy ManyToOne relationship - returns a column plus a relation - see
example below:
def ManyToOne(myclass,nullable=True, backref=None, index=False):
return Column(Integer, ForeignKey(myclass.__tablename__ +
".id"), nullable=nullable, index=index), relation(myclass,
backref=backref)
# master primary key generation function - will generate a unique
numbered sequence, or if a "seqprefix" is provided
# will use seqprefix as the prefix for the sequence name.
seqnum=0
def PrimaryKey(seqprefix=None):
global seqnum
if not seqprefix:
seqnum += 1
seqname = "id_seq_%s" % seqnum
else:
seqname = "%s_id_seq" % seqprefix
return Column(Integer, Sequence(seqname, optional=True),
primary_key=True)
# This creates a common base class that has an integer primary key
(with a sequence, if necessary for your particular database
# (ie. Oracle). It handles the situation where you may be using single
table inheritance, probably not 100% but it works for my
# purposes. This is pretty useful for most beginning db projects:
class ClassDefaults(DeclarativeMeta):
def __init__(cls,classname, bases, dict_):
if not ( dict_.has_key('__mapper_args__') and
dict_['__mapper_args__'].has_key('polymorphic_identity') ):
# Only add the key if we are not creating a
polymorphic SQLAlchemy object, because SQLAlchemy
# does not want a separate 'id' key added in that case.
# seqprefix can be None
seqprefix = getattr(cls,'__tablename__',None)
dict_['id'] = PrimaryKey(seqprefix=seqprefix)
return DeclarativeMeta.__init__(cls, classname, bases, dict_)
# Now, use the following "Base" class as the parent of your
declarative tables, and you will automatically get an integer primary
# key "id" added for you:
Base = declarative_base(metaclass=ClassDefaults)
Regards,
Daniel
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
