SQLAlchemy not really integrated; how do I use it?

[mindlace]

Gentlebeings,

I'm using today's checkout of TurboGears. I'd like to use SQLAlchemy,
but I can't seem to get things to work; I'd love some feedback as to
whether I'm missing something or things just aren't integrated yet.

* tg-admin sql create - doesn't seem to do anything, even when it runs
without error.
* weirdnesses with paths - a sqlite path entered into dev.cfg with the
syntax presented returns a "can't open db" error. Stripping out the
'current dir' business and replacing it with an absolute path doesn't
work. passing an identical path to create_engine() works fine. Why?
e.g. sqlalchemy.dburi = "sqlite:///Users/mindlace/Source/XUI/dev.db" #
doesn't work!

* None of the toolbox stuff seems to be sqlalchemy aware.

Should I even be trying to use SQLAlchemy in turbogears at this point?

[Yves-Eric Martin]

This may be a starting point:

http://trac.turbogears.org/turbogears/wiki/UsingSqlAlchemy

Haven't tried myself yet, though.

--
Yves-Eric

[Ethan Fremen]

On 8/10/06, Yves-Eric Martin <yema...@gmail.com> wrote:

This may be a starting point:

http://trac.turbogears.org/turbogears/wiki/UsingSqlAlchemy

Yeah, there are more updated documents than that, and the repository has more support in it than that document indicates.

However, aside from an enthusiastic statement on the SQLAlchemy site and several statements from Kevin Dangoor that SQLAlchemy is the way things are going, I can't figure out whether SQLAlchemy will be a 'first class' cousin to SQLObject in the 1.0 release of TurboGears; as I'm developing code for others to use I need to know at what point they'll be able to use the framework's docs/tools to understand the code.

~ethan fremen

[Sanjay]

I don't think toolbox and sql create are supported yet for SQLAlchemy.
Apart from these, it seems easy to use SQLAlchemy with whatever I have
experienced. Novice tips those I have learned:

1. create a project with --sqlalchemy flag
2. use the predefined "metadata" and "session" (from
turbogears.database import metadata, session)
3. The context is "session.context"
4. Use the activemapper layer if requirements are simple
5. Use assign_mapper if activemapper is not sufficient to handle the
requirements, or there is a liking to separate table definitions etc.
from business logic

Sanjay

[Kevin Dangoor]

On Aug 10, 2006, at 6:22 AM, Sanjay wrote:

>
> I don't think toolbox and sql create are supported yet for SQLAlchemy.
> Apart from these, it seems easy to use SQLAlchemy with whatever I have
> experienced. Novice tips those I have learned:

"sql create" is supported. The other Toolbox features do not yet
support SQLAlchemy (and won't in the 1.0 timeframe). Your tips seem
pretty reasonable, and SQLAlchemy support has worked for me (I demoed
it at EuroPython last month).

Kevin

[Sanjay]

Thanks for info about sql create support, which I was not aware of.

SQLAlchemy support is working nicely for me so far (except one trivial
bug involving autoload). My choice is SQLAlchemy.

Sanjay

[Ethan Fremen]

Hmm... I don't know what I was doing wrong, but sql create didn't actually make the tables.

What from where do I get assign_mapper? I was trying to use mapper() from SQLAlchemy.

The examples from sqlalchemy have me doing extra code to make sure the tables get created, I assumed that sql create was supposed to be handling that.

~ethan fremen

[Sanjay]

> What from where do I get assign_mapper? I was trying to use mapper() from
> SQLAlchemy.

The code sample below (extracted from my current project) tries to
explain the SQLAlchemy basics that I follow:

# requirement: Users can have many contacts
# and a contact can appear in many users contact list
# one some contacts are marked as favorites

from sqlalchemy import *
from sqlalchemy.ext.assignmapper import assign_mapper
import turbogears
from turbogears.database import metadata, session

# Table definitions

user_tbl = Table('tg_user', metadata,
Column('user_id', Integer, primary_key=True),
Column('user_name', Unicode(50), unique=True),
Column('email_address', Unicode(255), unique=True),
Column('display_name', Unicode(30), nullable=False),
Column('password', Unicode(40), nullable=False, default='welcome'),
Column('created', DateTime, default=datetime.now),
Column('middle_name', Unicode(30)),
Column('last_name', Unicode(30), nullable=False)
)

contact_tbl = Table('contact', metadata,
Column("user_id", Integer, ForeignKey("tg_user.user_id"),
primary_key=True),
Column("contact_id", Integer, ForeignKey("tg_user.user_id"),
primary_key=True),
Column("favorite", Boolean, nullable=False, default=False))

# End table definitions

# To separate business logic, I put the business object classes in
different
# files and import it here.
# But, for the sake of this sample code, writing those below.

class User(object):
def __repr__(self):
return "User: %s" % self.display_name + ". Contacts: " +
repr(self.contacts)

class Contact(object):
def __repr__(self):
return "Contact: " + repr(self.contact.display_name) + "
Favorite: " + repr(self.favorite)

# End business object classes

# Mapping
assign_mapper(session.context, Contact, contact_tbl,
primary_key = [contact_tbl.c.user_id, contact_tbl.c.contact_id],
properties={
'contact' : relation(User,

primaryjoin=user_tbl.c.user_id==contact_tbl.c.contact_id,
lazy=False)})

assign_mapper(session.context, User, user_tbl, properties = {
'contacts' : relation(Contact,

primaryjoin=user_tbl.c.user_id==contact_tbl.c.user_id,
association=User)})
#End mapping

I learned about this pattern from Michael Bayer's post at
https://sourceforge.net/mailarchive/forum.php?thread_id=24200415&forum_id=46961
(second thread)

Sanjay

[atwork]

I managed to convert my SQLObject model to SQLAlchemy (v0.2). My model
has one-to-many and many-to-many relations. Can't help you with
self-joins if that's your problem. Check this post:
http://atwork.wordpress.com/2006/08/11/switching-to-sqlalchemy/

-atwork

[Ethan Fremen]

Thank you both for the amazing, information filled response to my
query. I may use SQLAlchemy later, but as I am a bear of very little
brains on a very tight deadline I will use SQLObject as all of the
examples use it.

~ethan fremen