Re: [sqlalchemy] SQLAlchemy transaction ID

[Thierry Florac]

Hi Mike,
I'm effectively using cx-Oracle... but I didn't notice any deprecation
warning! Maybe it's because I'm still using release 8.0.1 (as I still need
Python 3.5 support)?

If you need anyone to test for cx-Oracle features, just ask!

Anyway, I'm also using other "no-transactional" databases (like
Elasticsearch indexes), for which I effectively just use a basic
datamanager to post data, without any need for synchronized transactions...

Best regards,
Thierry
-- 
  https://www.ulthar.net -- http://pyams.readthedocs.io


Le mer. 27 janv. 2021 à 20:40, Mike Bayer  a
écrit :

>
>
> On Wed, Jan 27, 2021, at 2:23 PM, Thierry Florac wrote:
>
> Hi,
> I'm actually using two databases connections: one is my "main" connection,
> opened on a ZODB (with RelStorage), and **sometimes** I have to open
> another connection on another database (and event more sometimes); the two
> transactions have to be synchronized: if one of them is aborted for any
> reason, the two transactions have to be aborted.
>
>
>
> OK, then two phase it is
>
> I have always thought that the two-phase transaction was created to handle
> this kind of use case, but if there is another better solution, I would be
> very happy to know about it!
>
>
> if you need the ORM to call prepare() then you need the XID and there you
> are.
>
> This is all stuff that I think outside of the Zope community (but still in
> Python) you don't really see much of.  If someone's Flask app is writing to
> Postgresql and MongoDB they're just going to spew data out to mongo and not
> really worry about it, but that's becasue mongo doesn't have any 2pc
> support.It's just not that commonly used because we get basically
> nobody asking about it.
>
>
>
> @jonathan, I made a patch to Pyramid DebugToolbar that I pushed to Github
> and made a pull request. But I don't know how to provide a test case as a
> two-phase commit is not supported by SQLite...
> I'll try anyway to provide a description of a "method" I use to reproduce
> this!
>
>
> So interesting fact, it looks like you are using Oracle for 2pc, that's
> what that tuple is, and we currently aren't including Oracle 2pc in our
> test support as cx_Oracle no longer includes the "twophase" flag which I
> think we needed for some of our more elaborate tests.  At the moment,
> create_xid() emits a deprecation warning.  I've been in contact with Oracle
> devs and it looks like we should be supporting 2pc as I can get help from
> them now for things that aren't working.   I've opened
> https://github.com/sqlalchemy/sqlalchemy/issues/5884 to look into this.
> you should have been seeing a deprecation warning in your logs all this
> time though.
>
>
>
>
>
> Best regards,
> Thierry
> --
>   https://www.ulthar.net -- http://pyams.readthedocs.io
>
>
> Le mer. 27 janv. 2021 à 19:19, Mike Bayer  a
> écrit :
>
>
>
>
> On Wed, Jan 27, 2021, at 8:32 AM, Thierry Florac wrote:
>
>
> Hi,
> I'm actually using SQLAlchemy with Pyramid and zope.sqlalchemy packages.
> My main database connection is a ZODB connection and, when required, I
> create an SQLAlchemy session which is joined to main transaction using this
> kind of code:
>
> *  from *sqlalchemy.orm *import *scoped_session, sessionmaker
>
> *  from *zope.sqlalchemy *import *register
> *  from *zope.sqlalchemy.datamanager *import* join_transaction
>
>   _engine = get_engine(*engine*, *use_pool*)
>   if *use_zope_extension*:
>   factory = scoped_session(sessionmaker(*bind*=_engine, *twophase*=
> *True*))
>   else:
>   factory = sessionmaker(*bind*=_engine, *twophase*=*True*)
>   session = factory()
>   if *use_zope_extension*:
>   register(session, *initial_state*=*STATUS_ACTIVE*)
>   if *join*:
>   join_transaction(session, *initial_state*=*STATUS_ACTIVE*)
>
> Everything is working correctly!
>
> So my only question is that I also use Pyramid_debugtoolbar package, which
> is tracking many SQLAlchemy events, including two-phase commits
> transactions, and which in this context receives transaction IDs as a three
> values tuple instead of a simple string (like, for example: (4660,
> '12345678901234567890123456789012', '0009'),
> which is raising an exception)!
> Is it normal behaviour, and what does this value mean?
>
>
> I would ask if you really really want to use the "twophase=True" flag, and
> I would suggest turning it off if you aren't in fact coordinating against
> multiple RDBMS backends (and even if you are, maybe).   I'm not really sure
> what that tuple is, I'd have to look but it seems likely to be related to
> the XID stuff, which is really not something 

Re: [sqlalchemy] SQLAlchemy transaction ID

[Thierry Florac]

Hi,
I'm actually using two databases connections: one is my "main" connection,
opened on a ZODB (with RelStorage), and **sometimes** I have to open
another connection on another database (and event more sometimes); the two
transactions have to be synchronized: if one of them is aborted for any
reason, the two transactions have to be aborted.
I have always thought that the two-phase transaction was created to handle
this kind of use case, but if there is another better solution, I would be
very happy to know about it!

@jonathan, I made a patch to Pyramid DebugToolbar that I pushed to Github
and made a pull request. But I don't know how to provide a test case as a
two-phase commit is not supported by SQLite...
I'll try anyway to provide a description of a "method" I use to reproduce
this!

Best regards,
Thierry
-- 
  https://www.ulthar.net -- http://pyams.readthedocs.io


Le mer. 27 janv. 2021 à 19:19, Mike Bayer  a
écrit :

>
>
> On Wed, Jan 27, 2021, at 8:32 AM, Thierry Florac wrote:
>
>
> Hi,
> I'm actually using SQLAlchemy with Pyramid and zope.sqlalchemy packages.
> My main database connection is a ZODB connection and, when required, I
> create an SQLAlchemy session which is joined to main transaction using this
> kind of code:
>
> *  from *sqlalchemy.orm *import *scoped_session, sessionmaker
>
> *  from *zope.sqlalchemy *import *register
> *  from *zope.sqlalchemy.datamanager *import* join_transaction
>
>   _engine = get_engine(*engine*, *use_pool*)
>   if *use_zope_extension*:
>   factory = scoped_session(sessionmaker(*bind*=_engine, *twophase*=
> *True*))
>   else:
>   factory = sessionmaker(*bind*=_engine, *twophase*=*True*)
>   session = factory()
>   if *use_zope_extension*:
>   register(session, *initial_state*=*STATUS_ACTIVE*)
>   if *join*:
>   join_transaction(session, *initial_state*=*STATUS_ACTIVE*)
>
> Everything is working correctly!
>
> So my only question is that I also use Pyramid_debugtoolbar package, which
> is tracking many SQLAlchemy events, including two-phase commits
> transactions, and which in this context receives transaction IDs as a three
> values tuple instead of a simple string (like, for example: (4660,
> '12345678901234567890123456789012', '0009'),
> which is raising an exception)!
> Is it normal behaviour, and what does this value mean?
>
>
> I would ask if you really really want to use the "twophase=True" flag, and
> I would suggest turning it off if you aren't in fact coordinating against
> multiple RDBMS backends (and even if you are, maybe).   I'm not really sure
> what that tuple is, I'd have to look but it seems likely to be related to
> the XID stuff, which is really not something anyone uses these days.
>
>
>
> Best regards,
> Thierry
>
> --
>   https://www.ulthar.net -- http://pyams.readthedocs.io
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAPX_VWCwQmHEsynbsSa54%2BvkWH5xkZC_-hh359iM%3D%2BYh1f%3DE6A%40mail.gmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/CAPX_VWCwQmHEsynbsSa54%2BvkWH5xkZC_-hh359iM%3D%2BYh1f%3DE6A%40mail.gmail.com?utm_medium=email_source=footer>
> .
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/e5458f0a-4cf1-4328-93e5-4c827641c590%40www.fastmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/e5458f0a-4cf1-4328-93e5-4c827641c590%40www.fastmail.com?utm_medium=email_source=footer>
> .
>

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

[sqlalchemy] SQLAlchemy transaction ID

[Thierry Florac]

Hi,
I'm actually using SQLAlchemy with Pyramid and zope.sqlalchemy packages.
My main database connection is a ZODB connection and, when required, I
create an SQLAlchemy session which is joined to main transaction using this
kind of code:

  from sqlalchemy.orm import scoped_session, sessionmaker

  from zope.sqlalchemy import register
  from zope.sqlalchemy.datamanager import join_transaction

  _engine = get_engine(engine, use_pool)
  if use_zope_extension:
  factory = scoped_session(sessionmaker(bind=_engine, twophase=True))
  else:
  factory = sessionmaker(bind=_engine, twophase=True)
  session = factory()
  if use_zope_extension:
  register(session, initial_state=STATUS_ACTIVE)
  if join:
  join_transaction(session, initial_state=STATUS_ACTIVE)

Everything is working correctly!

So my only question is that I also use Pyramid_debugtoolbar package, which
is tracking many SQLAlchemy events, including two-phase commits
transactions, and which in this context receives transaction IDs as a three
values tuple instead of a simple string (like, for example: (4660,
'12345678901234567890123456789012', '0009'),
which is raising an exception)!
Is it normal behaviour, and what does this value mean?

Best regards,
Thierry

-- 
  https://www.ulthar.net -- http://pyams.readthedocs.io

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAPX_VWCwQmHEsynbsSa54%2BvkWH5xkZC_-hh359iM%3D%2BYh1f%3DE6A%40mail.gmail.com.

Re: [sqlalchemy] Handy package to debug flask+sqlalchemy+orm (flask_sqla_debug)

[Thierry Florac]

Hi,

For Pyramid, another interesting package is SQLTap, which provides useful
informations about execution of SQLAlchemy queries...

Best regards,
Thierry

2016-10-31 11:05 GMT+01:00 Simon King :

