Re: [sqlalchemy] Is there a good ORM tutorial? And advice on parent/child management
actually benefit from the ORM? Or am I using it wrongly, and my problems come from misunderstanding the way it should be used? As I say, I've a lot of database experience but very little with ORMs, so maybe I have an unbalanced view of how much data management the ORM should be able to handle for me. The particular problem here is what's affecting me right now - but I'd be even more interested in a good ORM for experienced SQL developers tutorial that tells me how the ORM differs from the core level (and where its benefits lie). Thanks, Paul -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- -- Kevin Horn -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Polymorphic joined-table inheritance
= 'basic' When this line is commented out (as it should) and I try to insert a Client, I get the following error: ## IntegrityError: (IntegrityError) null value in column ctype violates not-null constraint ## which leads me to think that the polymorphism I am trying to get is not working properly, becasue I shouldn't need to force a value on the ctype column. A plea for help - I have been looking at this code for quite some time and I can't figure out what I am missing. If any of you have any idea of what I could be possibly doing wrong, or any ideas that I could try, I will be very very happy to hear them, because I have run out of ideas to try right now. Thank you very much. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- -- Kevin Horn -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Is there a good ORM tutorial? And advice on parent/child management
people having limited time, and the docs are certainly far better than a lot that's around. I felt the same way back when I started using SA. What fixed it for me was to go through the ORM tutorial as it is designed to be used, which (and I'm not sure this is actually explicitly stated anywhere) is to open a python interpreter and type in the commands, starting at the top, as you read. I don't know whether that will help you, but it might be worth a shot. Maybe I'll just have to have a thorough read of the docs before I carry on coding. Feels like I'm making more problems than I'm solving right now. Or maybe stick to what I know and drop back to the SQL core stuff. There's certainly nothing wrong with using the SA Core, if that's what you're more comfortable with. Even just using the core is a big win over using bare SQL IMO, since it makes it much easier to dynamically generate complex queries (and makes your code more portable across databases, at least potentially, if you care about that). Thanks for the help, Paul -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- -- Kevin Horn -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Insert from JSON data (coping with unneeded fields and varying types)
See also: ColanderAlchemy https://colanderalchemy.readthedocs.org/en/latest/index.html On Thu, Dec 19, 2013 at 8:23 AM, Paul Moore p.f.mo...@gmail.com wrote: On Tuesday, 17 December 2013 20:43:33 UTC, Michael Bayer wrote: typically you should write marshaling code here using a schema library - though I’ve not used it, I’d recommend colander for this: http://docs.pylonsproject.org/projects/colander/en/latest/ Nice, thanks! I hadn't heard of colander, and didn't know the term schema library so wouldn't have got very far with Google. I'll take a look at this. Paul -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- -- Kevin Horn -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Creating Tables from a Schema defined in YAML
On Wed, Aug 21, 2013 at 11:26 PM, monosij.for...@gmail.com wrote: Hello - Wondering if someone could please help me with this: I have created a schema definition file in YAML which I read into a dict. I am used to statically creating a table in this form: tableName = Table (theTableName, Metadata, Column(column1, String), Column(column2, String), Column(coulumn3, String) ... ) I am trying to see if there is a way to dynamically create a table using the column definition in the YAML file. So it would need to iterate over the column names and the types. I tried with creating the string = 'Column(column1, String), Column(column2, String), Column(coulumn3, String) ...' by iterating over the dict keys and then calling as above - but that did not work. Just wondering if there would be another way to do this. I looked at mapper as well but then that is for objects. Thanks. Mono The answer to this probably depends on exactly what your schema looks like, but based on what you've told us so far, I would probably try to create all the columns first, get them into a list or tuple, and then use star args to pass them into the Table constructor. Something like this: columns = [Column(name, type) for name, type in some_kind_of_iterable_generated_from_your_yaml] table = Table(theTableName, Metadata, *columns) though obviously that's a very rough example -- Kevin Horn -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] automated mapping - sqlalchemy or sqlsoup or something else
FYI: There's also sqlacodegen: https://pypi.python.org/pypi/sqlacodegen/ On Thu, Jul 25, 2013 at 11:29 AM, Michael Bayer mike...@zzzcomputing.comwrote: check out sqlautocode: https://code.google.com/p/sqlautocode/ On Jul 25, 2013, at 12:19 PM, Henning Sprang henning.spr...@gmail.com wrote: Hi, To automatically be able to access a large legacy database whithout having to write manual mapping code, I'd like to have a tool with automated mapping support. There seem to be two tools that offer something like this - sqlsoup and sqlasagna, but both seem not very actively supported - no commits since more than a year, sqlasagna has partly wrong documentation (saying its available in pypi, while it isn't, one example), so I'm not sure if I really should use one of them and if I'm not missing something. I am aware of the automated mapping functionality I can get with declarative_base and DeferredReflection, but then I still have to write classes for all tables plus define relationships. I might be able to script that myself (to make it work automated at runtime, or as a class generator as it is avialable in DjangoORM), but still, I'm asking if there is another tool I didn't find yet. Thanks, Henning -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- -- Kevin Horn -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy]
On Fri, Nov 2, 2012 at 2:09 PM, Michael Bayer mike...@zzzcomputing.com wrote:
On Nov 2, 2012, at 1:25 PM, Kevin Horn wrote:
I'm trying to copy some tables from MySQL to PostgreSQL, and it looked
like Table.tometadata() was exactly what I needed.
However, it's not working.
I keep getting a SQL syntax error when trying to create the new table.
--
sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or near
ON
LINE 5: ...STAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP ON UPDATE ...
^
\nCREATE TABLE legacy_user (\n\tusr_id SERIAL NOT NULL, \n\tusr_name
VARCHAR(64) DEFAULT '' NOT NULL, \n\tusr_modification_time TIMESTAMP
WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP NOT NULL, \n\tusr_creation_time TIMESTAMP WITHOUT
TIME ZONE DEFAULT '-00-00 00:00:00' NOT NULL, \n\tPRIMARY KEY
(usr_id)\n)\n\n {}
--
table reflection in SQLAlchemy is going to represent the datatypes on the
columns using the most specific type possible. In MySQL's case, there are
many MySQL-specific types that will fail to transfer over to Postgresql.
that's one thing that may need to be addressed in this particular reflection
process.
Hmmm...would be nice to have a way to generify those types (i.e. use
the more generic SA types instead), though I guess that would pretty
difficult to make cross-database.
In this specific case, I'm having a hard time seeing how you're getting that
output, however, SQLAlchemy does not emit ON UPDATE within a column
specification like that - we don't support that syntax.So I'm not sure if
that keyword is getting munged into the DEFAULT for the column and getting
spit out again, perhaps.
This indeed appears to be what happened.
You'll need to make yourself a fixer here that would fix this ON UPDATE line
and also do any processing for types that need to be changed:
from sqlalchemy import event
from sqlalchemy.schema import Table
@event.listens_for(Table, column_reflect)
def listen_for_reflect(table, column_info):
# look inside of column_info, change the data around
you may at first need to use print, or better yet pdb.set_trace(), inside
of the listen_for_reflect function here to see what's coming in and determine
how to adjust it for Postgresql.
docs for this are here:
http://docs.sqlalchemy.org/en/rel_0_7/core/events.html#sqlalchemy.events.DDLEvents.column_reflect
Thanks, this seems to have worked pretty well. It took me a while to
come up with the right incantation, but once I figured out what I was
doing, the resulting code was remarkably clean. Very nice.
This leaves me with another problem, though. In the old (mysql)
database, indexes are created with the same name as the column they
index. Since a number of those columns have the same names (though in
different tables), Postgres chokes pretty hard on this, and in really
strange ways.
I end up with a lot of errors like this:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation file_id
already exists
'CREATE INDEX file_id ON some_table (file_id)' {}
which makes a certain amount of sense, until I tell you that it
a) happens on different tables every time, which presumably means that
SA is processing the tables in a different order each time, which is
odd
b) sometimes it doesn't happen at all (!) which makes no sense. I
have no idea what's going on there.
Regardless of why this is happening, I think I can avoid the problem
if I can rename the indexes, perhaps to include a table prefix. I
tried using the 'before_create' event, but while I can easily get a
Table this way, I can't seem to get an index. Nor can I find a
reference to the index from the Table...
WAITAMINIT! I found it (Table.indexes...obvious, really...was
thinking too hard)
I still have no idea what's going on with b) above, but for the
moment, I don't have to care. Maybe I'll dig into it sometime when I
have some of this mythical free time I keep hearing about.
Thanks, Michael!
--
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]
I'm trying to copy some tables from MySQL to PostgreSQL, and it looked
like Table.tometadata() was exactly what I needed.
However, it's not working.
I keep getting a SQL syntax error when trying to create the new table.
--
sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or near ON
LINE 5: ...STAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP ON UPDATE ...
^
\nCREATE TABLE legacy_user (\n\tusr_id SERIAL NOT NULL, \n\tusr_name
VARCHAR(64) DEFAULT '' NOT NULL, \n\tusr_modification_time TIMESTAMP
WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP NOT NULL, \n\tusr_creation_time TIMESTAMP WITHOUT
TIME ZONE DEFAULT '-00-00 00:00:00' NOT NULL, \n\tPRIMARY KEY
(usr_id)\n)\n\n {}
--
Here's the possible causes that I can think of:
1) tometadata isn't compiling the SQL according to the new metadata,
but rather the old one
2) there's a bug in the PostgreSQL compiler that emits bad syntax
3) I'm totally misunderstanding what is supposed to happen here
(perhaps the most likely)
so my question is:
Is this possible? Advisable?
It looks like there might be a way to hook into the DDL creation
process, though I don't quite understand how...maybe that's a way
forward?
Otherwise I may have to just dump the MySQL tables, munge them with a
script, and load them into pgsql, which I'd rather not do, if
possible.
sample code below
##
import sys
from collections import namedtuple
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import sessionmaker
from sqlalchemy.schema import CreateTable
from sqlalchemy.sql import select
DBInfo = namedtuple('DBInfo', ['meta', 'engine', 'conn'])
def makeDBInfo(dbUri, echo=False, reflect=False):
engine = create_engine(dbUri, echo=echo)
meta = MetaData(engine)
if reflect:
meta.reflect(engine)
connection = engine.connect()
return DBInfo(meta=meta, engine=engine, conn=connection)
if __name__ == __main__:
from floworkdb import model
import transaction
sourceDBUri = 'mysql://olduser:oldpasswd@localhost/olddb'
source = makeDBInfo(sourceDBUri, reflect=True)
print 'source:'
print source.meta
print source.engine
print source.conn
print
destDBUri = 'postgres://newuser:newpasswd6@localhost/newdb'
dest = makeDBInfo(destDBUri, echo=True)
print 'dest:'
print dest.meta
print dest.engine
print dest.conn
print
print Creating 'user' table in dest DB
sourceTable = source.meta.tables['user']
destTable = sourceTable.tometadata(dest.meta)
destTable.create() # -- this fails!
# destTable.create(dest.engine) # -- this fails too!
# yes we're really connected to the new metadata
print destTable.metadata
# the same sql gets generated for both MySQL and Postgres!
print CreateTable(destTable)
print CreateTable(sourceTable)
# try to copy the table columns manually?
# same error...
#~ destTable = Table('legacy_user', dest.meta)
#~ print destTable.c
#~ for c in sourceTable.c:
#~ print c
#~ destTable.append_column(c.copy())
#~ print
#~ print destTable.c
#~ print destTable.metadata
#~ sourceTable.metadata.create_all(dest.engine)
sys.exit()
sys.exit('everything below here is ignored')
here's the SQL it tries to send to Postgres:
CREATE TABLE user (
usr_id INTEGER NOT NULL,
usr_name VARCHAR(64) DEFAULT '' NOT NULL,
usr_modification_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP NOT NULL,
usr_creation_time TIMESTAMP DEFAULT '-00-00 00:00:00' NOT NULL,
PRIMARY KEY (usr_id)
)
--
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] event interface
On Thu, Dec 30, 2010 at 11:50 AM, Michael Bayer mike...@zzzcomputing.comwrote: the twitterstream is leaning a lot towards not having the on_, we'll see what happens as the day goes on. For what it's worth, I think it's fine. It makes the interface seem very obvious to me. Kevin -- 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] Table as dictionary?
On Fri, Jan 15, 2010 at 11:28 AM, Nelson nelsonp...@comcast.net wrote:
Hello SQLAlchemy experts,
I'd like to view the contents of a table object as a dictionary.
Example:
s = Table('sparrow', Column('type', String(50)) , Column('weight',
Integer), ... etc)
s.type = 'African'
s.weight = 32
Then I want to see / get a dictionary:
{'type': 'African, weight: 32, ... etc}
Easy way to do that I'm missing?
Thanks
You could probably set up a mapper, and then make your mapped object behave
like a dictionary by using __getitem__(), __setitem__(), etc.
I don't know enough about SQLAlchemy internals to know whether that would
interfere with anything SQLAlchemy itself is doing though.
Kevin Horn
--
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: vertical partitioning
On Wed, Sep 30, 2009 at 1:04 PM, Adrian von Bidder avbid...@fortytwo.chwrote: Heyho! Is there a tutorial on vertical partitioning? I have a table Entry and a table EntryFlags (1:1 relation from EntryFlags to Entry). The idea is that while there is a large number of Entry rows only a small number has flags set (and thus needs an entry in EntryFlags; note that they don't need to be booleans, despite the name). So having a separate table for the EntryFlags keeps the rows of Entry smaller and should also speed up if I select by certain flags. How do I represent this in sqlalchemy? Obviously I can trivially do the relation stuff to get entry.flags.myflag, but nicer would be having the columns from the EntryFlags table appear in Entry as if they were inline, returning a default value where an EntryFlags column doesn't exist. (Bonus for removing the EntryFlags row if all values are back to default ;-) Thanks in advance. -- vbi -- The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offence. -- E. Dijkstra, 1975 Check out the Customizing Column Properties in the Mapper Configuation docs: http://www.sqlalchemy.org/docs/05/mappers.html#customizing-column-properties If I understand correctly, this should do what you want. (Disclaimer: haven't done it myself, YMMV) Kevin Horn --~--~-~--~~~---~--~~ 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] Re: Inheritance + Pseudo-adjacency-list?
On Wed, Sep 23, 2009 at 12:27 AM, Michael Bayer mike...@zzzcomputing.comwrote:
On Sep 22, 2009, at 11:59 AM, Kevin H wrote:
I'm having some trouble developing my model, and was hoping someone on
this list could help...
Here's what I want:
A BizEntity object
A Person and Company object (both descended from BizEntity, using
joined table inheritance)
A Company.employees attribute, which points to a list of Persons who
work for the company
A Person.company attribute, which points back to the company that
person works for
Whenever I try to combine inheritance with this sort of pseudo-
adjacency-list, I get really odd things happening when I try to query
from the tables...like getting the wrong company back when I query by
id.
Any ideas out there? Anyone done something like this?
I'm doing this.
Howdy, Michael! Knowing that I'm not trying to do something impossible is
definitely a relief.
The first thing to do is to definitely be on 0.5.6
at the least.
OK, I'm on 0.5.5, so that's the first thing to fix, I guess.
the next thing is to define the employees/company thing only once, as
a relation/backref pair on just one of your mapped classes. doing it
twice will mess things up for sure.
Good to know, thanks.
your example also mentions a table called nodes which from
everything else mentioned below would be erroneous. you don't need
remote_side when mapping between Company and Person.
Wow, that's from something _really_ old. Been commented out for a while...I
didn't even notice that.
None of this would cause the wrong Company to come back from a
simple query by id, though. If that is really the effect you're
seeing then something more fundamental might be amiss.
Looking at it again, it looks like this was caused by a problem in my
tests. I was assuming something I shouldn't have been about the order of
the data I was testing.
Thanks for the pointers, I'll post back later with results.
Kevin Horn
MODEL (so far):
(NOTE: the commented out lines are left over from some of my previous
attempts to get things working.)
class BizEntity(Base):
__tablename__ = 'biz_entities'
id = Column('bizentity_id', Integer, primary_key=True)
type = Column('bizentity_type', String(30), nullable=False)
__mapper_args__ = {'polymorphic_on': type}
class Company(BizEntity):
__tablename__ = 'companies'
id = Column(Integer, ForeignKey('biz_entities.bizentity_id'),
primary_key=True)
name = Column('company_name', String(50))
#~ employees = relation(Person, backref=backref(company,
remote_side=[])
#~ backref('parent', remote_side=[nodes.c.id])
__mapper_args__ = {'polymorphic_identity': 'company'}
class Person(BizEntity):
__tablename__ = 'people'
id = Column('bizentity_id', Integer, ForeignKey
('biz_entities.bizentity_id'), primary_key=True)
first_name = Column('first_name', String(50))
middle_init = Column('middle_init', String(1))
last_name = Column('last_name', String(50))
#~ company = relation(Company, backref=backref('employees',
order_by=id))
__mapper_args__ = {'polymorphic_identity':'person'}
--~--~-~--~~~---~--~~
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] Re: Inheritance + Pseudo-adjacency-list?
On Wed, Sep 23, 2009 at 10:57 AM, Kevin Horn kevin.h...@gmail.com wrote:
On Wed, Sep 23, 2009 at 12:27 AM, Michael Bayer
mike...@zzzcomputing.comwrote:
On Sep 22, 2009, at 11:59 AM, Kevin H wrote:
I'm having some trouble developing my model, and was hoping someone on
this list could help...
Here's what I want:
A BizEntity object
A Person and Company object (both descended from BizEntity, using
joined table inheritance)
A Company.employees attribute, which points to a list of Persons who
work for the company
A Person.company attribute, which points back to the company that
person works for
Whenever I try to combine inheritance with this sort of pseudo-
adjacency-list, I get really odd things happening when I try to query
from the tables...like getting the wrong company back when I query by
id.
Any ideas out there? Anyone done something like this?
I'm doing this.
Howdy, Michael! Knowing that I'm not trying to do something impossible is
definitely a relief.
The first thing to do is to definitely be on 0.5.6
at the least.
OK, I'm on 0.5.5, so that's the first thing to fix, I guess.
the next thing is to define the employees/company thing only once, as
a relation/backref pair on just one of your mapped classes. doing it
twice will mess things up for sure.
Good to know, thanks.
your example also mentions a table called nodes which from
everything else mentioned below would be erroneous. you don't need
remote_side when mapping between Company and Person.
Wow, that's from something _really_ old. Been commented out for a
while...I didn't even notice that.
None of this would cause the wrong Company to come back from a
simple query by id, though. If that is really the effect you're
seeing then something more fundamental might be amiss.
Looking at it again, it looks like this was caused by a problem in my
tests. I was assuming something I shouldn't have been about the order of
the data I was testing.
Thanks for the pointers, I'll post back later with results.
Kevin Horn
Still having problems...
Here's my new model:
# START OF MODEL
Base = declarative_base()
class BizEntity(Base):
__tablename__ = 'biz_entities'
id = Column('bizentity_id', Integer, primary_key=True)
type = Column('bizentity_type', String(30), nullable=False)
__mapper_args__ = {'polymorphic_on': type}
class Company(BizEntity):
__tablename__ = 'companies'
id = Column(Integer, ForeignKey('biz_entities.bizentity_id'),
primary_key=True)
name = Column('company_name', String(50))
__mapper_args__ = {'polymorphic_identity': 'company'}
def __init__(self, company_name):
self.name = company_name
def __repr__(self):
return Company('%s') % (self.name)
class Person(BizEntity):
__tablename__ = 'people'
id = Column('bizentity_id', Integer,
ForeignKey('biz_entities.bizentity_id'), primary_key=True)
first_name = Column('first_name', String(50))
middle_init = Column('middle_init', String(1))
last_name = Column('last_name', String(50))
company = relation(Company, backref=backref('employees', order_by=id))
__mapper_args__ = {'polymorphic_identity':'person'}
def __init__(self, first_name, middle_init, last_name):
self.first_name = first_name
self.middle_init = middle_init
self.last_name = last_name
def __repr__(self):
return Person('%s %s. %s') % (self.first_name, self.middle_init,
self.last_name)
# END OF MODEL
now when I try to use it like this:
# START SAMPLE CODE
comp1 = Company('Test Company')
#~ self.session.add(comp1)
#~ self.session.commit()
joe = Person('Joe', 'Q', 'Public')
joe.company = comp1
self.session.add(joe)
self.session.commit()
# END SAMPLE CODE
I get a traceback like this:
Traceback (most recent call last):
File ta_sa_test.py, line 98, in testCreateCompanyWithEmployees
self.session.commit()
File
C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py,
line 673, in commit
self.transaction.commit()
File
C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py,
line 378, in commit
self._prepare_impl()
File
C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py,
line 362, in _prepare_impl
self.session.flush()
File
C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py,
line 1356, in flush
self._flush(objects)
File
C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\session.py,
line 1434, in _flush
flush_context.execute()
File
C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py,
line 261, in execute
UOWExecutor().execute(self, tasks)
File
C:\Python26\lib\site-packages\sqlalchemy-0.5.6-py2.6.egg\sqlalchemy\orm\unitofwork.py,
line 753, in execute
[sqlalchemy] Re: Inheritance + Pseudo-adjacency-list?
On Wed, Sep 23, 2009 at 5:34 PM, Conor conor.edward.da...@gmail.com wrote:
You are missing a foreign key column in the people table that
corresponds to your Person-Company relation. As a result, SQLAlchemy
tries to use person.id as the foreign key column (because that column
happens to be a foreign key to a base table of Company) and everything
blows up.
Doh! I knew I was missing something basic.
So:
* Add a foreign key column to Person that refers to companies.id.
* Add a primaryjoin argument to your Persion.company relation, because
SQLAlchemy will now see two potential ways to get from Person to
Company (people.id - bizentities.bizentity_id and people.company_id -
companies.id) and refuse to guess which path to take. Try
primaryjoin=lambda: Person.company_id == Company.__table__.c.id. I'm
using Company.__table__.c.id instead of Company.id because Company.id
maps to the bizentities.bizentity_id column and not the
companies.id column.
-Conor
Ah. I wouldn't have thought of the primaryjoin bit, so it's a good thing
you mentioned it. The traceback you get if you leave it out is a little
intimidating. :)
Thanks for this, Connor! Everything works now.
For the benefit of future searchers, here was the final model:
class BizEntity(Base):
__tablename__ = 'biz_entities'
id = Column('bizentity_id', Integer, primary_key=True)
type = Column('bizentity_type', String(30), nullable=False)
__mapper_args__ = {'polymorphic_on': type}
class Company(BizEntity):
__tablename__ = 'companies'
id = Column(Integer, ForeignKey('biz_entities.bizentity_id'),
primary_key=True)
name = Column('company_name', String(50))
__mapper_args__ = {'polymorphic_identity': 'company'}
def __init__(self, company_name):
self.name = company_name
def __repr__(self):
return Company('%s') % (self.name)
class Person(BizEntity):
__tablename__ = 'people'
id = Column('bizentity_id', Integer,
ForeignKey('biz_entities.bizentity_id'), primary_key=True)
first_name = Column('first_name', String(50))
middle_init = Column('middle_init', String(1))
last_name = Column('last_name', String(50))
company_id = Column(Integer, ForeignKey('companies.id'))
company = relation(Company,
primaryjoin=lambda: Person.company_id ==
Company.__table__.c.id,
backref=backref('employees', order_by=id))
__mapper_args__ = {'polymorphic_identity':'person'}
def __init__(self, first_name, middle_init, last_name):
self.first_name = first_name
self.middle_init = middle_init
self.last_name = last_name
def __repr__(self):
return Person('%s %s. %s') % (self.first_name, self.middle_init,
self.last_name)
Kevin Horn
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
