Re: [sqlalchemy] Understanding session.begin(subtransactions=True) as a context manager

[Doug Farrell]

Hi Mike,

Thanks very much for the response and the clear explanation, and the link. 
All of that was very helpful to me, and I'm made some changes to my code 
accordingly. I am using Flask-SQLAlchemy by the way, so what you wrote 
makes even more sense in that context.

Doug

On Thursday, September 28, 2017 at 10:54:13 AM UTC-4, Mike Bayer wrote:
>
> On Thu, Sep 28, 2017 at 9:29 AM, Doug Farrell <doug.f...@gmail.com 
> > wrote: 
> > Hi all, 
> > 
> > I'm having trouble understanding how to use 
> > session.begin(subtransactions=True) as a context manager. I'm working in 
> > Python 2.7.5 with SQLAlchemy 1.1.14 in a Flask 0.12.2 application on 
> CentOS 
> > 7 servers. I like session.begin() as a context manager, but things 
> aren't 
> > working as I thought they would. For example: 
> > 
> > with db.session.begin(subtransactions=True): 
> > # create a model instance 
> > thing = Thing(...) 
> > db.session.add(thing) 
>
>
> I would not recommend using the Session in this pattern.Leaving 
> the session with its default setting of "autocommit=False" means you 
> never have to call begin() at all; this is desirable because there's 
> no reason to use a Session outside of an explicit transaction (this 
> was not the case many years ago which is why begin() remains). 
> Methods that work with objects should not also be demarcating 
> transaction scope; there's general discussion of this at 
>
> http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it.
>  
>
>
> if you're doing Flask, the commit of the Session should be at the end 
> of the request, the begin is implicit, and that's it. If you 
> absolutely need a commit to occur at some special place before you've 
> reached the official end of the request + commit, you can just simply 
> call session.commit() at any time.The Session will start a new 
> transaction if and when additional work is performed with it. 
>
> Now, if you need your work to be in an **independent** transaction, 
> that is, totally separate from the "main" one, then you need to do 
> your work in a totally different Session object.   This is an 
> acceptable pattern that is sometimes necessary. 
>
>
> > 
> > I thought when the context manager went out of scope it would perform a 
> > db.session.commit() to persist the thing instance, but I haven't seen 
> > changes to the database. 
>
> the commit() will happen if the begin() is the outermost begin().  If 
> not, then it's one of the "inner" blocks and no commit will occur. 
> If you haven't placed this session into autocommit=True, which is 
> required for making explicit use of the begin() method, then there is 
> already a transaction in progress and the block will never cause a 
> commit() to occur. 
>
> > But if I change the code to this: 
> > 
> > with db.session.begin(subtransactions=True): 
> > # create a model instance 
> > thing = Thing(...) 
> > db.session.add(thing) 
> > db.session.commit() 
> > 
> > it raises a ResourceClosedError: This transaction is closed 
>
> This is because that code interferes with the state of the context 
> manager.The context manager is referring to the current 
> SessionTransaction upon which it will call commit() at the end of the 
> block.  Because you are calling commit() on it, that effectively ends 
> that SessionTransaction being in a usable state, and the context 
> manager then fails to do its final step. 
>
>
> > 
> > What works for me is this: 
> > 
> > with db.session.begin(subtransactions=True): 
> > # create a model instance 
> > thing = Thing(...) 
> > db.session.add(thing) 
> > db.session.commit() 
> > 
> > Where the commit() is outside the scope of the context manager. But this 
> > seems contrary to me, and makes me think I'm doing something wrong as my 
> > expectation of what 
>
> this sounds a lot like you have the session in its default mode of 
> autocommit=False, which is great, but you shouldn't be using the 
> begin() method. 
>
> If we go to the doc for begin: 
>
>
> http://docs.sqlalchemy.org/en/latest/orm/session_api.html?highlight=session%20begin#sqlalchemy.orm.session.Session.begin
>  
>
> the second line is: "The Session.begin() method is only meaningful if 
> this session is in autocommit mode prior to it being called; see 
> Autocommit Mode for background on this setting." 
>
> then if we go to autocommit mode at 
>
> http://docs.sqlalchemy.or

[sqlalchemy] Understanding session.begin(subtransactions=True) as a context manager

[Doug Farrell]

Hi all,

I'm having trouble understanding how to use 
session.begin(subtransactions=True) as a context manager. I'm working in 
Python 2.7.5 with SQLAlchemy 1.1.14 in a Flask 0.12.2 application on CentOS 
7 servers. I like session.begin() as a context manager, but things aren't 
working as I thought they would. For example:

with db.session.begin(subtransactions=True):
# create a model instance
thing = Thing(...)
db.session.add(thing)

I thought when the context manager went out of scope it would perform a 
db.session.commit() to persist the thing instance, but I haven't seen 
changes to the database. But if I change the code to this:

with db.session.begin(subtransactions=True):
# create a model instance
thing = Thing(...)
db.session.add(thing)
db.session.commit()

it raises a ResourceClosedError: This transaction is closed

What works for me is this:

with db.session.begin(subtransactions=True):
# create a model instance
thing = Thing(...)
db.session.add(thing)
db.session.commit()

Where the commit() is outside the scope of the context manager. But this 
seems contrary to me, and makes me think I'm doing something wrong as my 
expectation of what 

with db.session.begin(...) 

does is this (pseudocode):

begin transaction
try:
  create some model instance
  add them to the session
  commit handled by leaving the scope of the context manager
except
  rollback on exception

It would be very much appreciated is someone could point me in the right 
direction, give me some suggestions or references about what I'm missing.

Thanks,
Doug







-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] unsubscribe

[Doug Farrell]

unsubscribe

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

[Doug Farrell]

Michael,

Congratulations on the release! I look forward to giving it a spin!

Doug

--~--~-~--~~~---~--~~
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: New instance ExtraStat with identity key (...) conflicts with persistent instance ExtraStat

[Doug Farrell]

I've met so few other Farrells, maybe we ARE related!! Get it, related,
in a SqlAlchemy group. Oh man I'm such a geek!!

 -Original Message-
 From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
 On Behalf Of Bob
 Sent: Tuesday, December 02, 2008 9:00 AM
 To: sqlalchemy@googlegroups.com
 Subject: [sqlalchemy] Re: New instance ExtraStat with identity key
 (...) conflicts with persistent instance ExtraStat
 
 
 I keep thinking one of my relatives has emailed me when you post here
 and I get all excited. Any chance you can change your name ? :-)
 
 2008/12/2 Doug Farrell [EMAIL PROTECTED]:
  Michael,
 
 
 
  Thanks for the pointer, that makes great sense, and once again
