[sqlalchemy] Re: Using a SQL function w/ strange syntax as default

[Michael Bayer]

how about func.convert(literal_column("UUID() USING utf8"))

Bryan wrote:
>
> I want to use MySQL's uuid() function to create default values for a
> column.  I need to convert the output of uuid() to utf-8 however, so
> the full function in SQL looks like this:
>
> CONVERT(UUID() USING utf8)
>
> I can't set this as a column default using the func.function() syntax,
> because python complains about invalid syntax when it encounters the
> USING part.
>
> How can I set this chain of functions as a default? Is there some sort
> of SQL literal function that I could use?  I tried using the literal
> object, but it of course escapes whatever the value is which does not
> work.
>
> Bryan
>
> >
>


--~--~-~--~~~---~--~~
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: Supporting fully-qualified table names and cross-database references in Sybase and SQL Server?

[phrrn...@googlemail.com]

OK. If it might be as easy as that, I will have a go and see how well
it works.

pjjH


On Mar 5, 4:31 pm, "Michael Bayer"  wrote:
> phrrn...@googlemail.com wrote:
>
> > Sybase (and SQL Server) support cross-database JOINs (Sybase even
> > supports cross-database foreign-key constraints). There are four
> > components to an object identifier:
>
> > 1 = Object name
> > 2 = Schema name
> > 3 = Database name
> > 4 = Server name
>
> > the dataserver, database and schema are assumed for one-part
> > identifiers (e.g. 'foo'), dataserver and database assumed for two-part
> > identifiers (e.g. 'dbo.foo') and, finally, dataserver assumed for
> > three-part identifiers ('production.dbo.foo')
>
> > e.g. SELECT foo.* FROM BANANA.production.dbo.foo
> >SELECT foo.* FROM production.dbo.foo
> >SELECT foo.* FROM production..foo -- same as the previous query
> > if the callers default schema is dbo
> >SELECT foo.* FROM dbo.foo
> >SELECT foo.* FROM foo
> >SELECT foo.* FROM ..foo
> > I am not so interested in supporting four-part identifiers in SA but I
> > would like to figure out how to support three-part identifiers as very
> > many of our databases have cross-database references.
>
> > One natural (to me!) way of doing this is to add a 'database' property
> > to the Table and ForeignKeyConstraint schema items and have the Sybase/
> > SQL Server dialects always emit fully-qualified three-part identifiers
> > for table names.
>
> we have an element on Table called "schema".  I had in mind that schema
> should accept dotted names, so SQLA generally doesn't need to get
> involved.  The only change needed is to the IdentifierPreparer, such that
> when quote_schema is called, it separates the name along the dot first so
> that it can quote each token separately.
--~--~-~--~~~---~--~~
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] Using a SQL function w/ strange syntax as default

[Bryan]

I want to use MySQL's uuid() function to create default values for a
column.  I need to convert the output of uuid() to utf-8 however, so
the full function in SQL looks like this:

CONVERT(UUID() USING utf8)

I can't set this as a column default using the func.function() syntax,
because python complains about invalid syntax when it encounters the
USING part.

How can I set this chain of functions as a default? Is there some sort
of SQL literal function that I could use?  I tried using the literal
object, but it of course escapes whatever the value is which does not
work.

Bryan

--~--~-~--~~~---~--~~
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: Using sqlalchemy in twisted.

[Jeff FW]

Don't use scoped_session--you'll run into problems no matter what you
do.  I'm using Perspective Broker from Twisted with SQLAlchemy.  I
make sure to create and commit/rollback a session for *every* PB
request.  It works perfectly, and that's the only way I was really
able to get it to work in all cases.

Assuming you're using Twisted in a similar way, you could write a
simple decorator to wrap any functions that need a database session in
the begin/commit stuff as necessary.

If you can give more details of how you're using Twisted, I might be
able to offer some more insight.

-Jeff

