[sqlalchemy] Examples for Index() in 07Migration wiki
In the 07Migration wiki, there is this line:
Index('name', name="idx_name")
which doesn't work for me. I have to change it to:
Index('idx_name', 'name')
Is it a typo or some python 3 only syntax?
--
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] Multi-table (polymorphic?) row-specific relations
so, some background, "table_name" is a Rails-style polymorphic association,
very old blog post about this at: http://techspot.zzzeek.org/archive/2007/05/1/
So there's a hacky way there, which you can adopt to declarative, to do the
"foreign key thats not really a foreign key" thing.I don't like it since
the lack of an FK means the relatoinship is entirely corruptable. From
reading the post you'll see i always find a way to use foreign keys correctly,
which means an extra table.
Polymorphically, youd have FooTag, BarTag, with a joined table, the table thats
joined has an FK back to the parent Foo or Bar.
Without using the polymorphic thing, which actually I usually don't for this,
you can have your tags @declared_attr pull out a "secondary" table, right in
the relationship there. Or polymorphically you could generate FooTag, BarTag
right there. @declared_attr is great for all that stuff.
The polymorphic association is something I'm slowly getting ready to re-present
in a declarative way, since its been coming up a lot lately and that blog post
is from 2007.
On Apr 22, 2011, at 12:20 AM, Andrey Petrov wrote:
> Hi there,
>
> I'm trying to make a table for annotating other tables' rows, perhaps even in
> relation to a user. I haven't been able to find similar examples of this
> elsewhere so I'd like a sanity check that I'm not doing something
> horrendously wrong. This is in SQLAlchemy 0.7 by the way.
>
> Here's an example:
>
> class Tag(BaseModel):
> __tablename__ = 'tag'
>
> id = Column(types.Integer, primary_key=True)
> table_name = Column(types.String(64))
> user_id = Column(types.Integer)
> row_id = Column(types.Integer)
>
> name = Column(types.String, nullable=False)
>
> Index('tag_backref_idx', Tag.table_name, Tag.user_id, Tag.row_id, Tag.name,
> unique=True)
> Index('tag_name_idx', Tag.table_name, Tag.user_id, Tag.name)
>
> So say I have a table called user, I want to be able to apply a tag to a row
> in it as Tag(table_name='foo', user_id=None, row_id=1, name='sometag'). The
> semantic is that user_id=None means it's a "global" (aka. system) tag,
> whereas a tag from a user would have that user_id filled respectively.
>
> On the Foo object, I can apply a relation like this (I'm doing this via a
> mixin class in my code):
>
> @declared_attr
> def tags(cls):
> return orm.relationship(Tag,
> primaryjoin=and_(
> Tag.table_name==cls.__tablename__,
> Tag.user_id==None,
> Tag.row_id==cls.id,
> ),
> foreign_keys=[Tag.table_name, Tag.user_id, Tag.row_id],
> )
>
> So now I can do things like...
>
> f = Session.query(Foo).get(1)
> f.tags # == [list of global Tag objects applied on object f]
>
> The goal is to be able to apply a "tag" on the system level (user_id=None) or
> on the user level for any row of any table (in reality there will be a couple
> of tables that will never have tags, but 90% will).
>
> My questions:
> Can we use the polymorphic_on stuff to make this better integrated?
> Is there a way to make a "dynamic" ForeignKey reference such that I can
> associate a tag to an object that hasn't been committed (ie. no `id` yet)?
>
> Example:
>
> f = Foo()
> t = Tag(name='blah', magic_triple_column_property=f)
> Session.add(f)
> Session.add(t)
> Session.commit()
>
> Am I crazy for trying to do this? Is there a better/simpler/more rational way
> to do something like this?
>
> Thanks in advance!
>
> - Andrey
>
> --
> 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.
--
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] Multi-table (polymorphic?) row-specific relations
Hi there,
I'm trying to make a table for annotating other tables' rows, perhaps even
in relation to a user. I haven't been able to find similar examples of this
elsewhere so I'd like a sanity check that I'm not doing something
horrendously wrong. This is in SQLAlchemy 0.7 by the way.
Here's an example:
class Tag(BaseModel):
__tablename__ = 'tag'
id = Column(types.Integer, primary_key=True)
table_name = Column(types.String(64))
user_id = Column(types.Integer)
row_id = Column(types.Integer)
name = Column(types.String, nullable=False)
Index('tag_backref_idx', Tag.table_name, Tag.user_id, Tag.row_id, Tag.name,
unique=True)
Index('tag_name_idx', Tag.table_name, Tag.user_id, Tag.name)
So say I have a table called *user*, I want to be able to apply a tag to a
row in it as Tag(table_name='foo', user_id=None, row_id=1, name='sometag').
The semantic is that user_id=None means it's a "global" (aka. system) tag,
whereas a tag from a user would have that user_id filled respectively.
On the Foo object, I can apply a relation like this (I'm doing this via a
mixin class in my code):
@declared_attr
def tags(cls):
return orm.relationship(Tag,
primaryjoin=and_(
Tag.table_name==cls.__tablename__,
Tag.user_id==None,
Tag.row_id==cls.id,
),
foreign_keys=[Tag.table_name, Tag.user_id, Tag.row_id],
)
So now I can do things like...
f = Session.query(Foo).get(1)
f.tags # == [list of global Tag objects applied on object f]
The goal is to be able to apply a "tag" on the system level (user_id=None)
or on the user level for any row of any table (in reality there will be a
couple of tables that will never have tags, but 90% will).
My questions:
1. Can we use the polymorphic_on stuff to make this better integrated?
2. Is there a way to make a "dynamic" ForeignKey reference such that I
can associate a tag to an object that hasn't been committed (ie. no `id`
yet)?
Example:
f = Foo()
t = Tag(name='blah', magic_triple_column_property=f)
Session.add(f)
Session.add(t)
Session.commit()
3. Am I crazy for trying to do this? Is there a better/simpler/more
rational way to do something like this?
Thanks in advance!
- Andrey
--
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] Strange occasional ProgrammingError: Cannot operate on a closed database
Lately, I've been getting the following error quite randomly while
developing a web app using sqlite3. I'm not sure how to fix it and it
hasn't to me before, but it is leading to occasional failed AJAX
requests.
ProgrammingError: (ProgrammingError) Cannot operate on a closed
database. None [{}]
Does anyone else have a similar issue or know how I can avoid it?
RHH
--
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] Re: Referencing col from a joined table
The difference is between a Python class and an instance of that class. Class.attribute and object.attribute are two totally different things. One represents SQL, the other your data. On Apr 21, 2011, at 4:24 PM, RVince wrote: > Are you sure? Because I can already access those fields from he mako > page from that query -- shouldn't I be able to access them in the > Puython controller of that page? -RVince > > On Apr 21, 4:07 pm, Michael Bayer wrote: >> On Apr 21, 2011, at 12:48 PM, RVince wrote: >> >>> Similarly, if I am doing an order_by: >> >>> query = >>> Session.query(SmartdataEligibilityRecord).order_by(SmartdataEligibilityReco >>> rd.member.middle_initial.desc()).all() >> >> place the Member class in the order_by() clause after constructing a join() >> to SmartRecord.member. >> >> Examples of joins are in the ORM tutorial at: >> >> http://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins >> >> >> >> >> >>> It returns: >> >>> Error - : Neither >>> 'InstrumentedAttribute' object nor 'Comparator' object has an >>> attribute 'middle_initial' >> >>> In this kind of circumstance of joined tables, in the python code >>> itself (the Pylons controller, actually) how would I refer to the >>> member table's middle_initial field where the members table is joined >>> in with a left outer join via SQLAlchemy? Thanks again >> >>> -RVince >> >>> -- >>> 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 >>> athttp://groups.google.com/group/sqlalchemy?hl=en.- Hide quoted text - >> >> - Show quoted text - > > -- > 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. > -- 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: Referencing col from a joined table
Are you sure? Because I can already access those fields from he mako page from that query -- shouldn't I be able to access them in the Puython controller of that page? -RVince On Apr 21, 4:07 pm, Michael Bayer wrote: > On Apr 21, 2011, at 12:48 PM, RVince wrote: > > > Similarly, if I am doing an order_by: > > > query = > > Session.query(SmartdataEligibilityRecord).order_by(SmartdataEligibilityRecord.member.middle_initial.desc()).all() > > place the Member class in the order_by() clause after constructing a join() > to SmartRecord.member. > > Examples of joins are in the ORM tutorial at: > > http://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins > > > > > > > It returns: > > > Error - : Neither > > 'InstrumentedAttribute' object nor 'Comparator' object has an > > attribute 'middle_initial' > > > In this kind of circumstance of joined tables, in the python code > > itself (the Pylons controller, actually) how would I refer to the > > member table's middle_initial field where the members table is joined > > in with a left outer join via SQLAlchemy? Thanks again > > > -RVince > > > -- > > 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 > > athttp://groups.google.com/group/sqlalchemy?hl=en.- Hide quoted text - > > - Show quoted text - -- 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] Re: Referencing col from a joined table
On Apr 21, 2011, at 12:48 PM, RVince wrote: > Similarly, if I am doing an order_by: > > query = > Session.query(SmartdataEligibilityRecord).order_by(SmartdataEligibilityRecord.member.middle_initial.desc()).all() place the Member class in the order_by() clause after constructing a join() to SmartRecord.member. Examples of joins are in the ORM tutorial at: http://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins > > It returns: > > Error - : Neither > 'InstrumentedAttribute' object nor 'Comparator' object has an > attribute 'middle_initial' > > In this kind of circumstance of joined tables, in the python code > itself (the Pylons controller, actually) how would I refer to the > member table's middle_initial field where the members table is joined > in with a left outer join via SQLAlchemy? Thanks again > > -RVince > > -- > 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. > -- 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] Different column default server function according to dialect possible?
On Apr 21, 2011, at 7:15 AM, Pedro Romano wrote: > Hi, > > Is there any way to set a column default to be a different server > function depending on the dialect? For python function defaults, this > is trivial using a context-sensitive default function and getting the > dialect from the context, however these don't cover server functions > for defaults, since these are inlined in the statement. > > Silly example just to make what a I need clear: > > for 'MySQL' I would like the column default to be: > > default=func.utc_timestamp() > > and for all other dialects: > > default=func.current_timestamp() > > Thanks to all SA authors and contributors for making such an excellent > and invaluable toolkit! you use the @compiles decorator and an example of a UTC timestamp construct is at: http://www.sqlalchemy.org/docs/07/core/compiler.html?highlight=timestamp#utc-timestamp-function > > --Pedro. > > -- > 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. > -- 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] Self referential relationship not including 'self'
On Apr 21, 2011, at 8:55 AM, Ed Singleton wrote: > A colleague of mine submitted a question to StackOverflow ( > http://stackoverflow.com/questions/5666732/sqlalchemy-self-referential-relationship-not-including-self > ) which I've been struggling to answer (code given below for those who don't > want to visit the link). > > Given, for example, a Film table and a Countries table, how can you add a > relationship to the Film mapper that will give you a list of countries that > have the same film, but which doesn't include the Film itself? The query above is probably not a valid candidate to be a relationship() - use a simple descriptor with Query for that. > > I tried: > > primaryjoin=sqlalchemy.and_( >film_table.c.country_id==film_table.c.country_id, >film_table.c.id!=film_table.c.id) > > but this doesn't work as it doesn't replace one of the `film_table.c.id`s > with a bindparam. > More generally, in a relationship, how do you force a column reference to be > to 'self' and how does SQLalchemy decide which references are to 'self' and > which are to the objects it's going to get? relationship() when used in a lazy load can inject bind parameters into the columns that are present on the "remote" side of the relationship, which can be equated to columns on the "local" side. In the case of a self-referential query the rules become more sensitive as it needs to know specifically which columns are considered "remote". Due to this design it's not capable of expressing a join of "table.id==table.id" where only one side is considered "remote". The short answer is that relationship() is designed to represent, both in class-level SQL generation as well as in instance-level attribute access, a simple reference to a scalar or collection based on traditional foreign key relationships, allowing a limited amount of leeway in how the join of the two tables is constructed.Because it has to work in lots of different contexts, the conditions it can handle are not open ended. -- 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] bug: distinct ON using subquery with un-named alias fails.
yeah lets start using trac for these, these are bugs with tests.
this is http://www.sqlalchemy.org/trac/ticket/2142 and your previous one is 2141
On Apr 21, 2011, at 1:31 PM, Jon Nelson wrote:
> diff -r 070e47edcfad test/dialect/test_postgresql.py
> --- a/test/dialect/test_postgresql.py Fri Apr 15 00:43:01 2011 -0400
> +++ b/test/dialect/test_postgresql.py Thu Apr 21 12:27:42 2011 -0500
> @@ -1214,6 +1214,32 @@
> 'SELECT DISTINCT ON (mytable.id, mytable.a) mytable.id, '
> 'mytable.a \nFROM mytable')
>
> +def test_distinct_on_subquery(self):
> +t1 = Table('mytable1', MetaData(testing.db), Column('id',
> + Integer, primary_key=True), Column('a', String(8)))
> +t2 = Table('mytable2', MetaData(testing.db), Column('id',
> + Integer, primary_key=True), Column('a', String(8)))
> +
> +sq = select([t1]).alias()
> +q = select([t2.c.id,sq.c.id],
> distinct=sq.c.id).where(t2.c.id==sq.c.id)
> +self.assert_compile(
> +q,
> +"SELECT DISTINCT ON (anon_1.id) mytable2.id, anon_1.id "
> +"FROM mytable2, (SELECT mytable1.id AS id, mytable1.a AS a "
> +"FROM mytable1) AS anon_1 "
> +"WHERE mytable2.id = anon_1.id"
> +)
> +
> +sq = select([t1]).alias('sq')
> +q = select([t2.c.id,sq.c.id],
> distinct=sq.c.id).where(t2.c.id==sq.c.id)
> +self.assert_compile(
> +q,
> +"SELECT DISTINCT ON (sq.id) mytable2.id, sq.id "
> +"FROM mytable2, (SELECT mytable1.id AS id, mytable1.a AS a "
> +"FROM mytable1) AS sq "
> +"WHERE mytable2.id = sq.id"
> +)
> +
> def test_schema_reflection(self):
> """note: this test requires that the 'test_schema' schema be
> separate and accessible by the test user"""
--
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] bug: distinct ON using subquery with un-named alias fails.
I have a test case. If this formats badly, I'll attach it as a file.
I'm not sure if this is a "distinct on" problem or not (probably not,
but here is a test case anyway).
If I don't specify a name for the alias, the compile fails. :-(
diff -r 070e47edcfad test/dialect/test_postgresql.py
--- a/test/dialect/test_postgresql.py Fri Apr 15 00:43:01 2011 -0400
+++ b/test/dialect/test_postgresql.py Thu Apr 21 12:27:42 2011 -0500
@@ -1214,6 +1214,32 @@
'SELECT DISTINCT ON (mytable.id, mytable.a) mytable.id, '
'mytable.a \nFROM mytable')
+def test_distinct_on_subquery(self):
+t1 = Table('mytable1', MetaData(testing.db), Column('id',
+ Integer, primary_key=True), Column('a', String(8)))
+t2 = Table('mytable2', MetaData(testing.db), Column('id',
+ Integer, primary_key=True), Column('a', String(8)))
+
+sq = select([t1]).alias()
+q = select([t2.c.id,sq.c.id], distinct=sq.c.id).where(t2.c.id==sq.c.id)
+self.assert_compile(
+q,
+"SELECT DISTINCT ON (anon_1.id) mytable2.id, anon_1.id "
+"FROM mytable2, (SELECT mytable1.id AS id, mytable1.a AS a "
+"FROM mytable1) AS anon_1 "
+"WHERE mytable2.id = anon_1.id"
+)
+
+sq = select([t1]).alias('sq')
+q = select([t2.c.id,sq.c.id], distinct=sq.c.id).where(t2.c.id==sq.c.id)
+self.assert_compile(
+q,
+"SELECT DISTINCT ON (sq.id) mytable2.id, sq.id "
+"FROM mytable2, (SELECT mytable1.id AS id, mytable1.a AS a "
+"FROM mytable1) AS sq "
+"WHERE mytable2.id = sq.id"
+)
+
def test_schema_reflection(self):
"""note: this test requires that the 'test_schema' schema be
separate and accessible by the test user"""
--
Jon
--
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] Fwd: [BUGS] database introspection error
ah no its actually this one SELECT i.indoption[0] AS options, CASE WHEN (o.opcdefault = FALSE) THEN pg_get_indexdef(i.indexrelid, 1, true) || ' ' || o.opcname ELSE pg_get_indexdef(i.indexrelid, 1, true) END AS coldef FROM pg_index i JOIN pg_attribute a ON (a.attrelid = i.indexrelid AND attnum = 1) LEFT OUTER JOIN pg_opclass o ON (o.oid = i.indclass[0]) WHERE i.indexrelid = 8028912::oid On Apr 21, 2011, at 1:16 PM, Michael Bayer wrote: > I'm assuming that's a paraphrase of the query in get_indexes().I'm not > sure why he said, "find out what they're trying to accomplish", as it seems > pretty obvious, but I will make it clear, what we're trying to accomplish is > to get the actual, current names of the columns referenced by the index. > Just like if you go to pgAdmin, click on a table->indexes->index, there's a > display on the right that says "Columns".If you want to tell him that, so > that he can tell me what the correct query is, that would be great.I > didn't write these queries and poking around its not immediately apparent how > else the pg_index rows relate back to things. > > If there is no such query and the Index represents some internal structure > that cant be linked back to the original columns, we just have to remove the > feature. > > Also, test case ? create table + index, alter column name, reflect ? > > > On Apr 21, 2011, at 12:43 PM, Jon Nelson wrote: > >> Forwarded from the pgsql-bugs mailing list. >> The short version is that after renaming a column, SQLAlchemy's >> introspection failed. >> I tried 0.6.5 and 0.6.7. >> >> >> -- Forwarded message -- >> From: Tom Lane >> Date: Thu, Apr 21, 2011 at 11:28 AM >> Subject: Re: [BUGS] database introspection error >> To: Jon Nelson >> Cc: pgsql-b...@postgresql.org >> >> >> Jon Nelson writes: >>> SQLAlchemy encountered an error introspecting the tables. After >>> inspecting the SQL that it was running, I boiled it down to this: >> >>> SELECT c.relname, a.attname >>> FROM pg_index i, pg_class c, pg_attribute a >>> WHERE i.indrelid = '16684' AND i.indexrelid = c.oid >>> AND a.attrelid = i.indexrelid >>> ORDER BY c.relname, a.attnum; >> >>> I believe that SQL gives me the name of an index and the attribute >>> upon which that index is built for a particular relation (16684). >>> However, the *results* of that query are _wrong_. The 'attname' value >>> for one row is wrong. It is the *previous* name of the column. >> >> That appears to be pulling out the names of the columns of the index, >> not the underlying table. While older versions of Postgres will try to >> rename index columns when the underlying table column is renamed, that >> was given up as an unproductive activity awhile ago (mainly because >> there isn't always a 1-to-1 mapping anyway). So it's not surprising >> to me that you're getting "stale" data here. >> >> You might want to have a discussion with the SQLAlchemy people about >> what it is that they're trying to accomplish and how it might be done >> in a more bulletproof fashion. The actual names of the columns of an >> index are an implementation detail that shouldn't be relied on. >> >> regards, tom lane >> >> >> >> -- >> Jon >> >> -- >> 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. >> > > -- > 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. > -- 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] Fwd: [BUGS] database introspection error
Well its definitely possible since pgAdmin gives you the right answer after a rename. Here's its query: SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as idxname, indrelid, indkey, indisclustered, indisunique, indisprimary, n.nspname, indnatts, cls.reltablespace AS spcoid, spcname, tab.relname as tabname, indclass, con.oid AS conoid, CASE contype WHEN 'p' THEN desp.description WHEN 'u' THEN desp.description ELSE des.description END AS description, pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable, condeferred, amname , substring(array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor FROM pg_index idx JOIN pg_class cls ON cls.oid=indexrelid JOIN pg_class tab ON tab.oid=indrelid LEFT OUTER JOIN pg_tablespace ta on ta.oid=cls.reltablespace JOIN pg_namespace n ON n.oid=tab.relnamespace JOIN pg_am am ON am.oid=cls.relam LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0') LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid) LEFT OUTER JOIN pg_description des ON des.objoid=cls.oid LEFT OUTER JOIN pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0) WHERE indrelid = 8028886::oid AND cls.oid=8028912::oid AND conname IS NULL ORDER BY cls.relname someone feel like distiling that down for our purposes ? On Apr 21, 2011, at 1:16 PM, Michael Bayer wrote: > I'm assuming that's a paraphrase of the query in get_indexes().I'm not > sure why he said, "find out what they're trying to accomplish", as it seems > pretty obvious, but I will make it clear, what we're trying to accomplish is > to get the actual, current names of the columns referenced by the index. > Just like if you go to pgAdmin, click on a table->indexes->index, there's a > display on the right that says "Columns".If you want to tell him that, so > that he can tell me what the correct query is, that would be great.I > didn't write these queries and poking around its not immediately apparent how > else the pg_index rows relate back to things. > > If there is no such query and the Index represents some internal structure > that cant be linked back to the original columns, we just have to remove the > feature. > > Also, test case ? create table + index, alter column name, reflect ? > > > On Apr 21, 2011, at 12:43 PM, Jon Nelson wrote: > >> Forwarded from the pgsql-bugs mailing list. >> The short version is that after renaming a column, SQLAlchemy's >> introspection failed. >> I tried 0.6.5 and 0.6.7. >> >> >> -- Forwarded message -- >> From: Tom Lane >> Date: Thu, Apr 21, 2011 at 11:28 AM >> Subject: Re: [BUGS] database introspection error >> To: Jon Nelson >> Cc: pgsql-b...@postgresql.org >> >> >> Jon Nelson writes: >>> SQLAlchemy encountered an error introspecting the tables. After >>> inspecting the SQL that it was running, I boiled it down to this: >> >>> SELECT c.relname, a.attname >>> FROM pg_index i, pg_class c, pg_attribute a >>> WHERE i.indrelid = '16684' AND i.indexrelid = c.oid >>> AND a.attrelid = i.indexrelid >>> ORDER BY c.relname, a.attnum; >> >>> I believe that SQL gives me the name of an index and the attribute >>> upon which that index is built for a particular relation (16684). >>> However, the *results* of that query are _wrong_. The 'attname' value >>> for one row is wrong. It is the *previous* name of the column. >> >> That appears to be pulling out the names of the columns of the index, >> not the underlying table. While older versions of Postgres will try to >> rename index columns when the underlying table column is renamed, that >> was given up as an unproductive activity awhile ago (mainly because >> there isn't always a 1-to-1 mapping anyway). So it's not surprising >> to me that you're getting "stale" data here. >> >> You might want to have a discussion with the SQLAlchemy people about >> what it is that they're trying to accomplish and how it might be done >> in a more bulletproof fashion. The actual names of the columns of an >> index are an implementation detail that shouldn't be relied on. >> >> regards, tom lane >> >> >> >> -- >> Jon >> >> -- >> 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. >> > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > T
Re: [sqlalchemy] Fwd: [BUGS] database introspection error
I'm assuming that's a paraphrase of the query in get_indexes().I'm not sure why he said, "find out what they're trying to accomplish", as it seems pretty obvious, but I will make it clear, what we're trying to accomplish is to get the actual, current names of the columns referenced by the index. Just like if you go to pgAdmin, click on a table->indexes->index, there's a display on the right that says "Columns".If you want to tell him that, so that he can tell me what the correct query is, that would be great.I didn't write these queries and poking around its not immediately apparent how else the pg_index rows relate back to things. If there is no such query and the Index represents some internal structure that cant be linked back to the original columns, we just have to remove the feature. Also, test case ? create table + index, alter column name, reflect ? On Apr 21, 2011, at 12:43 PM, Jon Nelson wrote: > Forwarded from the pgsql-bugs mailing list. > The short version is that after renaming a column, SQLAlchemy's > introspection failed. > I tried 0.6.5 and 0.6.7. > > > -- Forwarded message -- > From: Tom Lane > Date: Thu, Apr 21, 2011 at 11:28 AM > Subject: Re: [BUGS] database introspection error > To: Jon Nelson > Cc: pgsql-b...@postgresql.org > > > Jon Nelson writes: >> SQLAlchemy encountered an error introspecting the tables. After >> inspecting the SQL that it was running, I boiled it down to this: > >> SELECT c.relname, a.attname >> FROM pg_index i, pg_class c, pg_attribute a >> WHERE i.indrelid = '16684' AND i.indexrelid = c.oid >> AND a.attrelid = i.indexrelid >> ORDER BY c.relname, a.attnum; > >> I believe that SQL gives me the name of an index and the attribute >> upon which that index is built for a particular relation (16684). >> However, the *results* of that query are _wrong_. The 'attname' value >> for one row is wrong. It is the *previous* name of the column. > > That appears to be pulling out the names of the columns of the index, > not the underlying table. While older versions of Postgres will try to > rename index columns when the underlying table column is renamed, that > was given up as an unproductive activity awhile ago (mainly because > there isn't always a 1-to-1 mapping anyway). So it's not surprising > to me that you're getting "stale" data here. > > You might want to have a discussion with the SQLAlchemy people about > what it is that they're trying to accomplish and how it might be done > in a more bulletproof fashion. The actual names of the columns of an > index are an implementation detail that shouldn't be relied on. > >regards, tom lane > > > > -- > Jon > > -- > 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. > -- 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: Referencing col from a joined table
Similarly, if I am doing an order_by: query = Session.query(SmartdataEligibilityRecord).order_by(SmartdataEligibilityRecord.member.middle_initial.desc()).all() It returns: Error - : Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 'middle_initial' In this kind of circumstance of joined tables, in the python code itself (the Pylons controller, actually) how would I refer to the member table's middle_initial field where the members table is joined in with a left outer join via SQLAlchemy? Thanks again -RVince -- 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] Fwd: [BUGS] database introspection error
Forwarded from the pgsql-bugs mailing list. The short version is that after renaming a column, SQLAlchemy's introspection failed. I tried 0.6.5 and 0.6.7. -- Forwarded message -- From: Tom Lane Date: Thu, Apr 21, 2011 at 11:28 AM Subject: Re: [BUGS] database introspection error To: Jon Nelson Cc: pgsql-b...@postgresql.org Jon Nelson writes: > SQLAlchemy encountered an error introspecting the tables. After > inspecting the SQL that it was running, I boiled it down to this: > SELECT c.relname, a.attname > FROM pg_index i, pg_class c, pg_attribute a > WHERE i.indrelid = '16684' AND i.indexrelid = c.oid > AND a.attrelid = i.indexrelid > ORDER BY c.relname, a.attnum; > I believe that SQL gives me the name of an index and the attribute > upon which that index is built for a particular relation (16684). > However, the *results* of that query are _wrong_. The 'attname' value > for one row is wrong. It is the *previous* name of the column. That appears to be pulling out the names of the columns of the index, not the underlying table. While older versions of Postgres will try to rename index columns when the underlying table column is renamed, that was given up as an unproductive activity awhile ago (mainly because there isn't always a 1-to-1 mapping anyway). So it's not surprising to me that you're getting "stale" data here. You might want to have a discussion with the SQLAlchemy people about what it is that they're trying to accomplish and how it might be done in a more bulletproof fashion. The actual names of the columns of an index are an implementation detail that shouldn't be relied on. regards, tom lane -- Jon -- 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] Self referential relationship not including 'self'
A colleague of mine submitted a question to StackOverflow (
http://stackoverflow.com/questions/5666732/sqlalchemy-self-referential-relationship-not-including-self
) which I've been struggling to answer (code given below for those who don't
want to visit the link).
Given, for example, a Film table and a Countries table, how can you add a
relationship to the Film mapper that will give you a list of countries that
have the same film, but which doesn't include the Film itself?
I tried:
primaryjoin=sqlalchemy.and_(
film_table.c.country_id==film_table.c.country_id,
film_table.c.id!=film_table.c.id)
but this doesn't work as it doesn't replace one of the `film_table.c.id`s with
a bindparam.
More generally, in a relationship, how do you force a column reference to be to
'self' and how does SQLalchemy decide which references are to 'self' and which
are to the objects it's going to get?
Thanks
Ed
Example code follows:
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.orm import mapper, relationship
metadata = MetaData()
country_table = Table('country', metadata,
Column('id', String, primary_key=True),
Column('name', String),
)
film_table = Table('film', metadata,
Column('id', Integer, primary_key=True),
Column('title', String),
Column('year', Integer),
Column('country_id', Integer, ForeignKey('country.id'))
)
class Country(object):
pass
class Film(object):
pass
mapper(Country, country_table)
mapper(Film, film_table,
properties={
'country':relationship(
Country,
backref='films'),
'same_country_films':relationship(
Film,
primaryjoin=film_table.c.country_id==\
film_table.c.country_id,
foreign_keys=[
film_table.c.country_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.
[sqlalchemy] Re: Column different server function according to dialect possible?
PLEASE IGNORE... BAD SUBJECT! New post with correct subject. Sorry for the noise. --Pedro. On Apr 21, 12:12 pm, Pedro Romano wrote: > Hi, > > Is there any way to set a column default to be a different server > function depending on the dialect? For python function defaults, this > is trivial using a context-sensitive default function and getting the > dialect from the context, however these don't cover server functions > for defaults, since these are inlined in the statement. > > Silly example just to make what a I need clear: > > for 'MySQL' I would like the column default to be: > > default=func.utc_timestamp() > > and for all other dialects: > > default=func.current_timestamp() > > Thanks to all SA authors and contributors for making such an excellent > and invaluable toolkit! > > --Pedro. -- 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] Different column default server function according to dialect possible?
Hi, Is there any way to set a column default to be a different server function depending on the dialect? For python function defaults, this is trivial using a context-sensitive default function and getting the dialect from the context, however these don't cover server functions for defaults, since these are inlined in the statement. Silly example just to make what a I need clear: for 'MySQL' I would like the column default to be: default=func.utc_timestamp() and for all other dialects: default=func.current_timestamp() Thanks to all SA authors and contributors for making such an excellent and invaluable toolkit! --Pedro. -- 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] Column different server function according to dialect possible?
Hi, Is there any way to set a column default to be a different server function depending on the dialect? For python function defaults, this is trivial using a context-sensitive default function and getting the dialect from the context, however these don't cover server functions for defaults, since these are inlined in the statement. Silly example just to make what a I need clear: for 'MySQL' I would like the column default to be: default=func.utc_timestamp() and for all other dialects: default=func.current_timestamp() Thanks to all SA authors and contributors for making such an excellent and invaluable toolkit! --Pedro. -- 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] ShardedQuery bulk delete
You really did great job. Regarding sqlalchemy is so great a piece of work,
sharding deserves more love. I'm myself only touch the surface of it in a
real project, maybe I can feedback more or contribute some code if I can
write the right one:)
On Thu, Apr 21, 2011 at 9:23 AM, Michael Bayer wrote:
> ah well, glad to hear that ! It certainly can be more of a core element
> if it had some more dedicated maintainers. I haven't actually used it in a
> real project so i can't vouch strongly for it.It also might be a nice
> third-party project. Sharding is very tough and there's lots of complex
> cases that come up pretty fast.
>
>
> On Apr 20, 2011, at 8:05 PM, can xiang wrote:
>
> Thanks anyway.
>
> It's sad horizontal shard extension is only considered as a example. I
> hardly believe it, because it works so great in some of my simple use case.
> I really hope there would be more work on this extension or more docs on how
> to do it.
>
> Best regards!
> can
>
> On Thu, Apr 21, 2011 at 3:19 AM, Michael Bayer
> wrote:
>
>>
>> On Apr 20, 2011, at 4:37 AM, can xiang wrote:
>>
>> > Hi,
>> >
>> > I have a problem of bulk deleting from a sharded session, I tried to
>> > search the previous posts without any finding.
>> >
>> > I have the following table:
>> >
>> > usersession_table = Table('kg_usersession', meta,
>> > Column('session_id', String(32),
>> > primary_key=True),
>> > Column('user_id', BigInteger, index=True,
>> > nullable=False),
>> > Column('create_time', DateTime, index=True),
>> > Column('expire_time', DateTime, index=True),
>> > Column('site', String(10)),
>> > mysql_engine='MyISAM'
>> > )
>> >
>> > I use horizontal sharding by "session_id", with the following shard
>> > chooser:
>> >
>> > def shard_chooser(mapper, instance, clause=None):
>> >if instance:
>> >return shard_value(instance.session_id)
>> >
>> > Then, I want to delete all record earlier than a given expire_time,
>> > with the following code:
>> >
>> >
>> session.query(UserSession).filter(UserSession.expire_time<=expire_time).delete();
>>
>> range deletions are not supported by the ShardedSession extension right
>> now. You'd need to implement your own delete() onto ShardedQuery.
>>
>> Note that the horizontal shard extension really should have been an
>> example, not a full extension. It's really just a proof of concept and
>> real-world horizontal sharding scenarios will usually need to tweak it for
>> specific use cases.
>>
>>
>>
>>
>>
>> >
>> > It raises an error:
>> >
>> > Traceback (most recent call last):
>> > File "delete_expire_session.py", line 20, in
>> >delete_expire_session(expire_time)
>> > File "delete_expire_session.py", line 13, in delete_expire_session
>> >
>> >
>> session.query(UserSession).filter(UserSession.expire_time<=expire_time).delete();
>> > File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
>> > py2.6.egg/sqlalchemy/orm/query.py", line 2142, in delete
>> >result = session.execute(delete_stmt, params=self._params)
>> > File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
>> > py2.6.egg/sqlalchemy/orm/session.py", line 726, in execute
>> >engine = self.get_bind(mapper, clause=clause, **kw)
>> > File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
>> > py2.6.egg/sqlalchemy/ext/horizontal_shard.py", line 73, in get_bind
>> >return self.__binds[shard_id]
>> > KeyError: None
>> >
>> > I guess shard_chooser return None because of "instance" is None at
>> > runtime. I read from the docs: shard_chooser maybe in "some round-
>> > robin scheme". But I don't have any idea what does it exactly mean in
>> > my case.
>> >
>> > I appreciate any advice.
>> >
>> > Best regards!
>> > can
>> >
>> >
>> > PS: you can access partial source code in the gist:
>> https://gist.github.com/930708
>> >
>> > --
>> > 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.
>> >
>>
>> --
>> 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.
>>
>>
>
> --
> 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.co
[sqlalchemy] Re: multiple inheritance experiment
Thank you, In the last suggestion: @property def users_and_orders(self): return self.users + self.orders or to simulate a polymorphic union, do object_session(self).query().union(object_session(self).query()) etc. Do you have any suggestions to make the result have an append/remove method which persists to the database? Perhaps with a descriptor or custom collection type? Cheers, Lars On Apr 15, 6:49 am, Michael Bayer wrote: > On Apr 13, 2011, at 5:52 AM, Lars wrote: > > > Hi Michael, > > > I am trying to figure out the two suggestions you did, and not getting > > very far. Some basic questions: > > > - if A, B, C are mapped classes, can you do A.join(B, A.id == > > B.id).join(C, B.id == C.id).join( ? > > usually if you want to use join() you'd deal with tables, like > table_a.join(table_b, ...).join(...). though the orm.join() function will > receive classes directly, its > inhttp://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins > > > - Would using join in such a way make access to mapped attributes in > > one of the joined tables excessively slow? > > joins are slower than straight single table selects especially in MySQL, if > thats the question > > > - What is the difference between using association_proxy and > > relationship(... secondary = .., ..., secondaryjoin = ...)? > > three concepts. one is > many-to-many:http://www.sqlalchemy.org/docs/orm/relationships.html#many-to-many next > is the association pattern, a many to many where extra data is linked with > each > association:http://www.sqlalchemy.org/docs/orm/relationships.html#association-object next > is association proxy, when you've worked with an association for awhile and > are tired of saying parent.association.child and want to just skip the > ".association" part in the usual case > > > - in the example in poly_assoc_generic.py, is there a way to define an > > attribute on address that returns a list with both "orders" and > > "users" with that address (and be able to append that list) ? > > these collections load from entirely different tables. Usually you'd need to > do it manually: > > @property > def users_and_orders(self): > return self.users + self.orders > > or to simulate a polymorphic union, do > object_session(self).query().union(object_session(self).query()) etc. -- 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.