points
 how my
  generally small database design skills. I'll update my code to try
 this out.
 
 
 
  Again,
 
  Thanks!
 
  Doug
 
 
 
  From: sqlalchemy@googlegroups.com
 [mailto:[EMAIL PROTECTED] On
  Behalf Of Michael Bayer
  Sent: Saturday, November 29, 2008 9:28 PM
  To: sqlalchemy@googlegroups.com
  Subject: [sqlalchemy] Re: New instance ExtraStat with identity key
 (...)
  conflicts with persistent instance ExtraStat
 
 
 
  Simon's suggestion about the duplicate name still holds.  Your
 relation
  from Stat-ExtraStat currently needs to be one-to-one since you
 cannot have
  more than one ExtraStat referencing a single Stat, due to the PK
 constraint
  on ExtraStat.name.  The error is raising at the point of query()
 since
  autoflush is kicking in - use session.flush() to isolate the error.
 
 
 
  On Nov 29, 2008, at 12:18 PM, Doug Farrell wrote:
 
  Hi all,
 
 
 
  I'm having a problem with a new instance of a relation conflicting
 with
 
  an existing instance. I'm using SA 0.5rc with Sqlite3. Here are my
 
  simplified classes:
 
 
 
  class Stat(sqladb.Base):
 
   __tablename__ = stats
 
   name = Column(String(32), primary_key=True)
 
   total= Column(Integer)
 
   created  = Column(DateTime,
default=datetime.datetime.now())
 
   updated  = Column(DateTime)
 
   states   = Column(PickleType, default={})
 
   extraStats   = relation(ExtraStat, backref=stat)
 
 
 
  class ExtraStat(sqladb.Base):
 
   __tablename__ = extrastats
 
   name = Column(String(32), ForeignKey(stats.name),
  primary_key=True)
 
   total= Column(Integer)
 
   created  = Column(DateTime,
default=datetime.datetime.now())
 
   updated  = Column(DateTime)
 
   states   = Column(PickleType, default={})
 
 
 
  The above Stat class has a one-to-many relationship with the
 ExtraStat
 
  class (which I think I've implemented correctly). Later in the
 
  program I create an in memory data model that has as part of it's
 
  components two
 
  dictionaries that contain Stat instances. Those Stat instances have
 
  relationships to ExtraStat instances. My problem comes in the
 
  following when I'm trying to update the data in those
 instances/tables.
 
  Here is a section of code that throws the exception:
 
 
 
 
 
 
 
  pressName = press%s % pressNum
 
  # add new ExtraStat instances as relations
 
  self._addProductStatsPress(productType, pressName)
 
  self._addPressStatsProduct(pressName, productType)
 
  try:
 
 extraStat = session.query(Stat). \
 
 filter(Stat.name==productType). \
 
 join(extraStats). \
 
 filter(ExtraStat.name==pressName).one()
 
  except:
 
 extraStat = ExtraStat(pressName, ExtraStat.PRESS_TYPE)
 
 self.productStats[productType].extraStats.append(extraStat)
 
 extraStat.states.setdefault(sstate, 0)
 
 extraStat.states[sstate] += 1
 
 extraStat.updated = now
 
 extraStat = session.merge(extraStat)
 
  try:
 
 extraStat = session.query(Stat). \
 
 filter(Stat.name==pressName). \
 
 join(extraStats). \
 
 filter(ExtraStat.name==productType).one()   
 throws
  exception right here
 
  except:
 
 extraStat = ExtraStat(productType, ExtraStat.PRODUCT_TYPE)
 
 self.pressStats[pressName].extraStats.append(extraStat)
 
 extraStat.states.setdefault(sstate, 0)
 
 extraStat.states[sstate] += 1
 
 extraStat.updated = now
 
 
 
  The marked area is wear it throws the exception. I'm not sure what
to
 
  do here to get past this, any help or ideas would be greatly
 
  appreciated.
 
 
 
  The exact exception is as follows:
 
  Sqlalchemy.orm.exc.FlushError: New instance [EMAIL PROTECTED] With
 identity
 
  key (class '__main__.ExtraStat',(u'C',)) conflicts with persistent
 
  instance [EMAIL PROTECTED]
 
 
 
  Thanks!
 
  Doug
 
 
 
 
 
  
 
 
  
 r more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en
 -~--~~~~--~~--~--~---


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy

[sqlalchemy] Re: New instance ExtraStat with identity key (...) conflicts with persistent instance ExtraStat

[Doug Farrell]

Michael,

 

Thanks for the pointer, that makes great sense, and once again points
how my generally small database design skills. I'll update my code to
try this out.

 

Again,

Thanks!

Doug

 

From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
On Behalf Of Michael Bayer
Sent: Saturday, November 29, 2008 9:28 PM
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: New instance ExtraStat with identity key (...)
conflicts with persistent instance ExtraStat

 

Simon's suggestion about the duplicate name still holds.  Your
relation from Stat-ExtraStat currently needs to be one-to-one since you
cannot have more than one ExtraStat referencing a single Stat, due to
the PK constraint on ExtraStat.name.  The error is raising at the point
of query() since autoflush is kicking in - use session.flush() to
isolate the error.

 

On Nov 29, 2008, at 12:18 PM, Doug Farrell wrote:





Hi all,

 

I'm having a problem with a new instance of a relation conflicting with

an existing instance. I'm using SA 0.5rc with Sqlite3. Here are my

simplified classes:

 

class Stat(sqladb.Base):

 __tablename__ = stats

 name = Column(String(32), primary_key=True)

 total= Column(Integer)

 created  = Column(DateTime, default=datetime.datetime.now())

 updated  = Column(DateTime)

 states   = Column(PickleType, default={})

 extraStats   = relation(ExtraStat, backref=stat)

 

class ExtraStat(sqladb.Base):

 __tablename__ = extrastats

 name = Column(String(32), ForeignKey(stats.name),
primary_key=True)

 total= Column(Integer)

 created  = Column(DateTime, default=datetime.datetime.now())

 updated  = Column(DateTime)

 states   = Column(PickleType, default={})

 

The above Stat class has a one-to-many relationship with the ExtraStat

class (which I think I've implemented correctly). Later in the

program I create an in memory data model that has as part of it's

components two

dictionaries that contain Stat instances. Those Stat instances have

relationships to ExtraStat instances. My problem comes in the

following when I'm trying to update the data in those instances/tables.

Here is a section of code that throws the exception:

 

 

 

pressName = press%s % pressNum

# add new ExtraStat instances as relations

self._addProductStatsPress(productType, pressName)

self._addPressStatsProduct(pressName, productType)

try:

   extraStat = session.query(Stat). \

   filter(Stat.name==productType). \

   join(extraStats). \

   filter(ExtraStat.name==pressName).one()

except:

   extraStat = ExtraStat(pressName, ExtraStat.PRESS_TYPE)

   self.productStats[productType].extraStats.append(extraStat)

   extraStat.states.setdefault(sstate, 0)

   extraStat.states[sstate] += 1

   extraStat.updated = now

   extraStat = session.merge(extraStat)

try:

   extraStat = session.query(Stat). \

   filter(Stat.name==pressName). \

   join(extraStats). \

   filter(ExtraStat.name==productType).one()    throws
exception right here

except:

   extraStat = ExtraStat(productType, ExtraStat.PRODUCT_TYPE)

   self.pressStats[pressName].extraStats.append(extraStat)

   extraStat.states.setdefault(sstate, 0)

   extraStat.states[sstate] += 1

   extraStat.updated = now

 

The marked area is wear it throws the exception. I'm not sure what to

do here to get past this, any help or ideas would be greatly

appreciated.

 

The exact exception is as follows:

Sqlalchemy.orm.exc.FlushError: New instance [EMAIL PROTECTED] With identity

key (class '__main__.ExtraStat',(u'C',)) conflicts with persistent

instance [EMAIL PROTECTED]

 

Thanks!

Doug





 



 


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: New instance ExtraStat with identity key (...) conflicts with persistent instance ExtraStat

[Doug Farrell]

Sorry, I didn't realize I had done that, I thought I had started a new
post. My apologies.

 -Original Message-
 From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
 On Behalf Of [EMAIL PROTECTED]
 Sent: Friday, November 28, 2008 11:45 PM
 To: sqlalchemy
 Subject: [sqlalchemy] Re: New instance ExtraStat with identity key
 (...) conflicts with persistent instance ExtraStat
 
 
 What was your justification of changing the name of my thread to a
 completely different topic instead of starting a new thread?
 
 I don't think thats good etiquette.
 
 
 On Nov 29, 5:22 am, Doug Farrell [EMAIL PROTECTED] wrote:
  Hi all,
 
  I'm having a problem with a new instance of a relation conflicting
 with
  an existing instance. I'm using SA 0.5rc with Sqlite3. Here are my
  simplified classes:
 
  class Stat(sqladb.Base):
  __tablename__ = stats
  name = Column(String(32), primary_key=True)
  total= Column(Integer)
  created  = Column(DateTime, default=datetime.datetime.now())
  updated  = Column(DateTime)
  states   = Column(PickleType, default={})
  extraStats   = relation(ExtraStat, backref=stat)
 
  class ExtraStat(sqladb.Base):
  __tablename__ = extrastats
  name = Column(String(32), ForeignKey(stats.name),
  primary_key=True)
  total= Column(Integer)
  created  = Column(DateTime, default=datetime.datetime.now())
  updated  = Column(DateTime)
  states   = Column(PickleType, default={})
 
  The above Stat class has a one-to-many relationship with the
 ExtraStat
  class (which I think I've implemented correctly). Later in the
 program I
  create an in memory data model that has as part of it's components
 two
  dictionaries that contain Stat instances. Those Stat instances have
  relationships to ExtraStat instances. My problem comes in the
 following
  when I'm trying to update the data in those instances/tables. Here
is
 a
  section of code that throws the exception:
 
  pressName = press%s % pressNum
  # add new ExtraStat instances as relations
  self._addProductStatsPress(productType, pressName)
  self._addPressStatsProduct(pressName, productType)
  try:
extraStat = session.query(Stat). \
filter(Stat.name==productType). \
join(extraStats). \
filter(ExtraStat.name==pressName).one()
  except:
extraStat = ExtraStat(pressName, ExtraStat.PRESS_TYPE)
self.productStats[productType].extraStats.append(extraStat)
extraStat.states.setdefault(sstate, 0)
extraStat.states[sstate] += 1
extraStat.updated = now
extraStat = session.merge(extraStat)
  try:
extraStat = session.query(Stat). \
filter(Stat.name==pressName). \
join(extraStats). \
filter(ExtraStat.name==productType).one()   
 throws
  exception right here
  except:
extraStat = ExtraStat(productType, ExtraStat.PRODUCT_TYPE)
self.pressStats[pressName].extraStats.append(extraStat)
extraStat.states.setdefault(sstate, 0)
extraStat.states[sstate] += 1
extraStat.updated = now
 
  The marked area is wear it throws the exception. I'm not sure what
to
 do
  here to get past this, any help or ideas would be greatly
 appreciated.
 
  The exact exception is as follows:
  Sqlalchemy.orm.exc.FlushError: New instance [EMAIL PROTECTED] With
 identity
  key (class '__main__.ExtraStat',(u'C',)) conflicts with persistent
  instance [EMAIL PROTECTED]
 
  Thanks!
  Doug
 

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] New instance ExtraStat with identity key (...) conflicts with persistent instance ExtraStat

[Doug Farrell]

Hi all,

 

I'm having a problem with a new instance of a relation conflicting with

an existing instance. I'm using SA 0.5rc with Sqlite3. Here are my 

simplified classes:

 

class Stat(sqladb.Base):

 __tablename__ = stats

 name = Column(String(32), primary_key=True)

 total= Column(Integer)

 created  = Column(DateTime, default=datetime.datetime.now())

 updated  = Column(DateTime)

 states   = Column(PickleType, default={})

 extraStats   = relation(ExtraStat, backref=stat)

 

class ExtraStat(sqladb.Base):

 __tablename__ = extrastats

 name = Column(String(32), ForeignKey(stats.name),
primary_key=True)

 total= Column(Integer)

 created  = Column(DateTime, default=datetime.datetime.now())

 updated  = Column(DateTime)

 states   = Column(PickleType, default={})

 

The above Stat class has a one-to-many relationship with the ExtraStat

class (which I think I've implemented correctly). Later in the

program I create an in memory data model that has as part of it's 

components two

dictionaries that contain Stat instances. Those Stat instances have 

relationships to ExtraStat instances. My problem comes in the

following when I'm trying to update the data in those instances/tables. 

Here is a section of code that throws the exception:

 

 

 

pressName = press%s % pressNum

# add new ExtraStat instances as relations 

self._addProductStatsPress(productType, pressName) 

self._addPressStatsProduct(pressName, productType)

try:

   extraStat = session.query(Stat). \

   filter(Stat.name==productType). \

   join(extraStats). \

   filter(ExtraStat.name==pressName).one()

except:

   extraStat = ExtraStat(pressName, ExtraStat.PRESS_TYPE)

   self.productStats[productType].extraStats.append(extraStat)

   extraStat.states.setdefault(sstate, 0)

   extraStat.states[sstate] += 1

   extraStat.updated = now

   extraStat = session.merge(extraStat)

try:

   extraStat = session.query(Stat). \

   filter(Stat.name==pressName). \

   join(extraStats). \

   filter(ExtraStat.name==productType).one()    throws
exception right here

except:

   extraStat = ExtraStat(productType, ExtraStat.PRODUCT_TYPE)

   self.pressStats[pressName].extraStats.append(extraStat)

   extraStat.states.setdefault(sstate, 0)

   extraStat.states[sstate] += 1

   extraStat.updated = now

 

The marked area is wear it throws the exception. I'm not sure what to

do here to get past this, any help or ideas would be greatly

appreciated.

 

The exact exception is as follows:

Sqlalchemy.orm.exc.FlushError: New instance [EMAIL PROTECTED] With identity

key (class '__main__.ExtraStat',(u'C',)) conflicts with persistent 

instance [EMAIL PROTECTED]

 

Thanks!

Doug


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] New instance ExtraStat with identity key (...) conflicts with persistent instance ExtraStat

[Doug Farrell]

Hi all,

I'm having a problem with a new instance of a relation conflicting with
an existing instance. I'm using SA 0.5rc with Sqlite3. Here are my
simplified classes:

class Stat(sqladb.Base):
__tablename__ = stats
name = Column(String(32), primary_key=True)
total= Column(Integer)
created  = Column(DateTime, default=datetime.datetime.now())
updated  = Column(DateTime)
states   = Column(PickleType, default={})
extraStats   = relation(ExtraStat, backref=stat)

class ExtraStat(sqladb.Base):
__tablename__ = extrastats
name = Column(String(32), ForeignKey(stats.name),
primary_key=True)
total= Column(Integer)
created  = Column(DateTime, default=datetime.datetime.now())
updated  = Column(DateTime)
states   = Column(PickleType, default={})

The above Stat class has a one-to-many relationship with the ExtraStat
class (which I think I've implemented correctly). Later in the program I
create an in memory data model that has as part of it's components two
dictionaries that contain Stat instances. Those Stat instances have
relationships to ExtraStat instances. My problem comes in the following
when I'm trying to update the data in those instances/tables. Here is a
section of code that throws the exception:

pressName = press%s % pressNum
# add new ExtraStat instances as relations
self._addProductStatsPress(productType, pressName)
self._addPressStatsProduct(pressName, productType)
try:
  extraStat = session.query(Stat). \
  filter(Stat.name==productType). \
  join(extraStats). \
  filter(ExtraStat.name==pressName).one()
except:
  extraStat = ExtraStat(pressName, ExtraStat.PRESS_TYPE)
  self.productStats[productType].extraStats.append(extraStat)
  extraStat.states.setdefault(sstate, 0)
  extraStat.states[sstate] += 1
  extraStat.updated = now
  extraStat = session.merge(extraStat)
try:
  extraStat = session.query(Stat). \
  filter(Stat.name==pressName). \
  join(extraStats). \
  filter(ExtraStat.name==productType).one()    throws
exception right here
except:
  extraStat = ExtraStat(productType, ExtraStat.PRODUCT_TYPE)
  self.pressStats[pressName].extraStats.append(extraStat)
  extraStat.states.setdefault(sstate, 0)
  extraStat.states[sstate] += 1
  extraStat.updated = now

The marked area is wear it throws the exception. I'm not sure what to do
here to get past this, any help or ideas would be greatly appreciated.

The exact exception is as follows:
Sqlalchemy.orm.exc.FlushError: New instance [EMAIL PROTECTED] With identity
key (class '__main__.ExtraStat',(u'C',)) conflicts with persistent
instance [EMAIL PROTECTED]

Thanks!
Doug  

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Declarative application example

[Doug Farrell]

Michael,

Thanks for your response. I'll take another look at the ORM tutorial and
the Session chapter now that I've got a little experience and see if
some lights go on.

Your text about deleting instances matches my understanding of how
things work, but it's good to get confirmation and that I'm not missing
something. I'm guessing the Session having a strong reference keeps the
object alive even though I delete it from the list.

Again, thanks!
Doug

 -Original Message-
 From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
 On Behalf Of Michael Bayer
 Sent: Tuesday, October 28, 2008 10:30 AM
 To: sqlalchemy@googlegroups.com
 Subject: [sqlalchemy] Re: Declarative application example
 
 
 
 On Oct 28, 2008, at 9:32 AM, writeson wrote:
 
 
  Hi everyone,
 
  I'm struggling to try and use SqlAlchemy (0.5rc2) with a project I'm
  working on. The database part isn't very hard, it's just a flat
 table,
  but I keep running into problems with SA throwing exceptions. The SA
  documentation is good, but as of yet I haven't gotten the ah ha
  moment of how to put it all together. What I need to see is a
simple,
  complete application example that would show me how the pieces fit
  together as a whole. In particular I'm looking for things like this:
 
  1)When and how to update the database when modifying a Class
  instance.
 
 the ORM tutorial steps through this process fairly methodically, and
 touches upon the full lifecycle of an object.
 
 Also a good read of the session chapter explains exactly what the
 Session is doing.  0.5's default session configuration follows a model
 whereby you generally don't have to deal with anything except add/
 delete/commit.
 
 
  2)Can database object instances be maintained in a Python list,
 or
  should they be handled one at a time?
 
 there's no restriction on how mapped instances are structured or
 persisted.
 
 
  3)Does deleting a database object instance (del obj) delete the
  row from the database, or should session.delete(obj) be called first
  and then del obj?
 
 del obj just dereferences the variable named obj from the local
 namespace in the Python process.If no further references remain on
 the object, it will be garbage collected, including from SQLA's
 Session.  This does not communicate a database delete operation to the
 session.SQLAlchemy records an object for pending deletion using
 Session.delete(obj).  If you issue this call, the Session temporarily
 creates a strong reference to the object's mapped information so that
 it stays in scope, at least within the Session, until the transaction
 commits.
 
  4)Is it possible to apply a Python __cmp__() method to a list of
  database objects, or should SA order by operations be used instead?
 
 either approach is feasable depending on the situation
 
 
  
  options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en
 -~--~~~~--~~--~--~---


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: InvalidRequestError and ConcurrentModification problems

