[sqlalchemy] Re: Testing Conditions for insertion, and grouping queries together
Mike Lewis wrote: Hi, I'm new to SQLAlchemy, and I was wondering if there's a way to test a condition while inserting. I'm trying to make my application use as few separate queries at the same time as possible. Here's a couple cases I have had issues with: Inserting something with a unique column if it doesn't exist. Right now, I am doing a select with a count() call. If I just insert it if it already exists I get an exception, but the exception doesn't propogate until the session is flushed. you can flush() the individual object using flush([myobject]). if youre looking to catch a unique constraint exception at that point, SQLAlchemy version 0.4 exports all the DBAPI errors such as OperationalError, ProgrammingError, so you can catch the appropriate exception regarding the constraint. since this is an optimistic approach, depending on the kind of data you're working with its often the case that the actual exception is not raised very often. Also, I am having a tough time catching the SQLAlchemy exceptions. What's the best way to go about this? theres an exception hierarchy in sqlalchemy/exceptions.py which starts with SQLAlchemyError. You can catch that at the most generic level, or take a look at the exception classes present. I have a join table where you have tags and things, and the join table tags the things. I want to test if it already exists and whatnot, and insert it in the same query if it doesn't. im not familiar with a SQL-level conditional insert, otherwise update statement. The only way I can see this happening (i.e. in a single query) is to write a stored procedure or trigger which does this. --~--~-~--~~~---~--~~ 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: One To Many Polymorphic Association.
On Monday 27 August 2007 19:57:21 Michael Bayer wrote:
On Aug 24, 2007, at 8:39 AM, praddy wrote:
##
from sqlalchemy import *
meta = BoundMetaData('sqlite://', echo=False)
# Parents table.
parents = Table('parents', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50), nullable=False)
)
# Children_1 Table.
children_1 = Table('children_1', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50), nullable=False)
)
# Children_2 Table.
children_2 = Table('children_2', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50))
)
# Association Table.
# This is a generic table which can relate anything to parent.
assoc = Table('assoc', meta,
# parents.c.id
Column(parent_id, Integer, ForeignKey(parents.c.id)),
# associate's id either children_1.c.id or children_2.c.id or any
other child.
Column(assoc_id, Integer),
# Which can be either 'child_1' or 'child_2' for now (can be used for
extending children
# type, decides which table to look in.
Column(assoc_type, String(20))
)
###
I am a novice with respect to sqlalchemy may be RDBMS as well.
How would you like to work on this scenario to achieve backwards
cascading (may not be the right word) which means when one deletes
one specific child from children_1 table (for example), there should
not be any
association entry, which associates that child to the parent, in the
association table as well?
To allow proper foreign key support, you probably want to place the
foreign key to the association table on each of children_1 and
children_2, and remove the assoc_id column from the assoc table.
this is the key to the blog article about polymorphic associations.
its probably easiest for you to work with the example mentioned in
the blog and modify. (http://techspot.zzzeek.org/files/
poly_assoc_2.py ). at least, if you study the example it will lead
to more insight on how to approach this. If you really want to keep
your foreign keys as they are, the ActiveRecord example (http://
techspot.zzzeek.org/files/poly_assoc_1.py ) illustrates that pattern
with SA.
next, you're going to want to specify cascade='all, delete-orphan'
on both relations which reference the association table:
mapper(Parent, parents, properties={
'associations':relation(Association, cascade=all, delete-orphan)
})
class Association(object):
def _child(self):
if self.assoc_type='child1':
return self.child_1
else:
return self.child_2
child=property(_child)
mapper(Association, assoc, properties={
'child_1':relation(Child1, backref=backref(assoc_1,
cascade=all, delete-orphan)),
'child_2':relation(Child2, backref=backref(assoc_2,
cascade=all, delete-orphan)),
})
mapper(Child1, children_1)
mapper(Child2, children_2)
im out of town this week so I dont have the resources to work out the
full solution but this is the general idea.
Mike,
I have got the solution this way.
- Pradeep Jindal
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
poly_assoc_sep_table.py
Description: application/python
[sqlalchemy] Best way to handle in()
I have mappers configured for main and child, and those tables are
linked on main.childid=child.childid. How can I use in() to get rows where
child.othercolumn is in a list of values? I'd like to do something like:
foo = session.query(MainTable).filter_by(customer='CUSTNAME')
foo = foo.filter(ChildTable.c.othercolumn.in_('bar', 'baz', 'qux'))
Or, even better, some variant on:
foo = session.query(MainTable).filter_by(customer='CUSTNAME')
foo = foo.filter(othercolumn in ('bar', 'baz', 'qux'))
When I try to do that, though, I get SQL like:
SELECT main.value AS main_value, main.childid AS main_childid, child.childid
AS child_childid, child.othercolumn AS child_othercolumn
FROM testing.main, testing.child
WHERE child.othercolumn IN (%(child_othercolumn)s, %(child_othercolumn_1)s,
%(child_othercolumn_2)s) ORDER BY main.value
which is really doing a cartesian join and never enforcing
main.childid=child.childid. Is there another way I should be approaching
this?
--
Kirk Strauser
signature.asc
Description: This is a digitally signed message part.
[sqlalchemy] Re: Best way to handle in()
Hi,
foo = session.query(MainTable).filter_by(customer='CUSTNAME')
foo = foo.filter(ChildTable.c.othercolumn.in_('bar', 'baz', 'qux'))
You need to do the join; pass the name of the relation (not the target
table) to join:
foo = foo.join('childtable').filter(ChildTable.c.othercolumn.in_('bar', 'baz',
'qux'))
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: Generative group_by query specify columns
So why is it possible do a query.group_by() but not a possible to specify the columns to select as execution of the query fails in postgresql because it is trying to select all the columns of table Foo in this instance. Or is their some way of combining the sql.select() module with the query module? _ From: Michael Bayer [mailto:[EMAIL PROTECTED] Sent: Friday, August 24, 2007 7:14 PM To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: Generative group_by query specify columns On Aug 24, 2007, at 10:43 AM, Curtis Scheer wrote: What is the syntax to specify the columns in a generative query? q = session.query(Foo) q = q.group_by([foo_table.c.description]) for currow in q: print currow This query selects all the columns in the Foo table, wondering how to specify the columns to select. query(Foo) will always select all columns that correspond to a Foo object. for individual columns, use a SQL expression instead of a Query, i.e. select([foo_table.c.cola, foo_table.c.colb]).group_by(...).where(...).etc() . --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Best way to handle in()
sorry for my bad sql, but where have u specified that link?
u should have something like
foo.filter( (Main.chidlid==Child.childid)
Child.othercolumn.in_('a', 'b', 'c') )
or
foo.join( child).filter( Child.othercolumn.in_('a', 'b', 'c') )
(warning: the exact syntax may or may not be this, do check)
On Tuesday 28 August 2007 22:58:11 Kirk Strauser wrote:
I have mappers configured for main and child, and those tables
are linked on main.childid=child.childid. How can I use in() to
get rows where child.othercolumn is in a list of values? I'd like
to do something like:
foo = session.query(MainTable).filter_by(customer='CUSTNAME')
foo = foo.filter(ChildTable.c.othercolumn.in_('bar', 'baz', 'qux'))
Or, even better, some variant on:
foo = session.query(MainTable).filter_by(customer='CUSTNAME')
foo = foo.filter(othercolumn in ('bar', 'baz', 'qux'))
When I try to do that, though, I get SQL like:
SELECT main.value AS main_value, main.childid AS main_childid,
child.childid AS child_childid, child.othercolumn AS
child_othercolumn
FROM testing.main, testing.child
WHERE child.othercolumn IN (%(child_othercolumn)s,
%(child_othercolumn_1)s, %(child_othercolumn_2)s) ORDER BY
main.value
which is really doing a cartesian join and never enforcing
main.childid=child.childid. Is there another way I should be
approaching this?
--~--~-~--~~~---~--~~
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: Problem with tinyint(1) on mysql
Mike Bernson wrote:
Using SQLAlchemy version 0.4b4. When reflecting
table from mysql database with tinyint(1) you get
a boolean back. I think this is error because it
returns a bad data type for tinyint(1).
I have a database that work under 0.3.0 where to
store a single digit I used tinyint(1) and now this
breaks under 0.4.0.
As far a I known mysql version 5.x does not have
a real boolean so reflecting a boolean hiding a
real and valid type
Can this be switch back to tinyint ?
Sure, just override the column type on reflection:
from sqlalchemy.databases import mysql
Table('mytable', metadata,
Column('mydigit', mysql.MSTinyInteger), # or, just use Integer
autoload=True)
Treating tinyint(1) as boolean is a general MySQL convention that
SQLAlchemy honors. Note that the '1' is not a precision modifier-
'tinyint' and 'tinyint(1)' both store signed values up to 127.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