> On Sun, Oct 30, 2016 at 9:36 PM, Alfred Perlstein
>  wrote:
> > Hello,
> >
> > I wanted to start this out with a big thanks to the community, especially
> > Mike Bayer, Simon King, and Jonathan Vanasco.
> >
> > A few weeks ago I asked for help on debugging sqlalchemy and was able to
> use
> > the combined feedback, tips, and tricks presented to build a handy little
> > package for debugging flask+sqlalchemy.
> >
> > The main goal was to get a handle on the number of queries being made by
> the
> > orm and the latency involved.
> >
> > I've just released the 0.2 version of a new package "flask_sqla_debug",
> you
> > can find it on pypi (https://pypi.python.org/pypi/flask_sqla_debug) and
> > github (https://github.com/splbio/flask_sqla_debug).
> >
> > Right now it can:
> >
> > 1) turn on logging of sql statements for some blocks of code.
> >
> > 2) log/assert if a single query takes too long (~200ms).
> >
> > 3) log/assert if the combined queries inside of a request take too long
> > (~200ms).
> >
> > 4) log/assert if the number of queries exceeds a threshold. (~20)
> >
> > Most of these logs/asserts thresholds can be tuned per-endpoint, although
> > there are handy defaults.
> >
> > We are using it in our project now and finding all sorts of areas to
> > optimize.  It's been very handy just having it now for a week installed
> in
> > our project.
> >
> > To use it, just "pip install flask_sqla_debug" and then add this code to
> > your flask app:
> >
> > flask_app.flask_sql_debug = FlaskSqlaDebug(app=flask_app,
> > engine=db.engine)
> >
> > I'd love some feedback on features, documentation or anything else you
> have
> > input on.
> >
> > thanks again folks, this wouldn't have been possible without your help.
> >
> > -Alfred
>
> Hi Alfred,
>
> That looks very interesting, and I'd actually like to be able to use
> it in my applications. Unfortunately I usually use pyramid rather than
> flask, and this is obviously very flask-specific.
>
> I wonder if it could be split into 2 parts. The first would be a
> framework-agnostic class for tracking and logging queries and so on,
> and the second would be an adapter that hooks the class into a flask
> application. It should then be easy to write adapters for other
> frameworks as needed.
>
> Simon
>
> --
> 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.
>



-- 
http://www.imagesdusport.com -- http://www.ztfy.org

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

Re: [sqlalchemy] Using literals in ORM update/delete query

[Thierry Florac]

I don't think it's so easy to produce a test case...
Generaly speaking, indexes are used in a normal way!
There's only in a single case, for a single table, that they are not used
even for common operations. Maybe it's a statistics problem or something
else I don't understand; and AFAIK the problem didn't occured at start
while table was containing fewer records...

2016-09-12 22:00 GMT+02:00 Mike Bayer <mike...@zzzcomputing.com>:

> I'd try to see on the cx_oracle list if you can produce a self-contained
> cx_Oracle-only test case and illustrate that indexes are not being used...I
> think this comes up a lot over there it's like an OCI thing.
>
>
>
> On 09/12/2016 03:20 PM, Thierry Florac wrote:
>
>> Hi Mike,
>>
>> I really agrea with you on all the points you mentionned. And I won't
>> add anything about the cost of an Oracle database license and
>> administrators compared with their performances! But I actually don't
>> have the choice...  :-/
>> My problem is clearly a database problem at first! But as my DBA can't
>> find any solution to it, I'm just trying to find a bypass on the ORM side.
>> I definitively agree with SA ORM's way of handling all this. I was quite
>> sure in fact that there was no easy way to bypass the ORM...
>>
>> Thanks anyway,
>> Thierry
>>
>> 2016-09-12 20:53 GMT+02:00 Mike Bayer <mike...@zzzcomputing.com
>> <mailto:mike...@zzzcomputing.com>>:
>>
>>
>>
>>
>> On 09/12/2016 09:11 AM, Thierry Florac wrote:
>>
>> Hi,
>>
>> I'm working with an Oracle database which, for a reason I can't
>> understand, doesn't want to use indexes when working on a given
>> table
>> (which is quite large) with prepared statements!
>> I know I can use literal columns when running "select" queries
>> (using
>> the "literal_column" function), so that selects performances are
>> good,
>> but is there a way to use such literals while performing updates
>> ou
>> deletes through the ORM using sessions?
>>
>>
>> Three general stanzas on this:
>>
>>
>> If we are talking about the UPDATE / DELETE statements within the
>> ORM flush(), that would be extremely hard to trick the ORM into
>> doing that correctly; getting the bound parameters to render as
>> literals would be doable but the ORM really wants to batch those
>> statements together, which would have to also be turned off and
>> there's no public entryway to that.
>>
>> Next, brute force via the do_executemany() / do_execute() dialect
>> events which give you direct access to the cx_Oracle cursor.  You'd
>> need to break the list of parameters sent to executemany() into
>> multiple single execute() calls and rewrite all the parameters.
>>  This will get you the effect you want at the expense of much
>> ugliness.
>>
>> Third.  The ORM's UPDATE/DELETE statements only use the primary key
>> in the WHERE clause, not any other columns.   if your Oracle DB
>> can't even index by primary key on prepared statement that is really
>> something that is likely fixable on the database side.   Your
>> employer (or yourself, if this is your own company) is likely paying
>> hundreds of thousands of $$ per year to be able to use Oracle in the
>> first place, and if your Oracle DB can't look up a record by primary
>> key using the index, something is seriously broken with it.   Oracle
>> is pretty bad in this area but this one should not be the case; I'd
>> advise at least checking on the cx_Oracle list why your Oracle DB is
>> bypassing indexes for prepared statements.
>>
>>
>>
>>
>>
>>
>> Best regards,
>> Thierry
>> --
>> http://www.imagesdusport.com -- http://www.ztfy.org
>>
>> --
>> 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
>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com
>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>>.
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> <mailto:sqlalchemy@googlegrou

Re: [sqlalchemy] Using literals in ORM update/delete query

[Thierry Florac]

Hi Mike,

I really agrea with you on all the points you mentionned. And I won't add
anything about the cost of an Oracle database license and administrators
compared with their performances! But I actually don't have the choice...
 :-/
My problem is clearly a database problem at first! But as my DBA can't find
any solution to it, I'm just trying to find a bypass on the ORM side.
I definitively agree with SA ORM's way of handling all this. I was quite
sure in fact that there was no easy way to bypass the ORM...

Thanks anyway,
Thierry

2016-09-12 20:53 GMT+02:00 Mike Bayer <mike...@zzzcomputing.com>:

>
>
> On 09/12/2016 09:11 AM, Thierry Florac wrote:
>
>> Hi,
>>
>> I'm working with an Oracle database which, for a reason I can't
>> understand, doesn't want to use indexes when working on a given table
>> (which is quite large) with prepared statements!
>> I know I can use literal columns when running "select" queries (using
>> the "literal_column" function), so that selects performances are good,
>> but is there a way to use such literals while performing updates ou
>> deletes through the ORM using sessions?
>>
>
> Three general stanzas on this:
>
>
> If we are talking about the UPDATE / DELETE statements within the ORM
> flush(), that would be extremely hard to trick the ORM into doing that
> correctly; getting the bound parameters to render as literals would be
> doable but the ORM really wants to batch those statements together, which
> would have to also be turned off and there's no public entryway to that.
>
> Next, brute force via the do_executemany() / do_execute() dialect events
> which give you direct access to the cx_Oracle cursor.  You'd need to break
> the list of parameters sent to executemany() into multiple single execute()
> calls and rewrite all the parameters.   This will get you the effect you
> want at the expense of much ugliness.
>
> Third.  The ORM's UPDATE/DELETE statements only use the primary key in the
> WHERE clause, not any other columns.   if your Oracle DB can't even index
> by primary key on prepared statement that is really something that is
> likely fixable on the database side.   Your employer (or yourself, if this
> is your own company) is likely paying hundreds of thousands of $$ per year
> to be able to use Oracle in the first place, and if your Oracle DB can't
> look up a record by primary key using the index, something is seriously
> broken with it.   Oracle is pretty bad in this area but this one should not
> be the case; I'd advise at least checking on the cx_Oracle list why your
> Oracle DB is bypassing indexes for prepared statements.
>
>
>
>
>
>
>> Best regards,
>> Thierry
>> --
>> http://www.imagesdusport.com -- http://www.ztfy.org
>>
>> --
>> 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
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> <mailto:sqlalchemy@googlegroups.com>.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> 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.
>



-- 
http://www.imagesdusport.com -- http://www.ztfy.org

-- 
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] Using literals in ORM update/delete query

[Thierry Florac]

Hi,

I'm working with an Oracle database which, for a reason I can't understand,
doesn't want to use indexes when working on a given table (which is quite
large) with prepared statements!
I know I can use literal columns when running "select" queries (using the
"literal_column" function), so that selects performances are good, but is
there a way to use such literals while performing updates ou deletes
through the ORM using sessions?

Best regards,
Thierry
-- 
http://www.imagesdusport.com -- http://www.ztfy.org

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

Re: [sqlalchemy] Relationship between two databases

[Thierry Florac]

Hi,

I don't think that the solution can come from SQLAlchemy.
You may have to create a database link between your two databases to be
able to query both of them with a single instruction...

Best regards,
Thierry

2016-02-23 11:43 GMT+01:00 Mehdi :