[Doug Farrell]

Michael,

I'm not sure why the ConcurrentModification is occurring at all, my
application doesn't use threads. I'll look into issuing the rollback()
call and see how that helps my app.

I'm sure there are better ways to structure my code and use of SA, this
is the result of prototyping along in an effort to learn, understand and
use SA. The session.expire_all() was injected recently as the result of
reading some stuff during a Google Search and just casting about to
solve the thrown exceptions. I also had a job = session.merge(job) in
there at one time. I have one module called managerdb.py where I create
the engine and the first session object. All other modules reference
this module to create their own session objects. By the way, with
locally scoped session variables should I do a session.close() or not?

Thanks,
Doug

 -Original Message-
 From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
 On Behalf Of Michael Bayer
 Sent: Tuesday, October 28, 2008 10:20 AM
 To: sqlalchemy@googlegroups.com
 Subject: [sqlalchemy] Re: InvalidRequestError and
 ConcurrentModification problems
 
 
 the message you're getting is due to an exception being raised,but
 rollback() not being called on the session.   When you catch those
 ConcurrentModification exceptions, you have to issue a rollback().
 
 I dont see anything else with the code that would suggest the same row
 being deleted in two places, although it does seem like theres likely
 a more succinct way of structuring that code, and I also dont see the
 purpose that the session.expire_all() accomplishes.   Things missing
 here include what scope is the session managed under (i.e. where is it
 created/destroyed/shared among threads) as well as what other
 concurrency exists within this section of the application.
 
 On Oct 27, 2008, at 9:15 PM, Doug Farrell wrote:
 
 
  Hi all,
 
  I'm using Python 2.5.1 with SqlAlchemy 0.5rc2 with Sqlite on Windows
  Server 2003 and I'm having a problem with my application throwing
  InvalidRequestError and ConcurrentModification exceptions. Here is
