[sqlalchemy] joined table inheritance, association object between base class
-Base entity is Party, which has sub-types Person and Organization.
-Using joined table inheritance, with base table 'parties', and sub-
tables 'people' and 'organizations'
-Want to create association class to capture various types of
relationships between parties. Let's say this association would be
called PartyRelationship, which would be many-to-many between parties
to parties.
-Using declarative style
class Party(Base):
__tablename__ = 'parties'
type = Column(Integer)
__mapper_args__ = {'polymorphic_on': type}
id = Column(Integer, primary_key=True)
name = Column(String)
email = Column(String)
password = Column(String)
..
..
class Person(Party):
__tablename__ = 'people'
id = Column(Integer, ForeignKey('parties.id'), primary_key=True)
__mapper_args__ = {'polymorphic_identity': PartyTypes.PERSON}
date_of_birth = Column(String)
..
..
class Organization(Party):
__tablename__ = 'organizations'
id = Column(Integer, ForeignKey('parties.id'), primary_key=True)
__mapper_args__ = {'polymorphic_identity':
PartyTypes.ORGANIZATION}
purpose = Column(String)
..
..
class PartyRelationship(Base):
__tablename__ = 'party_relationships'
from = Column(Integer, ForeignKey('parties.id'), primary_key=True)
to = Column(Integer, ForeignKey('parties.id'), primary_key=True)
type = Column(Integer) # there are different types of
relationships
How would I go about defining a relation in this scenario, which,
instead of being many-to-many between two different tables/objects, is
many-to-many for parties?
Brad
P.S. Really liking sqlalchemy so far. Saves time but doesn't take
away control.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Problems with sub-queries
Mike wrote: > > > TypeError: 'Alias' object is unindexable > that error means you're trying to say x['foo'] on something that doesn't have a __getitem__(). I don't see that here, can you send a stack trace which would reveal if this is being produced from within SQLAlchemy internals ? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Commiting inserts made through Stored Procedure calls
Rodney Haynie wrote: >> >> > Ok, tried it. Still no data being committed. > No errors popping either. its likely a turbogears usage issue so check with their list. I know they have their own idea about transactional scope. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: 'both of the same direction' relation error appears when upgrading from 0.5.2 to 0.5.4p2
Matthieu Imbert wrote:
> Hi,
>
> sqlalchemy.exc.ArgumentError: Entity.creator and back-reference
> Client.entities_created are both of the same direction 'ONETOMANY>.
> Did you mean to set remote_side on the many-to-one side?
>
> relation(Client,
> collection_class = set,
> primaryjoin = entity_table.c.id_creator ==
> entity_table.c.id,
> foreign_keys = [entity_table.c.id_creator],
> backref = sqlalchemy.orm.backref('entities_created',
> foreign_keys = [entity_table.c.id]))})
>
> I looked at other mails mentionning this exception (notably:
> http://groups.google.com/group/sqlalchemy/browse_thread/thread/232305afeab6ea18/6d10fe9712b3fdef?lnk=gst&q=both+of+the+same+direction#6d10fe9712b3fdefand
> http://groups.google.com/group/sqlalchemy/browse_thread/thread/d183b378c38448bf/0d96e44d92d7a45d?lnk=gst&q=both+of+the+same+direction#0d96e44d92d7a45d)
> so i added the explicit foreign_keys declaration in the backref
> 'entities_created', but the problem is still there.
"remote_side" referenced in the error message as well as the google groups
messages you reference is an actual option you can set on relation() and
backref(), and is required any time you have a self-referential
many-to-one relation. Since Client->Entity is self-referential and m2o
its required, as "remote_side=Entity.id" (or entity_table.c.id).
>
> any help appreciated [?]
>
> >
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SqlAlchemy limiting returned rows prematurely
Mike Driscoll wrote: > > Hi, > > I am having an issue with what is getting returned from my SA query. > Here's my code: > > > > beginDate = "1/1/2007" > endDate = "12/31/2007" > qry = session.query(CheckHistory.CHK_DOC_NO, > CheckHistory.EMP_ID, > CheckHistory.CHECK_DATE, > CheckHistory.CHECK_NO, > CheckHistory.CHECK_AMT, > CheckHistory.STATUS, > CheckHistory.PAY_PERIOD, > CheckHistory.DIRECT_DEPOSIT > ) > qry = qry.filter(CheckHistory.CHECK_DATE.between(beginDate, endDate) > ).order_by(CheckHistory.CHECK_DATE.desc()) > result = qry.all() > > > > When I run this, it only shows results between 1/1 and 1/10. If I run > the equivalent SQL query directly in MS Sql Server 2000's Enterprise > Manager, I get everything between 1/1 and 1/12 as I should. Does > SqlAlchemy have some kind of result set limiter? There are lots of > rows returned... > > I am using SA 0.5.4p2 and Python 2.5. Thanks! use echo="debug" on your engine to see what rows are being fetched. the query you have above is very straightforward. > > Mike > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] SqlAlchemy limiting returned rows prematurely
Hi, I am having an issue with what is getting returned from my SA query. Here's my code: beginDate = "1/1/2007" endDate = "12/31/2007" qry = session.query(CheckHistory.CHK_DOC_NO, CheckHistory.EMP_ID, CheckHistory.CHECK_DATE, CheckHistory.CHECK_NO, CheckHistory.CHECK_AMT, CheckHistory.STATUS, CheckHistory.PAY_PERIOD, CheckHistory.DIRECT_DEPOSIT ) qry = qry.filter(CheckHistory.CHECK_DATE.between(beginDate, endDate) ).order_by(CheckHistory.CHECK_DATE.desc()) result = qry.all() When I run this, it only shows results between 1/1 and 1/10. If I run the equivalent SQL query directly in MS Sql Server 2000's Enterprise Manager, I get everything between 1/1 and 1/12 as I should. Does SqlAlchemy have some kind of result set limiter? There are lots of rows returned... I am using SA 0.5.4p2 and Python 2.5. Thanks! Mike --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] 'both of the same direction' relation error appears when upgrading from 0.5.2 to 0.5.4p2
Hi,
I have data model with joined table inheritance + relations between classes
of this joined table inheritance class hierarchy. This works well with
sqlalchemy 0.5.2, but when upgrading to 0.5.4p2, i get the following
exception:
sqlalchemy.exc.ArgumentError: Entity.creator and back-reference
Client.entities_created are both of the same direction .
Did you mean to set remote_side on the many-to-one side?
Here is a sample test case showing my problem:
First the class hierarchy schema:
[image: schema.png]
so Client inherits from Entity, and Entity and Client have a one to many
relation (each Entity is "created" by a Client)
then the code:
import sqlalchemy, sqlalchemy.orm
from sqlalchemy import Table, Column, Sequence, Integer, Text, Boolean,
DateTime, ForeignKey
from sqlalchemy.orm import mapper, relation
class EntityType(object):
Entity = 0
Client = 1
class Entity(object):
pass
class Client(Entity):
pass
engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
connexion = engine.connect()
metadata = sqlalchemy.MetaData()
metadata.bind = engine
session_maker = sqlalchemy.orm.sessionmaker(bind = engine, autoflush = True,
autocommit = False)
session = session_maker()
entity_table = Table(
'entity', metadata,
Column('id', Text, primary_key = True),
Column('entity_type', Integer, nullable = False, index = True),
Column('id_creator', Text, ForeignKey('entity.id'), nullable = False,
index = True),
Column('description', Text))
client_table = Table(
'client_data', metadata,
Column('id', Text, ForeignKey('entity.id'), primary_key = True),
Column('data', Text))
metadata.create_all()
connexion.execute(entity_table.insert(), [{
'id': 'root',
'id_creator': 'root',
'entity_type': EntityType.Client,
'description': 'Lorem ipsum' }])
connexion.execute(client_table.insert(), [{
'id': 'root',
'data': 'dolor sit amet'}])
mapper(Entity,
entity_table,
polymorphic_on = entity_table.c.entity_type,
polymorphic_identity = EntityType.Entity,
properties = {
'creator':
relation(Client,
collection_class = set,
primaryjoin = entity_table.c.id_creator ==
entity_table.c.id,
foreign_keys = [entity_table.c.id_creator],
backref = sqlalchemy.orm.backref('entities_created',
foreign_keys = [entity_table.c.id]))})
mapper(Client,
client_table,
inherits = Entity,
polymorphic_identity = EntityType.Client)
root = session.query(Client).get('root')
when running on 0.5.2, everything is ok.
when running on 0.5.4 i get the following stack trace:
Traceback (most recent call last):
File "test.py", line 63, in
root = session.query(Client).get('root')
File "/usr/lib/pymodules/python2.5/sqlalchemy/orm/session.py", line 895,
in query
return self._query_cls(entities, self, **kwargs)
File "/usr/lib/pymodules/python2.5/sqlalchemy/orm/query.py", line 91, in
__init__
self._set_entities(entities)
File "/usr/lib/pymodules/python2.5/sqlalchemy/orm/query.py", line 100, in
_set_entities
self.__setup_aliasizers(self._entities)
File "/usr/lib/pymodules/python2.5/sqlalchemy/orm/query.py", line 114, in
__setup_aliasizers
mapper, selectable, is_aliased_class = _entity_info(entity)
File "/usr/lib/pymodules/python2.5/sqlalchemy/orm/util.py", line 492, in
_entity_info
mapper = class_mapper(entity, compile)
File "/usr/lib/pymodules/python2.5/sqlalchemy/orm/util.py", line 567, in
class_mapper
mapper = mapper.compile()
File "/usr/lib/pymodules/python2.5/sqlalchemy/orm/mapper.py", line 658, in
compile
mapper._post_configure_properties()
File "/usr/lib/pymodules/python2.5/sqlalchemy/orm/mapper.py", line 687, in
_post_configure_properties
prop.init()
File "/usr/lib/pymodules/python2.5/sqlalchemy/orm/interfaces.py", line
408, in init
self.do_init()
File "/usr/lib/pymodules/python2.5/sqlalchemy/orm/properties.py", line
717, in do_init
self._post_init()
File "/usr/lib/pymodules/python2.5/sqlalchemy/orm/properties.py", line
1015, in _post_init
self.backref.compile(self)
File "/usr/lib/pymodules/python2.5/sqlalchemy/orm/properties.py", line
1174, in compile
prop._add_reverse_property(self.key)
File "/usr/lib/pymodules/python2.5/sqlalchemy/orm/properties.py", line
708, in _add_reverse_property
" Did you mean to set remote_side on the many-to-one side ?" % (self,
other, self.direction))
sqlalchemy.exc.ArgumentError: Entity.creator and back-reference
Client.entities_created are both of the same direction .
Did you mean to set remote_side on the many-to-one side ?
I looked at other mails mentionning this exception (notably:
http://groups.google.com/group/sqlalchemy/browse_thread/thread/232305afeab6ea18/6d10fe9712b3fdef?lnk=gst&q=both+of+the+same+direction#6d10fe9712b3fdefand
http://groups.google.com/group/sql
[sqlalchemy] Re: Commiting inserts made through Stored Procedure calls
Michael Bayer wrote:
> Rodney Haynie wrote:
>
>> Michael Bayer wrote:
>>
>>> Rodney Haynie wrote:
>>>
>>>
I am having problems getting my data to save when the changes are made
through stored procedures.
I created a sample block of code below to show exactly how I am
implementing this.
conn = DBSession.connection()
statement = sql.text("""CALL insert_test(321, 1);""")
results = conn.execute(statement).fetchall()
# The result set will have the new primary key value.
if results == []:
return None
newID = results[0][0]
transaction.commit()
return newID
>>> what is "transaction" here ? it seems to be ephemeral.
>>>
>>> I failed to say this is a TG2 app. There is the transaction module I
>>> import at the top of the controller file.
>>>
>>>
>> import transaction
>>
>> I was trying different things, and I believe I don't need the code
>> "transaction.commit()". But I was grasping at straws.
>> Is there any way I can tell that the automatic transaction sees changes?
>>
>
> ive no idea what that is. I think in this case you're looking for:
>
> tranaction = conn.begin()
>
> transaction.commit()
>
>
Ok, tried it. Still no data being committed.
No errors popping either.
>
>
> >
>
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Problems with sub-queries
Hi all, I've been banging my head against the wall on this one for about a day and a half. I pinged the IRC channel and they told me to use a sub- query, but I can't seem to get the code right. I hope someone here can give me a clue: Here's the original SQL I'm trying to convert to SA: SELECT Checks.CHK_DOC_NO, Checks.EMP_ID, Checks.CHECK_DATE, Checks.CHECK_NO, Checks.CHECK_AMT, Checks.STATUS, Checks.PAY_PERIOD, Checks.DIRECT_DEPOSIT, NetNames.NETNAME FROM myDB.dbo.ESYCHKHH Checks INNER JOIN (SELECT KEY1 AS EMP_ID, UDFVALUE AS NETNAME FROM myDB.dbo.ESXUDFDD WHERE UDF_ID = 24 and UDFVALUE='plz') NetNames ON Checks.EMP_ID = NetNames.EMP_ID ORDER BY Checks.PAY_PERIOD DESC And here's what I have so far: netnames = session.query(UserDefined.KEY1, UserDefined.UDFVALUE ).filter(and_(UserDefined.UDF_ID==24, UserDefined.UDFVALUE=="username")).subquery() qry = session.query(CheckHistory.CHK_DOC_NO, CheckHistory.EMP_ID, CheckHistory.CHECK_DATE, CheckHistory.CHECK_NO, CheckHistory.CHECK_AMT, CheckHistory.STATUS, CheckHistory.PAY_PERIOD, CheckHistory.DIRECT_DEPOSIT, netnames.c.UDFVALUE ) myJoin = join(CheckHistory, netnames, CheckHistory.EMP_ID == netnames.c.EMP_ID) qry = qry.select_from(myJoin).filter (CheckHistory.EMP_ID==netnames.c.EMP_ID) result = qry.order_by(CheckHistory.PAY_PERIOD).all() It throws an exception on the first qry: TypeError: 'Alias' object is unindexable I don't really know why it's giving me that. I can call "netnames.c.UDFVALUE" on the command line and it returns...albeit with this weird stuff: Column(u'UDFVALUE', MSChar(collation=u'SQL_Latin1_General_CP1_CI_AS', length=60), table=<%(23283536 anon)s>) I am running this against reflected MS SQL Server 2000 tables on Windows XP with SA 0.5.4p2 and Python 2.5. Thank you for you time! Mike --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Commiting inserts made through Stored Procedure calls
Rodney Haynie wrote:
>
>
> Michael Bayer wrote:
>> Rodney Haynie wrote:
>>
>>> I am having problems getting my data to save when the changes are made
>>> through stored procedures.
>>> I created a sample block of code below to show exactly how I am
>>> implementing this.
>>>
>>>
>>> conn = DBSession.connection()
>>> statement = sql.text("""CALL insert_test(321, 1);""")
>>> results = conn.execute(statement).fetchall()
>>> # The result set will have the new primary key value.
>>> if results == []:
>>> return None
>>>
>>> newID = results[0][0]
>>> transaction.commit()
>>> return newID
>>>
>>>
>>
>> what is "transaction" here ? it seems to be ephemeral.
>>
>> I failed to say this is a TG2 app. There is the transaction module I
>> import at the top of the controller file.
>>
>
> import transaction
>
> I was trying different things, and I believe I don't need the code
> "transaction.commit()". But I was grasping at straws.
> Is there any way I can tell that the automatic transaction sees changes?
ive no idea what that is. I think in this case you're looking for:
tranaction = conn.begin()
transaction.commit()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: unit testing idioms
I am learning fixtures right now to see if I can use it to successfully solve this problem. pjjH On Jun 24, 6:11 pm, Chris Withers wrote: > Hi All, > > I'm wondering what the common idiom is for unit testing w.r.t. data and > transactions... > > So, coming from my ZODB background, in unit tests we usually: > > - set up the objects required > - run the code to be tested (which might change, add or delete objects) > - abort the current transaction so no changes are saved and everything > goes back to how it was prior to the start of the test > > What's the equivalent in sqlalchemy-land? > > How do I get test data needed for a specific test to exist for only that > test? > > How do I abort changes made by a test in a unittest.TestCase sublcasses > tearDown method? > > What do I do if the app framework I'm using goes and does a commit as > part of the unit test? (I wish it wouldn't... ;-) ) > > (this is in the context of a Pylons app if that makes things easier/harder) > > cheers, > > Chris > > -- > Simplistix - Content Management, Zope & Python Consulting > -http://www.simplistix.co.uk --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: introspecting models
The MetaData object has a sorted_tables method/property that returns a list of Table objects. Also, MetaData.tables[] can be accessed by table-name (fully qualified including schema, if any). Table.columns has name and type information. Assuming a MetaData instance md, here is how you would list the columns in the 'banana' table: for i in md['banana'].columns: print i.name, i.type Likewise, for a mapped class one can iterate over the mapped properties: from sqlalchemy.orm import class_mapper for i in class_mapper(Banana).iterate_properties: print i pjjH On Jun 26, 12:24 pm, Didip Kerabat wrote: > I believe you can look inside object's __dict__ for list of field names. > > There are plenty of information you can pull from orm.Mapper. Those are > explained better here: > > http://www.sqlalchemy.org/docs/05/reference/orm/mapping.html > > - Didip - > > On Thu, Jun 25, 2009 at 6:12 PM, Chris Withers wrote: > > > > > Hi All, > > > What's the correct way to find out what fields a model contains? > > (eg: one that's been reflected from an existing table) > > > At this stage, I only care about the field names, if that makes things > > easier... > > > Also, how would I do the same from a mapped object? > > > cheers, > > > Chris > > > -- > > Simplistix - Content Management, Zope & Python Consulting > > -http://www.simplistix.co.uk --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Commiting inserts made through Stored Procedure calls
Michael Bayer wrote:
> Rodney Haynie wrote:
>
>> I am having problems getting my data to save when the changes are made
>> through stored procedures.
>> I created a sample block of code below to show exactly how I am
>> implementing this.
>>
>>
>> conn = DBSession.connection()
>> statement = sql.text("""CALL insert_test(321, 1);""")
>> results = conn.execute(statement).fetchall()
>> # The result set will have the new primary key value.
>> if results == []:
>> return None
>>
>> newID = results[0][0]
>> transaction.commit()
>> return newID
>>
>>
>
> what is "transaction" here ? it seems to be ephemeral.
>
> I failed to say this is a TG2 app. There is the transaction module I import
> at the top of the controller file.
>
import transaction
I was trying different things, and I believe I don't need the code
"transaction.commit()". But I was grasping at straws.
Is there any way I can tell that the automatic transaction sees changes?
-R
> >
>
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: introspecting models
I believe you can look inside object's __dict__ for list of field names. There are plenty of information you can pull from orm.Mapper. Those are explained better here: http://www.sqlalchemy.org/docs/05/reference/orm/mapping.html - Didip - On Thu, Jun 25, 2009 at 6:12 PM, Chris Withers wrote: > > Hi All, > > What's the correct way to find out what fields a model contains? > (eg: one that's been reflected from an existing table) > > At this stage, I only care about the field names, if that makes things > easier... > > Also, how would I do the same from a mapped object? > > cheers, > > Chris > > -- > Simplistix - Content Management, Zope & Python Consulting >- http://www.simplistix.co.uk > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Dynamic making of the where clause
Ashish Bhatia wrote: > > :( Is it possible ! > > On Jun 23, 3:32 pm, Ash wrote: >> Hello, >> >> I am tryin to make the dynamic where clause using append_whereclause. >> >> But how i can do that, For eg : >> >> I have a==b and c in (1,2,3,4) or d like %s >> >> So i made three sqlalchemy expression >> >> 1. a==b >> 2. c in (1,2,3,4) [ using in_] >> 3. d like %s [using like] >> >> now i want this 3 to stuff in where clause . >> >> I created select like sel = select() >> >> How can i make the where clause which have and and or both uisng >> append_whereclause. construct using and_() and or_(), then use a single where() to set it. >> >> Thnks in advance > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: can't adapt error appearing with SA 0.5
ucs wrote: > > I've been using 0.3 for a while and everything was peachy. > Now I'm trying to update the framework, which has 0.5 as requirement. > > Besides the syntax change (which cost my many hours to rewrite all the > code), I now get a "can't adapt" error every time there's a datetime > value involved. > Do I really have to recode every occurence of > > column >= datetime.now() > to > column >= datetime.now().isoformat() > > ??? > Why can't SA handle a simple datetime object anymore? > It's not a database issue since the database returns the values quite > happily - just SA can't make any sense out of it anymore. > > Database is postgresql. python datetime objects are handled directly by psycopg2. (note: *not* psycopg1, which is not supported). no isoformat()/string conversion is required. If you can be more specific about how you are arriving at an undesired result (theres no error message here, no description of whats "wrong"), more help may be available. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Commiting inserts made through Stored Procedure calls
Rodney Haynie wrote:
>
> I am having problems getting my data to save when the changes are made
> through stored procedures.
> I created a sample block of code below to show exactly how I am
> implementing this.
>
>
> conn = DBSession.connection()
> statement = sql.text("""CALL insert_test(321, 1);""")
> results = conn.execute(statement).fetchall()
> # The result set will have the new primary key value.
> if results == []:
> return None
>
> newID = results[0][0]
> transaction.commit()
> return newID
>
what is "transaction" here ? it seems to be ephemeral.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: aggregation with count and webhelpers.paginate
Thanks, the label syntax is what I was looking for.
Thanks to all!
On Jun 22, 11:40 am, Bobby Impollonia wrote:
> You could also use a label to avoid the repetition:
> from sqlalchemy.sql import desc
> meta.Session.query(m.Hit.referer, func.count(m.Hit.id).label('count'))\
> .group_by(m.Hit.referer)\
> .order_by(desc('count'))
>
> On Mon, Jun 22, 2009 at 2:22 AM, King
>
> Simon-NFHD78 wrote:
>
> >> -Original Message-
> >> From: sqlalchemy@googlegroups.com
> >> [mailto:sqlalch...@googlegroups.com] On Behalf Of Hollister
> >> Sent: 20 June 2009 02:15
> >> To: sqlalchemy
> >> Subject: [sqlalchemy] Re: aggregation with count and
> >> webhelpers.paginate
>
> >> Well, that worked great:
>
> >> q = meta.Session.query(m.Hit.referer, func.count(m.Hit.id))\
> >> .group_by(m.Hit.referer)\
> >> .order_by(func.count(m.Hit.id).desc())
>
> >> Thanks!
>
> >> ps: Is there a better way to specify the count in the order_by?
>
> > If it's just that you don't like repeating yourself, you should be able
> > to save the result of func.count (untested):
>
> > hit_count = func.count(m.Hit.id)
> > q = (meta.Session.query(m.Hit.referer, hit_count)
> > .group_by(m.Hit.referer)
> > .order_by(hit_count.desc())
>
> > Simon
>
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Dynamic making of the where clause
Thanks But i get input as
[['a==b'],'and', [],'and','(',
['x==y'],'or',['t==y'],')']
for a==b and id in(1,2,3,4,5) and (x==y or t==y)
Now from this ;list input i want to make the where expression.
I tried making all to the string but the objects (binary expression)
create problem.
On Jun 26, 5:16 pm, "King Simon-NFHD78"
wrote:
> > On Jun 23, 3:32 pm, Ash wrote:
> > > Hello,
>
> > > I am tryin to make the dynamic where clause using
> > append_whereclause.
>
> > > But how i can do that, For eg :
>
> > > I have a==b and c in (1,2,3,4) or d like %s
>
> > > So i made three sqlalchemy expression
>
> > > 1. a==b
> > > 2. c in (1,2,3,4) [ using in_]
> > > 3. d like %s [using like]
>
> > > now i want this 3 to stuff in where clause .
>
> > > I created select like sel = select()
>
> > > How can i make the where clause which have and and or both uisng
> > > append_whereclause.
>
> I think append_whereclause always uses AND, so you need to connect these
> conditions into a single clause and call append_whereclause just once.
>
> It's not clear how you want the grouping of your conditions to work. Is it:
>
> (a==b AND c in (1, 2, 3, 4)) OR d like %s
>
> Or
>
> a==b AND (c in (1, 2, 3, 4) OR d like %s)
>
> I think you need to do something like this (for the first option):
>
> from sqlalchemy import and_, or_
>
> clause = or_(and_(a == b, c.in_([1, 2, 3, 4])),
> d.like(something))
> sel.append_whereclause(clause)
>
> You may also be able to use the '&' and '|' operators, as long as you are
> careful with brackets. See the docs
> athttp://www.sqlalchemy.org/docs/05/sqlexpression.html#conjunctions
>
> Hope that helps,
>
> Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Dynamic making of the where clause
> On Jun 23, 3:32 pm, Ash wrote: > > Hello, > > > > I am tryin to make the dynamic where clause using > append_whereclause. > > > > But how i can do that, For eg : > > > > I have a==b and c in (1,2,3,4) or d like %s > > > > So i made three sqlalchemy expression > > > > 1. a==b > > 2. c in (1,2,3,4) [ using in_] > > 3. d like %s [using like] > > > > now i want this 3 to stuff in where clause . > > > > I created select like sel = select() > > > > How can i make the where clause which have and and or both uisng > > append_whereclause. > > I think append_whereclause always uses AND, so you need to connect these conditions into a single clause and call append_whereclause just once. It's not clear how you want the grouping of your conditions to work. Is it: (a==b AND c in (1, 2, 3, 4)) OR d like %s Or a==b AND (c in (1, 2, 3, 4) OR d like %s) I think you need to do something like this (for the first option): from sqlalchemy import and_, or_ clause = or_(and_(a == b, c.in_([1, 2, 3, 4])), d.like(something)) sel.append_whereclause(clause) You may also be able to use the '&' and '|' operators, as long as you are careful with brackets. See the docs at http://www.sqlalchemy.org/docs/05/sqlexpression.html#conjunctions Hope that helps, Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Concurrent updating of a field in the same row
Hey,
I'm using sqlalchemy-0.4.8 with mysql-5.0 on innodb tables. I'm having
trouble updating 1 field in 1 row concurrently. The problem is that I
can't seem to reliably update the fields. Simple example:
Program 1:
..
session.close()
session.begin()
u = User.get(id=1).with_lockmode("update").first()
print(u.bytes)
u.bytes += 100
time.sleep(5)
session.commit()
Program 2:
..
session.close()
session.begin()
u = User.get(id=1).with_lockmode("update").first()
print(u.bytes)
u.bytes -= 10
session.commit()
print(u.trafficbytes)
I start by running program 1 until it enters the sleep. It's holding
the update lock on the user table. Now, I run program 2. It nicely
waits until program 1 commits the session. However, u.bytes still has
the value _before_ program 1 adds 100 to it. I expected it to get the
newly, updated value.
What am I doing wrong here? Is it because program 2 gets the database
state it was in at the moment it starts the database transaction with
session.begin() (while program 1 hasn't committed its update yet)? If
so, how do I rewrite the code to get it to do what I mean?
Kind regards,
Bram
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Dynamic making of the where clause
:( Is it possible ! On Jun 23, 3:32 pm, Ash wrote: > Hello, > > I am tryin to make the dynamic where clause using append_whereclause. > > But how i can do that, For eg : > > I have a==b and c in (1,2,3,4) or d like %s > > So i made three sqlalchemy expression > > 1. a==b > 2. c in (1,2,3,4) [ using in_] > 3. d like %s [using like] > > now i want this 3 to stuff in where clause . > > I created select like sel = select() > > How can i make the where clause which have and and or both uisng > append_whereclause. > > Thnks in advance --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
