[sqlalchemy] circular model definitions

[Matt Haggard]

I'm stumped...

Setup: (Problem statement near the bottom)

In my Pylons app, I have three separate models: Customer, TPPAnswer,
SAQ
TPPAnswer is many-to-one Customer
SAQ is many-to-one Customer

Both have backreferences (so saq.customer and customer.saqs)

Currently, I have them defined in customer.py, tpp.py, saq.py.
Additionally, I have common.py which is where the metadata object
comes from -- all three import everything from common.py

In order to have the TPPAnswer many-to-one Customer reference, I
import customer_table and Customer so that I can do (w/i tpp.py):
from customer import customer_table, Customer
...
mapper(Answer, answers_t, properties={
'customer'  :relation(Customer, backref='tpp_answers'),
})

I have a similar setup for saq.py.


-
Problem:
in customer.py in the Customer class, I need to reference .tpp_answers
and .saqs... but because those references are created in tpp.py and
saq.py, they are not known to the Customer class.

If I have already imported the tpp model (in my controller), then the
Customer object is aware of self.tpp_answers but not
self.questionnaires:
  self.questionnaires
AttributeError: 'Customer' object has no attribute 'questionnaires'

If I have already imported the saq model, then the Customer object is
aware of self.questionnaires but not self.tpp_answers.

I can't import both saq and tpp in the controller:
  self._pre_existing_column = table._columns.get(self.key)
AttributeError: 'Column' object has no attribute '_columns'

How do I set up my model such that I can import the pieces I need when
I need them?  So if I call a certain method of the customer object
(adjust_grade() for example) it will be able to acquire all the
attributes it needs.  I don't want to have a massive file with all the
relations in them, because most of the time I only need a part of
them.  For instance, a lot of the things I do with the SAQ model never
even interact with the customer -- same with the TPP model.  It's the
occasional interaction amongst all three of them that's giving me
grief.

Sorry for such a wordy, confusing explanation.  I'm glad to clarify if
it helps.
-

class Customer(object):

def adjust_grade(self, *whatchanged):
self.tpp_answers
self.questionnaires
--~--~-~--~~~---~--~~
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: (ProgrammingError) current transaction is aborted, commands ignored until end of transaction block

[Matt Haggard]

I'm getting a ProgrammingError (I've pasted the last part of the
traceback at the bottom of the page).

The error comes from my first heavy-AJAX page in Pylons (postgres
backend).  If I cause too many AJAX requests at a time, or even after
doing 3 non-overlapping AJAX requests, I get the error.  I wonder if
there's some sort of handle releasing I need to do... or transaction
releasing?

As a side note... I test using SQLite and put in production with
Postgres, and the page works wonderfully in SQLite -- it's only
Postgres that has the problem.

Thanks,

Matt



Here are the methods called during the AJAX request (it starts on
toggle_property(option_id, 'select')):

# session is the user's session (browser stuff)
# Session = scoped_session(sessionmaker(autoflush=True,
transactional=True, bind=config['pylons.g'].sa_engine))

def _single_row(self, option_id, message='', withrow=False):
opt = Session.query(Option).filter_by(id=option_id).first()
if opt:
return render('tpp_manager/option_row.mtl', option=opt,
updateid=option_%s%opt.id, message=message, withrow=withrow)
else:
if not message:
message = Doesn't exist
return render('tpp_manager/option_row.mtl',
message=message, withrow=withrow)

def _toggle_property(self, option_id, prop):
if prop == 'select':
option_id = int(option_id)
if session['tpp_select'].get(option_id, False):
del(session['tpp_select'][option_id])
else:
session['tpp_select'][option_id] = True
session.save()
return True
else:
opt =
Session.query(Option).filter_by(id=option_id).first()
if opt:
if prop == 'whitelisted':
opt.whitelisted = not opt.whitelisted
if opt.whitelisted and opt.blacklisted:
opt.blacklisted = False
elif prop == 'blacklisted':
opt.blacklisted = not opt.blacklisted
if opt.blacklisted and opt.whitelisted:
opt.whitelisted = False
elif prop == 'approved':
opt.approved = not opt.approved
else:
return False
Session.commit()
Session.refresh(opt)
else:
return False
return True