my
  simplified declarative class:
 
  class Job(srsmanagerdb.Base):
 STATUS_INIT = 0
 STATUS_RUN = 1
 STATUS_DONE = 2
 STATUS_FAIL = 3
 __tablename__ = jobs
 id= Column(Integer, primary_key=True,
  autoincrement=True)
 nas   = Column(String(12), default=None)
 filename  = Column(String(64), default=None, index=True)
 filesize  = Column(Integer, default=None)
 created   = Column(DateTime, default=None)
 job_id= Column(String(32), default=None)
 productType   = Column(String(1), default=None)
 contentType   = Column(String(10), default=None)
 priorityType  = Column(String(10), default=None)
 priority  = Column(Integer, default=None)
 assignedPress = Column(Integer, default=None)
 status= Column(Integer, default=None)
 
 def __init__(self, objrefs, fileDetails):
 nas, filename, filesize, ctime = fileDetails
 self.nas = nas
 self.filename = filename
 self.filesize = filesize
 self.created =
  datetime.datetime(*time.strptime(ctime[:ctime.find(.)], %Y-%m-%d
  %H:%M:%S)[0:6])
 
  This object is used to track state information about jobs being
  handled
  by a looping state machine. I keep a list of all active jobs in a
 Jobs
  collection class, so there are many active intances of the above
  class.
  The simplified Jobs collection class looks like this:
 
  class Jobs(AppContext):
 def __init__(self, objrefs):
 self._logger = __logger__
 self._jobs = []
 self._markedForRemoval = []
 def markForRemoval(self, job):
  self._markedForRemoval.append(job)
 def removeMarkedJobs(self):  # throws exception