On Mar 5, 12:33 am, 一首诗  wrote:
> I'm not quite sure, but I think I'm pretty careful of sharing objects
> between threads.
>
> 1st, I only cached as few as possible orm objects.  I tried to detach
> them, but I found that if I detach them,  I can't access any of their
> fields any more.
>
> 2nd, I create new orm objects based on client request, pass them to
> class Database and then merge them to scoped sessions, change, commit
> and then discard these objects.
>
> 3rd, I switch to sqlite frequently to check if there is any database
> operation outside Database, because sqlite doesn't allow multi-thread
> access.
>
> Actually it seems to work until 2 or 3 days ago suddenly cases hang
> the server.
>
> Ah, as I've already written lots of code in ORM, I think maybe I
> should try to change Database to use a dedicated thread to handle all
> database operations.
>
> That might be a bottle neck of my application, but I really can't give
> up orm as these mapper classes are used everywhere in my application.
>
> On Mar 4, 7:26 pm, 一首诗  wrote:
>
> > Hi, all
>
> > I am using sqlalchemy in twisted in my project in the way below.
> > Defer any database operation so the twisted's main thread won't be
> > blocked.
>
> > And I use scoped_session, so that sessions won't have to be created
> > again and again.
>
> > ==
> > class Database()
> > def __init__(self, conn_str):
> > self.conn_str = conn_str
> > self.engine = create_engine(self.conn_str, echo=False)
> > self.Session = scoped_session(sessionmaker(bind = self.engine,
> >  expire_on_commit=False))
>
> > def getObjectById(self, klass, id):
> > return threads.deferToThread(self._getObjectById, klass, id)
>
> > def _getObjectById(self, klass, id):
> > sess = self.Session()
> > return sess.query(klass).get(id)
> > ==
>
> > The code doesn't work.   When I limit the thread numbers to 1
>
> > reactor.suggestThreadPoolSize(1)
>
> > Everything goes fine.  Other wise the server would be blocked and must
> > be killed by "kill 9 ...".
>
> > The result conflicts with my understanding of sqlalchemy.  Since I
> > don't share any object between threads, there should be no problem!
>
> > Ah  It always have risk to use something you haven't tried
> > before 
>
>
--~--~-~--~~~---~--~~
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: Supporting fully-qualified table names and cross-database references in Sybase and SQL Server?

[Michael Bayer]

phrrn...@googlemail.com wrote:
>
> Sybase (and SQL Server) support cross-database JOINs (Sybase even
> supports cross-database foreign-key constraints). There are four
> components to an object identifier:
>
> 1 = Object name
> 2 = Schema name
> 3 = Database name
> 4 = Server name
>
> the dataserver, database and schema are assumed for one-part
> identifiers (e.g. 'foo'), dataserver and database assumed for two-part
> identifiers (e.g. 'dbo.foo') and, finally, dataserver assumed for
> three-part identifiers ('production.dbo.foo')
>
> e.g. SELECT foo.* FROM BANANA.production.dbo.foo
>SELECT foo.* FROM production.dbo.foo
>SELECT foo.* FROM production..foo -- same as the previous query
> if the callers default schema is dbo
>SELECT foo.* FROM dbo.foo
>SELECT foo.* FROM foo
>SELECT foo.* FROM ..foo
> I am not so interested in supporting four-part identifiers in SA but I
> would like to figure out how to support three-part identifiers as very
> many of our databases have cross-database references.
>
> One natural (to me!) way of doing this is to add a 'database' property
> to the Table and ForeignKeyConstraint schema items and have the Sybase/
> SQL Server dialects always emit fully-qualified three-part identifiers
> for table names.


we have an element on Table called "schema".  I had in mind that schema
should accept dotted names, so SQLA generally doesn't need to get
involved.  The only change needed is to the IdentifierPreparer, such that
when quote_schema is called, it separates the name along the dot first so
that it can quote each token separately.


--~--~-~--~~~---~--~~
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: Could not locate any equated, locally mapped column pairs for primaryjoin condition

[Michael Bayer]

if there's an incompatible foreign_keys collection specified that can also
lead to this error.  the Page and User classes must map the author_id and
id columns normally as well, i.e. no exclude_columns or relations getting
in the way.

Alex Marandon wrote:
>
> Hi there,
>
> Could someone explain me a bit what this error message means and what
> could be causing it:
>
> sqlalchemy.exc.ArgumentError: Could not locate any equated, locally
> mapped column pairs for primaryjoin condition 'pages.author_id =
> users.id' on relation Page.author. For more relaxed rules on join
> conditions, the relation may be marked as viewonly=True.
>
> The tables are created properly with an author_id field on the pages
> table and of course the usual id field on the users table.
>
> Thanks,
> Alex
>
> >
>


--~--~-~--~~~---~--~~
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] Supporting fully-qualified table names and cross-database references in Sybase and SQL Server?