def toggle_property(self, option_id, prop):
message = ''
if not self._toggle_property(option_id, prop):
message = 'Failed to change flag.'
return self._single_row(option_id)


File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/orm/query.py', line 719 in first
  ret = list(self[0:1])
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/orm/query.py', line 748 in __iter__
  return self._execute_and_instances(context)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/orm/query.py', line 751 in _execute_and_instances
  result = self.session.execute(querycontext.statement,
params=self._params, mapper=self.mapper,
instance=self._refresh_instance)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/orm/session.py', line 535 in execute
  return self.__connection(engine,
close_with_result=True).execute(clause, params or {})
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 844 in execute
  return Connection.executors[c](self, object, multiparams, params)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 895 in execute_clauseelement
  return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params)  1), distilled_params=params)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 907 in _execute_compiled
  self.__execute_raw(context)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 916 in __execute_raw
  self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 953 in _cursor_execute
  self._handle_dbapi_exception(e, statement, parameters, cursor)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 935 in _handle_dbapi_exception
  raise exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) current transaction is aborted,
commands ignored until end of transaction block
 'SELECT anon_1.tpp_options_question_id AS
anon_1_tpp_options_question_id, anon_1.tpp_options_option AS
anon_1_tpp_options_option, anon_1.tpp_options_id AS
anon_1_tpp_options_id, anon_1.tpp_options_approved AS

[sqlalchemy] Re: ProgrammingError: (ProgrammingError) current transaction is aborted, commands ignored until end of transaction block

[Matt Haggard]

INSERT or UPDATE?  I don't do any inserts with this code... only
changing what's already there.

Is an integrity constraint a PG thing, or SQLAlchemy model thing?

And can I do Session.rollback() ?

Thanks!

On Jun 27, 2:19 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Jun 27, 2008, at 4:03 PM, Matt Haggard wrote:



  I'm getting a ProgrammingError (I've pasted the last part of the
  traceback at the bottom of the page).

  The error comes from my first heavy-AJAX page in Pylons (postgres
  backend).  If I cause too many AJAX requests at a time, or even after
  doing 3 non-overlapping AJAX requests, I get the error.  I wonder if
  there's some sort of handle releasing I need to do... or transaction
  releasing?

  As a side note... I test using SQLite and put in production with
  Postgres, and the page works wonderfully in SQLite -- it's only
  Postgres that has the problem.

 PG has this issue if you attempt to INSERT a row which throws an  
 integrity constraint; a rollback() is required after this occurs.  I  
 can't see it in your code below but it would imply that such an  
 exception is being caught and then thrown away.
--~--~-~--~~~---~--~~
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: (ProgrammingError) current transaction is aborted, commands ignored until end of transaction block

[Matt Haggard]

I've fixed it by calling Session.clear() at the end of every
controller action (it's in __after__()).

I'm gonna go read about what that does -- right now it's just magic as
far as I can tell :)

Thanks again for the help, Michael

On Jun 27, 3:30 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Jun 27, 2008, at 5:03 PM, Matt Haggard wrote:



  INSERT or UPDATE?  I don't do any inserts with this code... only
  changing what's already there.

  Is an integrity constraint a PG thing, or SQLAlchemy model thing?

 its a PG thing.  Other things can likely cause PG to get into this  
 state as well.   But unless you're squashing exceptions, SQLA can't  
 really let the DB get into this state without complaining loudly.

  And can I do Session.rollback() ?

 sure !
--~--~-~--~~~---~--~~
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] Models split across files

[Matt Haggard]

In my various models, I recently noticed that I have been reusing my
customer table as it relates to many other models.  I've been making
CustomerPart objects in each of the model files and retyping the table
schema and object (only including the pieces I need for that
particular case)

Now I'd like to have one customer model and let the other models
access it -- rather than continually retyping everything.

But I get this error when I break it out (this is when I try to use it
in a controller in pylons):
  raise exceptions.InvalidRequestError(Could not find table '%s' with
which to generate a foreign key % tname)
InvalidRequestError: Could not find table 'customer' with which to
generate a foreign key

I've included a before and after (hooray for GIT) of the models:

Thanks!

Matt



saq.py before (this one works)

from sqlalchemy import Column, MetaData, Table, types, ForeignKey,
func
from sqlalchemy.orm import mapper, relation

from datetime import datetime

