[sqlalchemy] foreign_keys changing cascade behavior for relationships? users going away.
Hi there. I have an application that has three different databases in MySQL (InnoDB). They are separated into distinct databases because sometimes I need to reload a database from scratch without impacting the other databases. For this reason, there are no ForeignKey() relationships defined at table creation time between these three databases, so I can wipe and reload an individual database at will. There are still relationships between the databases, from a SQLAlchemy perspective. Because I don't use ForeignKey(), I need to specify foreign_keys in my relationship, as follows: 'user' : relationship(User, foreign_keys=[User.__table__.c.email], primaryjoin=cls.__table__.c.user_email == User.__table__.c.email), When I have this relationship in my AllocatedHardware object, and I delete an AllocatedHardware record, this has the unfortunate site-effect of deleting the user account from the users table. :( When I leave the foreign_keys parameter out, then SQLAlchemy can't determine the join condition. The behavior I want is for the AllocatedHardware record to go away when I delete it, without impacting my user accounts. Can this be achieved with tweaking the cascade behavior? It seems like an unexpected side-effect that specifying foreign_keys will result in cascading deletes in tables that do not have any db-level foreign key relationships. Thanks and Regards, Daniel -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Repetitive Fields in declarative
On Fri, Aug 19, 2011 at 9:23 AM, Mark Erbaugh m...@microenh.com wrote:
On Aug 19, 2011, at 10:41 AM, Michael Bayer wrote:
Id use a mixin so that a superclass can be generated in a data driven
manner:
MyCols = type(MyCols, (object, ), dict((field%d % i, Column(Integer))
for i in xrange(1, 10)))
If you are going to do this a lot, it can be a pain with declarative. I
developed a framework that I used, based on ORM, which uses an explicit
_makeTable() and _mapTable() objects that I call in order to create and map
the tables. Since these are python methods, I can use any kind of python
iteration or code I want to decide what columns to create. It is a pretty
flexible model. Example conceptual code:
class Database(object):
table_args = { 'mysql_engine' : 'InnoDB' }
schema = database_name
def __init__(self,dbclasses=[]):
self.metadata = MetaData()
self.engine = ... (set up engine, etc.)
self.dbclasses = dbclasses
for c in self.dbclasses:
c._makeTable(self,self.engine)
cls.__table__.create(bind=self.engine,checkfirst=True)
for c in self.dbclasses:
c._mapTable(self)
class FooRecord(object):
@classmethod
def _makeTable(cls,db,engine):
cls.db = db
cls.__table__ = Table('foo', db.metadata,
Column('x'),
Column('y'),
**cls.table_args,
schema=cls.schema
etc.)
@classmethod
def _mapTable(cls,db):
mapper(cls, cls.__table__, properties={ ... })
db = Database([FooRecord])
You may find a model like this easier to use to create dynamically-generated
tables. The point here is that SQLAlchemy is sufficiently flexible so that
if declarative doesn't meet your needs or is a bit cumbersome for what you
want to do, you can just start at the ORM (below declarative) level and
build up a framework that works for you.
Regards,
Daniel
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] mysql table creation errno 105 when using vertical partitioning with InnoDB
Hi there, I have been using ORM vertical partitioning for a while with MySQL 5.1.58 and MyISAM tables and SQLAlchemy 0.7.2. I have recently switched over to using InnoDB tables and my vertical partitioning table creation is failing part-way in with an errno 105 code. I have confirmed that all the tables that *did* get created are using an InnoDB engine, so having stray MyISAM tables does not appear to be the problem. I have found a fix for the SQL that SQLAlchemy generates (and that MySQL barfs on) that allows the table creation to succeed, which involves simply prefixing SQLAlchemy's CREATE TABLE foreign key references with the database name. SQLAlchemy generates this line below, which also fails with errno 105 when I paste it into MySQL monitor, just like when executed by SQLA directly: FOREIGN KEY(project_id) REFERENCES projects (id) When I change it to this, table creation succeeds in mySQL monitor: FOREIGN KEY(project_id) REFERENCES car_res.projects (id) Basically, ForeignKey(projects.id) seems sufficient for SQLA to define the foreign key relationship, but MySQL seems to be wanting car_res.projects(id) to appear in the FOREIGN KEY SQL, instead of projects(id). So I'm a bit confused. Is SQLA to blame for not including this prefix in vertical partitioning table creation scenarios, or is there some bug in my code somewhere that is causing MySQL to barf or SQLA to generate incorrect SQL? I have some basic vertical partitioning test code that uses InnoDB with two tables, and for my basic test, it seems like the database. prefix is *not* required by MySQL to successfully create tables. Anyone have any idea about what might be going on? Regards, Daniel -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] mysql table creation errno 105 when using vertical partitioning with InnoDB
On Thu, Aug 11, 2011 at 12:20 PM, Michael Bayer mike...@zzzcomputing.comwrote:
Vertical partitioning implies tables in separate databases or schemas.
It seems like you have it set up such that a table in partition A can refer
to a table in partition B using a schema qualifier. So if this is the case
you'd use the schema argument on each Table to establish these names, when
you do your CREATE, as well as schema-qualify the ForeignKey:
t1 = Table(table_a, metadata, Column('id', Integer, primary_key=True),
schema=schema_a)
t2 = Table(table_b, metadata, Column('table_a_id', Integer, ForeignKey(
schema_a.table_a.id)), schema=schema_b)
Your answers raise more questions :) So it looks like I can use schema
prefixing as above and avoid using vertical partitioning altogether.
Another option would be to forego the usage of ForeignKey for table
relationships that span across two partitions, at least when CREATE TABLE is
emitted. This is effectively what you were doing when you were on MyISAM,
since REFERENCES is a no-op on MyISAM - it just gets thrown away hence you
had no error.
This is total conjecture since I don't know the details here nor do I know
MySQL's performance characteristics very deeply, but the existence of actual
cross-schema foreign key constraints in the MySQL database may be a
performance issue, if writing to table B means a disk operation on an
entirely separate schema for table A must occur in order to insert or update
a row.
OK, let me try to understand this.
The sample vertical partitioning code (this is my basic test that works,
shown below, based on the sample code in the O'Reilly book) works correctly
with InnoDB. It appears to emit ForeignKey for table relationships when
CREATE TABLE is emitted. Is this code supposed to fail? It's working.
from sqlalchemy import *
from sqlalchemy.orm import *
engine1 = create_engine('mysql://car:foo@localhost/car_fac')
engine2 = create_engine('mysql://car:foo@localhost/car_res')
engine1.echo = engine2.echo = True
metadata = MetaData()
product_table = Table( 'product', metadata,
Column('sku', String(20), primary_key=True),
Column('msrp', Numeric),
mysql_engine='InnoDB')
product_summary_table = Table( 'product_summary', metadata,
Column('sku', String(20), ForeignKey('product.sku'),
primary_key=True),
Column('name', Unicode(255)),
Column('description', Unicode(255)),
mysql_engine='InnoDB')
product_table.create(bind=engine1,checkfirst=True)
product_summary_table.create(bind=engine2,checkfirst=True)
class Product(object):
pass
class ProductSummary(object):
pass
mapper(ProductSummary, product_summary_table, properties=dict(
product=relation(Product, backref=backref('summary',
uselist=False
mapper(Product, product_table)
Session = sessionmaker(twophase=True)
Session.configure(binds={Product:engine1, ProductSummary:engine2})
session = Session()
As you can see, I'm using the same mysql account, but with two different
engines in a vertical partitioning configuration. MySQL is happy with the
foreign key relationships and creates the tables.
So... I don't understand your suggestion of not emitting ForeignKey at table
creation time. It appears to work in my basic test.
Shouldn't SQLA detect that the tables are vertically partitioned, treat the
ForeignKey relationships as NO-OPs to MySQL (because the tables may not be
able to *see* each other, since you could be using different MySQL accounts
with different permissions), and just use the ForeignKey definitions to help
set up the mappers properly?
I guess I don't understand the limitations/capabilities of vertical
partitioning in SQLA.
I have a more complex application that is basically doing the same thing as
this example code, actually using three engines to connect to three
different MySQL databases on the same server with the same account. This is
probably not the best way to do things, as schema prefixing would be better.
But as the above sample code shows, this *can* work. But my more complex app
is failing with the errno 105, which is what is confusing me. I can't figure
out the difference between my large application and this simple example, and
why the simple example works but my application does not, when they are
essentially doing the same thing. The sample code above emits SQL to MySQL
that defines the ForeignKey relationship and does not need a database.
prefix. But my big app seems to need that database. prefix. Maybe I have
the binds messed up?
-Daniel
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] mysql table creation errno 105 when using vertical partitioning with InnoDB
On Thu, Aug 11, 2011 at 2:12 PM, Michael Bayer mike...@zzzcomputing.comwrote:
Just FYI the Oreilly book is super, duper, extremely old and out of date.
A good read through the documentation on the SQLAlchemy site should be more
complete at this point and up to date, also stressing a whole set of new
techniques that weren't available when the OReilly book was written (it was
written against version 0.3).
OK, I think I am getting the hang of this. My working sample code in fact
didn't work when you tried to do anything with it.
I got the sample code working with two SQLite engines by dropping twophase,
which is not supported in SQLite. And I got the sample vertical partitioning
code working with two MySQL engines by removing the ForeignKey()
relationship and specifying the relationship explicitly in the mapper.
At this point, I was feeling adventurous, so I decided to try one MySQL
engine and one SQLite engine. It seems to work. Pretty cool. Example code
with comments below for anyone who might want to do this in the future. Run
python vertical_test.py init to populate the databases with data. On
successive runs, just run python vertical_test.py:
#!/usr/bin/python
import sys
from sqlalchemy import *
from sqlalchemy.orm import *
engine1 = create_engine('mysql://car:cargofast@localhost/car_res')
engine2 = create_engine('sqlite:///x.db')
engine1.echo = engine2.echo = True
metadata = MetaData()
class Product(object):
__table__ = Table( 'product', metadata,
Column('sku', String(20), primary_key=True),
Column('msrp', Numeric))
class ProductSummary(object):
__table__ = Table( 'product_summary', metadata,
Column('sku', String(20), primary_key=True),
Column('name', Unicode(255)),
Column('description', Unicode(255)),
mysql_engine='InnoDB')
# create tables in different databases:
Product.__table__.create(bind=engine1,checkfirst=True)
ProductSummary.__table__.create(bind=engine2,checkfirst=True)
# map tables to classes and define relationships between the tables:
mapper(ProductSummary, ProductSummary.__table__,
properties=dict(
product=relationship(
Product,
# Since there is no database-level foreign key
relationship,
# we need to define primaryjoin and foreign_keys
explicitly
# so SQLAlchemy understands how the tables are
connected:
primaryjoin=Product.__table__.c.sku ==
ProductSummary.__table__.c.sku,
foreign_keys=[Product.__table__.c.sku],
backref=backref('summary', uselist=False)
)
)
)
mapper(Product, Product.__table__)
# Create session, and bind each class to the appropriate engine:
Session = sessionmaker()
Session.configure(binds={Product:engine1, ProductSummary:engine2})
session = Session()
# Run with init as first argument to create tables and populate them
# with data:
# Run with init as first argument to create tables and populate them
# with data:
if __name__ == __main__ and len(sys.argv) == 2 and sys.argv[1] == init:
# create records using statements:
stmt = Product.__table__.insert()
engine1.execute(
stmt,
[
dict(sku=123, msrp=12.34),
dict(sku=456, msrp=22.12)
])
stmt = ProductSummary.__table__.insert()
engine2.execute(
stmt,
[
dict(sku=123, name=Shoes, description=Some
Shoes),
dict(sku=456, name=Pants, description=Some
Pants),
])
# or create records using ORM:
a = Product()
a.sku = blarg
session.add(a)
b = ProductSummary()
b.sku = a.sku
b.name = blarg
b.description = some blarg
session.add(b)
session.commit()
# Query records and SQLAlchemy relationships will help you to grab related
records
# from totally disparate database engines:
for p in session.query(Product):
print(PRODUCT INFO:,p.sku, p.msrp, p.summary.name,
p.summary.description)
Enjoy,
Daniel
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] declarative commit hook - onCommit()?
Hi All, Let's say that when a database record is added or updated, I need to perform some arbitrary action (in my case, ensuring that data in other tables is consistent with what is being committed.) What mechanisms are suggested for this? I could add a save() method to my declarative class that I need to explicitly call but in an ideal world, there would be a way for me to specify a method such as onCommit() that would automatically get called if it exists. -Daniel -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] declarative commit hook - onCommit()?
On Wed, Apr 28, 2010 at 10:04 AM, Chris Withers ch...@simplistix.co.uk wrote: Daniel Robbins wrote: Let's say that when a database record is added or updated, I need to perform some arbitrary action (in my case, ensuring that data in other tables is consistent with what is being committed.) What mechanisms are suggested for this? Mapper extesions: http://www.sqlalchemy.org/docs/05/reference/orm/interfaces.html#sqlalchemy.orm.interfaces.MapperExtension Thanks, Chris. Right now I am not defining a mapper, just a bunch of declarative classes. Can I still use MapperExtensions? Or should I move away from pure declarative and use mappers directly? I might be leaning in that direction anyway... Regards, Daniel -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] declarative commit hook - onCommit()?
On Wed, Apr 28, 2010 at 10:25 AM, King Simon-NFHD78 simon.k...@motorola.com wrote: The declarative docs include an example of using a MapperExtension: http://www.sqlalchemy.org/docs/reference/ext/declarative.html#mapper-con figuration Great, thanks for everyone's help. This is exactly the info and functionality I need. Best Regards, Daniel -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: SQLAlchemy Sessions
On Sun, Mar 28, 2010 at 10:19 AM, Peteris Krumins peteris.krum...@gmail.com wrote: This is the best explanation I have read on this topic! Thanks for writing it! Now I clearly see what is going on. Just one more thing - when should remove() be called? (if at all) The model that works for me is as follows: 1) use scoped_session() which will give every function in your Web app the same session when they call session = Session() locally. 2) If you have a long-running process that handles more than one HTTP request, only close() or remove() the session once, at the end of the HTTP request (generally, it's a good idea to add this to a finish() method for the HTTP request handler or something so it is done automatically.) This will ensure that each request gets a new, fresh session. Regards, Daniel -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] need 0.6_beta2-compat declarative meta
Hi All,
In 0.6_beta2, the following code is not properly adding a primary key Column
via DeclarativeMeta which calls my PrimaryKey() function:
def PrimaryKey(seqprefix):
return Column(Integer, Sequence(seqprefix, optional=True),
primary_key=True)
class ClassDefaults(DeclarativeMeta):
def __init__(cls,classname, bases, dict_):
seqprefix = getattr(cls,'__tablename__',None)
dict_['id'] = PrimaryKey(seqprefix=seqprefix)
return DeclarativeMeta.__init__(cls, classname, bases, dict_)
Base = declarative_base(metaclass=ClassDefaults)
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))
SQLAlchemy 0.6_beta2 complains on table initialization:
File /usr/lib64/python2.6/site-packages/sqlalchemy/orm/mapper.py, line 444,
in _configure_pks
key columns for mapped table '%s' % (self, self.mapped_table.description))
sqlalchemy.exc.ArgumentError: Mapper Mapper|Location|location could not
assemble any primary key columns for mapped table 'location'
This worked under 0.6_beta1 (and likely earlier versions of SQLAlchemy).
Can someone send me some code similar to above that works with 0.6_beta2, or is
this a bug in beta2?
Thanks,
Daniel
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] recommended declarative method design pattern for sessions
On Mar 23, 2010, at 9:29 AM, King Simon-NFHD78 wrote: object_session does indeed return the session that the instance is already bound to (so you shouldn't close it). I didn't know what object_session would return if the original session had been closed, so I tried it: Thanks for looking into this for me. As an update, I have been trying to use object_session but have been struggling with bugs related to the session returned from object_session() turning to None, presumably because it is somehow getting garbage collected. Because of this, I am going to try to use scoped_session() as this seems to be the preferred method for keeping things simple and reliable with session sharing between disparate pieces of code. This way, all my code can create a new session but will end up getting the same session, thus solving the complexity of grabbing the current object's session (I hope.) -Daniel -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: need 0.6_beta2-compat declarative meta
On Mar 27, 2010, at 5:58 AM, Chris Withers wrote:
avdd wrote:
In a metaclass's __init__, the attributes have already been placed on
the class, so mutating the attributes dict has no effect.
Spot on. SA fudged this prior to 0.6beta so you could get away with shoving
stuff in dict_, you now can't...
OK. Simply assigning something to cls.id now works. Here is my current
production code:
class ClassDefaults(DeclarativeMeta):
def __init__(cls,classname, bases, dict_):
if not ( dict_.has_key('__mapper_args__') and
dict_['__mapper_args__'].has_key('polymorphic_identity') ):
seqprefix = getattr(cls,'__tablename__',None)
cls.id = PrimaryKey(seqprefix=seqprefix)
return DeclarativeMeta.__init__(cls, classname, bases, dict_)
However, this new approach is incompatible with 0.6_beta1 (and earlier, I
assume.)
class ClassDefaults(DeclarativeMeta):
def __init__(cls,classname, bases, dict_):
seqprefix = getattr(cls,'__tablename__',None)
When are you expecting cls not to have a tablename?
The line Base = declarative_base(metaclass=ClassDefaults) requires this.
ext/declarative.py (at least in 0.6_beta1+) has a return metaclass(name,
bases, class_dict) on line 764 which causes the constructor to be called,
prior to assignment of a __tablename__. If I change my line above to seqprefix
= cls.__tablename__, I get a traceback.
Using tabs for intentation is evil.
I view choice of indentation as an issue of personal preference rather than one
that has larger moral and religious implications. I have always preferred tabs
over spaces for indent as I find them much easier to work with.
cls.id = Column(Integer, Sequence(cls.__tablename__, optional=True),
primary_key=True)
This is related to the possibility that the __tablename__ can be undefined.
When seqprefix is None, my PrimaryKey method will still return a primary key,
but it will have a unique sequence name based on a global, incrementing integer.
I do welcome any improvements to SQLAlchemy that may make this particular usage
case less complicated, but currently it appears that all my little tricks are
required.
http://www.sqlalchemy.org/docs/reference/ext/declarative.html#mix-in-classes
...I don't think they'll help here 'cos you're computing based on
__tablename__.
Right. Mix-ins look wonderful but they don't work for all cases.
Of course, nowadays, I tend to have tablename computed in a mix-in that does
all my common stuff:
class BaseMixin(object):
__table_args__ = {'mysql_engine':'InnoDB'}
@classproperty
def __tablename__(cls):
return cls.__name__.lower()
id = Column(Integer,primary_key=True)
I'm wondering if this would work for my purposes then:
class BaseMixin(object):
@classproperty
def __tablename__(cls):
return cls.__name__
id = Column(Integer, Sequence(cls.__name__, Optional=True), primary_key=True)
class Foo(Base,BaseMixin):
# will I get an id + sequence from the BaseMixin?
__name__ = foo
foo = Column(String(80), nullable=False)
Haven't tried it yet. :)
-Daniel
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] recommended declarative method design pattern for sessions
On Mar 22, 2010, at 5:10 AM, King Simon-NFHD78 wrote: See the 'How can I get the Session for a certain object' question at http://www.sqlalchemy.org/docs/session.html#frequently-asked-questions Basically, in your FindFriends method, replace: session = Session() with: session = Session.object_session(self) The reference documentation seems to indicate that Session.object_session() will return the existing session if one exists, rather than providing a new session that must be separately closed. Is this correct? If so, then FindFriends() should not close the session acquired via Session.object_session(obj), correct? Is it possible for object_session() to return None if the object's session was previously close()d? Thanks, Daniel -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] recommended declarative method design pattern for sessions
Hi All, One of the things that doesn't seem to be covered in the docs, and that I'm currently trying to figure out, is the recommended design pattern to use for managing sessions from declarative methods calls. Consider a declarative class User, where I want to implement a FindFriends() method: class User(Base): # declarative fields defined here def FindFriends(self): session = Session() # it's handy to use the self reference in query methods: friends = session.query(Friends).filter_by(friend=self).all() session.close() return friends Certainly, these types of methods would seem to be useful, but here's a dilemma - the above code doesn't work. Because the method uses a new session, which is guaranteed to not be the same session that was used to retrieve the original User object, the following code will fail: session = session() me = session.query(User).filter_by(name=Daniel).first() me.FindFriends() It would seem to be handy if SQLAlchemy placed a reference in each declarative object of the session from which it originated when query was called, so then my code could do something like this: class User(Base): # declarative fields defined here def FindFriends(self): # note the self.session.query - the idea is that sqlalchemy's query() would initialize this for us return self.session.query(Friends).filter_by(friend=self).all() Then this would allow the following code to work: session = session() me = session.query(User).filter_by(name=Daniel).first() me.FindFriends() This would work because me.FindFriends() would now have easy access to the same session that was used to retrieve me -- so the objects would be compatible and could be easily combined in queries. This would allow many methods to be added to the User class that could all do various kinds of db queries without having to pass a session variable around manually. My question is - what is the recommended design pattern to do what I am trying to do above? Passing the current session as an argument to FindFriends() seems cumbersome - is that the recommended approach or is there a more elegant way to handle it? Is my handy suggestion above something that would actually be useful or is there a better way to do what I am wanting to do? (I'm trying to be a good SQLAlchemy coder and not use a global session=Session() for everything, as explained here: http://www.sqlalchemy.org/docs/session.html#frequently-asked-questions . But to do this, I need to find a good design pattern to use in place of a global :) Thanks and Regards, Daniel -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] composite index using declarative
On Tue, Mar 16, 2010 at 8:08 AM, Michael Bayer mike...@zzzcomputing.com 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
Re: [sqlalchemy] composite index using declarative
On Tue, Mar 16, 2010 at 10:29 AM, Michael Bayer
mike...@zzzcomputing.com wrote:
Daniel Robbins wrote:
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 appreciate the rant but we're only talking about adding a distinct
section to the declarative documentation regarding indexes, which is
already accepted, and specific examples regarding remote_side and such are
non-controversial as well and we shall review the cases you present.
I'm not ranting, I'm offering constructive, critical feedback for your
excellent project.
It doesn't make much sense for the declarative documentation to completely
duplicate the entire mapping/relation/metadata sections of the
documentation, however.
Complete duplication is not required. However. I've written a lot of
technical documentation, and I've found that some repetition, and
building upon ideas introduced in other sections, does make technical
documentation much easier to use.
Index('endp_grp_ix', Endpoint.__table__.c.samplegroup_id,
Endpoint.__table__.c.name)
Index to my knowledge does accept declarative columns these days. You can
pass in Endpoint.samplegroup_id and Endpoint.name directly. The above
workaround was for a bug.
Then this should be documented in the declarative section, with an example.
Host.interfaces = relation(HostInterface,
collection_class=column_mapped_collection(HostInterface.name))
I'm not aware of this requirement. you should be able to create the
interfaces relation inside of Host, using a string to define
collection_class or otherwise use attribute_mapped_collection(name).
If this is not possible, then its a bug.
Looks like a bug in 0.6_beta1 then:
Traceback (most recent call last):
File base.py, line 525, in module
class HostInterface(Base):
File base.py, line 533, in HostInterface
interfaces = relation('HostInterface',
collection_class=column_mapped_collection('HostInterface.name'))
File /usr/lib64/python2.6/site-packages/sqlalchemy/orm/collections.py,
line 132, in column_mapped_collection
cols = [expression._no_literals(q) for q in util.to_list(mapping_spec)]
File /usr/lib64/python2.6/site-packages/sqlalchemy/sql/expression.py,
line 980, in _no_literals
to indicate a SQL expression literal, or 'literal()' to indicate
a bound value. % element)
sqlalchemy.exc.ArgumentError: Ambiguous literal: 'HostInterface.name'.
Use the 'text()' function to indicate a SQL expression literal, or
'literal()' to indicate a bound value.
Regardless, documentation in the declarative section about how to do
this cleanly would be appreciated.
I wouldn't consider Oracle to be a good candidate for diving in. But we
do have a prominent sidebox in the ORM tutorial now, describing the need
for the Sequence construct when using Firebird or Oracle, which you can
see at
http://www.sqlalchemy.org/docs/ormtutorial.html#define-and-create-a-table
That is a great addition.
I'd note that the metaclass stuff you've done has been worked into a
simple built in feature, using our new mixin capability, which is
described at:
I think Mix-Ins are a great idea and very elegant, and I'm very
thankful they were added by Chris, but again in this case, more
complex examples would be extremely helpful.
If you look closely at what I am trying to do -- naming the primary
key sequence based on the name of the table -- Mix-Ins provide no
obvious mechanism to do this, since I need to create the primary key
and sequence on the fly after the table name is provided. With the
primary key in the base class and the table name in the sub-class,
this becomes problematic.
If you know how to implement the code I provided using a Mix-in, then
I'd love to see an example on this mailing list, or even better, in
the docs :)
Regarding Oracle, if someone only has Oracle to work with, or wants to
create an Oracle-based solution, then they need to figure out how to
do sophisticated things like this in order to get meaningful work done
with SQLAlchemy and feel comfortable with the framework. I don't think
the extra code or design philosophy of SQLAlchemy is an issue, it's
the time required to harvest the necessary info from the docs and
compile it into a working skeleton. Once the skeleton has been
assembled, SQLAlchemy is a joy to use.
We mention prominently in the intro and on the wiki that there's a place
for recipes like these, which is the UsageRecipes section:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes
[sqlalchemy] composite index using declarative
Hi All, How does one define a composite index using declarative? Quick example? Couldn't find any examples in the docs or online. In my particular case, id will be a primary key, and I'd like a composite UniqueConstraint on group_id and name, and a composite index on group_id (pos 1) and name (pos 2). Thanks and Regards, -Daniel -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] composite index using declarative
On Mar 15, 2010, at 4:58 PM, Michael Bayer wrote: we have some index examples at http://www.sqlalchemy.org/docs/metadata.html#indexes . the Index is not related to declarative and is specified separately. Any plans to improve this? I would have thought that Indexes would be defined under __table_args__ along with constraints, which are often related. Since Index creation is such a common need, and the current means of creating one is counter-intuitive, do you think you could add a sample Index code snippet to the declarative documentation? -Daniel -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] case sensitive Unicode and String columns
On Thu, Mar 4, 2010 at 11:34 AM, Chris Withers ch...@simplistix.co.uk wrote: Hi All, I'm looking to create a model with a unicode or string column type that is case sensitive. I'm looking to do this in the model in such a way that the code in the model doesn't know or care about what backend database is used, but that barfs if it's ever used with a backend that doesn't actually support case-sensitive strings or unicodes. The current set of back ends we're targeting is (MySQL,sqlite)... How do I do this? I think all databases that SQLAlchemy supports (in fact, likely all databases in use today) support case-sensitive strings by default, so I don't know if this something you'll need to worry about in your code. Maybe I am misunderstanding what you are trying to do? -Daniel -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Base class with primary key and sequence (Mix-in?)
Hi All, I have created a base declarative object that has a pre-made primary key, so I don't have to add it to all my child tables: class ClassDefaults(DeclarativeMeta): def __init__(cls,classname, bases, dict_): dict_['id'] = Column(Integer, Sequence(id_seq, optional=True), primary_key=True) return DeclarativeMeta.__init__(cls, classname, bases, dict_) Base = declarative_base(metaclass=ClassDefaults) This allows me to create a table as follows, and have an implicit primary key named id: class UserGroup(Base): __tablename__ = 'usergroup' name = Column(String(80), nullable=False, unique=True, index=True) However, my base class currently uses the *same* sequence for all primary keys. I would like to create a new sequence for each primary key. I was thinking of naming the sequence based on the name of the table, so that UserGroup's sequence would be called usergroup_id_seq, etc. I am wondering how this is possible, using the above approach, or using Mix-ins, as documented here (Michael Bayer pointed me in this direction) -- http://www.sqlalchemy.org/docs/reference/ext/declarative.html#mix-in-classes While mix-ins look cool, I am not sure how I would reference the __tablename__ of the child class from the Mixin. I can probably work around this by *not* naming the sequences after the table name, but instead use an incrementing global variable to create the unique sequence names, but it seems like a better practice to base the sequence name on the name of the table itself. Michael says that Chris Withers may know how to do this with Mix-ins. Chris, you out there? :) Regards, Daniel -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Primary key Mix-in not working with adjacency list
Hi all,
I tried to convert some existing code containing an adjacency list to
mix-ins, and the mix-in version doesn't seem to be liked by SQLAlchemy
0.6_beta1:
Original code that works:
class ClassDefaults(DeclarativeMeta):
def __init__(cls,classname, bases, dict_):
dict_['id'] = Column(Integer, Sequence(id_seq,
optional=True), primary_key=True)
return DeclarativeMeta.__init__(cls, classname, bases, dict_)
Base = declarative_base(metaclass=ClassDefaults)
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))
New Mix-In code that doesn't work:
Base = declarative_base()
class Common(object):
id = Column(Integer, Sequence('id_seq', optional=True),
primary_key=True)
class Location(Base,Common):
__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))
SQLAlchemy complains:
Traceback (most recent call last):
File base.py, line 60, in module
class Location(Base,Common):
File /usr/lib64/python2.6/site-packages/sqlalchemy/ext/declarative.py,
line 561, in __init__
_as_declarative(cls, classname, dict_)
File /usr/lib64/python2.6/site-packages/sqlalchemy/ext/declarative.py,
line 554, in _as_declarative
cls.__mapper__ = mapper_cls(cls, table, properties=our_stuff, **mapper_args)
File /usr/lib64/python2.6/site-packages/sqlalchemy/orm/__init__.py,
line 778, in mapper
return Mapper(class_, local_table, *args, **params)
File /usr/lib64/python2.6/site-packages/sqlalchemy/orm/mapper.py,
line 189, in __init__
self._configure_pks()
File /usr/lib64/python2.6/site-packages/sqlalchemy/orm/mapper.py,
line 435, in _configure_pks
key columns for mapped table '%s' % (self, self.mapped_table.description))
sqlalchemy.exc.ArgumentError: Mapper Mapper|Location|location could
not assemble any primary key columns for mapped table 'location'
Question: do Mix-ins complicate the mechanism by which adjacency lists
are defined? If so, how does one work around this (and maybe update
the Mix-in docs to show an example of how to work around this issue?)
Thanks,
Daniel
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Base class with primary key and sequence (Mix-in?)
On Thu, Mar 4, 2010 at 2:31 PM, Daniel Robbins drobb...@funtoo.org wrote:
Hi All,
I have created a base declarative object that has a pre-made primary
key, so I don't have to add it to all my child tables:
I figured out how to do this, using the following code:
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)
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_)
Base = declarative_base(metaclass=ClassDefaults)
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))
This code above allows my adjacency list table Location to be handled
correctly, and also allows my Single Table inheritance (not included
in the above code) to work too. The PrimaryKey() function will
generate numerically increasing sequence names with no argument, or a
specific sequence name if supplied with an argument. ClassDefaults
calls it with the __tablename__ if one is available to create a
sequence that has a name similar to the underlying table (with a
_seq suffix.)
Regards,
Daniel
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