in
  here
 session = srsmanagerdb.Session()
 for markedJob in self._markedForRemoval:
 try:
 session.expire_all()
 session.delete(markedJob)
 session.commit()
 self._jobs.remove(markedJob)
 except sqlalchemy.exceptions.ConcurrentModificationError,
  e:
 self._logger.warn(%s threw exception %s %
  (job.filename, e))
 self._markedForRemoval = []
 def process(self):
for job for self._jobs:
 job.process()
 if job.status == Job.STATUS_DONE:
 self.markForRemoval(job)
 self.removeMarkedJobs()
 
  The above simplified code runs for awhile (10s of minutes) with
  hundreds
  of jobs and then it throws the exception below in the
  removeMarkedJobs()
  method. I've worked really hard trying to figure out what's going
  wrong
  here. This is the only place where I delete jobs and commit that
  delete
  to the database. One question I have is if it's a good idea to keep
a
  list of active Job instances (database rows) in a Python list. In my
  removeMarkedJobs() I'm deleting the job instances

[sqlalchemy] InvalidRequestError and ConcurrentModification problems

[Doug Farrell]

Hi all,

I'm using Python 2.5.1 with SqlAlchemy 0.5rc2 with Sqlite on Windows
Server 2003 and I'm having a problem with my application throwing
InvalidRequestError and ConcurrentModification exceptions. Here is my
simplified declarative class:

class Job(srsmanagerdb.Base):
STATUS_INIT = 0
STATUS_RUN = 1
STATUS_DONE = 2
STATUS_FAIL = 3
__tablename__ = jobs
id= Column(Integer, primary_key=True,
autoincrement=True)
nas   = Column(String(12), default=None)
filename  = Column(String(64), default=None, index=True)
filesize  = Column(Integer, default=None)
created   = Column(DateTime, default=None)
job_id= Column(String(32), default=None)
productType   = Column(String(1), default=None)
contentType   = Column(String(10), default=None)
priorityType  = Column(String(10), default=None)
priority  = Column(Integer, default=None)
assignedPress = Column(Integer, default=None)
status= Column(Integer, default=None)
 
def __init__(self, objrefs, fileDetails):
nas, filename, filesize, ctime = fileDetails
self.nas = nas
self.filename = filename
self.filesize = filesize
self.created =
datetime.datetime(*time.strptime(ctime[:ctime.find(.)], %Y-%m-%d
%H:%M:%S)[0:6])

This object is used to track state information about jobs being handled
by a looping state machine. I keep a list of all active jobs in a Jobs
collection class, so there are many active intances of the above class.
The simplified Jobs collection class looks like this:

class Jobs(AppContext):
def __init__(self, objrefs):
self._logger = __logger__
self._jobs = []
self._markedForRemoval = []
def markForRemoval(self, job):
self._markedForRemoval.append(job)
def removeMarkedJobs(self):  # throws exception in
here
session = srsmanagerdb.Session()
for markedJob in self._markedForRemoval:
try:
session.expire_all()
session.delete(markedJob)
session.commit()
self._jobs.remove(markedJob)
except sqlalchemy.exceptions.ConcurrentModificationError, e:
self._logger.warn(%s threw exception %s %
(job.filename, e))
self._markedForRemoval = []
def process(self):
  for job for self._jobs:
job.process()
if job.status == Job.STATUS_DONE:
self.markForRemoval(job)
self.removeMarkedJobs()

The above simplified code runs for awhile (10s of minutes) with hundreds
of jobs and then it throws the exception below in the removeMarkedJobs()
method. I've worked really hard trying to figure out what's going wrong
here. This is the only place where I delete jobs and commit that delete
to the database. One question I have is if it's a good idea to keep a
list of active Job instances (database rows) in a Python list. In my
removeMarkedJobs() I'm deleting the job instances, and then removing the
job instance from the list. Is this necessary or good practice? I
haven't figured out if just deleting the job instance from the list
(self._jobs.remove(markedJob)) will also delete the job from the
database or not. Anyway, here's the traceback of the exception I'm
getting. Any help would be appreciated.

Thanks, Doug

