[sqlalchemy] bad result when querying for null values (in pk at least)
hello friends, thank you very much for making and contributing to this great project. i have a table with a multi-field primary key, one of which allows null. when querying this table, i don't get the records with null in that field, even if i'm specifically asking them in where. but what makes the result wrong is, that one None is entering the result records list. and None is not a record. what do you think? -- alex smime.p7s Description: S/MIME Cryptographic Signature
[sqlalchemy] Best way to find out: outer session
Hello, when building a session extension with after_rollback or (after|before) _commit logic one might want to find out whether the session that triggered the extension by issuing a rollback or commit actually is an outer session resp. actually issues/issued a COMMIT or ROLLBACK to the database, contrary to just releasing a savepoint f.e. It appears to me that session.transaction is not None and session.transaction is session.transaction._iterate_parents()[-1] does the trick, but that would mean calling a private method of SessionTransaction, which I'm not sure is safe. What would be the best way to find that out? Best, Thomas --~--~-~--~~~---~--~~ 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: bad result when querying for null values (in pk at least)
add allow_null_pks to your mapper arguments See http://www.sqlalchemy.org/docs/05/reference/orm/mapping.html --~--~-~--~~~---~--~~ 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: ORM Many to Many Across Two Databases
My apologies. I must have misunderstanding the behavior of
the schema option and thus its intent. How do I configure a
relation across two logical databases using schema?
I have tried the following:
engine = create_engine(DATABASEURI, echo=True)
meta = MetaData()
meta.bind = engine
left_schema = LeftDatabaseName
right_schema = RightDatabaseName
left_table = Table('LeftTable', meta,
Column('id', Integer, primary_key=True),
Column('description', String(128)),
schema=left_schema)
right_table = Table('RightTable', meta,
Column('id', Integer, primary_key=True),
Column('description', String(128)),
schema=right_schema)
assoc_table = Table('LeftAssoc', meta,
Column('left_id', Integer),
Column('right_id', Integer),
#quote_schema=False,
schema=left_schema)
MySession = sessionmaker(bind=engine)
class MyBase(object):
def __init__(self, description):
self.description = description
def __str__(self):
return str(self.description)
class Left(MyBase): pass
class Right(MyBase): pass
mapper(Left, left_table)
mapper(Right, right_table, properties={
'lefts': relation(Left, secondary=assoc_table,
primaryjoin=(right_table.c.id==assoc_table.c.right_id),
secondaryjoin=(assoc_table.c.left_id==left_table.c.id),
foreign_keys=[assoc_table.c.left_id, assoc_table.c.right_id],
backref=rights),
})
if __name__ == '__main__':
meta.drop_all()
meta.create_all()
session = MySession()
left1 = Left('Left 1')
left2 = Left('Left 2')
right1 = Right('Right 1')
right2 = Right('Right 2')
left1.rights.extend([right1, right2])
right1.lefts.extend([left1, left2])
session.add_all([left1, left2, right1, right2])
session.commit()
left1 = session.query(Left).filter_by(description=Left 1).one()
print left1
for right in left1.rights:
print *4, right
for left in right.lefts:
print *8, left
The table name in the generated SQL is DBNAME.TABLENAME,
which doesn't work. It needs to be
DBNAME.SCHEMANAME.TABLENAME
or
DBNAME..TABLENAME (uses default schema)
I tried using quote_schema=False and adding a . to the end
of the schema value (schema=DBNAME.) but this results in
the broken SQL mentioned earlier:
SELECT [LeftTable_1].description AS [DBNAME._LeftTab_1]
Using schema=DBNAME.SCHEMANAME didn't work either.
Please let me know how to do this correctly.
I am using:
SQL Server 8.0
Hardy Heron
Python 2.5
SQLAlchemy 0.5.6
pymssql 1.0.2
Thanks, again!
- Luke
On Wed, Oct 14, 2009 at 6:46 PM, Michael Bayer mike...@zzzcomputing.comwrote:
Luke Arno wrote:
So, is there any chance that relations across multiple _logical_
databases on a _single_ physical server will be supported by
SQLAlchemy in the future?
that is supported now, via the schema argument to Table, assuming you
are connecting with an account that has access to all logical databases.
relation() supports spanning physical databases as well, if you either
ensure the secondary table is on the same server as the target, or
alternatively map the association table explicitly as I said in my initial
response.
Thanks.
- Luke
On Wed, Oct 14, 2009 at 3:01 PM, Luke Arno luke.a...@gmail.com wrote:
Thanks, Simon. Good suggestion, but these are databases and not
schema within a database. If I use the schema=MyDatabase. (
notice the .) and quote_schema=False, the table names come out
like I want, but look at what happens to the label here:
SELECT [LeftTable_1].description AS [MyDatabase._LeftTab_1]
FROM MyDatabase..[LeftTable] AS [LeftTable_1]
WHERE [LeftTable_1].id = %(param_1)s
That is really abusing the schema feature, so this is not a bug... I
just wish there were a databasename=Foo option for Table() so I
could use these ugly databases the way they are. :(
On Wed, Oct 14, 2009 at 11:51 AM, King Simon-NFHD78
simon.k...@motorola.com wrote:
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Luke Arno
Sent: 14 October 2009 16:41
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: ORM Many to Many Across Two Databases
It looks like if I put the relation on the obj mapped to the DB where
the association table is _not_, it works in one direction.
(So, in the
example Right.lefts can work but Left.rights cannot.) When trying to
use Left.rights, it looks for the table in the wrong database.
It appears that it would be fine if I could just get the
table names all
qualified with database name in the issued SQL. Is there a way to
make that happen, by any chance?
You can do this by using a single engine and metadata, and passing a
'schema' parameter when defining your tables:
http://www.sqlalchemy.org/docs/05/metadata.html#specifying-the-schema-na
me
Hope that helps,
Simon
[sqlalchemy] Re: bad result when querying for null values (in pk at least)
thanks a lot mike, it's working great now. but this flag should be implied if one of the primary key fields is nullable (especially since it's not nullable by default). what would you think? On Thu, Oct 15, 2009 at 14:28, Mike Conley mconl...@gmail.com wrote: add allow_null_pks to your mapper arguments See http://www.sqlalchemy.org/docs/05/reference/orm/mapping.html --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- -- alex smime.p7s Description: S/MIME Cryptographic Signature
[sqlalchemy] Declerative Relation trouble
Hi All, I am having a very bad day (or two to be honest), spending time reading error messages. I am sorry to say but the SQLALCHEMY documentation is not very helpful when using declarative_base when it is about relations.. It should be very easy for you database guy's (which I'm not, I'm more of a protocoll/low level programmer) The problem. I am implementing a calendaring system based on the ical specification. Consider the following ( I'll keep it as simple as possible): I am using MySQL 5.+ SQLAlchemy 0.5.6 Class | __tablename__ +-- Calendar| Calendars Event | CalendarEvents Todo| CalendarTodos Alarm | CalendarAlarms XProp | CalendarXProps (Tablenames do not reflect Objectnames, sorry for that, it is a requirement) so: class XProp(Base): __tablename__ = CalendarXProps Id = Column(Integer, primary_key=True) EventId = the Id of the event this XProp belongs to AlarmId = the Id of the Alarm this Xprop belongs to CalendarId = The Id of the Calendar .. Name= Column(... Value = Column(.. (either EventId or AlarmId or CalendarId is used) class Event(Base): __tablename__ =CalendarEvents Id = Column(Integer, primary_key=True) CalendarId = the ID of the Calendar this Object belongs to XProps = relation zero or more XProp (I would like to do: for XProp in self.XProps: .. ) X = Column( Y = Column(... def __init__(self): self.Xprops = [ ] self.X = X self.Y = 576234 class Alarm(Base): __tablename__ =CalendarAlarms Id = Column(Integer, primary_key=True) CalendarId = the ID of the Calendar this Object belongs to XProps = relation zero or more XProp X = Column( Y = Column(... def __init__(self): self.Xprops = [ ] self.X = X klsdjkladsjkd ddsa self.Y = 5 class Calendar(Base): __tablename__ = Calendars Id = Column(Integer, primairy_key=True) Events = relation One Calendar zero or more Events (i.e. [ ], [Event,Events,Event,] Alarms = relation One Calendar zero or more Alarms XProps = relation, One Calendar zero or more XProp X = Column( Y = Column(... def __init__(self): self.Events = [ ] self.Alarms = [ ] self.Xprops = [ ] self.X = X self.Y = 576234 cal = Calendar() the X, Y, Name and Value columns indicate example record data) The Errormessages are allways on the line with cal = Calendar(), saying the original errormessage has probaby been lost due to hasattr. Not very helpfull since over 40 objecttypes are trying to do the same thing. Whatever I do with ForeignKey, backref, relation keeps popping up errors whenever I try to make a Calendar Instance (e.g. cal = Calendar () ) One or mappers failed to compile. The big problem is that the tablenames are not equal to the Object names and that seems to confuse SQLAlchemy . Many errors (every time I run my program It generates a different error) Like table 'Calendar' not found (I never said there is a table Calendar, that is the objectname, I specify Calendars.Id) Can someone please help me with the setting up the relations. in the example above to give me a starting point. (there are about 40 XProp alike Objects and some Alarm/Even alike objects, I have reduced the above example to the minimum possible) So what I need is help with the relation and Foreign Key columns including the backrefs (I need to search CalendarEvents and find the corresponding Calendar) Thank you very mutch Martijn --~--~-~--~~~---~--~~ 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: bad result when querying for null values (in pk at least)
On Thu, Oct 15, 2009 at 9:21 AM, alexbodn.gro...@gmail.com wrote: thanks a lot mike, it's working great now. but this flag should be implied if one of the primary key fields is nullable (especially since it's not nullable by default). what would you think? You can argue just as easily that the null primary key means the record is incomplete and should not be available. It's an application code issue. --~--~-~--~~~---~--~~ 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: bad result when querying for null values (in pk at least)
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Mike Conley Sent: 15 October 2009 14:43 To: sqlalchemy@googlegroups.com Cc: SQLElixir Subject: [sqlalchemy] Re: bad result when querying for null values (in pk at least) On Thu, Oct 15, 2009 at 9:21 AM, alexbodn.gro...@gmail.com wrote: thanks a lot mike, it's working great now. but this flag should be implied if one of the primary key fields is nullable (especially since it's not nullable by default). what would you think? You can argue just as easily that the null primary key means the record is incomplete and should not be available. It's an application code issue. According to http://www.sqlalchemy.org/trac/wiki/06Migration the flag will be turned on by default in 0.6 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: Declerative Relation trouble
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling
Sent: 15 October 2009 14:42
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Declerative Relation trouble
Hi All,
I am having a very bad day (or two to be honest), spending
time reading error messages.
I am sorry to say but the SQLALCHEMY documentation is not very helpful
when using declarative_base when it is about relations..
Without a runnable example which actually shows your problem, it's very
difficult to debug. Here's something I cobbled together based on your
description. It may not be exactly right, but it seems to work:
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class XProp(Base):
__tablename__ = CalendarXProps
Id = sa.Column(sa.Integer, primary_key=True)
EventId = sa.Column(sa.ForeignKey('CalendarEvents.Id'))
AlarmId = sa.Column(sa.ForeignKey('CalendarAlarms.Id'))
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
Name = sa.Column(sa.String(20))
Value = sa.Column(sa.String(20))
class Event(Base):
__tablename__ = CalendarEvents
Id = sa.Column(sa.Integer, primary_key=True)
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
XProps = orm.relation(XProp, backref='Events')
class Alarm(Base):
__tablename__ = CalendarAlarms
Id = sa.Column(sa.Integer, primary_key=True)
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
XProps = orm.relation(XProp, backref='Alarms')
class Calendar(Base):
__tablename__ = Calendars
Id = sa.Column(sa.Integer, primary_key=True)
Events = orm.relation(Event, backref='Calendar')
Alarms = orm.relation(Alarm, backref='Calendar')
XProps = orm.relation(XProp, backref='Calendar')
if __name__ == '__main__':
engine = sa.create_engine('sqlite:///')
Base.metadata.create_all(bind=engine)
Session = orm.sessionmaker(bind=engine)()
cal = Calendar()
cal.Events.append(Event(XProps=[XProp(Name='Hello', Value='World'),
XProp(Name='foo', Value='bar')]))
Session.add(cal)
Session.flush()
print cal
for event in cal.Events:
print event
for prop in event.XProps:
print prop
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: Declerative Relation trouble
Hi Simon,
(I do things a little different on the import side)
Working example (very minimised):
from sqlalchemy import
Integer,Column,DateTime,Unicode,UnicodeText,Boolean,ForeignKey,Interval
from sqlalchemy.ext.declarative import declarative_base
import sqlalchemy.orm as orm
Base = declarative_base()
class Event(Base):
__tablename__ = CalendarEvents
Id = Column(Integer, primary_key=True)
CalendarId = Column(ForeignKey('Calendars.Id'))
class Calendar(Base):
__tablename__ = Calendars
Id = Column(Integer, primary_key=True)
UserId = Column(Integer, index=True)
ProdId = Column(Unicode(255))
Version = Column(Unicode(5))
CalScale= Column(Unicode(20))
Method = Column(Unicode(10))
Events = orm.relation(Event,backref='Calendar')#,
cascade=all)
if __name__ == '__main__':
engine = create_engine('sqlite:///')
Base.metadata.create_all(bind=engine)
Session = orm.sessionmaker(bind=engine)()
cal = Calendar()
when I run this I get:
Traceback (most recent call last):
File /var/www/PyWebOs/caltst.py, line 28, in module
cal = Calendar()
File string, line 4, in __init__
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/
state.py, line 71, in initialize_instance
fn(self, instance, args, kwargs)
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/
mapper.py, line 1810, in _event_on_init
instrumenting_mapper.compile()
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/
mapper.py, line 666, in compile
Message was: %s % mapper._compile_failed)
InvalidRequestError: One or more mappers failed to compile. Exception
was probably suppressed within a hasattr() call. Message was: One or
more mappers failed to compile. Exception was probably suppressed
within a hasattr() call. Message was: One or more mappers failed to
compile. Exception was probably suppressed within a hasattr() call.
Message was: Could not find table 'Calendar' with which to generate a
foreign key
which is excacly the same as I got.
I have done so mutch in python/sqlalchemy that I feel extremely stupid
not to get this working, it might be just a case of overreading the
problem
Martijn
On Oct 15, 2009, at 4:10 PM, King Simon-NFHD78 wrote:
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling
Sent: 15 October 2009 14:42
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Declerative Relation trouble
Hi All,
I am having a very bad day (or two to be honest), spending
time reading error messages.
I am sorry to say but the SQLALCHEMY documentation is not very
helpful
when using declarative_base when it is about relations..
Without a runnable example which actually shows your problem, it's
very
difficult to debug. Here's something I cobbled together based on your
description. It may not be exactly right, but it seems to work:
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class XProp(Base):
__tablename__ = CalendarXProps
Id = sa.Column(sa.Integer, primary_key=True)
EventId = sa.Column(sa.ForeignKey('CalendarEvents.Id'))
AlarmId = sa.Column(sa.ForeignKey('CalendarAlarms.Id'))
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
Name = sa.Column(sa.String(20))
Value = sa.Column(sa.String(20))
class Event(Base):
__tablename__ = CalendarEvents
Id = sa.Column(sa.Integer, primary_key=True)
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
XProps = orm.relation(XProp, backref='Events')
class Alarm(Base):
__tablename__ = CalendarAlarms
Id = sa.Column(sa.Integer, primary_key=True)
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
XProps = orm.relation(XProp, backref='Alarms')
class Calendar(Base):
__tablename__ = Calendars
Id = sa.Column(sa.Integer, primary_key=True)
Events = orm.relation(Event, backref='Calendar')
Alarms = orm.relation(Alarm, backref='Calendar')
XProps = orm.relation(XProp, backref='Calendar')
if __name__ == '__main__':
engine = sa.create_engine('sqlite:///')
Base.metadata.create_all(bind=engine)
Session = orm.sessionmaker(bind=engine)()
cal = Calendar()
cal.Events.append(Event(XProps=[XProp(Name='Hello', Value='World'),
XProp(Name='foo', Value='bar')]))
Session.add(cal)
Session.flush()
print cal
for event in cal.Events:
print event
for prop in event.XProps:
print prop
Hope that helps,
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this
[sqlalchemy] Re: Declerative Relation trouble
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling
Sent: 15 October 2009 15:38
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: Declerative Relation trouble
Hi Simon,
(I do things a little different on the import side)
Working example (very minimised):
from sqlalchemy import
Integer,Column,DateTime,Unicode,UnicodeText,Boolean,ForeignKey
,Interval
from sqlalchemy.ext.declarative import declarative_base
import sqlalchemy.orm as orm
Base = declarative_base()
class Event(Base):
__tablename__ = CalendarEvents
Id = Column(Integer, primary_key=True)
CalendarId = Column(ForeignKey('Calendars.Id'))
class Calendar(Base):
__tablename__ = Calendars
Id = Column(Integer, primary_key=True)
UserId = Column(Integer, index=True)
ProdId = Column(Unicode(255))
Version = Column(Unicode(5))
CalScale= Column(Unicode(20))
Method = Column(Unicode(10))
Events =
orm.relation(Event,backref='Calendar')#, cascade=all)
if __name__ == '__main__':
engine = create_engine('sqlite:///')
Base.metadata.create_all(bind=engine)
Session = orm.sessionmaker(bind=engine)()
cal = Calendar()
when I run this I get:
Traceback (most recent call last):
File /var/www/PyWebOs/caltst.py, line 28, in module
cal = Calendar()
File string, line 4, in __init__
File
/usr/local/lib/python2.6/site-packages/sqlalchemy/orm/state.p
y, line 71, in initialize_instance
fn(self, instance, args, kwargs)
File
/usr/local/lib/python2.6/site-packages/sqlalchemy/orm/mapper.
py, line 1810, in _event_on_init
instrumenting_mapper.compile()
File
/usr/local/lib/python2.6/site-packages/sqlalchemy/orm/mapper.
py, line 666, in compile
Message was: %s % mapper._compile_failed)
InvalidRequestError: One or more mappers failed to compile.
Exception was probably suppressed within a hasattr() call.
Message was: One or more mappers failed to compile.
Exception was probably suppressed within a hasattr() call.
Message was: One or more mappers failed to compile.
Exception was probably suppressed within a hasattr() call.
Message was: Could not find table 'Calendar' with which to
generate a foreign key
which is excacly the same as I got.
I have done so mutch in python/sqlalchemy that I feel
extremely stupid not to get this working, it might be just a
case of overreading the problem
Martijn
That script works for me (I needed to add 'create_engine' to the import
line) on SQLAlchemy 0.5.5. How are you running it?
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: bad result when querying for null values (in pk at least)
On Thu, Oct 15, 2009 at 15:43, Mike Conley mconl...@gmail.com wrote: On Thu, Oct 15, 2009 at 9:21 AM, alexbodn.gro...@gmail.com wrote: thanks a lot mike, it's working great now. but this flag should be implied if one of the primary key fields is nullable (especially since it's not nullable by default). what would you think? You can argue just as easily that the null primary key means the record is incomplete and should not be available. It's an application code issue. but i'm talking about nullable=True --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- -- alex smime.p7s Description: S/MIME Cryptographic Signature
[sqlalchemy] Re: Declerative Relation trouble
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling Sent: 15 October 2009 15:55 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: Declerative Relation trouble I downgraded SQLA to version 0.5.5, no luck, (Simon) what version of python are you using? 2.5.1 on Linux 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: Declerative Relation trouble
I downgraded SQLA to version 0.5.5, no luck, (Simon) what version of
python are you using?
On Oct 15, 2009, at 4:45 PM, Martijn Moeling wrote:
Hi Simon/all,
When I run your example i get:
Traceback (most recent call last):
File /var/www/PyWebOs/caltst.py, line 41, in module
cal = Calendar()
File string, line 4, in __init__
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/
state.py, line 71, in initialize_instance
fn(self, instance, args, kwargs)
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/
mapper.py, line 1810, in _event_on_init
instrumenting_mapper.compile()
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/
mapper.py, line 666, in compile
Message was: %s % mapper._compile_failed)
InvalidRequestError: One or more mappers failed to compile. Exception
was probably suppressed within a hasattr() call. Message was: One or
more mappers failed to compile. Exception was probably suppressed
within a hasattr() call. Message was: One or more mappers failed to
compile. Exception was probably suppressed within a hasattr() call.
Message was: Could not find table 'Calendar' with which to generate a
foreign key
So I am starting to get the impression there is something wrong beyond
my code.
Your and my example are ruling out MySQL (or the Mysql part of
SQLAlchemy)
Now there is python 2.6.2 and SQLA version 0.5.6 on a Linux box
Anyone having trouble with these versions?
Martijn
On Oct 15, 2009, at 4:10 PM, King Simon-NFHD78 wrote:
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class XProp(Base):
__tablename__ = CalendarXProps
Id = sa.Column(sa.Integer, primary_key=True)
EventId = sa.Column(sa.ForeignKey('CalendarEvents.Id'))
AlarmId = sa.Column(sa.ForeignKey('CalendarAlarms.Id'))
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
Name = sa.Column(sa.String(20))
Value = sa.Column(sa.String(20))
class Event(Base):
__tablename__ = CalendarEvents
Id = sa.Column(sa.Integer, primary_key=True)
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
XProps = orm.relation(XProp, backref='Events')
class Alarm(Base):
__tablename__ = CalendarAlarms
Id = sa.Column(sa.Integer, primary_key=True)
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
XProps = orm.relation(XProp, backref='Alarms')
class Calendar(Base):
__tablename__ = Calendars
Id = sa.Column(sa.Integer, primary_key=True)
Events = orm.relation(Event, backref='Calendar')
Alarms = orm.relation(Alarm, backref='Calendar')
XProps = orm.relation(XProp, backref='Calendar')
if __name__ == '__main__':
engine = sa.create_engine('sqlite:///')
Base.metadata.create_all(bind=engine)
Session = orm.sessionmaker(bind=engine)()
cal = Calendar()
cal.Events.append(Event(XProps=[XProp(Name='Hello', Value='World'),
XProp(Name='foo', Value='bar')]))
Session.add(cal)
Session.flush()
print cal
for event in cal.Events:
print event
for prop in event.XProps:
print prop
--~--~-~--~~~---~--~~
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: bad result when querying for null values (in pk at least)
On Thu, Oct 15, 2009 at 15:48, King Simon-NFHD78 simon.k...@motorola.com wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Mike Conley Sent: 15 October 2009 14:43 To: sqlalchemy@googlegroups.com Cc: SQLElixir Subject: [sqlalchemy] Re: bad result when querying for null values (in pk at least) On Thu, Oct 15, 2009 at 9:21 AM, alexbodn.gro...@gmail.com wrote: thanks a lot mike, it's working great now. but this flag should be implied if one of the primary key fields is nullable (especially since it's not nullable by default). what would you think? You can argue just as easily that the null primary key means the record is incomplete and should not be available. It's an application code issue. According to http://www.sqlalchemy.org/trac/wiki/06Migration the flag will be turned on by default in 0.6 it's indeed redundant, thanks. 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 -~--~~~~--~~--~--~--- -- alex smime.p7s Description: S/MIME Cryptographic Signature
[sqlalchemy] Re:[runs one one installation not on the other] Declerative Relation trouble
Hi All, bad problem I have trouble making relations in SQLA. My code runs on Simons computer but his (and mine) not. I downgraded SLQA from 0.5.6. to 0.5.5 to have the same version, still no luck. I run Python 2.6.2 and Simon 2.5.1 Downgrading Python is a hassle at the moment (Mod_python) so is there anything I can try? (Database changes give me the same results on both mysql and sqllite so that seems no problem) Martijn On Oct 15, 2009, at 4:59 PM, King Simon-NFHD78 wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling Sent: 15 October 2009 15:55 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: Declerative Relation trouble I downgraded SQLA to version 0.5.5, no luck, (Simon) what version of python are you using? 2.5.1 on Linux 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: [runs one one installation not on the other] Declerative Relation trouble
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling Sent: 15 October 2009 16:21 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re:[runs one one installation not on the other] Declerative Relation trouble Hi All, bad problem I have trouble making relations in SQLA. My code runs on Simons computer but his (and mine) not. I downgraded SLQA from 0.5.6. to 0.5.5 to have the same version, still no luck. I run Python 2.6.2 and Simon 2.5.1 Downgrading Python is a hassle at the moment (Mod_python) so is there anything I can try? (Database changes give me the same results on both mysql and sqllite so that seems no problem) Martijn Are you running the test script from mod_python, or from the command line? --~--~-~--~~~---~--~~ 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: [runs one one installation not on the other] Declerative Relation trouble
Mod_python has nothing to do with this project, so I run it from idle within X On Oct 15, 2009, at 5:23 PM, King Simon-NFHD78 wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling Sent: 15 October 2009 16:21 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re:[runs one one installation not on the other] Declerative Relation trouble Hi All, bad problem I have trouble making relations in SQLA. My code runs on Simons computer but his (and mine) not. I downgraded SLQA from 0.5.6. to 0.5.5 to have the same version, still no luck. I run Python 2.6.2 and Simon 2.5.1 Downgrading Python is a hassle at the moment (Mod_python) so is there anything I can try? (Database changes give me the same results on both mysql and sqllite so that seems no problem) Martijn Are you running the test script from mod_python, or from the command line? --~--~-~--~~~---~--~~ 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: [runs one one installation not on the other] Declerative Relation trouble
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling Sent: 15 October 2009 16:27 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: [runs one one installation not on the other] Declerative Relation trouble Mod_python has nothing to do with this project, so I run it from idle within X I have a feeling that Idle doesn't necessarily spawn a separate process to run your code, so you may have old definitions of your objects in memory. Try running it directly from the command line. --~--~-~--~~~---~--~~ 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] Eager loading with SELECT instead of JOIN
As far as I understand eager loading it works by adding a join clause to the select. That has the drawback of increased number or rows returned. Imagine having 100 objects and each with 200 related objects, most of which overlap so the total number of related objects is about 300. That will generate 20 000 rows, where in reality it would be much better to fetch the 100 objects with a single query and then fetch the 300 related objects with another query. It that possible with the current version of sqlalchemy. Hibernate has something like this in the form of Batch fetching: http://docs.jboss.org/hibernate/stable/core/reference/en/html_single/#performance-fetching-batch Regards, Emil Vladev --~--~-~--~~~---~--~~ 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: [runs one one installation not on the other] Declerative Relation trouble
(in the mean time I drove home. dinner soon) Indeed, running it from command line changes things, Strange since I even rebooted the machine in the process, but since I was messing with the code It could well have been really broken. Your sample works from the command line, mine still gives the error but needs changing. I will investigate some more, I have been using Idle with SQLA for more than a year now. but now I'm thinking about it, I use mod_python to actually run the code. Any suggestions for a alternative? I do not like eclipse very mutch (same with Aptana) I'll keep you posted! Martijn On Oct 15, 2009, at 5:33 PM, King Simon-NFHD78 wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling Sent: 15 October 2009 16:27 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: [runs one one installation not on the other] Declerative Relation trouble Mod_python has nothing to do with this project, so I run it from idle within X I have a feeling that Idle doesn't necessarily spawn a separate process to run your code, so you may have old definitions of your objects in memory. Try running it directly from the command line. --~--~-~--~~~---~--~~ 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: Eager loading with SELECT instead of JOIN
Emil Ivanov wrote: As far as I understand eager loading it works by adding a join clause to the select. That has the drawback of increased number or rows returned. Imagine having 100 objects and each with 200 related objects, most of which overlap so the total number of related objects is about 300. That will generate 20 000 rows, where in reality it would be much better to fetch the 100 objects with a single query and then fetch the 300 related objects with another query. It that possible with the current version of sqlalchemy. Hibernate has something like this in the form of Batch fetching: http://docs.jboss.org/hibernate/stable/core/reference/en/html_single/#performance-fetching-batch its not built in but you can run the second query manually, using the first query as a subquery to formulate the WHERE criterion for the second. I do this often, in fact. there is a helper method sqlalchemy.orm.attributes.set_committed_value() which you can use to populate the related scalar or collections of each parent object, without any history events firing off, so that the related object(s) will have the state of objects that were loaded from the database. the general idea is: q= sess.query(Parent).filter(...) parents = q.all() children = sess.query(Child).filter(Child.id.in_(sess.query(Parent.id).filter(...)) parent_dict = dict((p.id, []) for p in parents) for child in children: parent_dict[child.parent_id].append(child) for p in parents: set_committed_value(p, children, parent_dict[p.id]) there are several reasons this is not built in. one is that the subquery step is very difficult to formulate for relations that deal with complex join conditions and composite primary keys - by leaving this to the user SQLA avoids wading into what is probably kind of a bottomless hole. The other is that the ORM doesn't include an immediate second query in its architecture, nor does it include a deferred load among many objects from a previous query in response to a single attribute access - which is essentially the lazy version of this and is often what people prefer. there are two features though which would make the above operation simpler - a query.select_entities(*cols_or_entities) method, which is essentially what query.values() does without the execution (I always preferred it that way, in fact), and perhaps a knit_collections_together type of method that would do the second step. --~--~-~--~~~---~--~~ 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: Eager loading with SELECT instead of JOIN
Michael Bayer wrote: Emil Ivanov wrote: As far as I understand eager loading it works by adding a join clause to the select. That has the drawback of increased number or rows returned. Imagine having 100 objects and each with 200 related objects, most of which overlap so the total number of related objects is about 300. That will generate 20 000 rows, where in reality it would be much better to fetch the 100 objects with a single query and then fetch the 300 related objects with another query. Also something to clarify here. If you have 100 parents, each with 200 children, and there is overlap, that implies there is a many-to-many relation between parent/child, and that there are essentially 20K rows in the association table. But you still need to query all 20K of those rows in order to determine the correct associations to the parents. So no rows are saved. Simlarly, if the relation is one-to-many or many-to-one, there would be no overlap and you still ultimately have to fetch everything. The approach only saves on columns being fetched, not rows. --~--~-~--~~~---~--~~ 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] invalid transaction rollback etc and making a join join
Dear All People:
I'm using SQLSoup and getting errors like InvalidRequestError: Can't
reconnect until invalid transaction is rolled back and MySQL Server
has gone away. I have set autoflush=True and this has helped mitigate
some errors, but not these, they happen with varying frequency when I
haven't restarted the servers for a while. I am almost never using
db.flush(). Will that fix this? How can I make this things work? I
really want them to work and I hate MySQL but am stuck using it for
now.
SQLAlchemy thus far has added several hours to development time, which
I find sad. I have this block of code:
#this has consumed too much time
#will come back and fix but for now we have to give up on it
#ret = Session.query(sets).select_from(orm.outerjoin((slices,
slices.asset_slice_asset_id == id), (stores, stores.asset_store_id ==
slices.asset_slice_store_scheme_id))).filter(id == sets.asset_id).all
()
#how I hate sqlalchemy right now
#the raw query took under five minutes
#the other thing took more than hour and still doesn't work
ret = db.bind.execute( SELECT * FROM assets a
LEFT OUTER JOIN asset_slices `as` on as.asset_slice_asset_id =
a.asset_id
LEFT OUTER JOIN asset_storage_schemes `astor` ON
astor.asset_store_id = as.asset_slice_store_scheme_id
WHERE a.asset_id = {bid}
ORDER BY asset_slice_row ASC.format(bid = id))
So I need that actual query to be executable via SQLAlchemy,
preferably with SQLSoup. It doesn't work right now.
All help is loved and appreciated.
Signed
Jeff
--~--~-~--~~~---~--~~
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: Eager loading with SELECT instead of JOIN
Emil Ivanov wrote: As far as I understand eager loading it works by adding a join clause and...the rationale for the Hibernate feature is to optimize *lazy* loading, not eager loading - its a query that you only want if needed. Also hibernate does a poor job with join-based eager loading - they make no adjustment for things like LIMIT/OFFSET, for example (which is disastrous IMHO). The performance gains in the eager version are only that of fetching columns. In Hibernate's case, some JDBC implementations don't pull columns over the wire unless requested on the result. Sadly this is not the case for most Python DBAPIs. --~--~-~--~~~---~--~~ 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: invalid transaction rollback etc and making a join join
Jeff Cook wrote:
Dear All People:
I'm using SQLSoup and getting errors like InvalidRequestError: Can't
reconnect until invalid transaction is rolled back and MySQL Server
has gone away. I have set autoflush=True and this has helped mitigate
some errors, but not these, they happen with varying frequency when I
haven't restarted the servers for a while. I am almost never using
db.flush(). Will that fix this? How can I make this things work? I
really want them to work and I hate MySQL but am stuck using it for
now.
SQLAlchemy thus far has added several hours to development time, which
I find sad. I have this block of code:
#this has consumed too much time
#will come back and fix but for now we have to give up on it
#ret = Session.query(sets).select_from(orm.outerjoin((slices,
slices.asset_slice_asset_id == id), (stores, stores.asset_store_id ==
slices.asset_slice_store_scheme_id))).filter(id == sets.asset_id).all
()
#how I hate sqlalchemy right now
#the raw query took under five minutes
#the other thing took more than hour and still doesn't work
ret = db.bind.execute( SELECT * FROM assets a
LEFT OUTER JOIN asset_slices `as` on as.asset_slice_asset_id =
a.asset_id
LEFT OUTER JOIN asset_storage_schemes `astor` ON
astor.asset_store_id = as.asset_slice_store_scheme_id
WHERE a.asset_id = {bid}
ORDER BY asset_slice_row ASC.format(bid = id))
So I need that actual query to be executable via SQLAlchemy,
preferably with SQLSoup. It doesn't work right now.
These errors suggest operating within a transaction where an error has
already occurred, and was swallowed. It's a general Python practice to
allow exceptions to propagate and be reported as a general failure at some
level where they are explicitly handled and at the very least logged. If
you follow this procedure, and look for these errors occurring, you likely
won't have mysterious exceptions complaining about the invalid state of a
previous operation. There is also no way for anyone to help if you do
not attach the full stack traces to your emails.
I would also challenge your assertion that SQLAlchemy is adding hours to
your development time - unless your application is extremely trivial, it
is saving you dozens versus using raw DBAPI.
--~--~-~--~~~---~--~~
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: invalid transaction rollback etc and making a join join
It's not faster. I use pyscopg2 directly on some other projects and
it's definitely a lot faster to just be able to write the query I want
than to try to think of how to convert it to SQLAlchemy's contexts and
functions. Maybe it's just learning curve thing, but as shown, I can't
get that join to actually work, and I was trying different things for
hours; it gives me silly errors of every which-a-kind. If I can just
write something as simple as that in under five minutes, why does it
take so long for SQLAlchemy to let me do it? There's definitely a
problem somewhere in there.
File
'/home/jeff/vspy/lib/python2.6/site-packages/WebError-0.10.1-py2.6.egg/weberror/evalexception.py',
line 431 in respond
app_iter = self.application(environ, detect_start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Beaker-1.4.1-py2.6.egg/beaker/middleware.py',
line 73 in __call__
return self.app(environ, start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Beaker-1.4.1-py2.6.egg/beaker/middleware.py',
line 152 in __call__
return self.wrap_app(environ, session_start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Routes-1.10.3-py2.6.egg/routes/middleware.py',
line 130 in __call__
response = self.app(environ, start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/wsgiapp.py',
line 125 in __call__
response = self.dispatch(controller, environ, start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/wsgiapp.py',
line 324 in dispatch
return controller(environ, start_response)
File '/home/jeff/projecs/projecs/lib/base.py', line 18 in __call__
return WSGIController.__call__(self, environ, start_response)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/controllers/core.py',
line 221 in __call__
response = self._dispatch_call()
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/controllers/core.py',
line 172 in _dispatch_call
response = self._inspect_call(func)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/controllers/core.py',
line 107 in _inspect_call
result = self._perform_call(func, args)
File
'/home/jeff/vspy/lib/python2.6/site-packages/Pylons-0.9.7-py2.6.egg/pylons/controllers/core.py',
line 60 in _perform_call
return func(**args)
File '/home/jeff/projecs/projecs/controllers/viewer.py', line 18 in index
c.assets = assets.list_assets()
File '/home/jeff/projecs/projecs/model/assets.py', line 7 in list_assets
return db.join(db.assets, db.asset_storage_schemes,
db.assets.asset_storage_scheme_id==db.asset_storage_schemes.asset_store_id).order_by(db.assets.asset_id.desc()).all()
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py',
line 1267 in all
return list(self)
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py',
line 1361 in __iter__
return self._execute_and_instances(context)
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py',
line 1364 in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self._mapper_zero_or_none())
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/session.py',
line 755 in execute
clause, params or {})
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py',
line 824 in execute
return Connection.executors[c](self, object, multiparams, params)
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py',
line 872 in _execute_clauseelement
parameters=params
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py',
line 938 in __create_execution_context
return dialect.execution_ctx_cls(dialect, connection=self, **kwargs)
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/default.py',
line 170 in __init__
self.cursor = self.create_cursor()
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/default.py',
line 258 in create_cursor
return self._connection.connection.cursor()
File
'/home/jeff/vspy/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py',
line 576 in connection
raise exc.InvalidRequestError(Can't reconnect until invalid
transaction is rolled back)
InvalidRequestError: Can't reconnect until invalid transaction is rolled back
I don't fully understand what you're talking about. I have this error
and I need to make it stop. I just want SQLAlchemy to connect, run the
query I instructed, and give me the results back and do this reliably
without necessitating consistent server restarts. Thus far, it's been
a serious pain managing connection errors
[sqlalchemy] Re: invalid transaction rollback etc and making a join join
Jeff Cook wrote: I don't fully understand what you're talking about. I have this error and I need to make it stop. I just want SQLAlchemy to connect, run the query I instructed, and give me the results back and do this reliably without necessitating consistent server restarts. Thus far, it's been a serious pain managing connection errors and so on. SQLSoup may complicate this because they have no mention anywhere in their docs explaining the necessity to close your connections, and all methods I tried (explicit session.close()s at the end of each query, explicit db.close()s, and now autoflush=True) to make sure that the resources are being returned correctly to the pooler have failed and caused other blow-up problem attacks. none of the statements regarding SQLA in that paragraph are accurate. close() is not needed, autoflush=True is the default setting (did you mean autocommit? that's a feature better left off), SQLAlchemy always returns resources to their original pooled state when a transaction is not in progress. What is necessary, however, is that you must call rollback() when an exception is raised. I see you're using Pylons, the default Pylons template establishes this pattern within the BaseController. unfortunately there is no feature within SQLAlchemy that can fix your issue. Your application needs to get a handle on transaction failures. A transaction is only invalid if an error were already raised in a previous operation within the same transaction, and you haven't attached any stack trace for that. --~--~-~--~~~---~--~~ 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: invalid transaction rollback etc and making a join join
I see. So Pylons should handle this by default, but it's not doing so? That's highly disappointing. Clearly, something is quite incorrect here. Is my usage of SQLSoup causing rollback not to run? On Thu, Oct 15, 2009 at 1:16 PM, Michael Bayer mike...@zzzcomputing.com wrote: Jeff Cook wrote: I don't fully understand what you're talking about. I have this error and I need to make it stop. I just want SQLAlchemy to connect, run the query I instructed, and give me the results back and do this reliably without necessitating consistent server restarts. Thus far, it's been a serious pain managing connection errors and so on. SQLSoup may complicate this because they have no mention anywhere in their docs explaining the necessity to close your connections, and all methods I tried (explicit session.close()s at the end of each query, explicit db.close()s, and now autoflush=True) to make sure that the resources are being returned correctly to the pooler have failed and caused other blow-up problem attacks. none of the statements regarding SQLA in that paragraph are accurate. close() is not needed, autoflush=True is the default setting (did you mean autocommit? that's a feature better left off), SQLAlchemy always returns resources to their original pooled state when a transaction is not in progress. What is necessary, however, is that you must call rollback() when an exception is raised. I see you're using Pylons, the default Pylons template establishes this pattern within the BaseController. unfortunately there is no feature within SQLAlchemy that can fix your issue. Your application needs to get a handle on transaction failures. A transaction is only invalid if an error were already raised in a previous operation within the same transaction, and you haven't attached any stack trace for that. --~--~-~--~~~---~--~~ 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: invalid transaction rollback etc and making a join join
And when things _do_ work, there are serious caching problems. Sometimes it gives me the transaction rollback error, sometimes it gives me an old version of the page, and sometimes it gives me a current version of the page. I assume this has something to do with what connection is getting used. How can I remove these problems? Would pool_recycle be of any use? On Thu, Oct 15, 2009 at 1:27 PM, Jeff Cook cookieca...@gmail.com wrote: I see. So Pylons should handle this by default, but it's not doing so? That's highly disappointing. Clearly, something is quite incorrect here. Is my usage of SQLSoup causing rollback not to run? On Thu, Oct 15, 2009 at 1:16 PM, Michael Bayer mike...@zzzcomputing.com wrote: Jeff Cook wrote: I don't fully understand what you're talking about. I have this error and I need to make it stop. I just want SQLAlchemy to connect, run the query I instructed, and give me the results back and do this reliably without necessitating consistent server restarts. Thus far, it's been a serious pain managing connection errors and so on. SQLSoup may complicate this because they have no mention anywhere in their docs explaining the necessity to close your connections, and all methods I tried (explicit session.close()s at the end of each query, explicit db.close()s, and now autoflush=True) to make sure that the resources are being returned correctly to the pooler have failed and caused other blow-up problem attacks. none of the statements regarding SQLA in that paragraph are accurate. close() is not needed, autoflush=True is the default setting (did you mean autocommit? that's a feature better left off), SQLAlchemy always returns resources to their original pooled state when a transaction is not in progress. What is necessary, however, is that you must call rollback() when an exception is raised. I see you're using Pylons, the default Pylons template establishes this pattern within the BaseController. unfortunately there is no feature within SQLAlchemy that can fix your issue. Your application needs to get a handle on transaction failures. A transaction is only invalid if an error were already raised in a previous operation within the same transaction, and you haven't attached any stack trace for that. --~--~-~--~~~---~--~~ 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: Defining custom types
thatsanicehatyouh...@mac.com wrote: Hello, On 12 Oct 2009, at 21:04, Michael Bayer wrote: On Oct 12, 2009, at 4:26 PM, thatsanicehatyouh...@mac.com wrote: Hello, I have a custom type defined in my postgresql database, and this is giving me the warning: /Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site- packages/sqlalchemy/engine/base.py:1265: SAWarning: Did not recognize type 'fibertype' of column 'fiber_type' self.dialect.reflecttable(conn, table, include_columns) From an earlier email from Michael (13 September 2009), I saw that I can do something like this: sa_major_version = sqlalchemy.__version__[0:3] if sa_major_version == 0.5: from sqlalchemy.databases import postgres postgres.ischema_names['fiber_type'] = fibertype elif sa_major_version == 0.6: from sqlalchemy.dialects.postgresql import base as pg pg.ischema_names['fiber_type'] = fibertype But of course fibertype needs to be defined. How can I define this? The definition in the database is simply: CREATE TYPE fibertype AS ENUM ( 'A', 'B', 'C' ); what happens if you put fibertype: FiberType in the dictionary as well ? all its doing is looking in there for something to use. I'm not sure what you mean here. I take this to mean: postgres.ischema_names['fibertype'] = FiberType but in either case, the value of the dictionary here is undefined. That's the thing I'm unsure of how it's to be defined. Do you mean that it should simply be any value, e.g. postgres.ischema_names['fibertype'] = FiberType ah no it should be either a default type like String or a custom type like a TypeEngine or TypeDecorator subclass of your design. --~--~-~--~~~---~--~~ 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.sql.expression.func and composite types
Thanks for the tip about TypeDecorator I got it working to parse up the
string, still not sure why SA is returning a string and not a tuple of
integers,
but at this point I have a good work-around and I'm happy. Especially
since TypeDecorator allows me to return those numbers
as a dictionary.
David Gardner wrote:
Did a quick test using psycopg2 and it returns a tuple of six longs:
(9892718L, 1046L, 189L, 235L, 9890143L, 1105L)
---
import psycopg2
import psycopg2.extensions
DB_HOST = 'localhost'
DB_NAME = 'hdpsdb'
DB_USER = 'testuser'
DB_PASS = 'testuser'
db_uri = dbname='%s' user='%s' host='%s' password='%s' %
(DB_NAME,DB_USER,DB_HOST,DB_PASS)
pg2con = psycopg2.connect(db_uri)
cursor=pg2con.cursor()
cursor.execute(SELECT * FROM farm.call_job_status('testshow');)
row = cursor.fetchone()
print row
cursor.close()
pg2con.close()
Michael Bayer wrote:
David Gardner wrote:
I have a composite type that I defined as:
CREATE TYPE farm.job_status_ret AS
(total bigint,
valid bigint,
invalid bigint,
processing bigint,
pending bigint,
canceled bigint);
I dropped the text field. When I run the query in postgres I get the six
distinct fields:
hdpsdb=# SELECT * FROM farm.call_job_status('testshow');
total | valid | invalid | processing | pending | canceled
-+---+-++-+--
9892718 | 116 | 20 | 0 | 9886233 | 6349
but from SQLAlchemy I just get a string:
session.query(func.farm.call_job_status('testshow')).first()
('(9892718,116,20,0,9886233,6349)',)
Looks like the TypeDecorator will do what I need.
it would be interesting to nail down exactly what psycopg2's contract is
here. strange that it does that.
Michael Bayer wrote:
David Gardner wrote:
I have a PostgreSQL function that returns a composite type (a text
field
and 6 bigint columns).
Currently I am calling it with:
session.query(Job,func.farm.call_job_status(Job.path)).filter(Job.path=='testshow').first()
Which returns a tuple, but the second element is a string. I could
probably parse the string, but that wouldn't be very elegant.
I was wondering is there an object that I can subclass to support this?
I tried passing in type_=(String,Integer,...) as well as
type_=composite(SomeObj) neither worked.
unsure what this means. the text field + 6 int columns are returned as
one big string ? if so, that would be a postgresql/psycopg2 behavior,
so
you'd have to parse the string (most cleanly using TypeDecorator).
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Defining custom types
Hi Michael, Thanks for clarifying that for me. For anyone interested, this is what I ended up with: from sqlalchemy import String sa_major_version = sqlalchemy.__version__[0:3] if sa_major_version == 0.5: from sqlalchemy.databases import postgres postgres.ischema_names['fibertype'] = String elif sa_major_version == 0.6: from sqlalchemy.dialects.postgresql import base as pg pg.ischema_names['fibertype'] = String Cheers, Demitri On 15 Oct 2009, at 16:06, Michael Bayer wrote: thatsanicehatyouh...@mac.com wrote: Hello, On 12 Oct 2009, at 21:04, Michael Bayer wrote: On Oct 12, 2009, at 4:26 PM, thatsanicehatyouh...@mac.com wrote: Hello, I have a custom type defined in my postgresql database, and this is giving me the warning: /Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/ site- packages/sqlalchemy/engine/base.py:1265: SAWarning: Did not recognize type 'fibertype' of column 'fiber_type' self.dialect.reflecttable(conn, table, include_columns) From an earlier email from Michael (13 September 2009), I saw that I can do something like this: sa_major_version = sqlalchemy.__version__[0:3] if sa_major_version == 0.5: from sqlalchemy.databases import postgres postgres.ischema_names['fiber_type'] = fibertype elif sa_major_version == 0.6: from sqlalchemy.dialects.postgresql import base as pg pg.ischema_names['fiber_type'] = fibertype But of course fibertype needs to be defined. How can I define this? The definition in the database is simply: CREATE TYPE fibertype AS ENUM ( 'A', 'B', 'C' ); what happens if you put fibertype: FiberType in the dictionary as well ? all its doing is looking in there for something to use. I'm not sure what you mean here. I take this to mean: postgres.ischema_names['fibertype'] = FiberType but in either case, the value of the dictionary here is undefined. That's the thing I'm unsure of how it's to be defined. Do you mean that it should simply be any value, e.g. postgres.ischema_names['fibertype'] = FiberType ah no it should be either a default type like String or a custom type like a TypeEngine or TypeDecorator subclass of your design. --~--~-~--~~~---~--~~ 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.sql.expression.func and composite types
David Gardner wrote:
Thanks for the tip about TypeDecorator I got it working to parse up
the string, still not sure why SA is returning a string and not a
tuple of integers,
but at this point I have a good work-around and I'm happy. Especially
since TypeDecorator allows me to return those numbers
as a dictionary.
I don't think SA is at fault: I believe that your SA query is generating
different SQL than your manual SQL.
Your SA query likely generates this SQL:
SELECT farm.call_job_status(job.path) WHERE job.path = 'testshow' LIMIT 1;
whereas your manual SQL is:
SELECT * FROM farm.call_job_status('testshow');
The key point is that your SA query has the function call in the columns
clause (causing PostgreSQL to convert the tuple to a scalar), but your
manual SQL has the function call in the from clause. Please try the top
SELECT statement in psycopg2 and let us know if it returns a tuple
instead of a string. For kicks, try it in psql too.
I don't think this really helps you avoid parsing the result yourself,
but at least you know why! :)
-Conor
David Gardner wrote:
Did a quick test using psycopg2 and it returns a tuple of six longs:
(9892718L, 1046L, 189L, 235L, 9890143L, 1105L)
---
import psycopg2
import psycopg2.extensions
DB_HOST = 'localhost'
DB_NAME = 'hdpsdb'
DB_USER = 'testuser'
DB_PASS = 'testuser'
db_uri = dbname='%s' user='%s' host='%s' password='%s' %
(DB_NAME,DB_USER,DB_HOST,DB_PASS)
pg2con = psycopg2.connect(db_uri)
cursor=pg2con.cursor()
cursor.execute(SELECT * FROM farm.call_job_status('testshow');)
row = cursor.fetchone()
print row
cursor.close()
pg2con.close()
Michael Bayer wrote:
David Gardner wrote:
I have a composite type that I defined as:
CREATE TYPE farm.job_status_ret AS
(total bigint,
valid bigint,
invalid bigint,
processing bigint,
pending bigint,
canceled bigint);
I dropped the text field. When I run the query in postgres I get the six
distinct fields:
hdpsdb=# SELECT * FROM farm.call_job_status('testshow');
total | valid | invalid | processing | pending | canceled
-+---+-++-+--
9892718 | 116 | 20 | 0 | 9886233 | 6349
but from SQLAlchemy I just get a string:
session.query(func.farm.call_job_status('testshow')).first()
('(9892718,116,20,0,9886233,6349)',)
Looks like the TypeDecorator will do what I need.
it would be interesting to nail down exactly what psycopg2's contract is
here. strange that it does that.
Michael Bayer wrote:
David Gardner wrote:
I have a PostgreSQL function that returns a composite type (a text
field
and 6 bigint columns).
Currently I am calling it with:
session.query(Job,func.farm.call_job_status(Job.path)).filter(Job.path=='testshow').first()
Which returns a tuple, but the second element is a string. I could
probably parse the string, but that wouldn't be very elegant.
I was wondering is there an object that I can subclass to support this?
I tried passing in type_=(String,Integer,...) as well as
type_=composite(SomeObj) neither worked.
unsure what this means. the text field + 6 int columns are returned as
one big string ? if so, that would be a postgresql/psycopg2 behavior,
so
you'd have to parse the string (most cleanly using TypeDecorator).
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Do not use the idle -n command when using SQLA
Ok here is the deal Since I sometimes need to run multiple instances of idle in the same shell,so I use the -n switch by default: 8 [removed~u...@removed~host ~]# idle --help Error: option --help not recognized USAGE: idle [-deins] [-t title] [file]* idle [-dns] [-t title] (-c cmd | -r file) [arg]* idle [-dns] [-t title] - [arg]* -h print this help message and exit -n run IDLE without a subprocess (see Help/IDLE Help for details) 8 So To complete Simons suggestion about idle, Do not use the -n switch on idle when playing with SQLAlchemy. On Oct 15, 2009, at 6:55 PM, Martijn Moeling wrote: (in the mean time I drove home. dinner soon) Indeed, running it from command line changes things, Strange since I even rebooted the machine in the process, but since I was messing with the code It could well have been really broken. Your sample works from the command line, mine still gives the error but needs changing. I will investigate some more, I have been using Idle with SQLA for more than a year now. but now I'm thinking about it, I use mod_python to actually run the code. Any suggestions for a alternative? I do not like eclipse very mutch (same with Aptana) I'll keep you posted! Martijn On Oct 15, 2009, at 5:33 PM, King Simon-NFHD78 wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling Sent: 15 October 2009 16:27 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: [runs one one installation not on the other] Declerative Relation trouble Mod_python has nothing to do with this project, so I run it from idle within X I have a feeling that Idle doesn't necessarily spawn a separate process to run your code, so you may have old definitions of your objects in memory. Try running it directly from the command line. --~--~-~--~~~---~--~~ 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.sql.expression.func and composite types
Aha! thanks for the tip.
You are right, because that is the way psql returns it.
I re-wrote my PostgreSQL function to return an array of bigint,
which then psycopg2 and SQLAlchemy see as an array of integers, which
works out really great for me.
I don't think SA is at fault: I believe that your SA query is generating
different SQL than your manual SQL.
Your SA query likely generates this SQL:
SELECT farm.call_job_status(job.path) WHERE job.path = 'testshow' LIMIT 1;
whereas your manual SQL is:
SELECT * FROM farm.call_job_status('testshow');
The key point is that your SA query has the function call in the columns
clause (causing PostgreSQL to convert the tuple to a scalar), but your
manual SQL has the function call in the from clause. Please try the top
SELECT statement in psycopg2 and let us know if it returns a tuple
instead of a string. For kicks, try it in psql too.
I don't think this really helps you avoid parsing the result yourself,
but at least you know why! :)
-Conor
David Gardner wrote:
Did a quick test using psycopg2 and it returns a tuple of six longs:
(9892718L, 1046L, 189L, 235L, 9890143L, 1105L)
---
import psycopg2
import psycopg2.extensions
DB_HOST = 'localhost'
DB_NAME = 'hdpsdb'
DB_USER = 'testuser'
DB_PASS = 'testuser'
db_uri = dbname='%s' user='%s' host='%s' password='%s' %
(DB_NAME,DB_USER,DB_HOST,DB_PASS)
pg2con = psycopg2.connect(db_uri)
cursor=pg2con.cursor()
cursor.execute(SELECT * FROM farm.call_job_status('testshow');)
row = cursor.fetchone()
print row
cursor.close()
pg2con.close()
Michael Bayer wrote:
David Gardner wrote:
I have a composite type that I defined as:
CREATE TYPE farm.job_status_ret AS
(total bigint,
valid bigint,
invalid bigint,
processing bigint,
pending bigint,
canceled bigint);
I dropped the text field. When I run the query in postgres I get the six
distinct fields:
hdpsdb=# SELECT * FROM farm.call_job_status('testshow');
total | valid | invalid | processing | pending | canceled
-+---+-++-+--
9892718 | 116 | 20 | 0 | 9886233 | 6349
but from SQLAlchemy I just get a string:
session.query(func.farm.call_job_status('testshow')).first()
('(9892718,116,20,0,9886233,6349)',)
Looks like the TypeDecorator will do what I need.
it would be interesting to nail down exactly what psycopg2's contract is
here. strange that it does that.
Michael Bayer wrote:
David Gardner wrote:
I have a PostgreSQL function that returns a composite type (a text
field
and 6 bigint columns).
Currently I am calling it with:
session.query(Job,func.farm.call_job_status(Job.path)).filter(Job.path=='testshow').first()
Which returns a tuple, but the second element is a string. I could
probably parse the string, but that wouldn't be very elegant.
I was wondering is there an object that I can subclass to support this?
I tried passing in type_=(String,Integer,...) as well as
type_=composite(SomeObj) neither worked.
unsure what this means. the text field + 6 int columns are returned as
one big string ? if so, that would be a postgresql/psycopg2 behavior,
so
you'd have to parse the string (most cleanly using TypeDecorator).
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: invalid transaction rollback etc and making a join join
On Oct 15, 2009, at 3:32 PM, Jeff Cook wrote: And when things _do_ work, there are serious caching problems. Sometimes it gives me the transaction rollback error, sometimes it gives me an old version of the page, and sometimes it gives me a current version of the page. I assume this has something to do with what connection is getting used. How can I remove these problems? Would pool_recycle be of any use? there's no flag that is going to make your program work. your app is buggy, plain and simple. You need to do a code review, ensure no exceptions are being squashed, do some ab testing, and watch your logs. its likely theres just one activity in your app, maybe two, screwing everything up. the longer you just try to guess the cause and try random things the more confused things will seem.SQLSoup doesn't have anything to do with connection pools or transactions, its basically a thin layer over creating mappers and Query objects. It uses the same Session as everyone else, using the threaded ScopedSession to keep things local. And a default setup of Pylons does catch errors and roll the session back. There's also little to no caching used by the session and certainly not across requests unless you've worked very hard to store everything in global variables across requests. On Thu, Oct 15, 2009 at 1:27 PM, Jeff Cook cookieca...@gmail.com wrote: I see. So Pylons should handle this by default, but it's not doing so? That's highly disappointing. Clearly, something is quite incorrect here. Is my usage of SQLSoup causing rollback not to run? On Thu, Oct 15, 2009 at 1:16 PM, Michael Bayer mike...@zzzcomputing.com wrote: Jeff Cook wrote: I don't fully understand what you're talking about. I have this error and I need to make it stop. I just want SQLAlchemy to connect, run the query I instructed, and give me the results back and do this reliably without necessitating consistent server restarts. Thus far, it's been a serious pain managing connection errors and so on. SQLSoup may complicate this because they have no mention anywhere in their docs explaining the necessity to close your connections, and all methods I tried (explicit session.close()s at the end of each query, explicit db.close()s, and now autoflush=True) to make sure that the resources are being returned correctly to the pooler have failed and caused other blow-up problem attacks. none of the statements regarding SQLA in that paragraph are accurate. close() is not needed, autoflush=True is the default setting (did you mean autocommit? that's a feature better left off), SQLAlchemy always returns resources to their original pooled state when a transaction is not in progress. What is necessary, however, is that you must call rollback() when an exception is raised. I see you're using Pylons, the default Pylons template establishes this pattern within the BaseController. unfortunately there is no feature within SQLAlchemy that can fix your issue. Your application needs to get a handle on transaction failures. A transaction is only invalid if an error were already raised in a previous operation within the same transaction, and you haven't attached any stack trace for that. --~--~-~--~~~---~--~~ 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: invalid transaction rollback etc and making a join join
OK, man, well, I have one function that has the calls to perform the listing. I refresh and sometimes get old data and sometimes don't. There is no clustering and no other databases, there is no possibility that the server is retrieving old data. I haven't changed the base Pylons classes at all, so, according to you, I shouldn't be getting that error at all. But I am. How would you advise me to continue? Scorch earth and start over? That's pretty much the same; there's not much SQLAlchemy code in use right now, it's only a few queries ... yet we're still having these issues. So, SQLAlchemy is doing something here. There probably is some incorrect code in my program, which is why I am writing this list, to figure out what that is. I'm an experienced developer and I don't appreciate your disrespect. I can follow tutorials and documentation and I did so here, and it's always ended up with lots of errors. Your docs need work. I'm sorry if this idea offends you. : ( I still like you and I still like SQLAlchemy. Let's cultivate an environment of mutual professional respect here. : ) Love Jeff On Thu, Oct 15, 2009 at 6:13 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Oct 15, 2009, at 3:32 PM, Jeff Cook wrote: And when things _do_ work, there are serious caching problems. Sometimes it gives me the transaction rollback error, sometimes it gives me an old version of the page, and sometimes it gives me a current version of the page. I assume this has something to do with what connection is getting used. How can I remove these problems? Would pool_recycle be of any use? there's no flag that is going to make your program work. your app is buggy, plain and simple. You need to do a code review, ensure no exceptions are being squashed, do some ab testing, and watch your logs. its likely theres just one activity in your app, maybe two, screwing everything up. the longer you just try to guess the cause and try random things the more confused things will seem. SQLSoup doesn't have anything to do with connection pools or transactions, its basically a thin layer over creating mappers and Query objects. It uses the same Session as everyone else, using the threaded ScopedSession to keep things local. And a default setup of Pylons does catch errors and roll the session back. There's also little to no caching used by the session and certainly not across requests unless you've worked very hard to store everything in global variables across requests. On Thu, Oct 15, 2009 at 1:27 PM, Jeff Cook cookieca...@gmail.com wrote: I see. So Pylons should handle this by default, but it's not doing so? That's highly disappointing. Clearly, something is quite incorrect here. Is my usage of SQLSoup causing rollback not to run? On Thu, Oct 15, 2009 at 1:16 PM, Michael Bayer mike...@zzzcomputing.com wrote: Jeff Cook wrote: I don't fully understand what you're talking about. I have this error and I need to make it stop. I just want SQLAlchemy to connect, run the query I instructed, and give me the results back and do this reliably without necessitating consistent server restarts. Thus far, it's been a serious pain managing connection errors and so on. SQLSoup may complicate this because they have no mention anywhere in their docs explaining the necessity to close your connections, and all methods I tried (explicit session.close()s at the end of each query, explicit db.close()s, and now autoflush=True) to make sure that the resources are being returned correctly to the pooler have failed and caused other blow-up problem attacks. none of the statements regarding SQLA in that paragraph are accurate. close() is not needed, autoflush=True is the default setting (did you mean autocommit? that's a feature better left off), SQLAlchemy always returns resources to their original pooled state when a transaction is not in progress. What is necessary, however, is that you must call rollback() when an exception is raised. I see you're using Pylons, the default Pylons template establishes this pattern within the BaseController. unfortunately there is no feature within SQLAlchemy that can fix your issue. Your application needs to get a handle on transaction failures. A transaction is only invalid if an error were already raised in a previous operation within the same transaction, and you haven't attached any stack trace for that. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
