[sqlalchemy] mapping of Wordpress taxonomy tables
Hello, My python app uses Wordpress blog database and my sqlalchemy mappings recently broke due to Wordpress update. Can someone pls help to map the following taxonomy tables into SA? http://codex.wordpress.org/WordPress_Taxonomy I kind of stuck with sqlalchemy.exceptions.ArgumentError: Error determining primary and/or secondary join for relationship 'WordpressPost.terms (WordpressTerm)'. If the underlying error cannot be corrected, you should specify the 'primaryjoin' (and 'secondaryjoin', if there is an association table present) keyword arguments to the relation() function (or for backrefs, by specifying the backref using the backref() function with keyword arguments) to explicitly specify the join conditions. Nested error is Can't find any foreign key relationships between 'wp_terms' and 'wp_terms' --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: expensive .rollback() in pool implementation?
Ordered by: internal time
List reduced from 178 to 20 due to restriction 20
ncalls tottime percall cumtime percall filename:lineno(function)
# 2150.3710.0020.3710.002 {method 'query' of
'_mysql.connection' objects}
* 2150.3060.0010.3060.001 {method 'rollback' of
'_mysql.connection' objects}
2150.0290.0000.0290.000 {method 'store_result' of
'_mysql.connection' objects}
2150.0280.0000.0480.000 sqlalchemy/engine/base.py:
1425(_init_metadata)
2150.0220.0000.0220.000 {method 'next_result' of
'_mysql.connection' objects}
2320.0200.0000.2460.001 utils.py:278(new_fun)
2320.0170.0001.3050.006 RecentActions.py:
197(GetCounter)
2150.0130.0000.4980.002 MySQLdb/cursors.py:
129(execute)
2150.0120.0000.0500.000 MySQLdb/cursors.py:
107(_do_get_result)
2110.0110.0000.0370.000 decimal.py:516(__new__)
2150.0090.0000.0360.000 sqlalchemy/engine/
default.py:136(__init__)
2320.0090.0001.3150.006 utils.py:178(protected)
2150.0080.0000.0090.000 MySQLdb/cursors.py:
40(__init__)
4300.0080.0000.0140.000 threading.py:93(acquire)
6410.0080.0000.0080.000 {built-in method match}
2110.0060.0000.0140.000 decimal.py:
3092(_string2exact)
* 2150.0060.0000.3460.002 sqlalchemy/pool.py:
276(_finalize_fairy)
2150.0060.0000.6130.003 sqlalchemy/engine/base.py:
853(_execute_text)
6620.0060.0000.0060.000 logging/__init__.py:
1158(getEffectiveLevel)
This is cumulative log for 215 requests. '#' - query to DB, '*' -
returning connection to the pool.
Times for query() and rollback() execution are comparable.
Query is SELECT person_id, type, sum(count) FROM CommentCounter WHERE
person = %d AND type = '%s' and answered in (0, 1) group by person_id
On May 4, 7:52 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On May 4, 2008, at 9:41 AM, Andrew Stromnov wrote:
Recently I'd switched to SA MySQL connection pool implementation.
Every time, when app returns connection to pool (through .close()
method), SA triggers .rollback() on this connection (http://
www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/sqlalchemy/pool)
.
In my case ROLLBACK is rather expensive operation and waste too much
MySQL time. I'm using MySQL 5.1 and set autocommit=1 on
initialization.
How to disable this .rollback() triggering?
its necessary so that any transactional state existing on the
connection is discarded. autocommit=1 is not part of DBAPI so SQLA
is not built around that modelbut even if it is switched on, it
says nothing about table or row locks which may exist on the
connection which also would need to be released via ROLLBACK.
do you have any profiling data that illustrate ROLLBACK being
expensive ? its generally an extremely cheap operation particularly
if little or no state has been built up on the connection.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Doubt in forming a dynamic query
Hi,
I have a very basic doubt in using sqlalchemy. My query is as follows.
My requirement is to test the inclusion or exclusion of a list of
values for a particular field.
result = session.execute(
select([
table1.c.address,
func.count(table1.c.address).label('count')
]
).where(and_(table1.c.type=='from',
table1.c.table2_id == table2.c.id,
table2.c.domain==unicode('naukri.com'),
table2.c.date.between(sdate,edate) )
).group_by(table1.c.address).order_by(func.count(table1.c.address).desc()))
For example in the following code I have
table2.c.domain==unicode('naukri.com'). Now there can be multiple
domains and it is dynamic.
How can I do something like:
table2.c.domain in ['naukri.com', 'gmail.com', 'yahoo.com']
Also how can I do something like
table2.c.domain not in ['naukri.com', 'gmail.com', 'yahoo.com']
Thanks and Regards,
Roopesh
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: arbitrary information for sa objects
IMvhO: - table / columns are DB-side terms, relations - sqlalchemy.orm.relation() - are not really. They are more ORM. Foreign keys/Constraints are DB-side, yes. But if annotate relations, then mappers should follow... as they talk of mappers and then keys/joins. TO me, keeping the OO model in DB-side terms may not be the best thing, as OO-side (mappers/props) may be different beast alltogether - names / meanings / etc. On another hand, if model is wholly based on DB-side stuff, then its not really the relations that has to be annotated, its something lower... but i dont know what. ciao svilen On Sunday 04 May 2008 17:13:15 alex bodnaru wrote: hi paul, the relations should follow, indeed. thanks again, alex Paul Johnston wrote: Hi, http://www.sqlalchemy.org/docs/04/sqlalchemy_schema.html#docstrin gs_sqlalchemy.schema_Table shows info as a parameter in the kwargs to a table. So it does, so it's tables and columns. The column info setting is in the same page as you sent across, just a bit further up. What other objects would you like it for? I'm likely to have a requirement for it on relations pretty soon. Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: arbitrary information for sa objects
we were talking conceptually, of course :) [EMAIL PROTECTED] wrote: IMvhO: - table / columns are DB-side terms, relations - sqlalchemy.orm.relation() - are not really. They are more ORM. Foreign keys/Constraints are DB-side, yes. But if annotate relations, then mappers should follow... as they talk of mappers and then keys/joins. TO me, keeping the OO model in DB-side terms may not be the best thing, as OO-side (mappers/props) may be different beast alltogether - names / meanings / etc. On another hand, if model is wholly based on DB-side stuff, then its not really the relations that has to be annotated, its something lower... but i dont know what. ciao svilen On Sunday 04 May 2008 17:13:15 alex bodnaru wrote: hi paul, the relations should follow, indeed. thanks again, alex Paul Johnston wrote: Hi, http://www.sqlalchemy.org/docs/04/sqlalchemy_schema.html#docstrin gs_sqlalchemy.schema_Table shows info as a parameter in the kwargs to a table. So it does, so it's tables and columns. The column info setting is in the same page as you sent across, just a bit further up. What other objects would you like it for? I'm likely to have a requirement for it on relations pretty soon. Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: expensive .rollback() in pool implementation?
thanks for the effort. Please use r4640 and specify
rollback_returned=False to the Pool constructor.
On May 5, 2008, at 5:53 AM, Andrew Stromnov wrote:
Ordered by: internal time
List reduced from 178 to 20 due to restriction 20
ncalls tottime percall cumtime percall filename:lineno(function)
# 2150.3710.0020.3710.002 {method 'query' of
'_mysql.connection' objects}
* 2150.3060.0010.3060.001 {method 'rollback' of
'_mysql.connection' objects}
2150.0290.0000.0290.000 {method 'store_result' of
'_mysql.connection' objects}
2150.0280.0000.0480.000 sqlalchemy/engine/base.py:
1425(_init_metadata)
2150.0220.0000.0220.000 {method 'next_result' of
'_mysql.connection' objects}
2320.0200.0000.2460.001 utils.py:278(new_fun)
2320.0170.0001.3050.006 RecentActions.py:
197(GetCounter)
2150.0130.0000.4980.002 MySQLdb/cursors.py:
129(execute)
2150.0120.0000.0500.000 MySQLdb/cursors.py:
107(_do_get_result)
2110.0110.0000.0370.000 decimal.py:516(__new__)
2150.0090.0000.0360.000 sqlalchemy/engine/
default.py:136(__init__)
2320.0090.0001.3150.006 utils.py:178(protected)
2150.0080.0000.0090.000 MySQLdb/cursors.py:
40(__init__)
4300.0080.0000.0140.000 threading.py:93(acquire)
6410.0080.0000.0080.000 {built-in method match}
2110.0060.0000.0140.000 decimal.py:
3092(_string2exact)
* 2150.0060.0000.3460.002 sqlalchemy/pool.py:
276(_finalize_fairy)
2150.0060.0000.6130.003 sqlalchemy/engine/base.py:
853(_execute_text)
6620.0060.0000.0060.000 logging/__init__.py:
1158(getEffectiveLevel)
This is cumulative log for 215 requests. '#' - query to DB, '*' -
returning connection to the pool.
Times for query() and rollback() execution are comparable.
Query is SELECT person_id, type, sum(count) FROM CommentCounter WHERE
person = %d AND type = '%s' and answered in (0, 1) group by person_id
On May 4, 7:52 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On May 4, 2008, at 9:41 AM, Andrew Stromnov wrote:
Recently I'd switched to SA MySQL connection pool implementation.
Every time, when app returns connection to pool (through .close()
method), SA triggers .rollback() on this connection (http://
www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/sqlalchemy/pool)
.
In my case ROLLBACK is rather expensive operation and waste too much
MySQL time. I'm using MySQL 5.1 and set autocommit=1 on
initialization.
How to disable this .rollback() triggering?
its necessary so that any transactional state existing on the
connection is discarded. autocommit=1 is not part of DBAPI so
SQLA
is not built around that modelbut even if it is switched on, it
says nothing about table or row locks which may exist on the
connection which also would need to be released via ROLLBACK.
do you have any profiling data that illustrate ROLLBACK being
expensive ? its generally an extremely cheap operation particularly
if little or no state has been built up on the connection.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Doubt in forming a dynamic query
On May 5, 2008, at 9:10 AM, Roopesh wrote: How can I do something like: table2.c.domain in ['naukri.com', 'gmail.com', 'yahoo.com'] Also how can I do something like table2.c.domain not in ['naukri.com', 'gmail.com', 'yahoo.com'] use the in_ method, column.in_(['naukri.com', 'gmail.com', 'yahoo.com']), not_(column.in_(['naukri.com', 'gmail.com', 'yahoo.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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: expensive .rollback() in pool implementation?
Michael Bayer wrote: thanks for the effort. Please use r4640 and specify rollback_returned=False to the Pool constructor. I changed that name in r4643 to 'reset_on_return'. I'm hoping to be able to support database-specific methods for state reset in 0.5, and the new option name is a little more future proof in that regard. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Using declarative when imports happen before create_engine() ??
Thanks for the help. I almost have this up and running, but I have found one preplexing issue. My current code base has a very extensive test suite. As part of this test suite, I have my test fixtures setup and teardown databases, mappers, and just about every SA related. This is meant to help keep each test clean from anything remaining from the previous test. As part of the setup and teardown for the tests, I explicitly call sa.orm.clear_mappers() to clear out all mappers that were setup in the previous tests. This was not a problem before the declarative code change-over because each time I setup the DB's for the tests I have the system call an initialization method that sets up all metadata, mappers, etc. But now, with the mappers being created behind the scenes I don't have any way to force the system to recreate the metadata or mappers. Is there some way to clear the declarative layer and have it regenerate all automatically created metadata and mappers? -Allen On Mon, Apr 28, 2008 at 5:07 PM, Michael Bayer [EMAIL PROTECTED] wrote: On Apr 28, 2008, at 5:42 PM, Allen Bierbaum wrote: So, if I understand this right, I could import a base module that does a lazy creation of the Base class with a metadata object and then just use that base class everywhere I need it for the declarative class definitions. Then at a later time (before I use the mapped classes), I could go and bind the metadata for the Base class to an engine for use. Correct? (I apologize if I used the terms incorrectly). If this is true, then I think I see how I can solve my problem. thats right. the whole idea of declarative_base is that its just a common base class from which all your other classes inherit. then you can plug whatever engine configuration is needed at any point in time and it will become active for all base-inheriting classes. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Goofy Association Table
I've got a kind of goofy schema, and I'm trying to map it. I've got
Questionnaire types, Sections and Questions all joined in a single
association table:
join_table : type_id | section_id | question_id
questions_table : id | question_text
sections_table : id | section_name
types_table : id | type_name
So, a single question can appear in different sections for different
types. How do I do the mapping? This is what I've got, and it
doesn't work.
mapper(Question, questions_table)
mapper(Section, sections_table, properties={
'questions':relation(Question, backref='section',
secondary=join_table)
})
mapper(QType, types_table, properties={
'sections':relation(Section,
backref = 'type',
secondary = join_table
primaryjoin = types_table.c.id==join_table.c.type_id,
secondaryjoin = join_table.c.section_id==sections_table.id
)
})
I get this error when I try to save a type object:
AttributeError: 'PropertyLoader' object has no attribute
'_dependency_processor'
Thanks,
Matt
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Using declarative when imports happen before create_engine() ??
On May 5, 2008, at 5:04 PM, Allen Bierbaum wrote: Is there some way to clear the declarative layer and have it regenerate all automatically created metadata and mappers? not as of yet but this could be done. but if you are using declarative, that implies that for a class X there is only one mapping. what's the need to call clear_mappers() ? are there other, non-declarative mappers which are interacting with the declarative ones and require changes on each test ? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Goofy Association Table
On May 5, 2008, at 5:11 PM, Matt Haggard wrote:
I've got a kind of goofy schema, and I'm trying to map it. I've got
Questionnaire types, Sections and Questions all joined in a single
association table:
join_table : type_id | section_id | question_id
questions_table : id | question_text
sections_table : id | section_name
types_table : id | type_name
So, a single question can appear in different sections for different
types. How do I do the mapping? This is what I've got, and it
doesn't work.
mapper(Question, questions_table)
mapper(Section, sections_table, properties={
'questions':relation(Question, backref='section',
secondary=join_table)
})
mapper(QType, types_table, properties={
'sections':relation(Section,
backref = 'type',
secondary = join_table
primaryjoin = types_table.c.id==join_table.c.type_id,
secondaryjoin = join_table.c.section_id==sections_table.id
)
})
your table is not a many-to-many table, its just another entity table
with associations to other entities. secondary is not the
appropriate construct in this case; use an association mapping :
http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_patterns_association
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Using declarative when imports happen before create_engine() ??
On Mon, May 5, 2008 at 4:29 PM, Michael Bayer [EMAIL PROTECTED] wrote: On May 5, 2008, at 5:04 PM, Allen Bierbaum wrote: Is there some way to clear the declarative layer and have it regenerate all automatically created metadata and mappers? not as of yet but this could be done. but if you are using declarative, that implies that for a class X there is only one mapping. what's the need to call clear_mappers() ? are there other, non-declarative mappers which are interacting with the declarative ones and require changes on each test ? Not really. I was just trying to be very cautious and isolate each unit being tested. If that is not possible right now, that is fine. I can still get the test suite up and running, I just have to change the code that was trying to isolate each db test. Thanks, Allen --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Ordered merge of shard results (update)
On Sat, 3 May 2008 07:03:56 -0400 Kyle Schaffrick [EMAIL PROTECTED] wrote: I'm in the process now of rebasing against user_defined_state. I just got done uploading the rebased series. That little rowtuple change turned out to be the biggest headache to adapt to :) In any case, it now has a small handful of additional unit tests to make sure rowtuple-mangling works right as well. http://raidi.us/edarc/sqlalchemy/shard_merge_ordering/ Good times. -Kyle --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] column name must be lowercase for CheckConstraint.
If you define a column with column name contains capital letter, and define CheckConstraint for this column, then DDL issued by SA will be wrong. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Doubt in forming a dynamic query
use the in_ method, column.in_(['naukri.com', 'gmail.com', 'yahoo.com']), not_(column.in_(['naukri.com', 'gmail.com', 'yahoo.com'])) . But when I do this, I am getting an error as follows: not_(msg_headers.c.email_domain.in_['naukri.com','yahoo.com','hotmail.com']), TypeError: 'instancemethod' object is unsubscriptable Thanks and Regards Roopesh --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Doubt in forming a dynamic query
On Tuesday 06 May 2008 07:40:36 Roopesh wrote: use the in_ method, column.in_(['naukri.com', 'gmail.com', 'yahoo.com']), not_(column.in_(['naukri.com', 'gmail.com', 'yahoo.com'])) . But when I do this, I am getting an error as follows: not_(msg_headers.c.email_domain.in_['naukri.com','yahoo.com','hotma il.com']), TypeError: 'instancemethod' object is unsubscriptable well... read /copy _more_ carefuly? not_( msg_headers.c.email_domain.in_( ['naukri.com','yahoo.com','hotmail.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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
