[sqlalchemy] Re: mapping of Wordpress taxonomy tables
On 5 май, 11:55, Max Ischenko [EMAIL PROTECTED] wrote: 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? I've tried to follow Specifying Alternate Join Conditions to relation() recipe from the docs and got an error about invalid kwarg: File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.5-py2.5.egg/ sqlalchemy/orm/__init__.py, line 566, in mapper return Mapper(class_, local_table, *args, **params) TypeError: __init__() got an unexpected keyword argument 'foreign_keys' Here is the offending code: mapper(WordpressPost, wp_posts_tbl, properties={ 'categories': relation(WordpressTaxonomy, primaryjoin= and_(wp_terms_taxonomy_tbl.c.taxonomy=='category', wp_term_relationships_tbl.c.term_taxonomy_id==wp_terms_taxonomy_tbl.c.term_taxonomy_id)), }, foreign_keys=[wp_terms_taxonomy_tbl.c.term_taxonomy_id]) Max. --~--~-~--~~~---~--~~ 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] table union through ORM
Hello Here's my thing: I have two DB tables, refugees and offices, which are independent tables (no foreign key). I already have two mappers for those tables: mapper(Refugee, refugees_table) mapper(Office, offices_table) In addition to being able to do independent queries on each table, I'd like to do queries in both tables (UNION) and get results in objects of type Location (a Python class of my own). In other words, I'd like to do queries using session.query(Location). I've tried various things (Mapping a Class against Multiple Tables in the doc in particular), but with no luck. Does anyone have suggestions? Thanks a lot, -- Eric --~--~-~--~~~---~--~~ 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: column name must be lowercase for CheckConstraint.
can you file a trac ticket with a short example, please. On May 5, 2008, at 10:39 PM, James Su wrote: 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: mapping of Wordpress taxonomy tables
On May 6, 2008, at 3:19 AM, Max Ischenko wrote: On 5 май, 11:55, Max Ischenko [EMAIL PROTECTED] wrote: 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? I've tried to follow Specifying Alternate Join Conditions to relation() recipe from the docs and got an error about invalid kwarg: File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.5-py2.5.egg/ sqlalchemy/orm/__init__.py, line 566, in mapper return Mapper(class_, local_table, *args, **params) TypeError: __init__() got an unexpected keyword argument 'foreign_keys' Here is the offending code: mapper(WordpressPost, wp_posts_tbl, properties={ 'categories': relation(WordpressTaxonomy, primaryjoin= and_(wp_terms_taxonomy_tbl.c.taxonomy=='category', wp_term_relationships_tbl .c.term_taxonomy_id==wp_terms_taxonomy_tbl.c.term_taxonomy_id)), }, foreign_keys=[wp_terms_taxonomy_tbl.c.term_taxonomy_id]) foreign_keys goes inside of relation(). --~--~-~--~~~---~--~~ 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: table union through ORM
On May 6, 2008, at 7:49 AM, Eric Lemoine wrote: Hello Here's my thing: I have two DB tables, refugees and offices, which are independent tables (no foreign key). I already have two mappers for those tables: mapper(Refugee, refugees_table) mapper(Office, offices_table) In addition to being able to do independent queries on each table, I'd like to do queries in both tables (UNION) and get results in objects of type Location (a Python class of my own). In other words, I'd like to do queries using session.query(Location). I've tried various things (Mapping a Class against Multiple Tables in the doc in particular), but with no luck. Does anyone have suggestions? this would be concrete table inheritance: http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_mapper_inheritance_concrete Both Refugee and Office would need to include Location as at least one of their base 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] How to ignore primary key errors on insert
Hi to all, This may be a newbie question but I just can't find the answer. I have to make multiple submissions to a postgresql table and I want to use python. I found everything to execute my insert commands, the problem is that I have to repeat it regularly, and I expected not to check which record have already inserted thanks to the primary key ( which is a couple if integer I set ). So when it tries to insert again some records, it doesn't insert it, as expected, but it raises an error which interrupt the script : ** Traceback (most recent call last): File ./script.py, line 44, in module connection.execute(line) File . . ./python-2.5.1/lib/python2.5/site-packages/ SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py, line 844, in execute return Connection.executors[c](self, object, multiparams, params) File . . ./python-2.5.1/lib/python2.5/site-packages/ SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py, line 854, in _execute_text self.__execute_raw(context) File . . ./python-2.5.1/lib/python2.5/site-packages/ SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py, line 916, in __execute_raw self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File . . ./python-2.5.1/lib/python2.5/site-packages/ SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py, line 960, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor) File . . ./python-2.5.1/lib/python2.5/site-packages/ SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py, line 942, in _handle_dbapi_exception raise exceptions.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exceptions.IntegrityError: (IntegrityError) duplicate key violates unique constraint my_primarykey INSERT INTO . . . ) {} ** So I just wanted to know if there was an option to ignore the error , which possibly raise it, but don't interrupt the script. Thanks --~--~-~--~~~---~--~~ 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] ProgrammingError: Can't adapt INSERT
All, I am a newbie to SQLAlchemy. I needed an ORM for my system and after a research, I decided on SQLAlchemy. I used SQLAutocode that generated this model.py (a snippet is pasted here) - X-- Sources = Table('Sources', metadata, Column(u'id', PGInteger(), primary_key=True, nullable=False, default=PassiveDefault(u'nextval(\'public.Sources_id_seq\'::regclass)')), Column(u'name', PGString(length=256, convert_unicode=False, assert_unicode=None), primary_key=False, nullable=False), Column(u'description', PGString(length=256, convert_unicode=False, assert_unicode=None), primary_key=False, nullable=False), schema='public' ) class SourcesObject(object): pass mapper(SourcesObject, Sources) - X-- When I try to insert data into this table using this piece of code, source = SourcesObject() source.name = a2 source.description = b2 session.save(source) session.commit() I get this error, ** raise exceptions.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) can't adapt 'INSERT INTO public.Sources (id, name, description) VALUES (%(id)s, % (name)s, %(description)s)' {'description': 'b2', 'name': 'a2', 'id': PassiveDefault(sqlalchemy.sql.expression._TextClause object at 0x02AA3C50)} * I browsed through many forums, I couldn't get a solution. Could you assist me? Thanks, Nagarajan. --~--~-~--~~~---~--~~ 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: ProgrammingError: Can't adapt INSERT
On May 6, 2008, at 6:17 AM, Nagarajan wrote: All, I am a newbie to SQLAlchemy. I needed an ORM for my system and after a research, I decided on SQLAlchemy. I used SQLAutocode that generated this model.py (a snippet is pasted here) - X-- Sources = Table('Sources', metadata, Column(u'id', PGInteger(), primary_key=True, nullable=False, default =PassiveDefault(u'nextval(\'public.Sources_id_seq\'::regclass)')), Column(u'name', PGString(length=256, convert_unicode=False, assert_unicode=None), primary_key=False, nullable=False), Column(u'description', PGString(length=256, convert_unicode=False, assert_unicode=None), primary_key=False, nullable=False), schema='public' ) class SourcesObject(object): pass mapper(SourcesObject, Sources) that Table is a little messed up, in particular default doesnt take a PassiveDefault as an argument. the Sources_id_seq is implicit when the id column is created as SERIAL, but since im not sure how this table is getting created, do it like this: s = Table(Sources, metadata, Column('id', Integer, Sequence(Sources_id_seq), primary_key=True), Column('name', String(256), nullable=False), Column('description', String(256), nullable=False) ) --~--~-~--~~~---~--~~ 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: How to ignore primary key errors on insert
just use try/except from sqlalchemy import exceptions try: engine.execute(mytable.insert(), **kwargs) except exceptions.IntegrityError, e: print Error !, e, well, I guess we'll ignore it. engine.execute(some_other_insert_statement ... ) On May 6, 2008, at 10:06 AM, Alexis B wrote: Hi to all, This may be a newbie question but I just can't find the answer. I have to make multiple submissions to a postgresql table and I want to use python. I found everything to execute my insert commands, the problem is that I have to repeat it regularly, and I expected not to check which record have already inserted thanks to the primary key ( which is a couple if integer I set ). So when it tries to insert again some records, it doesn't insert it, as expected, but it raises an error which interrupt the script : ** Traceback (most recent call last): File ./script.py, line 44, in module connection.execute(line) File . . ./python-2.5.1/lib/python2.5/site-packages/ SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py, line 844, in execute return Connection.executors[c](self, object, multiparams, params) File . . ./python-2.5.1/lib/python2.5/site-packages/ SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py, line 854, in _execute_text self.__execute_raw(context) File . . ./python-2.5.1/lib/python2.5/site-packages/ SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py, line 916, in __execute_raw self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File . . ./python-2.5.1/lib/python2.5/site-packages/ SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py, line 960, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor) File . . ./python-2.5.1/lib/python2.5/site-packages/ SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py, line 942, in _handle_dbapi_exception raise exceptions.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exceptions.IntegrityError: (IntegrityError) duplicate key violates unique constraint my_primarykey INSERT INTO . . . ) {} ** So I just wanted to know if there was an option to ignore the error , which possibly raise it, but don't interrupt the script. Thanks --~--~-~--~~~---~--~~ 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: mapping of Wordpress taxonomy tables
On 5/6/08, Michael Bayer [EMAIL PROTECTED] wrote: mapper(WordpressPost, wp_posts_tbl, properties={ 'categories': relation(WordpressTaxonomy, primaryjoin= and_(wp_terms_taxonomy_tbl.c.taxonomy=='category', wp_term_relationships_tbl .c.term_taxonomy_id==wp_terms_taxonomy_tbl.c.term_taxonomy_id)), }, foreign_keys=[wp_terms_taxonomy_tbl.c.term_taxonomy_id]) foreign_keys goes inside of relation(). Thanks. Then I think this is a typo here: http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_customjoin_fks Max. --~--~-~--~~~---~--~~ 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: table union through ORM
On Tue, May 6, 2008 at 4:24 PM, Michael Bayer [EMAIL PROTECTED] wrote: On May 6, 2008, at 7:49 AM, Eric Lemoine wrote: Hello Here's my thing: I have two DB tables, refugees and offices, which are independent tables (no foreign key). I already have two mappers for those tables: mapper(Refugee, refugees_table) mapper(Office, offices_table) In addition to being able to do independent queries on each table, I'd like to do queries in both tables (UNION) and get results in objects of type Location (a Python class of my own). In other words, I'd like to do queries using session.query(Location). I've tried various things (Mapping a Class against Multiple Tables in the doc in particular), but with no luck. Does anyone have suggestions? this would be concrete table inheritance: http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_mapper_inheritance_concrete Both Refugee and Office would need to include Location as at least one of their base classes. In the Concrete Table Inheritance example in the doc, there's an employee table. In my case, there's no location table, and that's why I haven't seen Concrete Table Inheritance as my case's solution. So I'm still confused on how I'm going to achieve what I want. Thanks a lot, -- Eric --~--~-~--~~~---~--~~ 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] query.all() returns None?
Hi, I have been using SQLAlchemy with Pylons. I map a table to a class and use ORM(reflection) to work with it. I am using MySQL for the backend. The class is Job and the table is jobs. I can create and insert new Jobs but whenever I try to get any back I get None. This used to work just fine and it still works fine for my other mapped classes. I did change the jobs table quite a bit, but I restarted my computer and it still wouldnt work. There are records in the correct table and running the SQL generated by all()(with echo turned on) returns rows. Code: print Session.query(Job).all() [None] Any variation also fails. Thanks for the help. Dan --~--~-~--~~~---~--~~ 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: table union through ORM
On May 6, 2008, at 11:13 AM, Eric Lemoine wrote: In the Concrete Table Inheritance example in the doc, there's an employee table. In my case, there's no location table, and that's why I haven't seen Concrete Table Inheritance as my case's solution. So I'm still confused on how I'm going to achieve what I want. you wouldnt have the location table, and the Location mapper would be mapped directly to the polymorphic union , i.e. mapper(Location, pjoin). --~--~-~--~~~---~--~~ 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: query.all() returns None?
On May 6, 2008, at 11:17 AM, [EMAIL PROTECTED] wrote: Hi, I have been using SQLAlchemy with Pylons. I map a table to a class and use ORM(reflection) to work with it. I am using MySQL for the backend. The class is Job and the table is jobs. I can create and insert new Jobs but whenever I try to get any back I get None. This used to work just fine and it still works fine for my other mapped classes. I did change the jobs table quite a bit, but I restarted my computer and it still wouldnt work. There are records in the correct table and running the SQL generated by all()(with echo turned on) returns rows. Code: print Session.query(Job).all() [None] Any variation also fails. any chance that Job defines a __repr__() method with no return value ? --~--~-~--~~~---~--~~ 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: table union through ORM
Thanks a lot. I'll try that 2008/5/6, Michael Bayer [EMAIL PROTECTED]: On May 6, 2008, at 11:13 AM, Eric Lemoine wrote: In the Concrete Table Inheritance example in the doc, there's an employee table. In my case, there's no location table, and that's why I haven't seen Concrete Table Inheritance as my case's solution. So I'm still confused on how I'm going to achieve what I want. you wouldnt have the location table, and the Location mapper would be mapped directly to the polymorphic union , i.e. mapper(Location, pjoin). --~--~-~--~~~---~--~~ 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: query.all() returns None?
No, I do implement a __str__ so that I can format up the composite primary key. I didnt implement __repr__ at all. Dan On May 6, 10:25 am, Michael Bayer [EMAIL PROTECTED] wrote: On May 6, 2008, at 11:17 AM, [EMAIL PROTECTED] wrote: Hi, I have been using SQLAlchemy with Pylons. I map a table to a class and use ORM(reflection) to work with it. I am using MySQL for the backend. The class is Job and the table is jobs. I can create and insert new Jobs but whenever I try to get any back I get None. This used to work just fine and it still works fine for my other mapped classes. I did change the jobs table quite a bit, but I restarted my computer and it still wouldnt work. There are records in the correct table and running the SQL generated by all()(with echo turned on) returns rows. Code: print Session.query(Job).all() [None] Any variation also fails. any chance that Job defines a __repr__() method with no return value ? --~--~-~--~~~---~--~~ 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: query.all() returns None?
On May 6, 2008, at 11:42 AM, [EMAIL PROTECTED] wrote: No, I do implement a __str__ so that I can format up the composite primary key. I didnt implement __repr__ at all. Ok you'd have to work up an example script that can illustrate this happening, I can't see any codepath that would actually return None inside a list. Id check very carefully to ensure that it is actually [None] and not just a particular string representation of an instance. --~--~-~--~~~---~--~~ 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: mapping of Wordpress taxonomy tables
On May 6, 2008, at 11:12 AM, Max Ischenko wrote: Then I think this is a typo here: http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_customjoin_fks thanks this is fixed on the site and in r4663. --~--~-~--~~~---~--~~ 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: query.all() returns None?
Ok, its pretty hard to really put an example script up, but heres the relelvant parts. #From the Model jobs_table = Table('jobs', metadata, autoload=True) mapper(Job, jobs_table ) #The Job Class is pretty generic so I wont include it #The method that does the display with a debug print def list(self): c.jobs = Session.query(Job).all() for j in c.jobs: print j return render('/jobs/listjobs.mako') #Error/output to console None #Pylons Error type 'exceptions.AttributeError': 'NoneType' object has no attribute 'jobnum' #Interactive debugger is stuff I executed print Session.query(Job).all() [None] print Session.query(Job).first() None print Session.query(Job) SELECT jobs.cmdline AS jobs_cmdline, jobs.nextschd AS jobs_jobnum, jobs.repunit AS jobs_repunit, jobs.repvalue AS jobs_repvalue, etc. etc. (there are like a million fields) FROM jobs ORDER BY jobs.batch The SQL generated works perfectly when run in a query tool. The only thing I really changed was elminating a bunch of fields from this table, then restarting everything. Thanks, Dan On May 6, 11:00 am, Michael Bayer [EMAIL PROTECTED] wrote: On May 6, 2008, at 11:42 AM, [EMAIL PROTECTED] wrote: No, I do implement a __str__ so that I can format up the composite primary key. I didnt implement __repr__ at all. Ok you'd have to work up an example script that can illustrate this happening, I can't see any codepath that would actually return None inside a list. Id check very carefully to ensure that it is actually [None] and not just a particular string representation of an instance. --~--~-~--~~~---~--~~ 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
Thank you Michael! I've got a follow-up question if anyone's up to it. I've changed my classes and mappings to the following: class QType(object): ... questions = association_proxy('joinObj', 'questions', creator=_create_joinObj) sections = association_proxy('joinObj', 'sections') class Question(object): ... section = association_proxy('joinObj', 'section') mapper(QJoin, join_table, properties={ 'type' :relation(QType), 'sections' :relation(Section, backref='parent'), 'questions' :relation(Question, backref='parent') }) mapper(Question, questions_table) mapper(Section, sections_table) mapper(QType, types_table, properties={ 'joinObj' :relation(QJoin) }) And it's working, thanks to Michael's help. Here's my question: I have three interrelated thing: Questions, Sections, Types. I struggling to do the mappings that would allow these: 1) Given a Type, what are all the Sections (ignoring the Questions; grouping by Sections)? my_type = Type() my_sections = my_type.sections ?? 2) Given a Type and Section, what are the Questions? my_type = Type() my_questions = my_type.sections[0].questions ?? 3) Given a Type, what are all the Questions (ignoring the Sections; grouping by Questions)? my_type = Type() all_questions = my_type.questions ?? I appreciate the help, Matt On May 5, 3:32 pm, Michael Bayer [EMAIL PROTECTED] wrote: 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_relatio... --~--~-~--~~~---~--~~ 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: query.all() returns None?
that doesnt help much. We really need a standalone script which reproduces the behavior on its own. You'll probably find the issue yourself as you go through this process. On May 6, 2008, at 12:28 PM, [EMAIL PROTECTED] wrote: #From the Model jobs_table = Table('jobs', metadata, autoload=True) mapper(Job, jobs_table ) #The Job Class is pretty generic so I wont include it #The method that does the display with a debug print def list(self): c.jobs = Session.query(Job).all() for j in c.jobs: print j return render('/jobs/listjobs.mako') --~--~-~--~~~---~--~~ 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: query.all() returns None?
On May 6, 2008, at 12:28 PM, [EMAIL PROTECTED] wrote: #Interactive debugger is stuff I executed print Session.query(Job).all() [None] print Session.query(Job).first() None heres other things to try. 1. you are on SQLAlchemy 0.4.5. 2. do not use any 3rd party Python REPL tools like IPython. all kinds of weird things seem to happen with those. 3. sess = create_session() j = sess.query(Job).first() assert j is not None 4. j1 = Job() assert repr(j1) != 'None' 5. use pdb to trace through the query.all() process. methods to look at are Query.iterate_instances(), Mapper._instance(). Mapper._instance never returns None (its not really possible). --~--~-~--~~~---~--~~ 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] Ordering results ina self-referential join
Hi, Node is an orm mapped class, which is self-referential. myquery = Node.query() myquery = myquery.join('parent', aliased=True) myquery = myquery.filter(Node.c.desc.like('%something')) myquery = myquery.order_by(Node.c.name) The last line orders by the 'name' of the 2nd joined table. How can I add another order_by (after the one above) that orders by some column of the first instance of the table? --~--~-~--~~~---~--~~ 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: Ordering results ina self-referential join
On May 6, 2008, at 1:43 PM, Moshe C. wrote: Hi, Node is an orm mapped class, which is self-referential. myquery = Node.query() myquery = myquery.join('parent', aliased=True) myquery = myquery.filter(Node.c.desc.like('%something')) myquery = myquery.order_by(Node.c.name) The last line orders by the 'name' of the 2nd joined table. How can I add another order_by (after the one above) that orders by some column of the first instance of the table? place another order_by() either before the join(), or after calling reset_joinpoint(). Make sure you're on 0.4.5. --~--~-~--~~~---~--~~ 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: Ordering results ina self-referential join
Both methods cause a crash (yes, on 0.4.5) . converting a tuple to a list in sqlalchemy/orm/query.py fixes it for one of the methods, for the other you need to do the opposite, convert a list to a tuple. File '/home/moshe/top/webapp/rma/rma/controllers/list.py', line 215 in list_sources myquery = myquery.reset_joinpoint().order_by(model.Source.c.popularity) File '/home/moshe/top/webapp/rma/sqlalchemy/util.py', line 198 in starargs_as_list return func(self, *to_list(args[0], []), **kwargs) File '/home/moshe/top/webapp/rma/sqlalchemy/orm/query.py', line 571 in order_by q._order_by = q._order_by + criterion TypeError: can only concatenate list (not tuple) to list On May 6, 8:45 pm, Michael Bayer [EMAIL PROTECTED] wrote: On May 6, 2008, at 1:43 PM, Moshe C. wrote: Hi, Node is an orm mapped class, which is self-referential. myquery = Node.query() myquery = myquery.join('parent', aliased=True) myquery = myquery.filter(Node.c.desc.like('%something')) myquery = myquery.order_by(Node.c.name) The last line orders by the 'name' of the 2nd joined table. How can I add another order_by (after the one above) that orders by some column of the first instance of the table? place another order_by() either before the join(), or after calling reset_joinpoint(). Make sure you're on 0.4.5. --~--~-~--~~~---~--~~ 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: table union through ORM
On Tue, May 6, 2008 at 5:36 PM, Eric Lemoine [EMAIL PROTECTED] wrote: Thanks a lot. I'll try that It works great! I would never have found how to do it without support from you Michael. The doc wasn't explicit enough for me to figure it out by myself. But support on the mailing list is awesome as usual. Thanks again, -- Eric --~--~-~--~~~---~--~~ 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] Properly closing all database connections
I'm using SA with SQLite, and after executing session.close() and clear_mappers(), on Linux, lsof still says my SQLite file is open. Running engine.dispose() seems to finally close it, but I'm not sure if that's the proper way? Thanks, Karlo. --~--~-~--~~~---~--~~ 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: Properly closing all database connections
Karlo Lozovina wrote: I'm using SA with SQLite, and after executing session.close() and clear_mappers(), on Linux, lsof still says my SQLite file is open. Running engine.dispose() seems to finally close it, but I'm not sure if that's the proper way? That's correct. The engine holds the connection pool and dispose() will close the connections it manages. --~--~-~--~~~---~--~~ 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: Properly closing all database connections
yes, session/mappes are ORM level, metadata is lower and engine is lowest AFAIK. On Tuesday 06 May 2008 23:39:13 Karlo Lozovina wrote: I'm using SA with SQLite, and after executing session.close() and clear_mappers(), on Linux, lsof still says my SQLite file is open. Running engine.dispose() seems to finally close it, but I'm not sure if that's the proper way? --~--~-~--~~~---~--~~ 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: Properly closing all database connections
On May 6, 10:53 pm, jason kirtland [EMAIL PROTECTED] wrote: That's correct. The engine holds the connection pool and dispose() will close the connections it manages. Thanks... it's weird there's no mention of it in the docs. -- Karlo Lozovina - Mosor --~--~-~--~~~---~--~~ 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: Query Threading Issue
On May 4, 2008, at 10:59 AM, Paul Johnston wrote: Hi, I'm writing a ToscaWidget to do a kind of data grid. The way I've designed it so far, you pass an SA query object to the Widget when it's created. Then, each time it's displayed, it applies a few extra filters to the query and fetches the results. I'm using scoped sessions, and the query ends up being used in several different threads. My initial thoughts are that there could be thread safety problems with this approach, although in practice it works just fine. How bad are the threading issues? And is there anything I can do to fix it, e.g. a step that clones a query and attaches it to the session for the current thread? ive thought about this, and the Query is not in fact tied very hard to a partcular Session (or at all). I think it would be workable for us to add a using_session() method to it, i.e.: q = Query(User) print q.using_session(somesession).all() I sort of thought we had already done this at some point, or proposed it, and I'm not sure why it didn't actually happen. --~--~-~--~~~---~--~~ 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: Ordering results ina self-referential join
I couldn't create a simple test case, but I have analyzed the cause of the problem. The order_by() method is sent a list as an argument, but the argument 'criterion' becomes a tuple because of the def order_by(self, *criterion) syntax. in the case of if self._aliases_tail: , 'criterion' becomes a list again, but if _aliases_tail is None it remains a tuple. Now the cause of the problem is that on the first call to order_by(), self._aliases_tail exists, and on the 2nd call, following the reset_joinpoint() call, it is None. Therefore the '_order_by member' is initialized as a list, and later a tuple is attempted to be concatenated and hence the failure. The calling code from my source looks like this: myquery = Node.query() myquery = myquery.join('parent', aliased=True) myquery = myquery.order_by(Node.c.name) # _aliases_tail exists for this call myquery = myquery.reset_joinpoint().order_by(Node.c.popularity) # _aliases_tail is None for this call On May 6, 10:25 pm, Michael Bayer [EMAIL PROTECTED] wrote: On May 6, 2008, at 3:08 PM, Moshe C. wrote: Both methods cause a crash (yes, on 0.4.5) . converting a tuple to a list in sqlalchemy/orm/query.py fixes it for one of the methods, for the other you need to do the opposite, convert a list to a tuple. File '/home/moshe/top/webapp/rma/rma/controllers/list.py', line 215 in list_sources myquery = myquery.reset_joinpoint().order_by(model.Source.c.popularity) File '/home/moshe/top/webapp/rma/sqlalchemy/util.py', line 198 in starargs_as_list return func(self, *to_list(args[0], []), **kwargs) File '/home/moshe/top/webapp/rma/sqlalchemy/orm/query.py', line 571 in order_by q._order_by = q._order_by + criterion TypeError: can only concatenate list (not tuple) to list I cant reproduce that at all, even sending purposely wacky arguments to the previous order_by().can you please provide a test case ? --~--~-~--~~~---~--~~ 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: mssql, Linux, unixODBC - Could not locate column in row for column
Rick Morrison wrote: Sounds nice, thanks for the heads-up. There'll be opportunities for dialects to set up pool events as well. One of the things I'm looking to see is better reconnect support for dead database connections from network partitions, sql server restarts, etc. Is that going to be fully Dialect controlled, or is there some coming support for auto-reconnect as well? I was thinking of a user-level option for liveliness checking on pool checkout, with dialect-specific implementations (e.g. execute a 'SELECT 1', or something more efficient if the driver allows). Is that in line with what you were thinking? --~--~-~--~~~---~--~~ 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: mssql, Linux, unixODBC - Could not locate column in row for column
I was thinking of a user-level option for liveliness checking on pool checkout, with dialect-specific implementations (e.g. execute a 'SELECT 1', or something more efficient if the driver allows). Is that in line with what you were thinking? I had in mind something more of a optimistic / reactive nature, like a retry on a cursor failure. But this could work equally well and could be much simpler, albeit at some round-trip time on every pool checkout. What's the recovery strategy if the connection is found to be dead? An auto-reconnect with some retry count limit, or would it just notify the dialect and that's it? --~--~-~--~~~---~--~~ 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: Query Threading Issue
Hi Mike, ive thought about this, and the Query is not in fact tied very hard to a partcular Session (or at all). I think it would be workable for us to add a using_session() method to it, i.e.: The original workaround you suggested of using a callable works fine for now. A using_session method would be nice, avoid exposing this detail to the user of my widget. Although pretty soon after I made the change I found a separate advantage of using a callable, so I'll probably stay with that. I guess another option is to make the query bind to the scoped_session object, so it picks up an appropriate session when it actually uses it. Thanks for taking a look, 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: mssql, Linux, unixODBC - Could not locate column in row for column
Rick Morrison wrote: I was thinking of a user-level option for liveliness checking on pool checkout, with dialect-specific implementations (e.g. execute a 'SELECT 1', or something more efficient if the driver allows). Is that in line with what you were thinking? I had in mind something more of a optimistic / reactive nature, like a retry on a cursor failure. But this could work equally well and could be much simpler, albeit at some round-trip time on every pool checkout. What's the recovery strategy if the connection is found to be dead? An auto-reconnect with some retry count limit, or would it just notify the dialect and that's it? I believe right now it's a limited # of retries. (The basic support for ping-on-checkout is already in the pool as of 0.4, but no dialect hooks yet.) --~--~-~--~~~---~--~~ 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: Ordering results ina self-referential join
thats really weird. I dont have time to check this now but i added ticket 1027 to confirm. On May 6, 2008, at 5:22 PM, Moshe C. wrote: I couldn't create a simple test case, but I have analyzed the cause of the problem. The order_by() method is sent a list as an argument, but the argument 'criterion' becomes a tuple because of the def order_by(self, *criterion) syntax. in the case of if self._aliases_tail: , 'criterion' becomes a list again, but if _aliases_tail is None it remains a tuple. Now the cause of the problem is that on the first call to order_by(), self._aliases_tail exists, and on the 2nd call, following the reset_joinpoint() call, it is None. Therefore the '_order_by member' is initialized as a list, and later a tuple is attempted to be concatenated and hence the failure. The calling code from my source looks like this: myquery = Node.query() myquery = myquery.join('parent', aliased=True) myquery = myquery.order_by(Node.c.name) # _aliases_tail exists for this call myquery = myquery.reset_joinpoint().order_by(Node.c.popularity) # _aliases_tail is None for this call --~--~-~--~~~---~--~~ 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: Ordering results ina self-referential join
OK you got it in r4673, sorry youre hitting all these (kinda weird) On May 6, 2008, at 7:20 PM, Michael Bayer wrote: thats really weird. I dont have time to check this now but i added ticket 1027 to confirm. On May 6, 2008, at 5:22 PM, Moshe C. wrote: I couldn't create a simple test case, but I have analyzed the cause of the problem. The order_by() method is sent a list as an argument, but the argument 'criterion' becomes a tuple because of the def order_by(self, *criterion) syntax. in the case of if self._aliases_tail: , 'criterion' becomes a list again, but if _aliases_tail is None it remains a tuple. Now the cause of the problem is that on the first call to order_by(), self._aliases_tail exists, and on the 2nd call, following the reset_joinpoint() call, it is None. Therefore the '_order_by member' is initialized as a list, and later a tuple is attempted to be concatenated and hence the failure. The calling code from my source looks like this: myquery = Node.query() myquery = myquery.join('parent', aliased=True) myquery = myquery.order_by(Node.c.name) # _aliases_tail exists for this call myquery = myquery.reset_joinpoint().order_by(Node.c.popularity) # _aliases_tail is None for this call --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---