All these questions are real questions (and their answers) and were asked on the Elixir mailing list but they were edited at will to make them as useful and clear as possible in this FAQ.
- Entity definition
- Is there a way to specify the primary key manually?
- How do I define composite primary keys?
- How do I define a composite primary key including a foreign key?
- Can a string field be used as a foreign key?
- How do I declare uniqueness constraints?
- How do I set the id field not to be unique?
- How do I handle self-referencing foreign keys?
- How do I do enumerations?
- How do I declare RDBMS-specific data types?
- Can I use elixir to pickle arbitrary python objects to a database?
- How do I use field versus field_id?
- Do I always need to have a OneToMany for each ManyToOne?
- How do I use polymophic associations?
- When should I use the inverse argument?
- How do I create multiple self-referential many-to-many relationships?
- How do I force certain relations to be eager loaded?
- How do I create a db using introspection, without inheriting from the …
- How do I add functionality to all my entities/to the base class?
- How do I provide a different base class than Entity?
- How do I set up dynamic classes?
- Can I mix attribute based syntax and dsl based syntax in a model?
- What do you think of my model?
- How can I use an alternate collection class, such as …
- How do I create a relation as a property of an entity?
- Autoloaded tables/using already defined tables
- Customizing the names Elixir generates/uses
- Querying
- Introspection
- Misc
Entity definition
Is there a way to specify the primary key manually?
Yes, add primary_key=True to your field definition. Here is an example:
class Person(Entity): person_id = Field(Integer, primary_key=True) firstname = Field(Unicode(30)) surname = Field(Unicode(30))
This also works for composite primary keys.
How do I define composite primary keys?
If your primary key is composed of several fields, you only need to pass the primary_key keyword argument to each of those fields:
class Person(Entity): firstname = Field(Unicode(30), primary_key=True) surname = Field(Unicode(30), primary_key=True)
This also works for composite primary keys which include a foreign key/relationship.
How do I define a composite primary key including a foreign key?
If you want a foreign key (ManyToOne relationship) to be (part of) a primary key, just use the primary_key=True keyword argument:
class User(Entity): # 'name' is the only primary key (there is no integer 'id' field). name = Field(String(30), primary_key=True) class Post(Entity): # 'id' and 'user_name' are composite primary key for the 'post' table id = Field(Integer, primary_key=True) user = ManyToOne('User', primary_key=True)
All columns generated by the ManyToOne relationship (there can be several in case the target entity has itself a composite primary key) will be part of the composite primary key. In this example, the user relationship will generate a user_name column which will make up the primary key of the post table along with the id column.
Can a string field be used as a foreign key?
Can you have a relationship to an entity with a string field as primary key? Consider I have a User class with name as primary key, and a Post class with a user ManyToOne relationship to the User class. Will the generated column and foreignkey correctly handle the string primary key (the name column) of the User class?
Of course. That's what a ManyToOne/belongs_to relationship will do for you.
How do I declare uniqueness constraints?
For uniqueness constraints on one column only, you can simply use the unique=True keyword argument to the Field declaration. For constraints on several columns, you should use the using_table_options statement in combination with SQLAlchemy's UniqueConstraint. Here is an example:
from sqlalchemy import UniqueConstraint class Person(Entity): his_unique_number = Field(Integer, unique=True) firstname = Field(Unicode(30)) surname = Field(Unicode(30)) using_table_options(UniqueConstraint('firstname', 'surname'))
How do I set the id field not to be unique?
Here is the code I wrote:
class Post(Entity): using_options(tablename='Post') id = Field(Integer, autoincrement=True, unique=False, nullable=False) belongs_to('user_created', of_kind='User', colname="created_by", column_kwargs={'nullable': False}) using_table_options(UniqueConstraint('id', 'created_by'))
But it raise the following traceback:
Traceback (most recent call last): File "sqlalchemy_elixir_test.py", line 76, in ^lt;module> setup_all() File "/usr/lib/python2.5/site-packages/elixir/__init__.py", line 117, in setup_all setup_entities(entities) File "/usr/lib/python2.5/site-packages/elixir/entity.py", line 754, in setup_entities method() File "/usr/lib/python2.5/site-packages/elixir/entity.py", line 228, in setup_relkeys self.call_builders('create_non_pk_cols') File "/usr/lib/python2.5/site-packages/elixir/entity.py", line 438, in call_builders getattr(builder, what)() File "/usr/lib/python2.5/site-packages/elixir/fields.py", line 184, in create_non_pk_cols self.create_col() File "/usr/lib/python2.5/site-packages/elixir/fields.py", line 189, in create_col self.entity._descriptor.add_column(self.column) File "/usr/lib/python2.5/site-packages/elixir/entity.py", line 449, in add_column (col.key, self.entity.__name__)) Exception: Column 'id' already exist in 'Post' !
As long as you don't declare the id column as a primary_key explicitly, it won't be unique by default, so the above line is correct (even though it could be simpler). The traceback you get comes from the fact you don't specify any primary_key in your entity, so it tries to add the default primary key column which is named id by default and thus conflicts with your manually defined id column. If you want to simply turn off that feature, you can pass the appropriate option: auto_primarykey=False. See the Elixir options for more details.
What I think you want in fact is a compound primary_key containing id and created_by. This can be achieved simply by passing primary_key=True to both of them.
How do I handle self-referencing foreign keys?
For example : Employee has employee_id and name. Each employee has a supervisor, who is also an employee. The Employee at the highest level in the Hierarchy will have himself as his supervisor. Each supervisor can have many employees.
It might be easier to just say that this employee has no supervisor. Each supervisor can have many employees. How would the example model file look?
Self referencing relationships are defined just the same way as other relationships. So your example could be written like this:
class Employee(Entity): name = Field(String(32)) supervisor = ManyToOne('Employee') # and optionally employees = OneToMany('Employee')
See the Elixir test test_o2m.py for more examples.
You could also use a specific class for the supervisors, inheriting from the Employee class, so that the employees (subordinates) relationship is only present in that class.
How do I do enumerations?
e.g. When you have column like 'order_status' and it needs to hold one of the values representing perhaps "waiting for approval", "processing", "shipped", etc.
How do you call this out and where is the best place to put the descriptions and code?
If you are using SQLAlchemy 0.6+, you can use its Enum type like this:
class MyStuff(Entity): e = Field(Enum(u'foobar', u'baz', u'quux', None))
If you are using SQLAlchemy < 0.6, you could use the usage recipe for Enum. In that case, you would write:
class MyStuff(Entity): e = Field(Enum([u'foobar', u'baz', u'quux', None]))
How do I declare RDBMS-specific data types?
I need to define mysql tinyint, bigint field types, how do I do that?
RDBMS-specific data types can be provided as an argument to Field. Here's a runnable example:
from elixir import * from sqlalchemy.databases.mysql import MSBigInteger class Movie(Entity): director = ManyToOne('Director') viewed = Field(MSBigInteger) class Director(Entity): name = Field(Unicode(60)) movies = OneToMany('Movie') setup_all() metadata.bind = 'mysql://httpd@localhost/test' session.bind = metadata.bind metadata.create_all() session.begin() d = Director(name=u"George") m = Movie(director=d,viewed=999999999999999999) session.commit() session.flush() print m.viewed # Show native db type print m.table.columns.viewed.type.get_col_spec()
The available mysql types are listed in the SQLAlchemy documentation for the sqlalchemy.databases.mysql module:
Can I use elixir to pickle arbitrary python objects to a database?
Is there way to pickle arbitrary python object to a database using elixir, if possible by simply specifying the type of objects in the class declaration instead of a Field or Relation subclass? i.e. instead of this
class Foo(Entity): bar = Field(String(42)) credit = OneToMany('Foobar')
I'd like to write this
class Foo(Entity): bar = PythonObject(str) credit = Many(Foobar)
or even:
class Foo(Entity): bar = str credit = Many(Foobar)
I have tried subclassing elixir.properties.Property, with no error, but also with no success. My Property subclasses don't get added to their tables! How can this be done?
You should look into PickleType
Your class would be written as:
class Foo(Entity): bar = Field(PickleType) credit = OneToMany('Foobar')
Remember that it's your responsibility to make sure what you assign to your "bar" attribute is a picklable object (not everything is picklable).
How do I use field versus field_id?
How do I use the "two" fields created in a ManyToOne relationship?
From the tutorial, as below, Elixir will create (in Python) two fields: director and director_id. How are they used? And how are they kept in sync?
class Movie(Entity): director = ManyToOne('Director') class Director(Entity): # movies = OneToMany('Movie') d = Director(name="George") m = Movie(director=d) print m.director, m.director_id
The output will be something like:
<Director "George"> None
This seems inconsistent. The director_id field makes it appear if there is no director assigned to m. But director clearly holds a Director object.
Is this not dangerous? When do you use one or the other?
If director_id is just a shortcut, wouldn't it be more correct and safe to just refer to m.director.id?
Note that this is complicated, IMHO, by the fact that m.table.c contains director_id rather than director (the more useful attr).
You aren't actually creating two "fields". You are creating a single column in the database, representing the foreign key to the referenced table. In this case, that column is "director_id." The director_id attribute will be populated with the foreign key to the referenced row in your director table. The director attribute on your Movie entity will use that foreign key to load the referenced row from the director table into a Director object.
SQLAlchemy will keep these fields in sync, but not until after the object is flushed back to the database. In your example, if you add a "flush" of the objects to the database, then the synchronization will happen:
from elixir import * class Movie(Entity): director = ManyToOne('Director') class Director(Entity): movies = OneToMany('Movie') setup_all() metadata.bind = 'sqlite:///' metadata.create_all() d = Director(name="George") m = Movie(director=d) print m.director, m.director_id d.flush() m.flush() print m.director, m.director_id
The output will be like this:
<__main__.Director object at 0x66ee50> None <__main__.Director object at 0x66ee50> 1
SQLAlchemy can't set the foreign key until the objects have been flushed, since it doesn't yet have an id. Once the flush occurs, SQLAlchemy "links" everything back together again.
I'm obviously just not getting it, can someone expound on this a bit?
You're just confounding the database schema with the object's attributes. They aren't a direct one-to-one mapping. The director_id field is the database's way of knowing how one row relates to another. The director *attribtue* isn't stored in the database at all, its inferred by SQLAlchemy based upon the foreign key.
Thanks. I took your more complete example and added to it. It illustrates the part that is (was?) throwing me:
from elixir import * class Movie(Entity): director = ManyToOne('Director') class Director(Entity): name = Field(Unicode(60)) movies = OneToMany('Movie') metadata.bind = 'sqlite:///' setup_all() metadata.create_all() d = Director(name=u"George") m = Movie(director=d) print '---------------------------------' print m.director, m.director_id # Inconsistent d.flush() m.flush() print m.director, m.director_id # Consistent print '---------------------------------' # Down with the director! m.director_id = None print m.director, m.director_id # Inconsistent d.flush() m.flush() print m.director, m.director_id # Inconsistent!!! print '---------------------------------' # The correct way... m = Movie(director=d) d.flush() m.flush() print m.director, m.director_id # Consistent # Down with the director! m.director = None # Don't touch the '_id' thing print m.director, m.director_id # Inconsistent d.flush() m.flush() print m.director, m.director_id # Consistent Output:==================================================== --------------------------------- <__main__.Director object at 0x00F3BA70> None <__main__.Director object at 0x00F3BA70> 1 --------------------------------- <__main__.Director object at 0x00F3BA70> None <__main__.Director object at 0x00F3BA70> None --------------------------------- <__main__.Director object at 0x00F3BA70> 1 None 1 None None
I'm still struggling with the idea of the director and director_id being in an inconsistent state until flush(). I'll definitely have to remember that. Seems like it could lead to many hard to find bugs.
Well, again, it's not inconsistent. When you create a new Director and a new Movie and then relate them together by setting the movie's director attribute, neither object has been inserted into the database yet, and therefore they have no primary keys. It's completely consistent with what is in the database. Once you flush, the objects are updated to be consistent with what is in the database.
Because you are dealing with an object-relational mapper, you should largely interact with relationships through the relationship properties themselves, and not through the foreign keys. This is just a general rule of thumb, and not a hard and fast rule. I hardly ever find bugs in my code that relate to this particular issue, because I generally follow that rule when using the ORM for creating or updating data.
In that middle block if I try to get rid of the Director by setting director_id to None, the object never comes back into a consistent state even after flush(). I guess the dictum is to never-ever mess with the "director_id" thing and consider it read-only?
Well, you could certainly look at it that way, and it would definitely help you avoid those pitfalls. The ORM does its best to determine the state of the object, and by messing around with the foreign key you're probably just confusing it
Do I always need to have a OneToMany for each ManyToOne?
Is it necessary to specify both sides of a "parent/child" relationship? This is confusing as when doing this sort of stuff in SQL, I normally just put the F.K. spec in the child and have it point to the id of the parent. With the parent none the wiser.
class Movie(Entity): director = ManyToOne('Director') class Director(Entity): movies = OneToMany('Movie')
No, it's not necessary at all.
What happens if you leave off the OneToMany line?
Everything will be exactly the same, except you won't be able to fetch all Movie objects for a Director simply by accessing the movies attribute.
Essentially, a ManyToOne does two things:
- Creates a foreign key on the table for the entity to the table for the *related* entity. In this case, this column will be called director_id.
- Sets up a "relationship" between the entity and the referenced entity (in this case director) which will automatically load or set the referenced Director object using the aforementioned foreign key.
Now, a OneToMany represents the other side of this relationship, so it only creates a "relationship" and doesn't affect your schema in any way.
This is confusing as when doing this sort of stuff in SQL, I normally just put the F.K. spec in the child and have it point to the id of the parent. With the parent none the wiser.
Sure, and you're doing exactly the same thing here, through the ManyToOne relationship. The OneToMany is just the other side of the relationship that you can use to load related objects, perform queries based upon the relationship, and add related objects from the other side.
Personally, I always specify both sides of the relationship to make it absolutely clear in my code which objects are related to which other objects, and exactly how they are related, but again it's not required. Specifying both sides of the relationship also makes it easy to do things like eager loading and filtering based upon relationships on either side.
How do I use polymophic associations?
Polymorphic association allows special handling of tables and data to take place unobtrusively and undemandingly behind the scenes, allowing the programmer to maintain focus on the modeling task.
In the example shown below, the polymorphic association associable models a simple association list of key-value pairs. Each entity that is declared to have the defined association acquires the defined behaviour and presents the association list as a database field
from elixir.ext.associable import associable class Tag(Entity): key = Field(Unicode(63)) value = Field(Unicode(63)) has_tags = associable(Tag, 'tags') class Person(Entity): name = Field(Unicode(63)) email = Field(Unicode(63)) has_tags() class Place(Entity): name = Field(Unicode(63)) location = Field(Unicode(63)) has_tags()
Then you have a list of Tag objects at some_object.tags for every Person or Place and you can add distinctively separate tags to any Entity instance.
When should I use the inverse argument?
The inverse argument is only really useful for Elixir when there are more than one relationship between two entities and Elixir cannot guess which one is the inverse of the other. Some people prefer to always use it, to make it explicit which relation is the inverse of which. This also makes your code future-proof, in the case you later add another relationship between those two entities, you won't need to add the inverse argument to your first relationship if you had already done so.
How do I create multiple self-referential many-to-many relationships?
I want to create a User Entity. Such a User has many friends, also of type User. Friendships are "bi-directional", so you can't have a "one-way" friendship. In addition to friends, a User also has a list of enemies, also of type User, and the 'enemy' relation is similarly bi-directional.
If you have several self-referential many-to-many relationships, you have to specify the inverse relationships explicitly, otherwise the "friends" relationship will be considered the inverse relationship to the "enemies" relationship, which is plain wrong.
class User(Entity): using_options(tablename="user") friends = ManyToMany('User', inverse="is_friend_of") is_friend_of = ManyToMany('User', inverse="friends") enemies = ManyToMany('User', inverse="is_enemy_of") is_enemy_of = ManyToMany('User', inverse="enemies")
The thing which is confusing is that the "friends" relationship is not symmetrical in a relational database, even though the word (arguably) implies it. In other words, you can have somebody who regard many persons as his friends but those people don't consider that person to be a friend.
How do I force certain relations to be eager loaded?
You can specify lazy/eager loading on a per-relation basis by using the 'lazy' parameter (lazy == False for eager load). For example:
class EmailAddresses(Entity): user = ManyToOne('User', lazy=False)
How do I create a db using introspection, without inheriting from the Entity class?
I am trying to create a base class for my application which inherits from Entity like this:
class appBase(Entity): def __str__(self): outStr = '' attrList = list(self.__dict__.keys()) attrList.sort() for attrName in attrList: outStr = outStr + attrName value = getattr(self, attrName) if value.__class__.__name__ == 'int': outStr = outStr + '- ' + str(value) + '\n' attrName = Field(Integer) if value.__class__.__name__ == 'str': outStr = outStr + '- ' + str(value) + '\n' attrName = Field(String(100)) return outStr class User(appBase): using_options(tablename='SqlUser') credentials = OneToMany('Credential')
Well, what you are trying to do is provide a different base class than Entity. The behavior you are observing is normal even if can be a little confusing. Any class inheriting from Entity will create its own table. appBase being no exception! The other part of your confusion is that the inheritance "mode" is "single table inheritance" by default. Basically, it means that the data for children classes are put in the same table as their parent (and won't create a table for themselves). In your case, the User class data will end up in the appBase table.
That said, what I think you are really looking for is how to add functionality to all your entities.
How do I add functionality to all my entities/to the base class?
There are three ways to do that:
a) Extend the EntityBase class:
class MyBase(EntityBase): __metaclass__ = EntityMeta customProperty = Field(UnicodeText) def myCustomMethod(self): # do something great class MyType(MyBase): # do something awesome
By extending the EntityBase class, you can use all of the default methods from the Entity baseclass without having to copy them over.
c) use a mixin class:
class StrMixin(object): def __str__(self): [...] class User(Entity, StrMixin): [...]
How do I provide a different base class than Entity?
To provide a new base class (which can be used instead of the provided Entity class), you just need to set the metaclass of your base class to EntityMeta:
class appBase(object): __metaclass__ = EntityMeta common = Field(String(100)) def __str__(self): [...] class User(appBase): [...]
Note that in this case you'll lose all default methods provided by the Entity class (see the code for details), but nothing prevents you to copy-paste the code of the methods you want in your entity. Also, the 'common' field will be included in any derived sub-classes WITHOUT creating inheritance tables (i.e., 'common' will be added directly to each subclasses table).
How do I set up dynamic classes?
We have multiple legacy databases, some of which have the same table structure( but not necessarily the same table name). One of the ideas that we came up with was to dynamically generate our declarations. I've posted some example code and was hoping you could help me figure out where the error is coming from.
Here is the code: http://dpaste.com/56234/
and here is the output when I run in shell: http://dpaste.com/56235/
Your immediate problem comes from the fact that in your new metaclass, you are at missing the following line:
cls._descriptor = EntityDescriptor(cls)
But you'll have other problems too, as Elixir is not really designed to allow for a custom metaclass (or at least one which doesn't inherit from EntityMeta).
You should be able to define your classes dynamically without using a custom metaclass though.
def get_model(tablename, parent, metadata, class_name='MyClass'): cls = EntityMeta(class_name, (Entity,), {}) [...] for name, prop in properties: prop.attach(cls, name)
I think the cleanest way to solve your problem (assuming you don't need several of those different tables at the same time) would be to make Elixir able to generate several times its table from its entity definition and make it possible to change options (like the tablename) after class definition. I'll probably do that myself one day, but don't hold your breath as it could take months until I come to it as I don't consider this to be high priority. You might want to try to do it yourself though. I'll probably accept patches going in that direction.
Can I mix attribute based syntax and dsl based syntax in a model?
Mixing both syntaxes should work fine. The only "problem" I can think of is the ordering of columns in your tables *might* not follow the order in which you defined those fields. I'd have to look at the code to see if it's the case or not. That shouldn't cause any problem though.
What do you think of my model?
class User(Entity): username = Field(String(30), primary_key=True, nullable=False, unique=True) class Post(Entity): using_options(tablename='post') # id and username(foreign key), are composite # primary key for the Post table id = Field(Integer, primary_key=True, autoincrement=True, nullable=False) user = ManyToOne('User', column_kwargs={'primary_key': True, 'nullable': False}) using_table_options(UniqueConstraint('id', 'user'))
In the User entity, nullable=False and unique=True are useless as they are implied by the fact the field is a primary_key. In the Post entity, there are also some useless arguments: nullable: False, is implied by the primary_key: True. The UniqueConstrainst is also not necessary since there is already the primary key constraint, which implies uniqueness.
And one last thing is that you can use the shortcut:
user = ManyToOne('User', primary_key=True)
instead of specifying it through column_kwargs (which is perfectly correct but longer)
How can I use an alternate collection class, such as sqlalchemy.ext.orderinglist.ordering_list?
It should be as simple as passing collection_class to a OneToMany:
from sqlalchemy.ext.orderinglist import ordering_list class User(Entity): topten = OneToMany('Blurb', collection_class=ordering_list('position'), order_by='position')
How do I create a relation as a property of an entity?
This is an example of how to create a @property for a Person entity that takes a relationship and then further filters it (in this example, by date.)
from elixir import * from sqlalchemy import and_ from datetime import datetime class Event(Entity): name = Field(String(64)) date = Field(DateTime) attendees = ManyToMany("Person") def __repr__(self): return self.name class Person(Entity): name = Field(String(64)) events = ManyToMany("Event") @property def future_events(self): return Event.query.filter(and_( Event.attendees.any(id=self.id), Event.date >= datetime.now().date() )) setup_all() metadata.bind = 'sqlite:///' metadata.create_all() p1 = Person(name='Jon') p2 = Person(name='Andy') e1 = Event(name='Event One', date=datetime(2010, 1, 1)) e2 = Event(name='Event Two', date=datetime(2010, 1, 2)) e3 = Event(name='Event Three', date=datetime(2007, 12, 12)) e1.attendees.append(p1) e1.attendees.append(p2) e2.attendees.append(p1) e3.attendees.append(p1) e3.attendees.append(p2) session.commit() p1 = Person.get(1) print p1.future_events.all() p2 = Person.get(2) print p2.future_events.all()
Also See:
http://www.sqlalchemy.org/docs/05/ormtutorial.html#building-a-relation
and
http://www.sqlalchemy.org/docs/05/mappers.html#dynamic-relation-loaders
Note that dynamic_loader() is not supported in Elixir (but you can use lazy="dynamic" which does the same thing).
Autoloaded tables/using already defined tables
ManyToOne relationships with no foreign key constraint on the table
Given the following tables:
CREATE TABLE users ( userid bigint DEFAULT '0' NOT NULL, name varchar(100) DEFAULT '' NOT NULL, passwd char(32) DEFAULT '' NOT NULL, PRIMARY KEY (userid) ) with OIDS; CREATE TABLE media ( mediaid bigint DEFAULT '0' NOT NULL, userid bigint DEFAULT '0' NOT NULL, email varchar(100) DEFAULT '' NOT NULL, PRIMARY KEY (mediaid) ) with OIDS;
I want to define a ManyToOne relationship from 'Media' to 'User' by using the userid column.
If foreign key constraints were present on the table, SQLAlchemy would automatically figure the join condition for that relationship. Since it is not the case here, you need to use both the "primaryjoin" and "foreign_keys" arguments. And because the Table object is not spelled out explicitly, and is not available at the time you define the Entities, neither are their columns, which you need to reference in the primaryjoin and foreign_keys arguments. The solution adopted by Elixir for this is to allow a callable to be passed for those arguments. Those callable will be evaluated only when the table is ready. The solution to the above problem is thus:
class Media(Entity): using_options(tablename='media', autoload=True) user = ManyToOne('User', primaryjoin=lambda: Media.userid == User.userid, foreign_keys=lambda: [Media.userid]) class User(Entity): using_options(tablename='users', autoload=True) media = OneToMany('Media', primaryjoin=lambda: Media.userid == User.userid, foreign_keys=lambda: [Media.userid])
ManyToMany relationships with no foreign key constraint on the table
Given the following tables:
CREATE TABLE users ( userid bigint DEFAULT '0' NOT NULL, name varchar(100) DEFAULT '' NOT NULL, passwd char(32) DEFAULT '' NOT NULL, PRIMARY KEY (userid) ) with OIDS; CREATE TABLE usrgrp ( usrgrpid bigint DEFAULT '0' NOT NULL, name varchar(64) DEFAULT '' NOT NULL, gui_access integer DEFAULT '0' NOT NULL, PRIMARY KEY (usrgrpid) ) with OIDS; CREATE TABLE users_groups ( id bigint DEFAULT '0' NOT NULL, usrgrpid bigint DEFAULT '0' NOT NULL, userid bigint DEFAULT '0' NOT NULL, PRIMARY KEY (id) ) with OIDS;
I need to define a Many-to-many relationship between users and groups.
If Elixir was creating the table for a ManyToMany relationship like this, it would create it with foreign key constraints. But, here we have none in the users_groups table. As a result, it means that the Elixir model must explicitly declare the foreign keys.
If you're familiar with SQLAlchemy, then you might already know that such a declaration might look something like this:
user = Table('users', metadata, autoload=True) usrgrp = Table('usrgrp', metadata, autoload=True) users_groups = Table('users_groups', metadata, autoload=True) class User(object): pass class UsrGrp(object): pass mapper(User, user, { 'groups' : relation(UsrGrp, secondary=users_groups, foreign_keys=[users_groups.c.userid,usrgrp.c.usrgrpid,users_groups.c.usrgrpid], primaryjoin=user.c.userid==users_groups.c.userid, secondaryjoin=usrgrp.c.usrgrpid==users_groups.c.usrgrpid) }) mapper(UsrGrp, usrgrp, { 'users' : relation(User, secondary=users_groups, foreign_keys=[users_groups.c.userid,user.c.userid,users_groups.c.usrgrpid], primaryjoin=usrgrp.c.usrgrpid==users_groups.c.usrgrpid, secondaryjoin=user.c.userid==users_groups.c.userid) })
Unfortunately, at declaration time with Elixir, we do not have access to to the table instances used in the foreign_keys, primaryjoin, and secondaryjoin arguments above. However, Elixir as well as SQLAlchemy allows these arguments to be callable, meaning that we can declare them, and they will not be interpreted until later. So, the following will work as we hope.
users_groups = Table('users_groups', metadata, autoload=True) class User(Entity): using_options(tablename='users', autoload=True) groups = ManyToMany( 'Group', table=users_groups, foreign_keys=lambda: [users_groups.c.userid, users_groups.c.usrgrpid], primaryjoin=lambda: User.userid == users_groups.c.userid, secondaryjoin=lambda: Group.usrgrpid == users_groups.c.usrgrpid, ) class Group(Entity): using_options(tablename='usrgrp', autoload=True) users = ManyToMany( 'User', table=users_groups, foreign_keys=lambda: [users_groups.c.userid, users_groups.c.usrgrpid], primaryjoin=lambda: Group.usrgrpid == users_groups.c.usrgrpid, secondaryjoin=lambda: User.userid == users_groups.c.userid, )
Customizing the names Elixir generates/uses
How do I change the way column names are generated?
You can control how this column name is generated by overriding the format specified in elixir.options.MULTIINHERITANCECOL_NAMEFORMAT. Here is an example:
from elixir import * from elixir import options from sqlalchemy import create_engine options.MULTIINHERITANCECOL_NAMEFORMAT = "%(key)s" class Image(Entity): using_options(tablename='Images', inheritance='multi') class PersistentImage(Image): using_options(tablename='PersistentImages', inheritance='multi') foo1 = Field(Integer) class TransientImage(Image): using_options(tablename='TransientImages', inheritance='multi') foo2 = Field(Integer) setup_all() metadata.bind = create_engine('sqlite:///', echo=True) metadata.create_all()
How do I adjust the polymorphic inheritance column?
The polymorphic inheritance defaults to using a column named 'row_type'. If you have an existing schema that you want to use un-modified, you are able to override the default by passing in an additional option: polymorphic="my_col"
class Foo(Entity): ... using_options(polymorphic="my_col")
How can I define custom foreign keys names in ManyToMany relationships?
I need a way to define custom names for foreign keys. I have a DB with the following scheme:
CREATE TABLE wordforms ( wordform_id INTEGER NOT NULL, word TEXT NOT NULL, infinitives_loaded BOOLEAN NOT NULL, PRIMARY KEY (wordform_id) ) CREATE TABLE infinitives ( wordform_id INTEGER NOT NULL, infinitive_id INTEGER NOT NULL, PRIMARY KEY (wordform_id, infinitive_id), CONSTRAINT wordforms_infinitives_fk FOREIGN KEY(wordform_id) REFERENCES wordforms (wordform_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT wordforms_infinitives_inverse_fk FOREIGN KEY(infinitive_id) REFERENCES wordforms (wordform_id) )
I have defined entity class for wordforms mapping
class WordFormsMapping( elixir.Entity ) : elixir.options.using_options( tablename = 'wordforms', session = None, autosetup = True ) wordform_id = elixir.Field( elixir.Integer, primary_key = True ) word = elixir.Field( elixir.Text, index = True, nullable = False ) infinitives_loaded = elixir.Field( elixir.Boolean, index = True, nullable = False, default = False ) infinitives = elixir.ManyToMany( 'WordFormsMapping', tablename = 'infinitives', remote_side = 'wordform_id', local_side = 'wordform_id', uselist = True, backref = 'forms', onupdate = 'CASCADE', ondelete = 'CASCADE' )
which generates the following SQL:
CREATE TABLE wordforms ( wordform_id INTEGER NOT NULL, word TEXT NOT NULL, infinitives_loaded BOOLEAN NOT NULL, PRIMARY KEY (wordform_id) ) CREATE TABLE infinitives ( wordforms_wordform_id1 INTEGER NOT NULL, wordforms_wordform_id2 INTEGER NOT NULL, PRIMARY KEY (wordforms_wordform_id1, wordforms_wordform_id2), CONSTRAINT wordforms_infinitives_fk FOREIGN KEY(wordforms_wordform_id1) REFERENCES wordforms (wordform_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT wordforms_infinitives_inverse_fk FOREIGN KEY(wordforms_wordform_id2) REFERENCES wordforms (wordform_id) )
where names wordforms_wordform_id1 and wordforms_wordform_id2 are used instead of wordform_id and infinitive_id which are in DB.
The answer is to use the M2M column-format and the tablename option
ManyToMany("Relationtable",tablename=IntermediaryTable,column_format="%(key)s")
Querying
How do I query for an empty list?
Check the current syntax, these are the suggestions. The last one was found to work in Feb '08.
# vendors = Vendors.query.filter_by(items=[]) # vendors = Vendors.query.filter(Vendors.items == []) vendors = Vendors.query.filter(~Vendors.items.any())
How do I execute SQL directly?
from elixir import * for result in session.execute('select * from people'): print result['name']
Introspection
How can I get a String Field max length?
Given the following entity:
class User(Entity): name = Field(String(30)) l = User.table.columns['name'].type.length
What is the difference between table.c and table.columns?
Given the following entity (seen in the Elixir tutorial):
class Movie(Entity): director = ManyToOne('Director') m = Movie()
What is the difference between
m.table.c
and
m.table.columns
They are exactly the same.
How do I get a list of all attributes (including relationships)?
How do I get a list of attributes that includes relationships (eg. director) and not only columns (eg director_id)?
for property in Movie.mapper.iterate_properties: print property.key, type(property)
This will give you all of the properties for an entity, regardless of whether they are columns in the table, or relationships.
How do I enumerate all the relationship members of an Entity?
For a model browser, i need to enumerate all the relationship members of each class in the model. How can i determine and classify those members: onetomany, manytomany etc.?
YourEntity._descriptor.relationships
contains a collection of all relations on YourEntity.
How do I get at the table generated by a ManyToMany relationship?
If need to access the table generated for you by a ManyToMany relationship, you'd rather define the table manually (either by defining the table explicitly as in the "test_manual_table_auto_joins" test at elixir/trunk/tests/test_m2m.py#L224 or by using an intermediate entity and using the associationproxy SQLAlchemy extension).
If you really need to access the table object generated by a ManyToMany relationship, there are several ways you can do it after the setup phase (setup_all) is done. Assuming the following setup:
from elixir import * class Article(Entity): tags = ManyToMany('Tag', tablename='article_tag') class Tag(Entity): name = Field(Unicode(50), primary_key=True) articles = ManyToMany('Article', tablename='article_tag') setup_all()
The four methods below are equivalent at that point:
metadata.tables['article_tag']
Article.tags.property.secondary
Article.mapper.get_property('tags').secondary
Article._descriptor.find_relationship('tags').secondary_table
On the other hand, if you need to access the table object during the setup phase (ie after the table is generated but before it is mapped to the class, eg to define a custom join clause), only the first and last methods will work. The first one being the shortest, it is the recommended way. It only requires that you know the name of the ManyToMany table, but manually specifying the table name is recommended anyway.
Misc
How do I execute SQL statements at application initialization?
How can I execute SQL statements at application initialization (in order to make an Oracle session case insensitive). I was given a snippet which involves adding listeners to the SQLAlchemy create_engine call:
class MySetup: def connect(self, dbapi_con, con_record): dbapi_con.execute('alter session set NLS_SORT=BINARY_CI') dbapi_con.execute('alter session set NLS_COMP=LINGUISTIC') engine = create_engine('oracle:...', listeners=[MySetup()])
You may accomplish this in Elixir in exactly the same way as for raw SQLAlchemy. Elixir doesn't modify in any way that part of the process. *If* you use Elixir's provided metadata object, you can use it this way:
elixir.metadata.bind = engine
Is it safe to write tests on SQLite (with another DB for production)?
My production database is PostgreSQL. If I test good against SQLite in memory, can I rest assured that it will also work with Postgres?
Depends on what you test. I have the same setup as you and in most case you should have no problem. I've had some surprises when switching to Postgres (which I do once in a while) but those tend to be quickly fixed. The most common problem I had (which is not so common) is that SQLite is more tolerant to wrong data than Postgres (for example it doesn't enforce foreign keys, it accepts arbitrary length strings for limited length varchar and so on), so some wrong code passed on SQLite and failed on Postgres, but I just added explicit tests for those cases to my test suite and all is fine now.
How do I file Trac tickets for elixir?
I would have filed a ticket, but it looks like I need to request a Trac account first. I would also be happy to update the wiki page, but obviously would need a Trac account for that too.
You can login using the "guest" account (password: "guest") to post tickets or edit the Wiki.