> Hi
> Is it possible two have a one-to-many or many-to-many relationship between
> two models which they are exist in two different databases?
> I have two oracle dbs on two different machines on lan. so i've created
> two engines like:
> main_engine = create_engine("oracle://user:pass@ip1/sid")
> entry_engine = create_engine("oracle://user:pass@ip2/sid")
>
> Then i've created two different bases for my models and a session:
> meta_data = MetaData()
> DBSession =
> scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
> MainBase = declarative_base(cls=ModelBase, metadata=meta_data)
> EntryBase = declarative_base(cls=ModelBase, metadata=meta_data)
>
> MainBase.metadata.bind = main_engine
> EntryBase.metadata.bind = entry_engine
>
> And let's say i have two simple models like:
> class WeatherStation(MainBase):
> __tablename__ = "weather_stations"
> master_code = Column(NUMBER(10), primary_key=True)
> name = Column(String(50), index=True)
> evaporation_data = relationship("EvaprationData", backref="station")
> and the other one:
> class EvaporationData(Base):
> __tablename__ = "evaporations_data"
> id = Column(NUMBER(30), Sequence("evaporation_data_eid_seq"),
> primary_key=True)
> station_code = Column(NUMBER(10, 0),
> ForeignKey("weather_stations.master_code"))
> * each classes defined in separate files models folder.
>
> Now if i comment out the relationships queries works fine. but with
> relationships between my models, i've got errors like:
> failed to locate a name ("name 'EvaporationData' is not defined"). If this
> is a class name, consider adding this relationship() to the  'measurement.models.weather_station.WeatherStation'> class after both
> dependent classes have been defined.
>
> So is there any solution?
> Thank.
>
> --
> 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.
>



-- 
http://www.imagesdusport.com -- http://www.ztfy.org

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

Re: [sqlalchemy] Recommended way to delete record given instance?

[Thierry Florac]

Hi !

Did you try "session.delete(myrecord)" ?

Best regards,
Thierry


2016-02-06 5:16 GMT+01:00 Alex Hall :

> Hello all,
> Another basic question: given an instance of a record, can I somehow
> delete the record from a table? The longer story is this.
>
> I have my app, which lists tables on the left and the selected table's
> rows on the right. Adding and editing rows both now work correctly (though
> I have yet to do serious validation--thanks for all your thoughts on that,
> by the way). Now I just need to get deletion working. However, because the
> user could be looking at any table, I can't do
> table.query.filter(id==x).delete()
> because I can't know what field to use as the search field in that query.
> One table might have an ID field, another something different. I do,
> however, have the entire object representing the row selected for deletion.
> Could I somehow issue a delete statement using that whole object, and know
> that this will work for any table? I hope I'm making sense--it's been a
> long day. Thanks!
>
>
> --
> Alex Hall
> Automatic Distributors, IT Department
> ah...@autodist.com
>
> --
> 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.
>



-- 
http://www.imagesdusport.com -- http://www.ztfy.org

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

Re: [sqlalchemy] SQL join between two tables from two databases

[Thierry Florac]

What database server do you use?

2015-01-17 0:49 GMT+01:00 Brian Glogower bglogo...@ifwe.co:

 Hi all,

 I am trying to do a join between two tables, each residing on a separate
 databases.

 Here is the table info for both. I have removed extraneous columns from
 each table.

 Table A from DB 1:

 class Host(Base):
 __tablename__ = 'hosts'
 __table_args__ = {'mysql_engine': 'InnoDB'}

 id = Column(u'HostID', INTEGER(), primary_key=True)
 hostname = Column(String(length=30))

 Table B from DB 2:

 class ssh_host_keys(Base):
 __tablename__ = 'ssh_host_keys'
 __table_args__ = {'mysql_engine': 'InnoDB'}

 hostname = Column(VARCHAR(30), primary_key=True)
 sha256 = Column(CHAR(64))

 I would like to use the sqlalchemy orm to do something like the following
 query:

 SELECT hostname, sha256 FROM hosts LEFT JOIN ssh_host_keys ON
 ssh_host_keys.hostname == hosts.hostname

 I did some searching and did find
 https://www.mail-archive.com/sqlalchemy@googlegroups.com/msg14445.html,
 but there wasn't enough details for me to get it working (I am a sqlalchemy
 novice).

 Is what I want to do even possible with sqlalchemy?

 Thanks,
 Brian

 --
 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 http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.




-- 
http://www.imagesdusport.com -- http://www.ztfy.org

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Closing pool connections

[Thierry Florac]

Hi,

I have a multi-threaded web application using SQLAlchemy connections pool.
As soon as a connection is opened, it's maintained opened in the pool even
if it's not used anymore. On a long run, this can consume too much
unnecessary connections and database server resources.

So what I'm actually looking for is a way to close a given connection which
is returned to the pool if it wasn't used for a given amount of time...

Any idea ?

Best regards,
Thierry
-- 
http://www.imagesdusport.com -- http://www.ztfy.org

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Closing pool connections

[Thierry Florac]

Hi Michael,

My first problem is that I have to deal with Oracle instead of PostgreSQL,
so using PGBouncer is not an option :-(
Moreover, what I need is not to be able to create a new connection each
time it's accessed from the pool, but to be able to close a connection if
it's unused for a given time. Because I have several processes, each
process having several threads with a connection pool maintaining
connections to several database schemas; in the end that means a lot of
connections, most being unfrequently used!
As you say, that probably means creating a new thread to monitor unused
connection. Do you think that using pool events to monitor a pool's
connections could be a good starting point?

Best regards,
Thierry


2014-09-25 18:04 GMT+02:00 Michael Bayer mike...@zzzcomputing.com:


 On Sep 25, 2014, at 4:33 AM, Thierry Florac tflo...@gmail.com wrote:

  Hi,
 
  I have a multi-threaded web application using SQLAlchemy connections
 pool.
  As soon as a connection is opened, it's maintained opened in the pool
 even if it's not used anymore. On a long run, this can consume too much
 unnecessary connections and database server resources.
 
  So what I'm actually looking for is a way to close a given connection
 which is returned to the pool if it wasn't used for a given amount of
 time...


 OK well first we assume that you do want pooling in the first place.   If
 you just don't want any, you'd use NullPool. So assuming you do want
 pooling, the next thing that resembles what you describe, but I'm guessing
 still is not what you want, is the pool_recycle setting, which will prevent
 a connection that is older than N seconds from being used.  This recycle
 occurs when the connection is to be fetched; if it is past the expiration
 time, it is closed and replaced with a new one.  However the connection
 stays in the pool until the pool is accessed.

 So the final option is, you want the connection returned to the pool while
 the pool is idle.   The challenge there is that nothing is happening in the
 app to make this happen, which implies a background thread or other
 asynchronous task system, so you'd have to roll that yourself.

 Overall if you have more detailed pooling needs the suggestion to use
 PGBouncer is probably a good one.  If it were me, I'd just use a low pool
 size, just have 5 connections hanging around with a higher overflow.


 --
 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 http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.




-- 
http://www.imagesdusport.com -- http://www.ztfy.org

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Oracle index not used on SQLAlchemy prepared statement

[Thierry Florac]

Hi Michael,
We already tried to explain the Oracle query execution plan with our DBA.
The result is that the Oracle server prefers to make a full table scan
instead of using the index; the reason is still indeterminate (the database
statistics are up to date), we checked arguments data type and as
previously said the equivalent SQL request executed without prepared
statement is OK.
Of course I could give a raw SQL to SA but that is in the opposite
philosophy of what an ORM should be used, no?
I think you talked somewhere else of an hidden feature which allows SA to
generate literal SQL queries. Even if only a small set or arguments data
types are supported, how could I use it?

Best regards,
Thierry


2014-05-13 0:37 GMT+02:00 Michael Bayer mike...@zzzcomputing.com:


 On May 12, 2014, at 6:12 PM, Thierry Florac tflo...@gmail.com wrote:

  Hi,
 
  I use Python and SQLAlchemy to access an Oracle 11 database. As far as I
 think, SQLAlchemy always use prepared statements.

 SQLAlchemy has no control over this as the DBAPI has no prepared statement
 system exposed publicly.  This is entirely dependent on the choices that
 cx_Oracle makes internally.  SQLAlchemy's interaction with the DBAPI is
 exactly:

 cursor = connection.cursor()
 cursor.set_input_sizes( some params )   # we normally don't use this but
 cx_oracle needs this for some datatypes, see
 http://cx-oracle.sourceforge.net/html/cursor.html#Cursor.setinputsizes
 cursor.execute(your SQL statement, { your parameters })

 everything else is how cx_Oracle choices to deal with these inputs at the
 OCI level and there's a lot that can go wrong, most of which is not
 something SQLAlchemy can anticipate or have control over.

 
  On a huge table (4 millions records), correctly indexed, SQLAlchemy
 filters queries doesn't use the index, so doing a full table scan is very
 slow ; using the same SQL code in a raw SQL editor (SQLplus) make Oracle
 use the index with good performances.

 SQLAlchemy's job here is to send a string plus parameters to the cx_Oracle
 execute() method.  SQLAlchemy also calls cx_oracle's setinputsizes() as
 well in order to deal with some parameter conversion issues we've observed
 with cx_Oracle; these may be involved with the choices that cx_Oracle makes
 which could affect query planning.

 Turn on echo=True on your create_engine() and you will see the full
 conversation with the DBAPI.The issue here is with the choices that
 cx_Oracle makes, not that of SQLAlchemy so you should distill your queries
 to a raw cx_Oracle case, using a usage as illustrated above and possibly in
 conjunction with EXPLAIN, to further determine the cause of the issue.


 --
 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 http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.




-- 
http://www.imagesdusport.com -- http://www.ztfy.org

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Oracle index not used on SQLAlchemy prepared statement

[Thierry Florac]

Hi Michael,
I just tried using literal_column function and now performances are as
good as they can :-/
As my problem is only limited to a single use case, using this method, even
if not perfect (arguments must be carefully verified!), if far better than
a global option.

Many thanks,
Thierry



2014-05-13 12:20 GMT+02:00 Michael Bayer mike...@zzzcomputing.com:


 On May 13, 2014, at 4:21 AM, Thierry Florac tflo...@gmail.com wrote:

 Hi Michael,
 We already tried to explain the Oracle query execution plan with our DBA.
 The result is that the Oracle server prefers to make a full table scan
 instead of using the index; the reason is still indeterminate (the database
 statistics are up to date), we checked arguments data type and as
 previously said the equivalent SQL request executed without prepared
 statement is OK.
 Of course I could give a raw SQL to SA but that is in the opposite
 philosophy of what an ORM should be used, no?
 I think you talked somewhere else of an hidden feature which allows SA
 to generate literal SQL queries. Even if only a small set or arguments
 data types are supported, how could I use it?


 well there’s another thread regarding pyodbc/MSSQL right now regarding the
 same thing.  For whatever reason I forgot the most obvious solution which
 is literal_column():

 q = s.query(Something).filter(Something.foo == literal_column(“‘my
 value’”))

 or text():

 q = s.query(Something).filter(Something.foo == text(“‘my value’”))

 considering that this is probably a small subset of queries/expressions
 causing this issue.

 As i mentioned in the other thread, supporting the feature by which SQLA
 “literalizes” queries en masse would expose a large security surface which
 the project cannot support, even though the mechanism for this feature is
 present in a non-public way.Using explicit “literal_column()” or
 “text()” on just those queries/values that are problematic is much safer.




 Best regards,
 Thierry


 2014-05-13 0:37 GMT+02:00 Michael Bayer mike...@zzzcomputing.com:


 On May 12, 2014, at 6:12 PM, Thierry Florac tflo...@gmail.com wrote:

  Hi,
 
  I use Python and SQLAlchemy to access an Oracle 11 database. As far as
 I think, SQLAlchemy always use prepared statements.

 SQLAlchemy has no control over this as the DBAPI has no prepared
 statement system exposed publicly.  This is entirely dependent on the
 choices that cx_Oracle makes internally.  SQLAlchemy's interaction with the
 DBAPI is exactly:

 cursor = connection.cursor()
 cursor.set_input_sizes( some params )   # we normally don't use this
 but cx_oracle needs this for some datatypes, see
 http://cx-oracle.sourceforge.net/html/cursor.html#Cursor.setinputsizes
 cursor.execute(your SQL statement, { your parameters })

 everything else is how cx_Oracle choices to deal with these inputs at the
 OCI level and there's a lot that can go wrong, most of which is not
 something SQLAlchemy can anticipate or have control over.

 
  On a huge table (4 millions records), correctly indexed, SQLAlchemy
 filters queries doesn't use the index, so doing a full table scan is very
 slow ; using the same SQL code in a raw SQL editor (SQLplus) make Oracle
 use the index with good performances.

 SQLAlchemy's job here is to send a string plus parameters to the
 cx_Oracle execute() method.  SQLAlchemy also calls cx_oracle's
 setinputsizes() as well in order to deal with some parameter conversion
 issues we've observed with cx_Oracle; these may be involved with the
 choices that cx_Oracle makes which could affect query planning.

 Turn on echo=True on your create_engine() and you will see the full
 conversation with the DBAPI.The issue here is with the choices that
 cx_Oracle makes, not that of SQLAlchemy so you should distill your queries
 to a raw cx_Oracle case, using a usage as illustrated above and possibly in
 conjunction with EXPLAIN, to further determine the cause of the issue.


 --
 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 http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.




 --
 http://www.imagesdusport.com -- http://www.ztfy.org

 --
 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 http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.


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

[sqlalchemy] Oracle index not used on SQLAlchemy prepared statement

[Thierry Florac]

Hi,

I use Python and SQLAlchemy to access an Oracle 11 database. As far as I
think, SQLAlchemy always use prepared statements.

On a huge table (4 millions records), correctly indexed, SQLAlchemy filters
queries doesn't use the index, so doing a full table scan is very slow ;
using the same SQL code in a raw SQL editor (SQLplus) make Oracle use the
index with good performances.

We tried to add +index hints on requests, without effect on Oracle
execution path which still doesn't want to use the index.

Any idea ? Is it possible to really force Oracle to use an index, or to
make SQLAlchemy to not use prepared statements ??

Best regards,
Thierry

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] [ANN] SQLTap - SQL profiling and introspection for SQLAlchemy applications

