Re: [sqlalchemy] Is there a good ORM tutorial? And advice on parent/child management
impetus for people outside the project to write >> tutorials, blog posts, etc. I'd like to see more of that kind of thing as >> well, but everyone has limited time, I guess. >> > > Without wishing to seem critical, I find the ORM docs pretty difficult to > follow. They seem to jump around between schema design (DDL) and usage > (select and DML) in a pretty haphazard fashion, and the information about > transactional control and session management seems to be spread around the > various sections. That's basically just my perspective, and may reflect my > experience, but it is frustrating. Agreed entirely about 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] Polymorphic joined-table inheritance
; WHERE client.ctype = %(ctype_1)s AND client.ctype IN (NULL) > ## > > which also looks for NULL values in the where clause. > > > And what happens if I query the ClientVip? > Well, there are no where clauses, so we are good: > > ## > SELECT clientvip.id AS clientvip_id, client.id AS client_id, client.ctype > AS client_ctype, client.refinternal AS client_refinternal, > ... > clientvip.isclienttop AS clientvip_isclienttop, clientvip.numlisttop AS > clientvip_numlisttop > FROM client JOIN clientvip ON client.id = clientvip.id > ## > > > Appendix: Inserting client > - > > You might have seen that in the __init__ methods I have commented out the > " self.ctype = '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
magic", I don't want to interfere with its mechanisms any > more than I have to. If I have to keep track of what's gone to the > database, and query for existing instances and manage the transactions, I > probably should just use the SQL layer directly (I have a lot of > experiences with databases, but very little with ORMs, so pure DB code > isn't too scary for me, but on the other hand I don't know what benefits > the ORM should be giving me that I'm not seeing). > > Is this an application that doesn't 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] 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 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, 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 wrote: > check out sqlautocode: https://code.google.com/p/sqlautocode/ > > > On Jul 25, 2013, at 12:19 PM, Henning Sprang > 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 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 wrote: > 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 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 wrote: > 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 5:34 PM, Conor 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 "" % (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 "" % (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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Inheritance + Pseudo-adjacency-list?
On Wed, Sep 23, 2009 at 10:57 AM, Kevin Horn wrote:
>
>
> On Wed, Sep 23, 2009 at 12:27 AM, Michael Bayer
> wrote:
>
>>
>>
>> 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 "" % (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 "" % (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
[sqlalchemy] Re: Inheritance + Pseudo-adjacency-list?
On Wed, Sep 23, 2009 at 12:27 AM, Michael Bayer wrote:
>
>
> 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
-~--~~~~--~~--~--~---