[phrrn...@googlemail.com]

Sybase (and SQL Server) support cross-database JOINs (Sybase even
supports cross-database foreign-key constraints). There are four
components to an object identifier:

1 = Object name
2 = Schema name
3 = Database name
4 = Server name

the dataserver, database and schema are assumed for one-part
identifiers (e.g. 'foo'), dataserver and database assumed for two-part
identifiers (e.g. 'dbo.foo') and, finally, dataserver assumed for
three-part identifiers ('production.dbo.foo')

e.g. SELECT foo.* FROM BANANA.production.dbo.foo
   SELECT foo.* FROM production.dbo.foo
   SELECT foo.* FROM production..foo -- same as the previous query
if the callers default schema is dbo
   SELECT foo.* FROM dbo.foo
   SELECT foo.* FROM foo
   SELECT foo.* FROM ..foo
I am not so interested in supporting four-part identifiers in SA but I
would like to figure out how to support three-part identifiers as very
many of our databases have cross-database references.

One natural (to me!) way of doing this is to add a 'database' property
to the Table and ForeignKeyConstraint schema items and have the Sybase/
SQL Server dialects always emit fully-qualified three-part identifiers
for table names.

Thoughts?

pjjH



--~--~-~--~~~---~--~~
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: SQLAlchemy Migrate

[jarrod.ches...@gmail.com]

Yes, Option 2 - However, From the example documentation, Its doesn't
implement everything.
Although its most of the way there - So option 2 is looking good to
me.

On Mar 6, 1:23 am, Lawrence Oluyede  wrote:
> On Thu, Mar 5, 2009 at 10:03 AM, jarrod.ches...@gmail.com
>
>  wrote:
>
> > I'm writing a metadata based schema migration tool.
>
> Keep in mind there are some other projects like
> 
>
> HTH
>
> --
> Lawrence Oluyede
> [eng]http://oluyede.org-http://twitter.com/lawrenceoluyede
> [ita]http://neropercaso.it-http://twitter.com/rhymes
--~--~-~--~~~---~--~~
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: Modifiable collections on a many-to-many, same table mapping

[Kevin Dangoor]

On Thu, Mar 5, 2009 at 12:38 PM, Michael Bayer  wrote:
> make those m2m relations(), and any other relation that doesn't represent
> a single path of persistence, viewonly=True .   its all the "three ways to
> see the same thing" going on, minus viewonly=True means "three ways to
> persist the same thing", leading to errors like that.

OK, I can see this case being a bit complex. I just wanted to be sure
I wasn't missing some mechanism for handling it.

Thanks!
Kevin

-- 
Kevin Dangoor

work: http://labs.mozilla.com/
email: k...@blazingthings.com
blog: http://www.BlueSkyOnMars.com

--~--~-~--~~~---~--~~
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] Could not locate any equated, locally mapped column pairs for primaryjoin condition

[Alex Marandon]

Hi there,

Could someone explain me a bit what this error message means and what
could be causing it:

sqlalchemy.exc.ArgumentError: Could not locate any equated, locally
mapped column pairs for primaryjoin condition 'pages.author_id =
users.id' on relation Page.author. For more relaxed rules on join
conditions, the relation may be marked as viewonly=True.

The tables are created properly with an author_id field on the pages
table and of course the usual id field on the users table.

Thanks,
Alex

--~--~-~--~~~---~--~~
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: Sybase DB-API driver uses @foo for placeholder names and expect parameter dict keys to be similarly named

[phrrn...@googlemail.com]

The problem with the connection being returned to the pool was due to
executing the SET IDENTITY_INSERT statement on the *cursor* rather
than the *connection*. The documentation states that the connection
will be returned to the pool when a statement is executed on it that
doesn't return any results (such as the SET statement). using
self.connection.execute solved that problem.

There was a difficult to diagnose problem with python-sybase in that
sometimes the keys of the parameter dict were in unicode which caused
the buf.name = name assignment to throw a TypeError. Coercing the
param keys via str() solved that problem.

In Sybase, a column declaration without NULL/NOT NULL defaults to NOT
NULL so a bunch of the tests need to be updated.

The schema introspection stuff seems to work OK albeit with some low-
level querying of the system tables. I started off with a higher-level
implementation but abandoned it due to all kinds of (possibly
spurious) problems. There are still some problems with the test tear-
downs as tables are not being dropped in the correct order.