[Thierry Florac]

That sounds really great.
Simple question : is there any way to use SQLTap in a multi-processes WSGI
environment ??

Best regards,
Thierry


2014-03-10 5:28 GMT+01:00 Michael Bayer mike...@zzzcomputing.com:

 hey that looks pretty nice, ill give it a shoutout.


 On Mar 9, 2014, at 9:21 PM, Alan Shreve a...@inconshreveable.com wrote:

 A few years ago, I built SQLTap, a simple library to hook into SQLAlchemy
 and pull out statistics and information on the queries you ran. This last
 couple days I’ve overhauled it an updated it to make it more useful!

 You basically just start the profiler and then it can dump out nice
 browsable HTML reports like this:

 http://sqltap.inconshreveable.com/_images/sqltap-report-example.png

 SQLTap helps you answer questions like:

 What queries is my application running?
 How long do they take on average? At maximum? At minimum? At median?
 What sequences of function calls lead to each invocation of a query? Where
 in my source code is the query executed?

 The latest updates and improvements make up sqltap version 0.3 and include:

 - The report now has a sexy new HTML UI based on bootstrap3.
 - You can add sqltap to any WSGI application and get a live, updating
 dashboard of all the queries going through an application.
 - The WSGI integration has controls to enable/disable the profiling at any
 time so you can even include it in production applications for selective
 profiling.
 - The library’s API is now greatly improved, allowing you to do your own
 real-time collection of statistics as well as allowing you to create
 individual profilers for different engines instead of forcing a global
 profiler.
 - Updated/improved documentation

 Install with:

 pip install sqltap

 And in your code:

 import sqltap
 profiler = sqltap.start()

 # sometime later after queries have been run
 sqltap.report(profiler.collect(), “report.html”)

 Links for reference:
 Code and some documentation (star it!):
 https://github.com/inconshreveable/sqltap
 Documentation: http://sqltap.inconshreveable.com/
 On PyPI: https://pypi.python.org/pypi/sqltap

 Enjoy!

 - alan

 --
 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 http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.


  --
 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 http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.




-- 
http://www.imagesdusport.com -- http://www.ztfy.org

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Anybody have twophase/zope.sqlalchemy/MySQL working?

[Thierry Florac]

Hi,
I'm using two-phase transactions with ZODB, PostgreSQL and Oracle databases
connected with SQLAlchemy without problem.
I'm not using native zope.sqlalchemy package, but another package called
ztfy.alchemy that I've built (based on zc.alchemy and zope.sqlalchemy), and
I didn't tried with MySQL.
Maybe you can have a look if that can help... =
https://pypi.python.org/pypi/ztfy.alchemy

Best regards,
Thierry


