TG2 SQLAlchemy relations

[Artem Marchenko]

Hi All

I am playing with TurboGears 2 for about a week already. So far
everything looks great and I am able to create functionality with
exceptionally small amount of neat-looking code.

At the moment I am stuck with the SQLAlchemy based relations. I want
to build simple one-to-many relationship (think User-Addresses for
example) and I can't get how to do that. Wiki20 tutorial doesn't touch
it and SQLAlchemy tutorial at http://www.sqlalchemy.org/docs/05/ormtutorial.html#building-a-relation
seems to be based on a functionality not yet supported (not
recommended?) by TG yet. I was able to find some topics about similar
things in this group, but it looks like TG and SQLAlchemy evolve
faster, than tutorials and discussions :)

Could anybody, please, post a simple one to many model example?
Something that would allow accessing addresses e.g. via the following:
>>> jack = User('jack')
>>> jack.addresses
[]


Best regards,
Artem.

[francois chesnay]

+1

Completely agree with you. Maybe a "library of simple TG2.0 apps to help the programmers understand quicker tg would be useful.

I started created a small application in tg2 just as an example and would be happy to contribute, once the "save" problem in Wiki20 is solved.

[David Gardner]

Not sure about TG2, but I have been using SQLAlchemy 0.5 against TG 1.0.7 for a while now. Note in my case I'm using SQLAlchemy from the command line, and then importing my SA code into model.py in TG and exposing it as a webapp. I'm not sure I remember why but adding 'save_on_init=False' to my mappers fixed something.
 

from sqlalchemy import *
from sqlalchemy.orm import *

# Turbogears compatability http://www.sqlalchemy.org/docs/04/session.html#unitofwork_contextual
Session = scoped_session(sessionmaker(autoflush=True, autocommit=True))
mapper = Session.mapper

db_uri='postgres://%s:%s@%s/%s' % (DB_USER,DB_PASS,DB_HOST,DB_NAME)
db = create_engine (db_uri)
metadata = MetaData(db)

class Note(object):
    pass

class User (object):
    pass

note_table = Table('note', metadata, autoload=True)
users_table = Table ('users', metadata, autoload=True)

mapper(Note, note_table, save_on_init=False)

mapper (User, users_table, properties={
        'Notes':relation(Note, backref='Author', lazy=True)},
        save_on_init=False)

........

session=create_session()
user=session.query(User).get('dgardner')
for n in user.Notes:
    print n.note

-- 
David Gardner
Pipeline Tools Programmer, "Sid the Science Kid"
Jim Henson Creature Shop
dgar...@creatureshop.com

[Artem Marchenko]

Thank you, David

I tried your example and SQLAlchemy seems to correctly modify the
objects for linking them.
Now I only need to create tables first. In your case, you probably
manually created them in Postgre. I am trying to follow the wiki20
style and make SQLAlchemy create tables (in Sqlite). At the moment I
am stuck at the point of creating the foreign key. When calling
metadata.create_all (and actually drop_all) SQLAlchemy tells that it
can't create foreign key to the non-existing table.. of course,
because it hasn't been created yet. I wonder how people create linked
tables via SQLAlchemy.

wordpairtopics_table = Table("wordpairtopics", metadata,
Column("id", Integer, primary_key = True),
Column("title", Unicode(100), nullable = False)
)

class WordPairTopic(object):
def __init__(self, title):
self.title = title

wordpairs_table = Table("wordpairs", metadata,
Column("id", Integer, primary_key=True),
Column("sourcetext", Unicode(100), nullable = False),
Column("desttext", Unicode(100), nullable = False),
Column("topic_id", Integer, ForeignKey("worpairtopics.id"))
)

...
engine = create_engine("sqlite:///testdevdata2.db", echo=True)
DBSession.configure(bind=engine)

# Create the tables
metadata.drop_all(engine)
metadata.create_all(engine)


Best regards,
Artem.

On Jan 9, 11:55 pm, David Gardner <dgard...@creatureshop.com> wrote:
> Not sure about TG2, but I have been using SQLAlchemy 0.5 against TG
> 1.0.7 for a while now. Note in my case I'm using SQLAlchemy from the
> command line, and then importing my SA code into model.py in TG and
> exposing it as a webapp. I'm not sure I remember why but adding
> 'save_on_init=False' to my mappers fixed something.
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
>

> # Turbogears compatabilityhttp://www.sqlalchemy.org/docs/04/session.html#unitofwork_contextual

> > it and SQLAlchemy tutorial athttp://www.sqlalchemy.org/docs/05/ormtutorial.html#building-a-relation

> > seems to be based on a functionality not yet supported (not
> > recommended?) by TG yet. I was able to find some topics about similar
> > things in this group, but it looks like TG and SQLAlchemy evolve
> > faster, than tutorials and discussions :)
>
> > Could anybody, please, post a simple one to many model example?
> > Something that would allow accessing addresses e.g. via the following:
>
> >>>> jack = User('jack')
> >>>> jack.addresses
>
> > []
>
> > Best regards,
> > Artem.
>
> --
> David Gardner
> Pipeline Tools Programmer, "Sid the Science Kid"
> Jim Henson Creature Shop

> dgard...@creatureshop.com

[Artem Marchenko]

But that's slightly different topic, so I created a separate thread in
this group - http://groups.google.com/group/turbogears/t/d22452934e8fd6e4.
Hopefully narrow focus will help guys who will be looking for concrete
answers later.

Artem.

[Mark Ramm]

> wordpairtopics_table = Table("wordpairtopics", metadata

...

> Column("topic_id", Integer, ForeignKey("worpairtopics.id"))

the second line here is wrong. You have to spell the name of the
table the same in both cases ;)