All in all, the driver is now in a state that can be called
'buggy' (as opposed to being completely dysfunctional)

pjjH


On Feb 27, 4:29 pm, "phrrn...@googlemail.com"
 wrote:
> Yes, it is based off the mssql code but I made some modifications to
> it to take care of situations like, for example, where there is an
> identity column but it is not the primary key (and hence not a
> 'sequence'). This means a read off the catalog to find the identity
> column (I believe that only one identity column is permitted per
> table). I was wondering if some 'bad thing' happens if you execute a
> select on the cursor and retrieve results when you are in the
> pre_exec.
>
> I don't know what you are referring to when you say 'throw a pdb' .. I
> hope it has something to do with the debugger!
>
> As for being in deep, I am afraid we are only starting: Sybase has
> enough 'special' stuff to keep us busy for a long time e.g. cross-
> database referential integrity constraints. database-specific default
> schemas (e.g. login foo may have schema 'dbo' in database apple  but
> schema 'guest' in database pear and schema 'prod' in database banana).
> Then what does one do about remote objects mapped in via CIS (e.g.
> REMOTE.production.dbo.very_important_table) (actually this is a
> problem with SQL Server also)
>
> pjjH
>
> On Feb 27, 4:05 pm, "Michael Bayer"  wrote:
>
> > phrrn...@googlemail.com wrote:
>
> > > I want to automatically set IDENTITY_INSERT for a table if the
> > > identity column is explicitly listed. Likewise, after execution of an
> > > insert on a table with an identity column we want to retrieve the
> > > identity value.
>
> > > Any idea why the following code would cause the connection to be
> > > checked in between the pre_exec() and the actual execution of the
> > > statement?  I have enabled high levels of debugging on the python-
> > > sybase driver and can see that a new connection is made *after* the
> > > 'SET IDENTITY_INSERT foo ON'  and the actual command runs on that new
> > > connection (and, of course, fails as IDENTITY_INSERT is not enabled on
> > > that table for the new connection).
>
> > Assuming you took that code from the MSSQL dialect, it should be fine.
> > that looks like an older version of it, though...in 0.6 take a look in
> > mssql/base.py for the latest version of the IDENTITY_INSERT dance.
>
> > but no there's nothing in there in any case that would cause a second
> > connection to be checked out. throw a pdb into the Connection constructor,
> > or perhaps in pool.connect(), to track where that's coming from.
>
> > sorry you're in deep  :)
>
> > > pjjH
>
> > > class SybaseSQLExecutionContext(default.DefaultExecutionContext):
> > > def _table_identity_column(self, t):
> > > """Return the name of the this table's identity column"""
> > > # negative caching
> > > if not hasattr(t, '_identity_column'):
> > > t._identity_column = None
> > > s = r"""SELECT cols.name FROM syscolumns as cols JOIN
> > > sysobjects as o ON (cols.id = o.id) WHERE o.name ='%s' and cols.status
> > > & 0x80 = 0x80""" % (t.name)
> > > self.cursor.execute(s)
> > > r = self.cursor.fetchone()
> > > if r:
> > > t._identity_column  = r[0]
> > > return t._identity_column
>
> > > def pre_exec(self):
> > > self.HAS_IDENTITY = False
> > > self.IDENTITY_INSERT = False
> > > # What about UPDATE statements? Is this even possible in
> > > Sybase?
> > > if self.compiled.isinsert:
> > > if self._table_identity_column
> > > (self.compiled.statement.table):
> > > self.HAS_IDENTITY = True
> > > identity_column = self._table_identity_column
> > > (self.compiled.statement.table)
> > > if identity_column in self.compiled_parameters[0].keys
> > > ():
> > > 

[sqlalchemy] Re: Database migrations using declarative

[Michael Bayer]

im finding it easiest to just autoload the tables in my migrate script.  
That way there's no "cut and paste the table def" going on, you just load
them as they are from the DB.  and "the schema definition stays in the
database" for those who get bent out of shape about that.  problem solved.
 the migrate docs should suggest this too.


Joril wrote:
>
> Hi everyone!
> I'm looking for a way to handle database migrations/upgrades and found
> projects such as sqlalchemy-migrate and mikuru.. My understanding is
> that those projects expect the model to be in NON-declarative form,
> that is, with users_table = Table(...) and the like.. But what if my
> program uses exclusively declarative? Can I use them all the same? Or
> are there tools that work with declarative, or should I roll my own?
>
> Many thanks for your time!
> >
>