from formencode import validators
from smmodels import fe_obj, NoHTML, SuperDateValidator, fe_setter

metadata = MetaData()

...

customer_table_part = Table('customer', metadata,
Column('id', types.Integer, primary_key=True, index=True),
Column('email', types.Unicode, unique=True, index=True),
Column('validation_type', types.Unicode),
)

questionnaire_table = Table('saq_questionnaire', metadata,
...
Column('customer_id', types.Integer, ForeignKey('customer.id'),
index=True),
...
)

questions_table = Table('saq_questions_new', metadata,
...
)


class Questionnaire(fe_setter):

def __str__(self):
return 'id: %s customer_id: %s' % (self.id, self.customer_id)

def __repr__(self):
return Questionnaire(%s, customer_id:%s) % (self.id,
self.customer_id)


class Question(fe_setter):
pass


class CustomerPart(fe_setter):

def __init__(self):
pass


mapper(Question, questions_table)
mapper(CustomerPart, customer_table_part)
mapper(Questionnaire, questionnaire_table, properties={
...
'customer'  :relation(CustomerPart, backref='questionnaires')
})





saq.py after (all the same except removed references to CustomerPart

...
from smmodels.customer import customer_table, Customer
...

mapper(Question, questions_table)
mapper(Questionnaire, questionnaire_table, properties={
...
'customer'  :relation(Customer, backref='questionnaires')
})


customer.py after (newly created)

from sqlalchemy import Column, MetaData, Table, types, ForeignKey
from sqlalchemy.orm import mapper, relation

from formencode import validators
from smmodels import fe_obj, NoHTML, fe_setter

from datetime import date

metadata = MetaData()

customer_table = Table('customer', metadata,
Column('id', types.Integer, primary_key=True, index=True),
Column('email', types.Unicode, unique=True, index=True),
Column('validation_type', types.Unicode),
)

class Customer(object):

def __init__(self):
pass

mapper(Customer, customer_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: Triple Join Table

[Matt Haggard]

Thank you andrija and Micheal (especially recommending getting it
working first without associationproxy -- that really helped)

Here's a solution I've got that works (not exactly as I intended, but
I can live with it). I just keep the section and question paired
together.

from sqlalchemy import Column, MetaData, Table, types, ForeignKey,
func
from sqlalchemy.orm import mapper, relation
from sqlalchemy.sql.expression import select, and_
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.collections import attribute_mapped_collection

from datetime import datetime

from formencode import validators
from smmodels import NoHTML, SuperDateValidator

metadata = MetaData()

class SaqType:

def __init__(self):
pass

def __str__(self):
return 'id: %s name: %s' % (self.id, self.name)

def __repr__(self):
return Questionnaire Type(%s, name:'%s') % (self.id,
self.name)


class SaqJoin:

def __init__(self, type_obj=None, question_obj=None,
section_obj=None):
self.type = type_obj
self.question = question_obj
self.section = section_obj

def __repr__(self):
return 'SaqJoin type,question,section:(%s,%s,%s)' %
(self.type_id, self.question_id, self.section_id)



class Questionnaire:

def __str__(self):
return 'id: %s customer_id: %s' % (self.id, self.customer_id)

def __repr__(self):
return Questionnaire(%s, customer_id:%s) % (self.id,
self.customer_id)


question_types = ['yn','yn_na','label','comment']
class Question:
pass

class Section:

def __init__(self):
pass


class Answer:

def __init__(self):
pass


class CustomerPart:

def __init__(self):
pass


sections_by_type = select(
[join_table.c.type_id, join_table.c.section_id],
group_by=[join_table.c.section_id,
join_table.c.type_id]).alias('sections_by_type')

mapper(Question, questions_table)
mapper(Section, sections_table)
mapper(CustomerPart, customer_table_part)
mapper(Answer, answers_table, properties={
'question'  :relation(Question, backref='answer', uselist=False)
})
mapper(SaqJoin, join_table, properties={
'type'  :relation(SaqType),
'section'  :relation(Section, backref='parent',
order_by=join_table.c.ord),
'question' :relation(Question, backref='parent',
order_by=join_table.c.ord),
})
mapper(SaqType, types_table, order_by=types_table.c.id, properties={
'qs':relation(SaqJoin, order_by=join_table.c.ord),
'my_sections'   :relation(Section, secondary=sections_by_type,
primaryjoin = types_table.c.id == sections_by_type.c.type_id,
backref='type', order_by=sections_table.c.secnum),
})
mapper(Questionnaire, questionnaire_table, properties={
'answers'   :relation(Answer, backref='questionnaire'),
'type'  :relation(SaqType),
'customer'  :relation(CustomerPart, backref='questionnaires')
})


On Jun 17, 9:26 am, Michael Bayer [EMAIL PROTECTED] wrote:
 any table that has more than just two foreign keys to remote tables
 does not normally qualify as a secondary table (its only allowed for
 certain edge cases which are not present here).  The Join class
 mapped to the join_table is the right approach.  In which case, you
 *definitely* don't want to be using secondary or secondary_join in
 any case here.  Any access from A-(Join)-B where you don't want to
 see the Join object, you should use the associationproxy at that
 point - but get the entire thing to work first without using
 associationproxy, as its only a convenience extension.

 On Jun 16, 5:11 pm, Matt Haggard [EMAIL PROTECTED] wrote:

  I've got a triple join table (joining three things together) and I'm
  really struggling to get it to work as I intend.  I've pasted the full
  model at the bottom.  I've struggled with this off and on for months
  now... I don't really understand how I can get SQLAlchemy to do what I
  want.

  The join table has 4 columns:

  type_id | section_id | question_id | ord

  What I'd like is the following:

  Questionnaire object with:
     .sections list that contain
        .questions list that contain:
           .answer

  So, something like:
  q = Questionnaire()
  print q.sections[2].questions[1].answer
  # yield the answer to question 1 of section 2 (or question 2 of
  section 3 depending on your indexing :) )

  I encounter a problem because a Question object doesn't know what the
  type_id is because that is stored with the Questionnaire object.
  Likewise from a section object.  I'm pulling my hair out...

  Thanks,

  Matt Haggard

  
  from sqlalchemy import Column, MetaData, Table, types, ForeignKey,
  func
  from sqlalchemy.orm import mapper, relation
  from sqlalchemy.sql.expression import select
  from sqlalchemy.ext.associationproxy import association_proxy

  from datetime import datetime

  from formencode import validators
  from smmodels import NoHTML