2013/12/11 Jeff Dairiki dair...@dairiki.org

 I just noticed my connection pool isn't pooling.  Whenever a connection
 which has been used only for reading is returned to the pool an
 XAER_RMFAIL operational error is returned in response to the pools
 rollback-on-return.  (This causes the connection to
 be closed rather than returned to the pool.  So far I haven't noticed
 any other deleterious effects.)

 Here's a simple test script.  I've tried this with 0.9b1 and 0.8.4.

   import sqlalchemy as sa
   from zope.sqlalchemy import ZopeTransactionExtension
   import transaction

   engine = sa.create_engine('mysql://guest@furry/test',
 echo='debug',
 echo_pool='debug')

   Sess = sa.orm.sessionmaker(bind=engine, twophase=True,
  extension=ZopeTransactionExtension())
   sess = Sess()
   sess.query(sa.null()).scalar()
   transaction.commit()

 Log output looks like (edited for brevity):

   [...]
   INFO sqlalchemy.engine.base.Engine BEGIN TWOPHASE (implicit)
   INFO sqlalchemy.engine.base.Engine XA BEGIN %s
   INFO sqlalchemy.engine.base.Engine
 ('_sa_ab2538f3cc26258e0a30bfd407d0d687',)
   INFO sqlalchemy.engine.base.Engine SELECT NULL AS anon_1
   INFO sqlalchemy.engine.base.Engine ()
   DEBUG sqlalchemy.engine.base.Engine Col ('anon_1',)
   DEBUG sqlalchemy.engine.base.Engine Row (None,)
   DEBUG sqlalchemy.pool.QueuePool Connection _mysql.connection open to
 'furry' at 2093320 being returned to pool
   DEBUG sqlalchemy.pool.QueuePool Connection _mysql.connection open to
 'furry' at 2093320 rollback-on-return
   INFO sqlalchemy.pool.QueuePool Invalidate connection _mysql.connection
 open to 'furry' at 2093320 (reason: OperationalError:(1399, 'XAER_RMFAIL:
 The command cannot be executed when global transaction is in the  ACTIVE
 state'))
   DEBUG sqlalchemy.pool.QueuePool Closing connection _mysql.connection
 open to 'furry' at 2093320

 So it looks like the zope.sqla data manager is not managing to commit
 the transaction.

 Things work okay with twophase=False.

 Anybody have a hint?

 --
 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 http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/groups/opt_out.




-- 
http://www.imagesdusport.com -- http://www.ztfy.org

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] SQLAlchemy transaction problem

[Thierry Florac]

Hi Michael,

I applied your patch on my current SA 0.7.3 without any problem, and added
a few traces. Everything seems to work perfectly!

Here is the first output with updates:

  do_begin_twophase
  do_prepare_twophase = True
  do_commit_twophase
oci_prepared = True
do_commit


and without real updates:

  do_begin_twophase
  do_prepare_twophase = False
  do_commit_twophase
oci_prepared = False
no commit !


That's great, for me it works like a charm!
Do you think that this patch could be backported to a new 0.7.x release of
SA ?

Best regards,
Thierry


2012/11/21 Michael Bayer mike...@zzzcomputing.com

 great.  can you try the attached patch please (latest 0.8 tip), which will
 not call commit if the prepare returned false.   I'm not sure if this
 is complete though, if we should be doing a rollback afterwards, or what.


 not sure if I can get my local oracle XE to do two phase for real, that
 would help a lot.  this patch would need quite a few tests.




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




 On Nov 20, 2012, at 6:33 PM, Thierry Florac wrote:

 Hi Michael,

 Following your guidelines, this is the first test I made against cx_Oracle
 :

  conn = cx_Oracle.connect('scott','tiger','localhost:1521/ORCL',
 twophase=True)
  id = random.randint(0, 2 ** 128)
  xid = (0x1234, %032x % id, %032x % 9)
  conn.begin(*xid)
  conn.prepare()
 False
  conn.commit()
 Traceback (most recent call last):
   File console, line 1, in module
 DatabaseError: ORA-24756: la transaction n'existe pas


 As you can see, the prepare method returns False, meaning that the
 transaction was *NOT* prepared.
 If I add any update statement, everything is OK.

 Oracle 11g documentation seems to indicate that the prepare call can
 return three status :

 PreparedData on the node has been modified by a statement in the
 distributed transaction, and the node was successfully prepared. Read-onlyNo
 data on the node has been, or can be, modified (only queried), so no
 preparation is necessary. AbortThe node cannot successfully prepare.

 cx_Oracle returns False when there is nothing available to prepare, so
 probably at least in case 2, case for which commit shouldn't be done to
 avoid ORA-24756 error.
 So it's behaviour seems conform with Oracle OCI documentation...

 So my last question is : in the context of an SA session, who is
 responsible of the prepare/commit statements, so that commit is not called
 if prepared was not OK ?

 Best regards,
 Thierry


 2012/11/20 Michael Bayer mike...@zzzcomputing.com

 Googling for the ORA code yields the usual tautological answers that say
 nothing.

 The first step here would be to isolate the issue in a simple test case
 with SQLAlchemy only, and then cx_oracle where I suspect the bug lies.

 The Session will not create a transaction at all, if no SQL is emitted.
 So that would mean SQL is definitely emitted.  Can you confirm this ? (look
 at your SQL logging).

 Then, an initial test would be like this:

 s = Session(twophase=True, bind=my_oracle_engine)
 s.execute(select 1 from dual)
 s.commit()


 Here's my test, against Postgresql:

 from sqlalchemy.orm import Session
 from sqlalchemy import create_engine

 e = create_engine('postgresql://scott:tiger@localhost/test', echo=True)

 s = Session(e, twophase=True)
 s.execute(select 1)
 s.commit()

 the output:

 2012-11-20 10:53:06,894 INFO sqlalchemy.engine.base.Engine select
 version()
 2012-11-20 10:53:06,894 INFO sqlalchemy.engine.base.Engine {}
 2012-11-20 10:53:06,897 INFO sqlalchemy.engine.base.Engine select
 current_schema()
 2012-11-20 10:53:06,897 INFO sqlalchemy.engine.base.Engine {}
 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine select 1
 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine {}
 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine PREPARE
 TRANSACTION '_sa_ddca4886b1f5db002e83058341de2609'
 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine {}
 2012-11-20 10:53:06,908 INFO sqlalchemy.engine.base.Engine COMMIT
 PREPARED '_sa_ddca4886b1f5db002e83058341de2609'
 2012-11-20 10:53:06,908 INFO sqlalchemy.engine.base.Engine {}
 2012-11-20 10:53:06,909 INFO sqlalchemy.engine.base.Engine BEGIN
 2012-11-20 10:53:06,909 INFO sqlalchemy.engine.base.Engine {}

 which I will explain, first there's a BEGIN TWOPHASE that unfortunately
 is not logged, I have just committed a fix for that in tip.  Then there's
 the PREPARE and the COMMIT - but this is Postgresql's syntax.  The Oracle
 implementation only uses an XID when it calls cx_oracle's begin() method,
 and then I'd assume its up to cx_oracle to do the rest.So if there's a
 bug anywhere, it's probably in cx_oracle.   My guess

Re: [sqlalchemy] SQLAlchemy transaction problem

[Thierry Florac]

 is greatly
welcome...

Best regards,
Thierry



2012/11/20 Michael Bayer mike...@zzzcomputing.com


 On Nov 19, 2012, at 6:23 PM, Thierry Florac wrote:

  As far as I can understand it, I'm globally OK with you but... probably
  not completely :-\
  I agree with the fact that SQLAlchemy is not the only package which
  takes part into the global transaction, as SA's session is handled by a
  Zope transaction manager. And the whole mechanism works globally
  perfectly.
  What I don't understand is that when:
  - I open a session,
  - I load a set of objects from the database,
  - I update these objects, setting an attribute with it's current value
(I know, said like that it can seems a little silly!),

 no problem with that, it produces an event which will cause the flush to
 do something, but no UPDATE will be emitted in most cases if no net change

  - then when the transaction is committed, SA probably knows that the
objects were not really modified, because no UPDATE instruction is
executed;

 yup

  but the SA two-phases transaction is begun, prepared and
aborts on commit because of the given Oracle error.

 the first news to me here is that two-phase commit works with cx_oracle
 and SQLAlchemy.   I've never seen it work or been able to test it.

 I'm also not familiar with this behavior.   Oracle won't allow PREPARE ?
 Can I see stack trace + full ORA message ?  the error code is especially
 significant, had no idea this was an Oracle error.

 Also, is this two-phase as implemented by SQLAlchemy itself ?   You have
 twophase=True with sessionmaker() ?  I hope zope.sqlalchemy isn't
 hardcoding that..


  - but if I add a check to update objects attribute only if the new
value is different from the actual one, the SA transaction is begun
on my first request, but not prepared nor committed, and no error is
raised,

 doesn't sound right.   If SA starts a transaction, it will also be
 PREPARE/COMMITTED because zope.sqlalchemy is calling commit() on the
 Session.You can't be just leaving that new transaction hanging open.
 Or it might be rolled back, but its one or the other.





-- 
http://www.imagesdusport.com -- http://www.ztfy.org

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

Re: [sqlalchemy] SQLAlchemy transaction problem

[Thierry Florac]

Hi Michael,

Following your guidelines, this is the first test I made against cx_Oracle :

 conn = cx_Oracle.connect('scott','tiger','localhost:1521/ORCL',
twophase=True)
 id = random.randint(0, 2 ** 128)
 xid = (0x1234, %032x % id, %032x % 9)
 conn.begin(*xid)
 conn.prepare()
False
 conn.commit()
Traceback (most recent call last):
  File console, line 1, in module
DatabaseError: ORA-24756: la transaction n'existe pas


As you can see, the prepare method returns False, meaning that the
transaction was *NOT* prepared.
If I add any update statement, everything is OK.

Oracle 11g documentation seems to indicate that the prepare call can
return three status :

PreparedData on the node has been modified by a statement in the
distributed transaction, and the node was successfully prepared.Read-onlyNo
data on the node has been, or can be, modified (only queried), so no
preparation is necessary.AbortThe node cannot successfully prepare.

cx_Oracle returns False when there is nothing available to prepare, so
probably at least in case 2, case for which commit shouldn't be done to
avoid ORA-24756 error.
So it's behaviour seems conform with Oracle OCI documentation...

So my last question is : in the context of an SA session, who is
responsible of the prepare/commit statements, so that commit is not called
if prepared was not OK ?

Best regards,
Thierry


2012/11/20 Michael Bayer mike...@zzzcomputing.com

 Googling for the ORA code yields the usual tautological answers that say
 nothing.

 The first step here would be to isolate the issue in a simple test case
 with SQLAlchemy only, and then cx_oracle where I suspect the bug lies.

 The Session will not create a transaction at all, if no SQL is emitted.
 So that would mean SQL is definitely emitted.  Can you confirm this ? (look
 at your SQL logging).

 Then, an initial test would be like this:

 s = Session(twophase=True, bind=my_oracle_engine)
 s.execute(select 1 from dual)
 s.commit()


 Here's my test, against Postgresql:

 from sqlalchemy.orm import Session
 from sqlalchemy import create_engine

 e = create_engine('postgresql://scott:tiger@localhost/test', echo=True)

 s = Session(e, twophase=True)
 s.execute(select 1)
 s.commit()

 the output:

 2012-11-20 10:53:06,894 INFO sqlalchemy.engine.base.Engine select version()
 2012-11-20 10:53:06,894 INFO sqlalchemy.engine.base.Engine {}
 2012-11-20 10:53:06,897 INFO sqlalchemy.engine.base.Engine select
 current_schema()
 2012-11-20 10:53:06,897 INFO sqlalchemy.engine.base.Engine {}
 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine select 1
 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine {}
 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine PREPARE
 TRANSACTION '_sa_ddca4886b1f5db002e83058341de2609'
 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine {}
 2012-11-20 10:53:06,908 INFO sqlalchemy.engine.base.Engine COMMIT PREPARED
 '_sa_ddca4886b1f5db002e83058341de2609'
 2012-11-20 10:53:06,908 INFO sqlalchemy.engine.base.Engine {}
 2012-11-20 10:53:06,909 INFO sqlalchemy.engine.base.Engine BEGIN
 2012-11-20 10:53:06,909 INFO sqlalchemy.engine.base.Engine {}

 which I will explain, first there's a BEGIN TWOPHASE that unfortunately
 is not logged, I have just committed a fix for that in tip.  Then there's
 the PREPARE and the COMMIT - but this is Postgresql's syntax.  The Oracle
 implementation only uses an XID when it calls cx_oracle's begin() method,
 and then I'd assume its up to cx_oracle to do the rest.So if there's a
 bug anywhere, it's probably in cx_oracle.   My guess is that cx_oracle
 itself is not emitting the BEGIN unless it sees DML (INSERT, UPDATE,
 DELETE).The BEGIN you see afterwards is a tweak that's specific to
 the psycopg2 dialect when twophase is used so that it can run the next
 transaction.

 A cx_oracle script would be:

 import cx_Oracle
 import random

 conn = cx_Oracle.connect(...)

  id = random.randint(0, 2 ** 128)
 xid = (0x1234, %032x % id, %032x % 9)  # no idea where i got this
 from, probably cx_oracle
 conn.begin(*xid)
 conn.prepare()
 conn.commit()


 if that fails, but then if you were to insert in the middle:

 cursor = conn.cursor()
 cursor.execute(INSERT INTO sometable (q) VALUES (3))

 and then it works, there you go.   cx_oracle's bad behavior.

 All of the two phase implementations required weird hacks to get them to
 work, as this feature is exceedingly rare.   But for cx_Oracle, I never saw
 it work completely hence we haven't been able to come up with workarounds
 for existing issues.



 On Nov 20, 2012, at 4:28 AM, Thierry Florac wrote:

 Hi Michael,

 This is a complete traceback produced by removing my checking code against
 attribute modification.
 Original code is then as simple as this:

 @ajax.handler
 def updateOrderMenuItemBO(self):
 session = getSession(SESSION_NAME)
 ids = [ int(id) for id in self.request.form.get('ids') ]
 for menu in
 session.query(MenuItemBO).filter(MenuItemBO.id.in_

Re: [sqlalchemy] SQLAlchemy transaction problem

[Thierry Florac]

Le Mon, 19 Nov 2012 10:23:14 -0500,
  Michael Bayer mike...@zzzcomputing.com a écrit:

 On Nov 19, 2012, at 8:10 AM, Thierry Florac wrote:
 
  Hi,
  
  I'm using SQLALchemy (currently 0.7.3 in this project, also tested
  with 0.7.9) with cx_Oracle-5.1.1 in a Zope3 project. SQLAlchemy
  transactions are handled in two-phase with ZopeTransactionExtension.
  
  My problem is quite simple : in a form, I can order several items
  which updates an index attribute. Problem : if no ordering is
  done and save button is clicked, I get the given transaction
  does not exist error. If I just add a control in my form handling
  method to set a new index value only if it was modified (instead of
  setting the attribute with the same value), everything is fine.
  
  So my question is : is it normal that setting objects attributes
  without changing their actual value generates this error ?
 
 That's not a SQLAlchemy error.   SQLAlchemy's Session has no linkage
 between its notion of a transaction and whether or not any state was
 changed.  If you're working with a Session that hasn't accessed the
 database, there won't be any actual database transaction present
 until that happens.  But that isn't an error condition.
 
 You might want to check with the zope.transaction folks as well as
 with whatever web framework you're using.

As far as I can understand it, I'm globally OK with you but... probably
not completely :-\
I agree with the fact that SQLAlchemy is not the only package which
takes part into the global transaction, as SA's session is handled by a
Zope transaction manager. And the whole mechanism works globally
perfectly.
What I don't understand is that when:
 - I open a session,
 - I load a set of objects from the database,
 - I update these objects, setting an attribute with it's current value
   (I know, said like that it can seems a little silly!),
 - then when the transaction is committed, SA probably knows that the
   objects were not really modified, because no UPDATE instruction is
   executed; but the SA two-phases transaction is begun, prepared and
   aborts on commit because of the given Oracle error.
 - but if I add a check to update objects attribute only if the new
   value is different from the actual one, the SA transaction is begun
   on my first request, but not prepared nor committed, and no error is
   raised,
 - and of course, if I really modify any object of my set, the
   transaction is begun, prepared and committed without any error...

Any idea ?

Best regards,
Thierry

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

Re: [sqlalchemy] Foreign key between single field and remote composite key

[Thierry Florac]

Hi Michael,

Le samedi 16 juin 2012 17:54:21 UTC+2, Michael Bayer a écrit :


 On Jun 16, 2012, at 11:41 AM, Michael Bayer wrote: 

  It's not a basic question at all as this is a rare edge case, and it's 
 not a foreign key by definition.   You need to relate the two tables 
 based on a SQL function, in this case a concatenation.   In some cases this 
 can be tricky, and there's improvements in 0.8 to address that, though in 
 this case it seems to work without too much difficulty: 

 ha ha, except that example was running in 0.8 :).Prior to 0.8 you need 
 to use an undocumented attribute _local_remote_pairs.   Undocumented 
 because, it was never the best way to do this and in 0.8 it isn't needed 
 anymore.  But for now: 

 from sqlalchemy import * 
 from sqlalchemy.orm import * 
 from sqlalchemy.ext.declarative import declarative_base 

 Base= declarative_base() 

 class A(Base): 
 __tablename__ = a 

 id1 = Column(String, primary_key=True) 
 id2 = Column(String, primary_key=True) 

 class B(Base): 
 __tablename__ = b 
 id = Column(Integer, primary_key=True) 
 a_id = Column(String) 

 A.bs = relationship(B, 
 primaryjoin=B.a_id == A.id1 + A.id2, 
 foreign_keys=B.a_id, 
 _local_remote_pairs=[(A.__table__.c.id1, B.__table__.c.a_id), 
 (A.__table__.c.id2, B.__table__.c.a_id)], 
 viewonly=True) 

 e = create_engine(sqlite://, echo=True) 
 Base.metadata.create_all(e) 
 s = Session(e) 

 s.add_all([ 
 A(id1=x, id2=y, bs=[ 
 B(a_id=xy), 
 B(a_id=xy) 
 ]), 
 A(id1=q, id2=p, bs=[ 
 B(a_id=qp) 
 ]) 
 ]) 

 s.commit() 

 print s.query(A).first().bs 

 for a in s.query(A).options(joinedload(A.bs)): 
 print a.bs


I tried your code this morning and, as is, it works !
But I still have a problem to define a back reference. If creating the 
relation as:

Commune.regions = relationship(Region,
   primaryjoin=Region.cheflieu == Commune.dep 
+ Commune.com,
   foreign_keys=Region.cheflieu,
  
 _local_remote_pairs=[(Commune.__table__.c.dep, 
Region.__table__.c.cheflieu),

(Commune.__table__.c.com, Region.__table__.c.cheflieu)],
   viewonly=True,
   backref=backref('commune', uselist=False))


I get an error if trying to access the entity:

reg = session.query(Region).get(7)
Traceback (most recent call last):
  File console, line 1, in module
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/session.py,
 
line 969, in query
return self._query_cls(entities, self, **kwargs)
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
 
line 107, in __init__
self._set_entities(entities)
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
 
line 116, in _set_entities
self._setup_aliasizers(self._entities)
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
 
line 131, in _setup_aliasizers
_entity_info(entity)
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/util.py,
 
line 550, in _entity_info
mapperlib.configure_mappers()
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py,
 
line 2861, in configure_mappers
mapper._post_configure_properties()
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py,
 
line 1166, in _post_configure_properties
prop.init()
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/interfaces.py,
 
line 128, in init
self.do_init()
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py,
 
line 919, in do_init
self._generate_backref()
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py,
 
line 1418, in _generate_backref
mapper._configure_property(backref_key, relationship)
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py,
 
line 1145, in _configure_property
prop.init()
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/interfaces.py,
 
line 128, in init
self.do_init()
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py,
 
line 916, in do_init
self._determine_direction()
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py,
 
line 1228, in _determine_direction
elif self._refers_to_parent_table():
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py,
 
line 1458, in _refers_to_parent_table
pt.is_derived_from(c.table) and \
AttributeError: '_BinaryExpression' object has no attribute 'table'

 
So what 

Re: [sqlalchemy] Foreign key between single field and remote composite key

[Thierry Florac]

Hi,

Le lundi 18 juin 2012 12:07:46 UTC+2, Thierry Florac a écrit :


 Hi Michael,

 Le samedi 16 juin 2012 17:54:21 UTC+2, Michael Bayer a écrit :


 On Jun 16, 2012, at 11:41 AM, Michael Bayer wrote: 

  It's not a basic question at all as this is a rare edge case, and it's 
 not a foreign key by definition.   You need to relate the two tables 
 based on a SQL function, in this case a concatenation.   In some cases this 
 can be tricky, and there's improvements in 0.8 to address that, though in 
 this case it seems to work without too much difficulty: 

 ha ha, except that example was running in 0.8 :).Prior to 0.8 you 
 need to use an undocumented attribute _local_remote_pairs.   Undocumented 
 because, it was never the best way to do this and in 0.8 it isn't needed 
 anymore.  But for now: 

 from sqlalchemy import * 
 from sqlalchemy.orm import * 
 from sqlalchemy.ext.declarative import declarative_base 

 Base= declarative_base() 

 class A(Base): 
 __tablename__ = a 

 id1 = Column(String, primary_key=True) 
 id2 = Column(String, primary_key=True) 

 class B(Base): 
 __tablename__ = b 
 id = Column(Integer, primary_key=True) 
 a_id = Column(String) 

 A.bs = relationship(B, 
 primaryjoin=B.a_id == A.id1 + A.id2, 
 foreign_keys=B.a_id, 
 _local_remote_pairs=[(A.__table__.c.id1, B.__table__.c.a_id), 
 (A.__table__.c.id2, B.__table__.c.a_id)], 
 viewonly=True) 

 e = create_engine(sqlite://, echo=True) 
 Base.metadata.create_all(e) 
 s = Session(e) 

 s.add_all([ 
 A(id1=x, id2=y, bs=[ 
 B(a_id=xy), 
 B(a_id=xy) 
 ]), 
 A(id1=q, id2=p, bs=[ 
 B(a_id=qp) 
 ]) 
 ]) 

 s.commit() 

 print s.query(A).first().bs 

 for a in s.query(A).options(joinedload(A.bs)): 
 print a.bs


 I tried your code this morning and, as is, it works !
 But I still have a problem to define a back reference. If creating the 
 relation as:

 Commune.regions = relationship(Region,
primaryjoin=Region.cheflieu == Commune.dep 
 + Commune.com,
foreign_keys=Region.cheflieu,
   
  _local_remote_pairs=[(Commune.__table__.c.dep, 
 Region.__table__.c.cheflieu),
 (Commune.__table__.
 c.com, Region.__table__.c.cheflieu)],
viewonly=True,
backref=backref('commune', uselist=False))


 I get an error if trying to access the entity:

 reg = session.query(Region).get(7)
 Traceback (most recent call last):
   File console, line 1, in module
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/session.py,
  
 line 969, in query
 return self._query_cls(entities, self, **kwargs)
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
  
 line 107, in __init__
 self._set_entities(entities)
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
  
 line 116, in _set_entities
 self._setup_aliasizers(self._entities)
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
  
 line 131, in _setup_aliasizers
 _entity_info(entity)
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/util.py,
  
 line 550, in _entity_info
 mapperlib.configure_mappers()
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py,
  
 line 2861, in configure_mappers
 mapper._post_configure_properties()
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py,
  
 line 1166, in _post_configure_properties
 prop.init()
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/interfaces.py,
  
 line 128, in init
 self.do_init()
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py,
  
 line 919, in do_init
 self._generate_backref()
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py,
  
 line 1418, in _generate_backref
 mapper._configure_property(backref_key, relationship)
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py,
  
 line 1145, in _configure_property
 prop.init()
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/interfaces.py,
  
 line 128, in init
 self.do_init()
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py,
  
 line 916, in do_init
 self._determine_direction()
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/properties.py,
  
 line 1228, in _determine_direction
 elif self._refers_to_parent_table():
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm

[sqlalchemy] Foreign key between single field and remote composite key

[Thierry Florac]

Hi,

I have a (probably) quite basic foreign key problem.

My parent table has a composite primary key based on two (fixed width)
string attributes; in my child tables, this key is stored in a single
attribute which stores the concatenation of the two parent key
attributes.

So, is there any easy way to build an SQLAlchemy relation between these
two tables ?

Best regards,
Thierry

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

Re: [sqlalchemy] Foreign key between single field and remote composite key

[Thierry Florac]

Hi Michael,

Do you take a break sometimes during the week-end? ;-)