--~--~-~--~~~---~--~~
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: Modifiable collections on a many-to-many, same table mapping

[Michael Bayer]

make those m2m relations(), and any other relation that doesn't represent
a single path of persistence, viewonly=True .   its all the "three ways to
see the same thing" going on, minus viewonly=True means "three ways to
persist the same thing", leading to errors like that.


Kevin Dangoor wrote:
>
> I have a many-to-many mapping that joins against a single table. The
> relation works fine, except when I try to add a new mapping via the
> list created by relation(). To make things extra interesting, I'm
> using declarative (which I really like, actually, because it maps in a
> fairly straightforward manner to standard SA-isms). Here's the setup:
>
> class Connection(Base):
> __tablename__ = "connections"
> followed_id = Column(Integer, ForeignKey('users.id',
> ondelete='cascade'), primary_key=True)
> followed = relation('User',
> primaryjoin='User.id==Connection.followed_id')
>
> following_id = Column(Integer, ForeignKey('users.id',
> ondelete='cascade'), primary_key=True)
> following = relation('User',
> primaryjoin='User.id==Connection.following_id')
>
> followed_viewable = Column(Boolean, default=False)
>
> class User(Base):
> __tablename__ = "users"
>
> id = Column(Integer, primary_key=True)
> username = Column(String(128), unique=True)
> email = Column(String(128))
> password = Column(String(20))
> settings = Column(PickleType())
> projects = relation('Project', backref='owner')
> quota = Column(Integer, default=10)
> amount_used = Column(Integer, default=0)
>
> # this is the interesting part
> users_i_follow = association_proxy('i_follow', 'followed')
> i_follow = relation(Connection,
> primaryjoin=Connection.following_id==id,
> secondary=Connection.__table__,
> secondaryjoin=id==Connection.followed_id)
>
> users_following_me = association_proxy('following_me', 'following')
> following_me = relation(Connection,
> primaryjoin=Connection.followed_id==id,
> secondary=Connection.__table__,
> secondaryjoin=id==Connection.following_id)
>
> def follow(self, ou):
> self.i_follow.append(Connection(following=self, followed=ou))
>
> So, everything works just fine when I add the connection to the session:
 u1 = User("kevin", "foo", "bar")
 u2 = User("joe", "foo", "bar")
 s.add(u1)
 s.add(u2)
 s.add(Connection(following=u1, followed=u2))
 s.flush()
 u1.users_i_follow
> []
 u1.i_follow
> []
 u2.users_following_me
> []
>
> But when I try to use that follow method defined up there, I get an
> exception:
>
>   File
> "/Users/admin/projects/bespin/lib/python2.5/site-packages/sqlalchemy/orm/sync.py",
> line 81, in _raise_col_to_prop
> raise exc.UnmappedColumnError("Can't execute sync rule for source
> column '%s'; mapper '%s' does not map this column.  Try using an
> explicit `foreign_keys` collection which does not include destination
> column '%s' (or use a viewonly=True relation)." % (source_column,
> source_mapper, dest_column))
> sqlalchemy.orm.exc.UnmappedColumnError: Can't execute sync rule for
> source column 'users.id'; mapper 'Mapper|Connection|connections' does
> not map this column.  Try using an explicit `foreign_keys` collection
> which does not include destination column 'connections.followed_id'
> (or use a viewonly=True relation).
>
> users.id *is* mapped in Connection (twice, in fact, which may be the
> problem...)  I'm hoping that there's some simple flag I'm missing
> here.
>
> Kevin
>
> --
> Kevin Dangoor
>
> work: http://labs.mozilla.com/
> email: k...@blazingthings.com
> blog: http://www.BlueSkyOnMars.com
>
> >
>


--~--~-~--~~~---~--~~
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: Modifiable collections on a many-to-many, same table mapping

[az]

as i see u have normal mapping for the Connection, and still use it as 
secondary table; once i did use such (semi-legal?:) thing but the relation 
was readonly.
try not giving secondary* args ?