2008-10-27 18:15:54 srsmanager   ERRORunexpected error, restarting:
Traceback (most recent call last):   
File c:\cygwin\home\ripadmin\dev\srsmanager\srsprocess.py, line 154,
in runjobs isActive = self._jobs.process()   
File c:\cygwin\home\ripadmin\dev\srsmanager\jobs.py, line 436, in
process self.removeMarkedJobs()   
File c:\cygwin\home\ripadmin\dev\srsmanager\jobs.py, line 397, in
removeMarkedJobs self._logger.warn(%s threw exception %s %
(markedJob.filename, e))   
File
c:\python\2.5\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg\sqlalchem
y\orm\attributes.py, line 135, in __get__ return
self.impl.get(instance_state(instance))   
File
c:\python\2.5\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg\sqlalchem
y\orm\attributes.py, line 327, in get value = callable_()   
File
c:\python\2.5\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg\sqlalchem
y\orm\attributes.py, line 909, in __call__ attr.impl.key in
unmodified   
File
c:\python\2.5\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg\sqlalchem
y\orm\mapper.py, line 1715, in _load_scalar_attributes result =
session.query(mapper)._get(identity_key, refresh_state=state,
only_load_props=attribute_names)   
File
c:\python\2.5\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg\sqlalchem
y\orm\query.py, line 1211, in _get return q.all()[0]   
File
c:\python\2.5\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg\sqlalchem
y\orm\query.py, line 985, in all return list(self)   
File
c:\python\2.5\lib\site-packages\SQLAlchemy-0.5.0rc1-py2.5.egg\sqlalchem
y\orm\query.py, line 1073, in __iter__ return

[sqlalchemy] Can't attach instance; another instance with key (?)

[Doug Farrell]

Hi all,

I'm using SqlAlchemy 0.5rc1 to track jobs with a sqlite datatabe in a state 
machine used by a server. Each job represents a currently active job in the 
server and will be alive for awhile as it takes time for the server to 
process each job. When a job is done it is removed from the system, and the 
database. Each job has an integer autoincrementing primary key. This is just a 
flat table with no relations to any other table, with records being created and 
deleted as the jobs come into and go out of existance. The server runs for 
awhile (hours) but I've been getting these exceptions:

Can't attach instance [EMAIL PROTECTED]; another instance with key (, (220,)) 
is already present in this session.

What is this trying to tell me? Does this mean that a newly created job is 
trying to use the primary key of an already existing job?

Thanks in advance for your help!

Doug

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] How to perform inner joins

[Doug Farrell]

Hi all,

 

Well, I'm still stumped by SqlAlchemy syntax, but it's getting better.
I've got some tables built this way using SqlAlchemy 0.5:

 

press_table = Table('press', metadata, autoload=True) 

press_routing_table = Table('press_routing', metadata, autoload=True) 

press_routing_press_table = Table('press_routing_press', 

  metadata,

  Column('press_id', 

 Integer, 

 ForeignKey('press.id'),

 primary_key=True),

  Column('press_routing_id',

 Integer,

 ForeignKey('press_routing.id'),

 primary_key=True),

  Column('type', MSEnum),

  autoload=True)



class Press(object): pass

 

class PressRouting(object): pass

 

mapper(Press, 

   press_table, 

   properties=dict(routes=relation(PressRouting, 

 
secondary=press_routing_press_table,

   backref='presses')))

   

mapper(PressRouting, press_routing_table)

 

I'm trying to represent a many-to-many relationship between the
press_table and the press_routing table using the linking table,
press_routing_press. I think I've got the table structure and mapping
set up, but now I need some help to build a query using SqlAlchemy that
does the same thing as this MySQL query:

 

select p.id, p.code

from press p

inner join press_routing_press prp

on p.id=prp.press_id

inner join press_routing pr

on pr.id=prp._press_routing_id

where pr.code='A'

 

This gives me the results I want from the MySQL command line against the
existing tables in the database, but I can't figure out how to construct
an equivalent SqlAlchemy version to do the same thing passing in 'A' as
the paramter.

 

I know I'm being dense about this, thanks in advance for the help, 

Doug


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: How to perform inner joins

[Doug Farrell]

Michael,

 

You're right of course, your version works fine. I cut and pasted what
you have into my code framework, cleaned up some stuff and it works
there as well. One thing I cleaned up was this:

 

# initialize the mysql engine and SqlAlchemy base objects

engine = create_engine(__config__.database.workflow_url, echo=True)

metadata = MetaData(engine)

 

Rather than what you did, which was to create the metatdata = MetaData()
first and then bind it to the engine after the mapping with this:
metadata.create_all(engine). I don't know if that made the difference or
not.

 

To impose on your further, how can I get the 'type' field that is
defined as a column in the press_routing_press linking table that goes
with everyting that comes back with PressRouting.code=='A' ?

 

Again, thanks for your help and patience, I've been using Python for
awhile, but I'm a DB novice...

Doug

 

 

From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
On Behalf Of Michael Bayer
Sent: Friday, September 26, 2008 3:07 PM
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: How to perform inner joins

 

Doug -

 

what happened with the example I pasted ?  It generates the exact SQL
you describe.   A full script is attached, using the mappings you've
sent.  It uses sqlite, so you can just run it.

 


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: How to perform inner joins

[Doug Farrell]

I'm have the 'type' column in the press_routing_press linking table;
something like this:

 

Sess.query(Press,
PressRoutingPress.type).join(Press.routes).filter(PressRouting.code=='A'
).all()

 

I've tried some variations of this, but no luck. I've also tried
applying what the documentation says about using the Association Object,
but haven't figured it out yet. I've gotten back lots of data, just not
the limited set I'm looking fore.

 

Thanks,

Doug

 

From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
On Behalf Of Michael Bayer
Sent: Friday, September 26, 2008 4:10 PM
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: How to perform inner joins

 

 

 

sess.query(Press,
PressRouting.code).join(Press.routes).filter(PressRouting.code=='A').all
()

 

will return tuples in the form:

 

(some Press object, somecode)

 

 

On Sep 26, 2008, at 3:25 PM, Doug Farrell wrote:





Michael,

 

You're right of course, your version works fine. I cut and pasted what
you have into my code framework, cleaned up some stuff and it works
there as well. One thing I cleaned up was this:

 

# initialize the mysql engine and SqlAlchemy base objects

engine = create_engine(__config__.database.workflow_url, echo=True)

metadata = MetaData(engine)

 

Rather than what you did, which was to create the metatdata = MetaData()
first and then bind it to the engine after the mapping with this:
metadata.create_all(engine). I don't know if that made the difference or
not.

 

To impose on your further, how can I get the 'type' field that is
defined as a column in the press_routing_press linking table that goes
with everyting that comes back with PressRouting.code=='A' ?

 

Again, thanks for your help and patience, I've been using Python for
awhile, but I'm a DB novice...