Well, this seems quite fine, and a very quick answer as usual!!!
I also agree that this is a bad model, but data is provided by an
external partner and I can't update it :-(
I was just sure that SA was able to handle it anyway :-)

I'll try to test this quickly, but not before monday morning in
fact when being back at work :-/

Many thanks,
Thierry


Le Sat, 16 Jun 2012 11:41:29 -0400, Michael Bayer
mike...@zzzcomputing.com a �crit:

 
 It's not a basic question at all as this is a rare edge case, and
 it's not a foreign key by definition.   You need to relate the two
 tables based on a SQL function, in this case a concatenation.   In
 some cases this can be tricky, and there's improvements in 0.8 to
 address that, though in this case it seems to work without too much
 difficulty:
 
 from sqlalchemy import *
 from sqlalchemy.orm import *
 from sqlalchemy.ext.declarative import declarative_base
 
 Base= declarative_base()
 
 class A(Base):
 __tablename__ = a
 
 id1 = Column(String, primary_key=True)
 id2 = Column(String, primary_key=True)
 bs = relationship(B, 
 primaryjoin=B.a_id == A.id1 + A.id2,
 foreign_keys=B.a_id,
 viewonly=True)
 
 class B(Base):
 __tablename__ = b
 id = Column(Integer, primary_key=True)
 a_id = Column(String)
 
 e = create_engine(sqlite://, echo=True)
 Base.metadata.create_all(e)
 s = Session(e)
 
 s.add_all([
 A(id1=x, id2=y, bs=[
 B(a_id=xy),
 B(a_id=xy)
 ]),
 A(id1=q, id2=p, bs=[
 B(a_id=qp)
 ])
 ])
 
 s.commit()
 
 print s.query(A).first().bs
 
 for a in s.query(A).options(joinedload(A.bs)):
 print a.bs
 

-- 
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] Problem with declarative polymorphic inheritance