On Thursday 05 March 2009 18:37, Kevin Dangoor wrote:
> I have a many-to-many mapping that joins against a single table. The
> relation works fine, except when I try to add a new mapping via the
> list created by relation(). To make things extra interesting, I'm
> using declarative (which I really like, actually, because it maps in a
> fairly straightforward manner to standard SA-isms). Here's the setup:
>
> class Connection(Base):
> __tablename__ = "connections"
> followed_id = Column(Integer, ForeignKey('users.id',
> ondelete='cascade'), primary_key=True)
> followed = relation('User',
> primaryjoin='User.id==Connection.followed_id')
>
> following_id = Column(Integer, ForeignKey('users.id',
> ondelete='cascade'), primary_key=True)
> following = relation('User',
> primaryjoin='User.id==Connection.following_id')
>
> followed_viewable = Column(Boolean, default=False)
>
> class User(Base):
> __tablename__ = "users"
>
> id = Column(Integer, primary_key=True)
> username = Column(String(128), unique=True)
> email = Column(String(128))
> password = Column(String(20))
> settings = Column(PickleType())
> projects = relation('Project', backref='owner')
> quota = Column(Integer, default=10)
> amount_used = Column(Integer, default=0)
>
> # this is the interesting part
> users_i_follow = association_proxy('i_follow', 'followed')
> i_follow = relation(Connection,
> primaryjoin=Connection.following_id==id,
> secondary=Connection.__table__,
> secondaryjoin=id==Connection.followed_id)
>
> users_following_me = association_proxy('following_me', 'following')
> following_me = relation(Connection,
> primaryjoin=Connection.followed_id==id,
> secondary=Connection.__table__,
> secondaryjoin=id==Connection.following_id)
>
> def follow(self, ou):
> self.i_follow.append(Connection(following=self, followed=ou))
>
> So, everything works just fine when I add the connection to the session:
> >>> u1 = User("kevin", "foo", "bar")
> >>> u2 = User("joe", "foo", "bar")
> >>> s.add(u1)
> >>> s.add(u2)
> >>> s.add(Connection(following=u1, followed=u2))
> >>> s.flush()
> >>> u1.users_i_follow
>
> []
>
> >>> u1.i_follow
>
> []
>
> >>> u2.users_following_me
>
> []
>
> But when I try to use that follow method defined up there, I get an
> exception:
>
>   File
> "/Users/admin/projects/bespin/lib/python2.5/site-packages/sqlalchemy/orm/sy
>nc.py", line 81, in _raise_col_to_prop
> raise exc.UnmappedColumnError("Can't execute sync rule for source
> column '%s'; mapper '%s' does not map this column.  Try using an
> explicit `foreign_keys` collection which does not include destination
> column '%s' (or use a viewonly=True relation)." % (source_column,
> source_mapper, dest_column))
> sqlalchemy.orm.exc.UnmappedColumnError: Can't execute sync rule for
> source column 'users.id'; mapper 'Mapper|Connection|connections' does
> not map this column.  Try using an explicit `foreign_keys` collection
> which does not include destination column 'connections.followed_id'
> (or use a viewonly=True relation).
>
> users.id *is* mapped in Connection (twice, in fact, which may be the
> problem...)  I'm hoping that there's some simple flag I'm missing
> here.
>
> Kevin

--~--~-~--~~~---~--~~
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: Database migrations using declarative

[Kevin Dangoor]

On Thu, Mar 5, 2009 at 12:17 PM, Joril  wrote:
> I'm looking for a way to handle database migrations/upgrades and found
> projects such as sqlalchemy-migrate and mikuru.. My understanding is
> that those projects expect the model to be in NON-declarative form,
> that is, with users_table = Table(...) and the like.. But what if my
> program uses exclusively declarative? Can I use them all the same? Or
> are there tools that work with declarative, or should I roll my own?

Declarative is not really a problem... You can always get to the table
object with ClassName.__table__

When you create new columns, don't forget that you need to flip the
column declaration around

class Bar(Base):
foo = Column(String)

becomes

foo = Column('foo', String)


I'm using declarative with sqlalchemy-migrate just fine.

Kevin


-- 
Kevin Dangoor

work: http://labs.mozilla.com/
email: k...@blazingthings.com
blog: http://www.BlueSkyOnMars.com

--~--~-~--~~~---~--~~
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] Database migrations using declarative

[Joril]