Doug

 

 

From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
On Behalf Of Michael Bayer
Sent: Friday, September 26, 2008 3:07 PM
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: How to perform inner joins

 

Doug -

 

what happened with the example I pasted ?  It generates the exact SQL
you describe.   A full script is attached, using the mappings you've
sent.  It uses sqlite, so you can just run it.

 





 



 


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: How to perform inner joins

[Doug Farrell]

Michael, 


After some tweaking around, it works! Thanks for all your help, it was
invaluable! I'm sure I'll be back for more though... J

 

Doug

 

From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
On Behalf Of Michael Bayer
Sent: Friday, September 26, 2008 5:50 PM
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: How to perform inner joins

 

 

On Sep 26, 2008, at 5:42 PM, Doug Farrell wrote:





I'm have the 'type' column in the press_routing_press linking table;
something like this:

 

Sess.query(Press,
PressRoutingPress.type).join(Press.routes).filter(PressRouting.code=='A'
).all()

 

I've tried some variations of this, but no luck. I've also tried
applying what the documentation says about using the Association Object,
but haven't figured it out yet. I've gotten back lots of data, just not
the limited set I'm looking fore.

 

oh.  This is a different situation.  You have press_routing_press mapped
to its own class, and it has columns that contain information distinct
from the join between Press and PressRouting.   You'd have to join that
instead.  If you have a relation on Press for it, its easy:

 

query(Press, PressRoutingPress.type).join(Press.pressrouting,
PressRoutingPress.route).filter(...)...

 

you should also set viewonly=True on your Press.routes relation(),
otherwise during flush you could have conflicting data placed in the
press_routing_press table.

 

The preferred pattern for an association table that has additonal
columns in it is the association object pattern, which can be used in
conjunction with the associationproxy to simplify operations from
Press-PressRouting.   But in your case I'd just use straight
association object for starters.   This pattern is described in the
mapping docs.

 

 



 


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Newbie many-to-many using Declarative question

[Doug Farrell]

Michael,

 
  class PressRoutingPress(Base):
 '''This class defines the many-to-many join table between press
 and press_routing.
 '''
 __tablename__ = press_routing_press
 __table_args__ = {'autoload' : True}
 
 press_id = Column(Integer, ForeignKey('press.id'),
  primary_key=True)
 press_routing_id = Column(Integer,
ForeignKey('press_routing.id'),
  primary_key=True)
 
  class PressRouting(Base):
 '''This class defines the press_routing table information.
 '''
 __tablename__ = press_routing
 __table_args__ = {'autoload' : True}
 
  class Press(Base):
 '''This class defines the press table information.
 '''
 __tablename__ = press
 __table_args__ = {'autoload' : True}
 
 # many to many Press-PressRouting
 press_routing = relation('PressRouting',
  secondary=PressRoutingPress,
 
  primaryjoin=id==PressRoutingPress.press_id,
 
 foreign_keys=[PressRoutingPress.press_id],
 
  secondaryjoin=PressRouting.id==PressRoutingPress.press_routing_id,
 
  foreign_keys=[PressRoutingPress.press_routing_id],
  uselist=False)
  #backref=backref('press'))
  #viewonly=True)
 
  This all works till I try to instantiate an instance of a Press()
  object, then I get the following exception:
 
 when you use the secondary argument on relation(), that should be a
 plain Table object and should not be mapped (i.e. there should be no
 separate class for it):
 
 press_routing_press = Table(press_routing_press, Base.metadata,
   Column(press_id, Integer, ForeignKey('press.id'),
 primary_key=True),
   Column(press_routing_id, Integer,
 ForeignKey('press_routing.id'),primary_key=True)
 )
 
 class Press(Base):
  ...
 
  press_routing = relation(PressRouting,
 secondary=press_routing_press)
 
 no other arguments to relation() are needed.
 
 If you do want PressRoutingPress to be mapped, you use the association
 object pattern, which means you aren't using the secondary keyword.
 The non-declarative version is here:

http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_relation_
 patterns_association
 

I implemented your suggestion and that cleared things right up, and made
the set up code much simpler. Thanks for your help, considering how much
time you spend answering questions, I really appreciate your attention
to my issues!

Thanks again,
Doug

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] How to perform inner joins

[Doug Farrell]

Hi all,

Well, I'm still stumped by SqlAlchemy syntax, but it's getting better.
I've got some tables built this way using SqlAlchemy 0.5:

press_table = Table('press', metadata, autoload=True)
press_routing_table = Table('press_routing', metadata, autoload=True)
press_routing_press_table = Table('press_routing_press', 
  metadata,
  Column('press_id', 
 Integer, 
 ForeignKey('press.id'),
 primary_key=True),
  Column('press_routing_id',
 Integer,
 ForeignKey('press_routing.id'),
 primary_key=True),
  Column('type', MSEnum),
  autoload=True)

class Press(object): pass

class PressRouting(object): pass

mapper(Press, 
   press_table, 
   properties=dict(routes=relation(PressRouting, 
 
secondary=press_routing_press_table,
   backref='presses')))
   
mapper(PressRouting, press_routing_table)

I'm trying to represent a many-to-many relationship between the
press_table and the press_routing table using the linking table,
press_routing_press. I think I've got the table structure and mapping
set up, but now I need some help to build a query using SqlAlchemy that
does the same thing as this MySQL query:

select p.id, p.code
from press p
inner join press_routing_press prp
on p.id=prp.press_id
inner join press_routing pr
on pr.id=prp._press_routing_id
where pr.code='A'

This gives me the results I want from the MySQL command line against the
existing tables in the database, but I can't figure out how to construct
an equivalent SqlAlchemy version to do the same thing passing in 'A' as
the paramter.

I know I'm being dense about this, thanks in advance for the help,
Doug

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Newbie many-to-many using Declarative question

[Doug Farrell]

Hi all,

I'm trying to set up a many-to-many system using the Declarative syntax
against an existing MySQL set of tables. We're using a 'link' table
between two other tables we want to relate many-to-many. Here's the
simplified layout of those tables:

mysql desc press_routing_press;
+--+--+--+-+-+--
-+
| Field| Type | Null | Key | Default | Extra
|
+--+--+--+-+-+--
-+
| press_routing_id | int(10) unsigned | NO   | MUL | |
|
| press_id | int(10) unsigned | NO   | MUL | |
|
| type | enum('new','rework') | YES  | | NULL|
|
+--+--+--+-+-+--
-+

mysql desc press;
+-+--+--+-+-+---+
| Field   | Type | Null | Key | Default | Extra |
+-+--+--+-+-+---+
| id  | int(10) unsigned | NO   | PRI | |   |
| code| varchar(15)  | YES  | | NULL|   |
| name| varchar(25)  | YES  | | NULL|   |
+-+--+--+-+-+---+