[sqlalchemy] appending an object through an 2-level association table

[Matt Haggard]

I'm trying to figure out how to add objects through the ORM.  (My
schema and mappings are below.)

In the shell, I can do the following:
newQ = Question()
# ... set the attributes of newQ
mytype = session.query(QType).first()
mytype.my_sections
# correctly gives all the sections that belong to the type
mytype.my_sections[0].questions
# correctly gives only the questions that belong to both the type and
the section (how does this work btw?)
mytype.my_sections[0].questions.append(newQ)
# inserts only the section_id and question_id into the jointable; it's
missing the type id

How can I get it to also insert the type_id?

Thanks,

Matt


-
questions_table : id | question
sections_table : id | name
types_table : id | name
join_table : type_id | question_id | section_id

class QType(object):
allquestions = association_proxy('joinObj', 'questions')
class QJoin(object): pass
class Question(object): pass
class Section(object): pass

sections_by_type = select(
[join_table.c.type_id, join_table.c.section_id],
group_by=[join_table.c.section_id]).alias('sections_by_type')

mapper(Question, questions_table)
mapper(Section, sections_table, properties={
'questions' :relation(Question, secondary=join_table,
primaryjoin = sections_table.c.id == join_table.c.section_id,
secondaryjoin = join_table.c.question_id ==
questions_table.c.id,
backref='section'),
})
mapper(QJoin, join_table, properties={
'type'  :relation(QType),
'sections'  :relation(Section, backref='parent'),
'questions' :relation(Question, backref='parent'),
})
mapper(QType, types_table, properties={
'joinObj'   :relation(QJoin),
'my_sections'   :relation(Section, secondary=sections_by_type,
primaryjoin = types_table.c.id == sections_by_type.c.type_id,
backref='type'),
})
--~--~-~--~~~---~--~~
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

[Matt Haggard]

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] Goofy Association Table

[Matt Haggard]

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] Handling unique constraints

[Matt Haggard]

I'm using SQLAlchemy with Pylons and am having trouble validating
data.  I have an App object mapped to a table with a unique constraint
on App.number.