[Thierry Florac]

Hi,

I'm using SQLAlchemy (0.7.4) with GeoAlchemy to map tables containing 
geographic entities from a PostgreSQL/PostGIS database.

As I have to handle data from several parts of the world, each part using 
it's own geographic projection, my testing model which I'll try to describe 
is based on:
- a parent class (TestIMG), containing common attributes and a shape in 
world-wide WGS84 coordinates system
- for each projection system, a child class, inheriting from TestIMG and 
containing only the shapes in the given projection system.
TestIMG can be seen as an abstract class; discrimination between child 
classes is based on a common SRID attribute.

I've mapped my classes as follow:

class TestIMG(Base):
__tablename__ = 'test_img'
id = Column('id', Integer, primary_key=True)
data = Column('data', Unicode(20))
wgs_shape = GeometryColumn('wgs_shape', Point(srid=4326))
srid = Column('srid', Integer)
__mapper_args__ = { 'concrete': False,
'polymorphic_on': srid }

class TestIMG_france(TestIMG):
__tablename__ = 'test_img_france'
__mapper_args__ = { 'polymorphic_identity': 2154,
'concrete': True }
id = Column('id', Integer, ForeignKey(TestIMG.id), primary_key=True)
shape = GeometryColumn('shape', Point(srid=2154))

class TestIMG_guyane(TestIMG):
__tablename__ = 'test_img_guyane'
__mapper_args__ = { 'polymorphic_identity': 32622,
'concrete': True }
id = Column('id', Integer, ForeignKey(TestIMG.id), primary_key=True)
shape = GeometryColumn('shape', Point(srid=32622))

TestIMG_union = polymorphic_union({ 'test_img_france': 
TestIMG_france.__table__,
'test_img_guyane': 
TestIMG_guyane.__table__ },
  'projection', 'test_img')


When I make a query in a child class, everything is OK; but when I try to 
query the main class, I get an error:

 session.query(TestIMG).all()

Traceback (most recent call last):
  File console, line 1, in module
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
 
line 1947, in all
return list(self)
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
 
line 2178, in instances
rows = [process[0](row, None) for row in fetch]
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py,
 
line 2614, in _instance
return _instance(row, result)
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py,
 
line 2627, in _instance
tuple([row[column] for column in pk_cols])
  File 
/var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
 
line 2654, in _key_fallback
expression._string_or_unprintable(key))
NoSuchColumnError: Could not locate column in row for column 
'test_img_france.id'


I've tried to test several configurations but everything fails until now.
I'll also add that I **have to** use declarative form of SQLAlchemy, 
because GeoAlchemy doesn't seem to handle anything else :-/

Any help would be greatly welcome !

Best regards,
Thierry

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/mbMu9ozxiAsJ.
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: Problem with declarative polymorphic inheritance

[Thierry Florac]

Le vendredi 15 juin 2012 12:00:28 UTC+2, Thierry Florac a écrit :

 Hi,

 I'm using SQLAlchemy (0.7.4) with GeoAlchemy to map tables containing 
 geographic entities from a PostgreSQL/PostGIS database.

 As I have to handle data from several parts of the world, each part using 
 it's own geographic projection, my testing model which I'll try to describe 
 is based on:
 - a parent class (TestIMG), containing common attributes and a shape in 
 world-wide WGS84 coordinates system
 - for each projection system, a child class, inheriting from TestIMG and 
 containing only the shapes in the given projection system.
 TestIMG can be seen as an abstract class; discrimination between child 
 classes is based on a common SRID attribute.

 I've mapped my classes as follow:

 class TestIMG(Base):
 __tablename__ = 'test_img'
 id = Column('id', Integer, primary_key=True)
 data = Column('data', Unicode(20))
 wgs_shape = GeometryColumn('wgs_shape', Point(srid=4326))
 srid = Column('srid', Integer)
 __mapper_args__ = { 'concrete': False,
 'polymorphic_on': srid }

 class TestIMG_france(TestIMG):
 __tablename__ = 'test_img_france'
 __mapper_args__ = { 'polymorphic_identity': 2154,
 'concrete': True }
 id = Column('id', Integer, ForeignKey(TestIMG.id), primary_key=True)
 shape = GeometryColumn('shape', Point(srid=2154))

 class TestIMG_guyane(TestIMG):
 __tablename__ = 'test_img_guyane'
 __mapper_args__ = { 'polymorphic_identity': 32622,
 'concrete': True }
 id = Column('id', Integer, ForeignKey(TestIMG.id), primary_key=True)
 shape = GeometryColumn('shape', Point(srid=32622))

 TestIMG_union = polymorphic_union({ 'test_img_france': 
 TestIMG_france.__table__,
 'test_img_guyane': 
 TestIMG_guyane.__table__ },
   'projection', 'test_img')


 When I make a query in a child class, everything is OK; but when I try to 
 query the main class, I get an error:

  session.query(TestIMG).all()

 Traceback (most recent call last):
   File console, line 1, in module
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
  
 line 1947, in all
 return list(self)
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
  
 line 2178, in instances
 rows = [process[0](row, None) for row in fetch]
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py,
  
 line 2614, in _instance
 return _instance(row, result)
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py,
  
 line 2627, in _instance
 tuple([row[column] for column in pk_cols])
   File 
 /var/local/eggs/SQLAlchemy-0.7.4-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
  
 line 2654, in _key_fallback
 expression._string_or_unprintable(key))
 NoSuchColumnError: Could not locate column in row for column '
 test_img_france.id'


 I've tried to test several configurations but everything fails until now.
 I'll also add that I **have to** use declarative form of SQLAlchemy, 
 because GeoAlchemy doesn't seem to handle anything else :-/

 Any help would be greatly welcome !

 Best regards,
 Thierry


Hum, hum...!
In fact, it finally works, just by removing concrete argument from child 
classes mapper args...
Sorry for the noise :-/

Regards,
Thierry

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/4fFRswwC4o8J.
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.

Re: [sqlalchemy] Strange update problem...

[Thierry Florac]

2011/12/2 Michael Bayer mike...@zzzcomputing.com:

 On Dec 1, 2011, at 7:09 PM, Thierry Florac wrote:

 2011/12/2 Michael Bayer mike...@zzzcomputing.com:

 On Dec 1, 2011, at 5:38 PM, Thierry Florac wrote:

 Hi,

 So it seems that when resources are in the form and NOT modified, the
 matching task is not flagged dirty and is not saved in database. I
 just don't understand why !!!

 Only a full usage example would make it clear in this case.    Again (like 
 another email) it sounds like objects are not necessarily attached to any 
 session in all cases.

 OK.
 I'll check my code another time and will send you my full example on
 tomorrow morning when I'll be back at work.
 But as you say, how can I :
 - check if an object is actually attached to a session ?
 - if not (but how could it be ?), attach it to the session ??

 hopefully that will be all you need to debug, you can say obj in session, 
 obj in session.new, some info on this at 
 http://www.sqlalchemy.org/docs/orm/session.html#session-attributes