Hi everyone!
I'm looking for a way to handle database migrations/upgrades and found
projects such as sqlalchemy-migrate and mikuru.. My understanding is
that those projects expect the model to be in NON-declarative form,
that is, with users_table = Table(...) and the like.. But what if my
program uses exclusively declarative? Can I use them all the same? Or
are there tools that work with declarative, or should I roll my own?

Many thanks for your time!
--~--~-~--~~~---~--~~
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] Modifiable collections on a many-to-many, same table mapping

[Kevin Dangoor]

I have a many-to-many mapping that joins against a single table. The
relation works fine, except when I try to add a new mapping via the
list created by relation(). To make things extra interesting, I'm
using declarative (which I really like, actually, because it maps in a
fairly straightforward manner to standard SA-isms). Here's the setup:

class Connection(Base):
__tablename__ = "connections"
followed_id = Column(Integer, ForeignKey('users.id',
ondelete='cascade'), primary_key=True)
followed = relation('User', primaryjoin='User.id==Connection.followed_id')

following_id = Column(Integer, ForeignKey('users.id',
ondelete='cascade'), primary_key=True)
following = relation('User', primaryjoin='User.id==Connection.following_id')

followed_viewable = Column(Boolean, default=False)

class User(Base):
__tablename__ = "users"

id = Column(Integer, primary_key=True)
username = Column(String(128), unique=True)
email = Column(String(128))
password = Column(String(20))
settings = Column(PickleType())
projects = relation('Project', backref='owner')
quota = Column(Integer, default=10)
amount_used = Column(Integer, default=0)

# this is the interesting part
users_i_follow = association_proxy('i_follow', 'followed')
i_follow = relation(Connection,
primaryjoin=Connection.following_id==id,
secondary=Connection.__table__,
secondaryjoin=id==Connection.followed_id)

users_following_me = association_proxy('following_me', 'following')
following_me = relation(Connection,
primaryjoin=Connection.followed_id==id,
secondary=Connection.__table__,
secondaryjoin=id==Connection.following_id)

def follow(self, ou):
self.i_follow.append(Connection(following=self, followed=ou))

So, everything works just fine when I add the connection to the session:
>>> u1 = User("kevin", "foo", "bar")
>>> u2 = User("joe", "foo", "bar")
>>> s.add(u1)
>>> s.add(u2)
>>> s.add(Connection(following=u1, followed=u2))
>>> s.flush()
>>> u1.users_i_follow
[]
>>> u1.i_follow
[]
>>> u2.users_following_me
[]

But when I try to use that follow method defined up there, I get an exception:

  File 
"/Users/admin/projects/bespin/lib/python2.5/site-packages/sqlalchemy/orm/sync.py",
line 81, in _raise_col_to_prop
raise exc.UnmappedColumnError("Can't execute sync rule for source
column '%s'; mapper '%s' does not map this column.  Try using an
explicit `foreign_keys` collection which does not include destination
column '%s' (or use a viewonly=True relation)." % (source_column,
source_mapper, dest_column))
sqlalchemy.orm.exc.UnmappedColumnError: Can't execute sync rule for
source column 'users.id'; mapper 'Mapper|Connection|connections' does
not map this column.  Try using an explicit `foreign_keys` collection
which does not include destination column 'connections.followed_id'
(or use a viewonly=True relation).

users.id *is* mapped in Connection (twice, in fact, which may be the
problem...)  I'm hoping that there's some simple flag I'm missing
here.

Kevin

-- 
Kevin Dangoor

work: http://labs.mozilla.com/
email: k...@blazingthings.com
blog: http://www.BlueSkyOnMars.com

--~--~-~--~~~---~--~~
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: recursive sql

[Michael Bayer]

its something we should take on at some point, yes, but no concrete plans
have been made.each DB has a different take on it and the common set
of functionality would need to be extracted.

che wrote:
>
> hi alchemysts,
>
> as of end of march 2009 there will be version of PostgreSQL
> (hopefully, finally;) that will support recursive sqls (WITH
> RECURSIVE...) and there are also at least 4 other main SQL that
> already support it (DB2, MSSQL, Firebird, Oracle (syntax diff)), do
> you have plans for adding this useful feature to the core SQL Alchemy?
>
> regards,
> stefan
> >
>


--~--~-~--~~~---~--~~
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] recursive sql

[che]

hi alchemysts,