mysql desc press_routing;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| id   | int(10) unsigned | NO   | PRI | |   |
| code | varchar(20)  | NO   | | |   |
| press| int(10) unsigned | NO   | | |   |
+--+--+--+-+-+---+

And here's the Python SqlAlchemy code I've put together trying to
describe this:

class PressRoutingPress(Base):
'''This class defines the many-to-many join table between press 
and press_routing.
'''
__tablename__ = press_routing_press
__table_args__ = {'autoload' : True}

press_id = Column(Integer, ForeignKey('press.id'), primary_key=True)
press_routing_id = Column(Integer, ForeignKey('press_routing.id'),
primary_key=True)

class PressRouting(Base):
'''This class defines the press_routing table information.
'''
__tablename__ = press_routing
__table_args__ = {'autoload' : True}

class Press(Base):
'''This class defines the press table information.
'''
__tablename__ = press
__table_args__ = {'autoload' : True}

# many to many Press-PressRouting
press_routing = relation('PressRouting', 
 secondary=PressRoutingPress, 
 primaryjoin=id==PressRoutingPress.press_id,
 foreign_keys=[PressRoutingPress.press_id],
 
secondaryjoin=PressRouting.id==PressRoutingPress.press_routing_id,
 
foreign_keys=[PressRoutingPress.press_routing_id],
 uselist=False)
 #backref=backref('press')) 
 #viewonly=True)

This all works till I try to instantiate an instance of a Press()
object, then I get the following exception:

sqlalchemy.exc.ArgumentError: Could not determine relation direction for
primaryjoin condition 'press_routing_press.press_id = %s', on relation
Press.press_routing. Are the columns in 'foreign_keys' present within
the given join condition ?

I've tinkered around with various things in the relation() defined in
the Press class, but that just seems to generate other exceptions. From
what I've read the above code looks closest to something that should
work based on what I've seen others posting.

Any help and/or guidance would be appreciated, thanks!
Doug

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: ORM mapping existing data tables

[Doug Farrell]

Michael,

Thanks for the response and help, I made the change and the class is
populated, this is great!

Again, thanks for your help,
Doug

 -Original Message-
 From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
 On Behalf Of Michael Bayer
 Sent: Sunday, September 21, 2008 10:41 PM
 To: sqlalchemy@googlegroups.com
 Subject: [sqlalchemy] Re: ORM mapping existing data tables
 
 
 
 On Sep 21, 2008, at 9:09 PM, Doug Farrell wrote:
 
 
  Hi everyone,
 
  I'm new to SqlAlchemy, but I've got some things working and really
am
  enjoying it. Right now I'm trying to ORM map some existing MySQL
  database tables to a class. I've read the documentation, I guess I'm
  just not getting it. Can someone help me out. I've done this:
 
  # initialize the sqlite engine and SqlAlchemy base objects
  engine = create_engine('mysql://username:@hostname/database',
  echo=True)
  meta = MetaData(engine)
 
  myTable = Table('mytable', meta, autoload=True)
  l = [c.name for c in pressrouting.columns]
  print l
 
  And this works fine, but if I try this:
 
  # initialize the sqlite engine and SqlAlchemy base objects
  engine = create_engine('mysql://username:@hostname/database',
  echo=True)
  meta = MetaData(engine)
  Base = declarative_base(metadata=meta)
 
  class MyTable(Base):
 __tablename__ = mytable
 Pass
 
  I get this error:
 
  sqlalchemy.exc.ArgumentError: Mapper Mapper|MyTable|mytable could
not
  assemble any primary key columns for mapped table 'mytable'
 
  What is this error message trying to tell me?
 
 the autoload=True part is missing from your second recipe, so the
 table has no columns and therefore no primary key either.  Add in
 __table_args__ = {'autoload':True}.
 
  
 t this group at http://groups.google.com/group/sqlalchemy?hl=en
 -~--~~~~--~~--~--~---


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] ORM mapping existing data tables

[Doug Farrell]

Hi everyone,

I'm new to SqlAlchemy, but I've got some things working and really am
enjoying it. Right now I'm trying to ORM map some existing MySQL
database tables to a class. I've read the documentation, I guess I'm
just not getting it. Can someone help me out. I've done this:

# initialize the sqlite engine and SqlAlchemy base objects
engine = create_engine('mysql://username:@hostname/database', echo=True)
meta = MetaData(engine)

myTable = Table('mytable', meta, autoload=True)
l = [c.name for c in pressrouting.columns]
print l

And this works fine, but if I try this:

# initialize the sqlite engine and SqlAlchemy base objects
engine = create_engine('mysql://username:@hostname/database', echo=True)
meta = MetaData(engine)
Base = declarative_base(metadata=meta)

class MyTable(Base):
__tablename__ = mytable
Pass

I get this error:

sqlalchemy.exc.ArgumentError: Mapper Mapper|MyTable|mytable could not
assemble any primary key columns for mapped table 'mytable'

What is this error message trying to tell me?

Thanks in advance,
Doug

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Declarative documentation

[Doug Farrell]

Michael,

Thanks for getting back to me so quickly. I've figured out how to get a
one-to-many, single table relationship working with one way relations,
here is that configuration:

class JobData(Base):
__tablename__ = jobs
id   = Column(Integer, primary_key=True, autoincrement=True)
pid  = Column('pid', Integer, ForeignKey('jobs.id'))
srcpath  = Column(String(128), default=None)
press= Column(Integer, default=None)
priority = Column(Integer, default=None)
created  = Column(DateTime, default=datetime.datetime.now)
  
def __init__(self, srcpath=None):
session = Session()
self.srcpath = srcpath
session.add(self)
session.commit()

Jobs that are children of other jobs get their pid field initialized,
and this seems to work well.

I wasn't sure if the link you sent was what you intended as that brought
up a page about eager loading. However, it was interesting reading all
the same!

Thanks again,
Doug


 -Original Message-
 From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
 On Behalf Of Empty
 Sent: Wednesday, September 17, 2008 9:02 AM
 To: sqlalchemy@googlegroups.com
 Subject: [sqlalchemy] Re: Declarative documentation
 
 
 Hi Doug,
 
  I'm a new user (like this week) of SqlAlchemy and I'm trying to find
  more information about using the Declarative system. In particular
 I'm
  trying to build a hierarchical table with one-to-many relationships
  within the table. So if anyone knows where there might be some
  additional documentation about this, examples or just some guidance,
  I'd very much appreciate it!!
 
 There's extensive documentation online and in the ext/declarative.py
 module itself.  Beyond that it's basically just straight SQLAlchemy.
 So you would be handling a self referential hierarchy as demonstrated
 here:
 

http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_relation_
 selfreferential
 
 I hope that helps.
 
 Michael
 http://blog.michaeltrier.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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---