Hi,

My problem is solved !!
I checked my code and noticed that I was creating a new session in the
custom property handling resources assignment, instead of reusing the
session from which my object was loaded.
Everything is OK now, works like a charm ;-)

Best regards,
Thierry

-- 
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] Outer join question

[Thierry Florac]

Hi,

I have a little problem with outer joins (I use QSLAlchemy 0.5.6).

I have two tables, managing tasks and activities :

class Task(Base):
__tablename__ = 'tache'

id = Column(Integer, Sequence('seq_tache_id'), primary_key=True)
libelle = Column(Unicode(50))
description = Column(Unicode(4000))
...

class Activity(Base):
__tablename__ = 'activite'

id_ressource = Column(Integer, ForeignKey(Resource.id))
date_realisation = Column(Date)
id_tache_am = Column(Integer, ForeignKey(Task.id))
id_tache_pm = Column(Integer, ForeignKey(Task.id))

__table_args__ = (
PrimaryKeyConstraint('id_ressource', 'date_realisation'),
{}
)

Activity.task_am = relation(Task, primaryjoin=Activity.id_tache_am ==
Task.id, backref='activity_am')
Activity.task_pm = relation(Task, primaryjoin=Activity.id_tache_pm ==
Task.id, backref='activity_pm')


My problem is that I want to select activities and, for each of them,
their related AM and PM tasks labels (which can be null), as follow :

TaskAM = aliased(Task)
TaskPM = aliased(Task)
for activity, libelle_am, libelle_pm in
session.query(Activity, TaskAM.libelle, TaskPM.libelle) \

.outerjoin(TaskAM.activity_am, TaskPM.activity_pm) \

.filter(and_(Activity.id_ressource == User.getCurrentUser().id,

Activity.date_realisation.between(start_date, end_date))):
...

The generated SQL query is as follow :

SELECT projetsdi.activite.id_ressource AS
projetsdi_activite_id_re_1, ..., tache_1.libelle AS tache_1_libelle,
tache_2.libelle AS tache_2_libelle
FROM projetsdi.tache tache_2, projetsdi.tache tache_1 LEFT OUTER
JOIN projetsdi.activite ON projetsdi.activite.id_tache_am = tache_1.id
WHERE projetsdi.activite.id_ressource = :id_ressource_1 AND
projetsdi.activite.date_realisation BETWEEN :date_realisation_1 AND
:date_realisation_2

So this query only selects activities for which id_tache_am is defined !!
The good query should be something like :

SELECT ...
FROM projetsdi.activite
LEFT OUTER JOIN projetsdi.tache tache_1 ON
projetsdi.activite.id_tache_am = tache_1.id
LEFT OUTER JOIN projetsdi.tache tache_2 ON
projetsdi.activite.id_tache_pm = tache_2.id

Any idea about how to get such a result ??

Many thanks,
Thierry
-- 
http://www.imagesdusport.com -- http://www.ztfy.org

-- 
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: Outer join question

[Thierry Florac]

Hi,

Problem is solved with last SQLAlchemy 0.7.6 !!
I was a little affrayed to change but only had minor incompatibilities :-)

For anybody interested, the good syntax is :

session.query(Activity, TaskAM.libelle, TaskPM.libelle) \
   .outerjoin(TaskAM, Activity.task_am) \
   .outerjoin(TaskPM, Activity.task_pm) \
   .filter(...)

Best regards,
Thierry


2011/12/1 Thierry Florac tflo...@gmail.com:
 Hi,

 I have a little problem with outer joins (I use QSLAlchemy 0.5.6).

 I have two tables, managing tasks and activities :

 class Task(Base):
    __tablename__ = 'tache'

    id = Column(Integer, Sequence('seq_tache_id'), primary_key=True)
    libelle = Column(Unicode(50))
    description = Column(Unicode(4000))
    ...

 class Activity(Base):
    __tablename__ = 'activite'

    id_ressource = Column(Integer, ForeignKey(Resource.id))
    date_realisation = Column(Date)
    id_tache_am = Column(Integer, ForeignKey(Task.id))
    id_tache_pm = Column(Integer, ForeignKey(Task.id))

    __table_args__ = (
        PrimaryKeyConstraint('id_ressource', 'date_realisation'),
        {}
    )

 Activity.task_am = relation(Task, primaryjoin=Activity.id_tache_am ==
 Task.id, backref='activity_am')
 Activity.task_pm = relation(Task, primaryjoin=Activity.id_tache_pm ==
 Task.id, backref='activity_pm')


 My problem is that I want to select activities and, for each of them,
 their related AM and PM tasks labels (which can be null), as follow :

        TaskAM = aliased(Task)
        TaskPM = aliased(Task)
        for activity, libelle_am, libelle_pm in
 session.query(Activity, TaskAM.libelle, TaskPM.libelle) \

 .outerjoin(TaskAM.activity_am, TaskPM.activity_pm) \

 .filter(and_(Activity.id_ressource == User.getCurrentUser().id,

 Activity.date_realisation.between(start_date, end_date))):
            ...

 The generated SQL query is as follow :

    SELECT projetsdi.activite.id_ressource AS
 projetsdi_activite_id_re_1, ..., tache_1.libelle AS tache_1_libelle,
 tache_2.libelle AS tache_2_libelle
    FROM projetsdi.tache tache_2, projetsdi.tache tache_1 LEFT OUTER
 JOIN projetsdi.activite ON projetsdi.activite.id_tache_am = tache_1.id
    WHERE projetsdi.activite.id_ressource = :id_ressource_1 AND
 projetsdi.activite.date_realisation BETWEEN :date_realisation_1 AND
 :date_realisation_2

 So this query only selects activities for which id_tache_am is defined !!
 The good query should be something like :

    SELECT ...
    FROM projetsdi.activite
        LEFT OUTER JOIN projetsdi.tache tache_1 ON
 projetsdi.activite.id_tache_am = tache_1.id
        LEFT OUTER JOIN projetsdi.tache tache_2 ON
 projetsdi.activite.id_tache_pm = tache_2.id

 Any idea about how to get such a result ??

 Many thanks,
 Thierry
 --
 http://www.imagesdusport.com -- http://www.ztfy.org



-- 
http://www.imagesdusport.com -- http://www.ztfy.org

-- 
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] Strange update problem...

[Thierry Florac]

Hi,

Another quite strange problem using SQLAlchemy...
I've created two classes, with matching interfaces build with
zope.interface and zope.schema packages ; a web form is build with
z3c.form package ; everything is fine !

My problem is quite simple to describe : I have a main class which is
a Task, with a many-to-many relation to a Resource class ; nothing
complicated !

  class Task(Base):
implements(ITask)
id = Column(Integer, primary_key=True)
...

  class Resource(Base):
implements(IResource)
id = Column(Integer, primary_key=True)
...

  class Affectation(Base):
task_id = Column(Integer, ForeignKey(Task.id)
task = relation(Task)
resource_id = Column(Integer, ForeignKey(Resource.id)
resource = relation(Resource)

  Task.affectations = relation(Affectation)


Web creation/update forms are generated and working correctly (checked
via debugger on validation), but :
 - when I create a new task, everything is saved correctly in database
on commit ;
 - when I update an existing task and modify resources list,
everything is saved correctly ;
 - when I update an existing task and DON'T MODIFY resources list,
updates are NOT saved !!
 - if I remove resources assignment widget from the form and modify an
existing task, updates ARE saved !!

So it seems that when resources are in the form and NOT modified, the
matching task is not flagged dirty and is not saved in database. I
just don't understand why !!!

Any idea would be of great help !!!

Best regards,
Thierry
-- 
http://www.imagesdusport.com -- http://www.ztfy.org

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

Re: [sqlalchemy] Strange update problem...

[Thierry Florac]

2011/12/2 Michael Bayer mike...@zzzcomputing.com:

 On Dec 1, 2011, at 5:38 PM, Thierry Florac wrote:

 Hi,

 So it seems that when resources are in the form and NOT modified, the
 matching task is not flagged dirty and is not saved in database. I
 just don't understand why !!!

 Only a full usage example would make it clear in this case.    Again (like 
 another email) it sounds like objects are not necessarily attached to any 
 session in all cases.

OK.
I'll check my code another time and will send you my full example on
tomorrow morning when I'll be back at work.
But as you say, how can I :
 - check if an object is actually attached to a session ?
 - if not (but how could it be ?), attach it to the session ??

Many thanks for your help !!

Best regards,
Thierry

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

Re: [sqlalchemy] SQLAlchemy opened connection on shutdown Oracle server

[Thierry Florac]

Yes, that's what I thought...
I actually use a pool_recycle parameter of 3600 to recycle active
connections every hour, but the problem remains the same as before
(with default value of -1).
So I'm a bit stuck :-(

Regards,
Thierry


2010/12/20 Michael Bayer mike...@zzzcomputing.com:
 This is a classic use case for pool_recycle=some number of seconds , which 
 will prevent old connections in the pool from being used.   It does of course 
 assume that connections remain checked into the pool when not being used 
 (i.e. no Sessions hanging open, etc).

 http://www.sqlalchemy.org/docs/core/engines.html?highlight=create_engine#sqlalchemy.create_engine


 On Dec 20, 2010, at 9:01 AM, Borax wrote:

 Hi,

 I have a Python application (using Zope 3 framework) which is
 connected to an Oracle database throught SQLAlchemy.
 Everything is OK except for one thing : each week, the Oracle server
 is shutted down for a full cold backup and afterwards, opened
 SQLAlchemy connections are broken and can't be re-established cleanly,
 so that I have to restart the application.
 I'm already using connections pooling and I recently activated
 connections recycling, but it doesn't seems to change anything :-(

 Any help or idea would be welcome...

 Regards,
 Thierry

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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.