Here's some code:

q = Session.query(App)
if app_id:
q = q.filter_by(id=app_id).first()
if q:
c.app = q
number = request.params.get('number')
notes = request.params.get('notes')
if appmodel and number:
try:
q.number = number
q.notes = notes
Session.save(q)
Session.commit()
c.message = 'Record updated'
except:
# restore pre-form data ?? how??
c.message = 'Error updating record'
return render('index.mtl')
else:
return self.index()

My questions are:

1) When I do the try statement, the value of q.number changes to
whatever the user passed in via the form -- even if it's invalid, so
that when I render the page, the invalid value is used.  How do I
reset the object to have the values it had before I did the try?  Do I
have to get it afresh from the db?

2) How do I let the user know which value caused the record not to
update?  What information does SQLAlchemy provide back that I can use
to say: You're number must be unique... and such-and-such must be
greater than 0, etc..?

Thanks,

Matt Haggard
--~--~-~--~~~---~--~~
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: Sum with Grouping

[Matt Haggard]

Oh, okay.

Thanks you.

On Nov 20, 5:23 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 the aggregate methods on Query, such as apply_sum(), apply_avg(),
 etc.,  are not in such great shape right now...they've been neglected
 and in fact aren't even working correctly with GROUP BY, etc...I've
 added trac ticket #876 for this.  If you know the exact SQL and
 columns you'd like to get back, typically its best just to issue that
 query without using an ORM construct.  your options for this are
 either just text:

 engine.execute(SELECT sum(amount), type from purchases group by
 type).fetchall()

 or the expression construct would look like:

 engine.execute(select([func.sum(Purchase.amount),
 Purchase.type]).group_by(Purchase.type)).fetchall()

 one important thing to be aware of is that SA has two distinct levels
 of SQL API; the ORM, which deals with Sessions and Query objects, and
 the SQL Expression Language, which deals with select(), update(),
 etc.  the expression language provides more direct access to SQL
 constructs.

--~--~-~--~~~---~--~~
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: Sum with Grouping

[Matt Haggard]

thank you for the response. I have a few more followup questions (I am
really a newbie to this...) :

1. Where does engine come from?  Is there anyway to do what you've
suggested with Session?  If it helps, I'm using this with pylons and
am trying to get stuff working in the controller of my app.

2. If I have to write SQL (or a pythonic version of SQL) to get info
out of the DB, why am I even using SQL Alchemy?  it seems a little
ridiculous.

On Nov 20, 5:23 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 the aggregate methods on Query, such as apply_sum(), apply_avg(),
 etc.,  are not in such great shape right now...they've been neglected
 and in fact aren't even working correctly with GROUP BY, etc...I've
 added trac ticket #876 for this.  If you know the exact SQL and
 columns you'd like to get back, typically its best just to issue that
 query without using an ORM construct.  your options for this are
 either just text:

 engine.execute(SELECT sum(amount), type from purchases group by
 type).fetchall()

 or the expression construct would look like:

 engine.execute(select([func.sum(Purchase.amount),
 Purchase.type]).group_by(Purchase.type)).fetchall()

 one important thing to be aware of is that SA has two distinct levels
 of SQL API; the ORM, which deals with Sessions and Query objects, and
 the SQL Expression Language, which deals with select(), update(),
 etc.  the expression language provides more direct access to SQL
 constructs.

--~--~-~--~~~---~--~~
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] Sum with Grouping

[Matt Haggard]

I'm very new to sqlalchemy and I'm still trying to wrap my head around
how it works.

I have a table with columns: type, amount.  I want to sum the amounts
grouped by type.  In SQL I would write:
SELECT sum(amount), type from purchases group by type;

How do I do this with SQLAlchemy?  This is what I have so far, but I
don't really understand what's going on:

pq = Session.query(Purchase).apply_sum(Purchase.amount)
for x in pq:
  ret += 'br' + str(x.type) + str(x.amount)
  # This prints out every item in the db... the sum seems to not have
done anything

bytypes = pq.group_by(Purchase.type)
for x in bytypes:
  ret += 'br' + str(x.type) + str(x.amount)
  # This prints out one of each type, but the amount is not the sum of
all the types, it's just the last one of each type

bytypes = bytypes.sum(Purchase.amount)
# This is the sum of everything.

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---