as of end of march 2009 there will be version of PostgreSQL
(hopefully, finally;) that will support recursive sqls (WITH
RECURSIVE...) and there are also at least 4 other main SQL that
already support it (DB2, MSSQL, Firebird, Oracle (syntax diff)), do
you have plans for adding this useful feature to the core SQL Alchemy?

regards,
stefan
--~--~-~--~~~---~--~~
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] Complex joins (newbie alert)

[Marcin Krol]

Hello everyone,

So I've got this piece of code:

selectexpr = session.query(Host, Architecture, OS_Kind)

selectexpr = 
selectexpr.filter(Host.Architecture_id==Architecture.id).filter(Architecture.Architecture=='IBM
 
xSeries')

selectexpr = 
selectexpr.filter(Host.OS_Kind_id==OS_Kind.id).filter(OS_Kind.OS_Kind=='Linux')

selectexpr = selectexpr.filter_by(Up_n_running = True).order_by(field)


Now, this works. The reason I'm doing it not in one piece (i.e. not in 
one big expression) is primarily because some expressions in the final 
app will be optional, i.e. the user may or may not filter by 
Architecture and may or may not filter by OS_Kind.

This also works:

selectexpr = 
session.query(Host).select_from(join(Host,OS_Kind)).filter(OS_Kind.OS_Kind 
=='Linux').filter_by(Up_n_running = True).order_by(field)

Is there some way in sqa to join on 3 tables (like in the 1st piece of 
code above) or more?

To tell the truth, I'm not all that fluent in SQL per se, so I have 
trouble working out the solution..

Regards,
Marcin Krol

--~--~-~--~~~---~--~~
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: miss for postgres_from in update() :(

[Michael Bayer]

visit_update() would have to be rewritten in the base compiler to  
support addition of extra components.


On Mar 5, 2009, at 5:05 AM, sector119 wrote:

>
> I create a small (4 lines) patch for databases/postgres.py
>
> def _append_from(self, text, stmt):
>return text.replace(' WHERE', ' FROM ' + string.join([table.name
> for table in stmt.kwargs['postgres_from']], ', ') + ' WHERE')
>
> def visit_update(self, update_stmt):
>  text = super(PGCompiler, self).visit_update(update_stmt)
>  if 'postgres_returning' in update_stmt.kwargs:
>text = self._append_returning(text, update_stmt)
>  if 'postgres_from' in update_stmt.kwargs:
>text = self._append_from(text, update_stmt)
>  return text
>
> But I think that text.replace(...) is ugly, may be you know how to
> insert FROM clause before WHERE without replacements?
>
> >


--~--~-~--~~~---~--~~
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: SQLAlchemy Migrate

[Lawrence Oluyede]

On Thu, Mar 5, 2009 at 10:03 AM, jarrod.ches...@gmail.com
 wrote:
>
>
> I'm writing a metadata based schema migration tool.

Keep in mind there are some other projects like


HTH

-- 
Lawrence Oluyede
[eng] http://oluyede.org - http://twitter.com/lawrenceoluyede
[ita] http://neropercaso.it - http://twitter.com/rhymes

--~--~-~--~~~---~--~~
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: miss for postgres_from in update() :(

[sector119]

I create a small (4 lines) patch for databases/postgres.py

def _append_from(self, text, stmt):
return text.replace(' WHERE', ' FROM ' + string.join([table.name
for table in stmt.kwargs['postgres_from']], ', ') + ' WHERE')

def visit_update(self, update_stmt):
  text = super(PGCompiler, self).visit_update(update_stmt)
  if 'postgres_returning' in update_stmt.kwargs:
text = self._append_returning(text, update_stmt)
  if 'postgres_from' in update_stmt.kwargs:
text = self._append_from(text, update_stmt)
  return text

But I think that text.replace(...) is ugly, may be you know how to
insert FROM clause before WHERE without replacements?

--~--~-~--~~~---~--~~
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] SQLAlchemy Migrate

[jarrod.ches...@gmail.com]

Hi All

I'm writing a metadata based schema migration tool.

As SQLAlchemy doesn't support much schema modification. I will
implement a complete set of schema migration functions one way or
another for several of the SQLAlchemy supported databases.

My question is, Where should these function reside? Options are :
1) Branch SQLAlchemy and make the modifications
2) Branch SQLAlchemy Migrate and make the modifications
3) Write the code and not distribute it cause no one cares.
4) ? Suggestions ?



PS, Thanks Michael Bayer, SQLAlchemy is great.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---