date:20160219




On 02/19/2016 11:52 PM, bill.ad...@level12.io wrote:

I got a test case working. It seems that the limit/offset operations
have an effect. The script fails less than half the time and so far only
fails when I run python with the -R flag



this reproduces and is a critical issue captured in 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3657/positional-result-column-logic-failing. 
 the full nature of the failure is not yet understood.







|
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base

engine =
sa.create_engine('mssql+pymssql://badams:password@192.168.56.101:1443/testdb',
echo=True)
session = saorm.sessionmaker(bind=engine)()

Base = declarative_base()

class Person(Base):
 __tablename__ = 'people'
 id = sa.Column(sa.Integer, primary_key=True)
 name = sa.Column(sa.String)

Base.metadata.create_all(engine)

session.query(Person).delete()

session.add(Person(name='foo'))
session.add(Person(name='bar'))

session.commit()

results = session.query(
 Person.name.label('person'),
).add_entity(
 Person
).order_by(
 Person.name
)

print results.count()
print results.limit(1).offset(1).all()



|

Thanks,
Bill


On Friday, February 19, 2016 at 11:20:44 PM UTC-5, Bill Adams wrote:

Yes, that is what I was trying to describe. I've been trying to
create a simple test case but have as of yet been unable to
reproduce the problem in a simpler environment. I was hoping someone
had encountered something similar before. I'll keep trying to get
that MCVE "working"..

Thanks,
Bill

On Fri, Feb 19, 2016 at 11:13 PM, Mike Bayer
> wrote:



On Fri, Feb 19, 2016 at 9:30 PM, > wrote:


The issue seems to be occurring for queries where we use the
add_entity() method to select a declarative model entity
when a column from the same table is already in the query
constructor and labeled.


just to make sure, here is that:

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

Base = declarative_base()


class A(Base):
 __tablename__ = 'a'
 id = Column(Integer, primary_key=True)
 x = Column(Integer)

e = create_engine("postgresql://scott:tiger@localhost/test",
echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)


s = Session(e)

s.add(A(x=5))
s.commit()

print s.query(A.x.label("foo")).add_entity(A).all()


query output at the end:

BEGIN (implicit)
2016-02-19 23:12:33,455 INFO sqlalchemy.engine.base.Engine
SELECT a.x AS foo, a.id  AS a_id, a.x AS a_x
FROM a
2016-02-19 23:12:33,455 INFO sqlalchemy.engine.base.Engine {}
[(5, <__main__.A object at 0x7f0c2fd94990>)]


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




--

*Bill Adams*
Developer
Direct: 502.276.1006
Office: 812.285.8766

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


--
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] NoneType not callable exception

2016-02-19 Thread bill . adams

I got a test case working. It seems that the limit/offset operations have 
an effect. The script fails less than half the time and so far only fails 
when I run python with the -R flag


import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base

engine = 
sa.create_engine('mssql+pymssql://badams:password@192.168.56.101:1443/testdb', 
echo=True)
session = saorm.sessionmaker(bind=engine)()

Base = declarative_base()

class Person(Base):
__tablename__ = 'people'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String)

Base.metadata.create_all(engine)

session.query(Person).delete()

session.add(Person(name='foo'))
session.add(Person(name='bar'))

session.commit()

results = session.query(
Person.name.label('person'),
).add_entity(
Person
).order_by(
Person.name
)

print results.count()
print results.limit(1).offset(1).all()




Thanks,
Bill


On Friday, February 19, 2016 at 11:20:44 PM UTC-5, Bill Adams wrote:
>
> Yes, that is what I was trying to describe. I've been trying to create a 
> simple test case but have as of yet been unable to reproduce the problem in 
> a simpler environment. I was hoping someone had encountered something 
> similar before. I'll keep trying to get that MCVE "working"..
>
> Thanks,
> Bill
>
> On Fri, Feb 19, 2016 at 11:13 PM, Mike Bayer  
> wrote:
>
>>
>>
>> On Fri, Feb 19, 2016 at 9:30 PM,  wrote:
>>
>>>
>>> The issue seems to be occurring for queries where we use the 
>>> add_entity() method to select a declarative model entity when a column 
>>> from the same table is already in the query constructor and labeled. 
>>>
>>
>> just to make sure, here is that:
>>
>> from sqlalchemy import *
>> from sqlalchemy.orm import *
>> from sqlalchemy.ext.declarative import declarative_base
>>
>> Base = declarative_base()
>>
>>
>> class A(Base):
>> __tablename__ = 'a'
>> id = Column(Integer, primary_key=True)
>> x = Column(Integer)
>>
>> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
>> Base.metadata.drop_all(e)
>> Base.metadata.create_all(e)
>>
>>
>> s = Session(e)
>>
>> s.add(A(x=5))
>> s.commit()
>>
>> print s.query(A.x.label("foo")).add_entity(A).all()
>>
>>
>> query output at the end:
>>
>> BEGIN (implicit)
>> 2016-02-19 23:12:33,455 INFO sqlalchemy.engine.base.Engine SELECT a.x AS 
>> foo, a.id AS a_id, a.x AS a_x 
>> FROM a
>> 2016-02-19 23:12:33,455 INFO sqlalchemy.engine.base.Engine {}
>> [(5, <__main__.A object at 0x7f0c2fd94990>)]
>>
>>
>> -- 
>> 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.
>>
>
>
>
> -- 
>
> *Bill Adams*
> Developer
> Direct: 502.276.1006
> Office: 812.285.8766
>
>

-- 
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] How do I start a transaction without regard to whether it is nested or not?

On Fri, Feb 19, 2016 at 1:12 PM, Daniel Fortunov <
googlegro...@danielfortunov.com> wrote:

> Context
>
> I would like to make use of nested transactions using SQLAlchemy (in
> postgres), but I want to write an independent function that doesn't care if
> the session already has a transaction or not -- it can start a nested
> transaction or outer transaction appropriately.
>

the good news is this is how the session works already.

>
> Question
>
> What is the most elegant syntax to begin a transaction, which will be
> either an outer or nested transaction, as appropriate?
>
> So far the best I have seen is: session.begin(nested=session.is_active)
>
> Is there something better than this?
>

The best way is to not use "autocommit=True" on the Session and not call
begin() at all.  That way, all functions are already present in a
transaction and they never have to ensure that they start one.   Only a
function that explicitly needs to do something that may fail in the middle
of this transaction can choose to call begin_nested() with a very local
scope.  The documentation at
http://docs.sqlalchemy.org/en/rel_1_0/orm/session_transaction.html#autocommit-mode
tries to make it clear that autocommit mode is not for general use and is
itself sort of a legacy mode of use.

All of that said, big projects like Openstack have gone ahead anyway and
used the Session with autocommit=True, and then they call begin() all over
the place.  In this style of use,  the begin()/commit() sequence is
designed to support nesting, however this nesting uses *subtransactions*,
not savepoints.  Savepoints shouldn't be used unless you definitely need to
be able to emit a ROLLBACK within the middle of a larger transaction
without rolling back the whole thing, and this is not a general use case.

To use subtransactions, pass subtransactions=True to begin().   it means
that if begin() was already called, this new begin() will only demarcate an
additional block whereby commit() has to be called to exit the block, then
the outermost commit() actually commits the transaction.

The reason the subtransactions flag is there is only to ensure that users
aren't inadvertently using nested begin/commit pairs without knowing what
they're doing.It's again not a pattern that I consider to be very
useful, and in Openstack I've worked hard to propose new patterns which
don't rely upon this feature. The Python DBAPI produces a connection that
itself is always effectively in a transaction, so explicit use of begin()
is not needed.   commit() is of course still important but the concern of
when transactions are committed should IMO be separate from the business
cases that proceed within that transaction.

>
> Discussion
>
> I am using postgres, which does not natively support nested transactions,
> so nested transactions are implemented using the SAVEPOINT command.
>
> Specifically, what the above code does (against postgres, at least) is: *
> If there is no transaction in progress, start a (non-nested) transaction
> (BEGIN) * If there is already a transaction in progress, begin a nested
> transaction (SAVEPOINT)
>

the Python DBAPI, in this case psycopg2, already emits BEGIN for you
automatically whether or not you want it to.  As long as you are emitting
SQL and aren't using it's special-use "autocommit" mode, there is a
transaction in progress.

> Is there something better than this? Ideally I'd like to just call
> session.begin() and have it internally work out if it needs to be an
> outer transaction (BEGIN) or a nested transaction (SAVEPOINT) without me
> having to be explicit about it.
>
>
> Motivation
>
> I would like to write a function f(session) which takes a SQLAlchemy
> session and makes some changes within a transaction. Also, I want the
> ability to rollback the changes that f() has made (and *only* the changes
> that f() has made).
>

then have all f() functions which need to roll back without affecting the
larger transaction emit begin_nested() and commit() at the end.

> If the calling code has begun a transaction, and made changes, then I
> don't want a rollback within f() to discard changes that were made by the
> calling code.
>
>
> Thanks in advance,
>
> Dani
>
> --
> 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.
>

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

Re: [sqlalchemy] NoneType not callable exception

2016-02-19 Thread Bill Adams

Yes, that is what I was trying to describe. I've been trying to create a
simple test case but have as of yet been unable to reproduce the problem in
a simpler environment. I was hoping someone had encountered something
similar before. I'll keep trying to get that MCVE "working"..

Thanks,
Bill

On Fri, Feb 19, 2016 at 11:13 PM, Mike Bayer 
wrote:

>
>
> On Fri, Feb 19, 2016 at 9:30 PM,  wrote:
>
>>
>> The issue seems to be occurring for queries where we use the add_entity() 
>> method
>> to select a declarative model entity when a column from the same table is
>> already in the query constructor and labeled.
>>
>
> just to make sure, here is that:
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
>
> class A(Base):
> __tablename__ = 'a'
> id = Column(Integer, primary_key=True)
> x = Column(Integer)
>
> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
> Base.metadata.drop_all(e)
> Base.metadata.create_all(e)
>
>
> s = Session(e)
>
> s.add(A(x=5))
> s.commit()
>
> print s.query(A.x.label("foo")).add_entity(A).all()
>
>
> query output at the end:
>
> BEGIN (implicit)
> 2016-02-19 23:12:33,455 INFO sqlalchemy.engine.base.Engine SELECT a.x AS
> foo, a.id AS a_id, a.x AS a_x
> FROM a
> 2016-02-19 23:12:33,455 INFO sqlalchemy.engine.base.Engine {}
> [(5, <__main__.A object at 0x7f0c2fd94990>)]
>
>
> --
> 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.
>



-- 

*Bill Adams*
Developer
Direct: 502.276.1006
Office: 812.285.8766

-- 
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] NoneType not callable exception

On Fri, Feb 19, 2016 at 9:30 PM,  wrote:

>
> The issue seems to be occurring for queries where we use the add_entity() 
> method
> to select a declarative model entity when a column from the same table is
> already in the query constructor and labeled.
>

just to make sure, here is that:

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

Base = declarative_base()


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
x = Column(Integer)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)


s = Session(e)

s.add(A(x=5))
s.commit()

print s.query(A.x.label("foo")).add_entity(A).all()


query output at the end:

BEGIN (implicit)
2016-02-19 23:12:33,455 INFO sqlalchemy.engine.base.Engine SELECT a.x AS
foo, a.id AS a_id, a.x AS a_x
FROM a
2016-02-19 23:12:33,455 INFO sqlalchemy.engine.base.Engine {}
[(5, <__main__.A object at 0x7f0c2fd94990>)]

-- 
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] NoneType not callable exception

On Fri, Feb 19, 2016 at 9:30 PM,  wrote:

> My company is in the process of upgrading one of our applications from
> SQLAlchemy 0.9 to 1.0.12. The process is mostly complete but we are getting
> exceptions seemingly at random for some of our more complicated queries.
>
> The relevant stack trace:
>
>   File
> "/home/bill/.virtualenvs/rrwr-sqla/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
> line 2588, in all
> return list(self)
>   File
> "/home/bill/.virtualenvs/rrwr-sqla/lib/python2.7/site-packages/sqlalchemy/orm/loading.py",
> line 86, in instances
> util.raise_from_cause(err)
>   File
> "/home/bill/.virtualenvs/rrwr-sqla/lib/python2.7/site-packages/sqlalchemy/util/compat.py",
> line 200, in raise_from_cause
> reraise(type(exception), exception, tb=exc_tb, cause=cause)
>   File
> "/home/bill/.virtualenvs/rrwr-sqla/lib/python2.7/site-packages/sqlalchemy/orm/loading.py",
> line 74, in instances
> for row in fetch]
>
> The issue seems to be occurring for queries where we use the add_entity() 
> method
> to select a declarative model entity when a column from the same table is
> already in the query constructor and labeled.
>
> If I remove the label or change the column expression in the query
> constructor to use literal_column().label() to reference the column
> expression for the same column as added by add_entity() then everything
> seems to work.
>
> My best guess is that it's caused by the dict assignment here
> 
>  overwriting
> the unlabeled column but I could be way off.
>
> Is this a bug in SQLAlchemy or is there a better supported method of
> handling this sort of query?
>

this stack trace does not show the actual error you're getting nor do I
know what you refer to when you say "this sort of query",  so unfortunately
I can't make any guesses as to what's going on.   The best way to identify
what is happening here is if you provide an MCVE (
http://stackoverflow.com/help/mcve) that reproduces the issue.



>
> I don't know if this is relevant but I'm connecting to MS SQL Server via
> pymssql.
>
> Thanks,
> Bill
>
> --
> 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.
>

-- 
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 declarative base and automap base

On Fri, Feb 19, 2016 at 7:09 PM, Brian Cherinka  wrote:

> Hi.
>
> I have two database schemas, with a table from each I would like to join.
> The classes for one schema have been created as explicit declarative Bases,
> while the classes for the other were all created via automap Base.  I have
> a foreign key joining the two tables.  Sqlalchemy sees the foreign key, yet
> does not recognize it or use.  Upon import of my Classes, I'm getting this
> error
>
> NoForeignKeysError: Could not determine join condition between parent/child
> tables on relationship Cube.target - there are no foreign keys linking
> these tables.  Ensure that referencing columns are associated with a
> ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.
>
> datadb.Cube is my declarative base and sampledb.MangaTarget is an automap
> base.  Here are the foreign keys on datadb.Cube
>
> In [4]: datadb.Cube.__table__.foreign_keys
> Out[4]:
> {ForeignKey(u'mangadatadb.ifudesign.pk'),
>  ForeignKey(u'mangasampledb.manga_target.pk'),
>  ForeignKey(u'mangadatadb.pipeline_info.pk'),
>  ForeignKey(u'mangadatadb.wavelength.pk')}
>
>
OK but you're assuming that your auto-reflected metadata contains tables
under the schema "mangadatadb" and 'mangasampledb" and that these schemas
are assigned to "sampledb.MangaTarget".  If you're getting NoForeignKeys it
means that these ForeignKey constraint objects are pointing at something
else.




> I've created the relationship via
>
> Cube.target = relationship(sampledb.MangaTarget, backref='cubes')
>
>
> I've also tried explicitly adding a foreign_keys=[Cube.manga_target_pk]
> here, but that didn't work either.
>

That approach can work as long as you also provide the "primaryjoin"
expression explicitly, otherwise it still doesn't know what
Cube.manga_target_pk is supposed to be pointing at.


>
> I tested out the relationships with an explicit declarative Base class for
> MangaTarget and everything works perfectly.  However, explicitly declaring
> all the tables in my sampledb schema is not really an option,
> unfortunately. I'm at a loss here.
>
> If it should be possible, is there a procedure somewhere documented on how
> to get that working?
>

Well when you automap you need to pass the "schema" argument to the
MetaData.reflect() method for it to consider this schema,
automapbase.prepare() doesn't yet have this argument exposed so you
probably need to use MetaData.reflect(schema='whatever') up front.   Then
make sure it has Table objects that have the "schema" argument you expect.
Then when you do automapbase.prepare(), the default naming scheme for
classes does not take schema into account, so if you have the same
tablename in multiple schemas the classes will overwrite each other in the
Base.classes collection.  So there's several stages here that you have to
make sure automap is putting the Tabe and class objects you expect in the
places they go, if it were me I'd step through it a bit with pdb.   The
automap system doesnt have strong support for multiple schemas right now.



>
> Thanks for any help.
>
> Cheers, 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 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.

[sqlalchemy] How do I start a transaction without regard to whether it is nested or not?

2016-02-19 Thread Daniel Fortunov

Context

I would like to make use of nested transactions using SQLAlchemy (in 
postgres), but I want to write an independent function that doesn't care if 
the session already has a transaction or not -- it can start a nested 
transaction or outer transaction appropriately.


Question

What is the most elegant syntax to begin a transaction, which will be 
either an outer or nested transaction, as appropriate?

So far the best I have seen is: session.begin(nested=session.is_active)

Is there something better than this?


Discussion

I am using postgres, which does not natively support nested transactions, 
so nested transactions are implemented using the SAVEPOINT command.

Specifically, what the above code does (against postgres, at least) is: * 
If there is no transaction in progress, start a (non-nested) transaction 
(BEGIN) * If there is already a transaction in progress, begin a nested 
transaction (SAVEPOINT)

Is there something better than this? Ideally I'd like to just call 
session.begin() and have it internally work out if it needs to be an outer 
transaction (BEGIN) or a nested transaction (SAVEPOINT) without me having 
to be explicit about it.


Motivation

I would like to write a function f(session) which takes a SQLAlchemy 
session and makes some changes within a transaction. Also, I want the 
ability to rollback the changes that f() has made (and *only* the changes 
that f() has made).

If the calling code has begun a transaction, and made changes, then I don't 
want a rollback within f() to discard changes that were made by the calling 
code.


Thanks in advance,

Dani

-- 
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] NoneType not callable exception

2016-02-19 Thread bill . adams

My company is in the process of upgrading one of our applications from
SQLAlchemy 0.9 to 1.0.12. The process is mostly complete but we are getting
exceptions seemingly at random for some of our more complicated queries.

The relevant stack trace:

File
"/home/bill/.virtualenvs/rrwr-sqla/lib/python2.7/site-packages/sqlalchemy/orm/query.py",

line 2588, in all
return list(self)
File
"/home/bill/.virtualenvs/rrwr-sqla/lib/python2.7/site-packages/sqlalchemy/orm/loading.py",

line 86, in instances
util.raise_from_cause(err)
File
"/home/bill/.virtualenvs/rrwr-sqla/lib/python2.7/site-packages/sqlalchemy/util/compat.py",

line 200, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File
"/home/bill/.virtualenvs/rrwr-sqla/lib/python2.7/site-packages/sqlalchemy/orm/loading.py",

line 74, in instances
for row in fetch]

The issue seems to be occurring for queries where we use the add_entity()
method
to select a declarative model entity when a column from the same table is
already in the query constructor and labeled.

If I remove the label or change the column expression in the query
constructor to use literal_column().label() to reference the column
expression for the same column as added by add_entity() then everything
seems to work.

My best guess is that it's caused by the dict assignment here

overwriting
the unlabeled column but I could be way off.

Is this a bug in SQLAlchemy or is there a better supported method of
handling this sort of query?

I don't know if this is relevant but I'm connecting to MS SQL Server via
pymssql.

Thanks,
Bill

--
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] Re: connected using pyodbc; how to hook that to SA?

2016-02-19 Thread Jonathan Vanasco

This may or may not help...

Whenever I have issues with db drivers, I clear out my environment and 
start from scratch (before that, I'll remove all the .pyc files too).

1. Sometimes there's a weird import going on and the wrong versions are 
loading (due to packaging issues. this happened a lot with mysql to me).
2. If you upgraded python (lets say from 2.7.1 to 2.7.2) and there were 
c-compiled extensions, they often break.

There's a good chance none of that is happening -- but because this is a db 
driver issue and you've been trying multiple driver versions... that's just 
a warning sign to me for weird import errors possibly going on.

-- 
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] relationship between declarative base and automap base

2016-02-19 Thread Brian Cherinka

Hi.  

I have two database schemas, with a table from each I would like to join. 
 The classes for one schema have been created as explicit declarative 
Bases, while the classes for the other were all created via automap Base. 
 I have a foreign key joining the two tables.  Sqlalchemy sees the foreign 
key, yet does not recognize it or use.  Upon import of my Classes, I'm 
getting this error

NoForeignKeysError: Could not determine join condition between parent/child 
tables on relationship Cube.target - there are no foreign keys linking 
these tables.  Ensure that referencing columns are associated with a 
ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

datadb.Cube is my declarative base and sampledb.MangaTarget is an automap 
base.  Here are the foreign keys on datadb.Cube
  
In [4]: datadb.Cube.__table__.foreign_keys
Out[4]:
{ForeignKey(u'mangadatadb.ifudesign.pk'),
 ForeignKey(u'mangasampledb.manga_target.pk'),
 ForeignKey(u'mangadatadb.pipeline_info.pk'),
 ForeignKey(u'mangadatadb.wavelength.pk')}

I've created the relationship via 

Cube.target = relationship(sampledb.MangaTarget, backref='cubes')


I've also tried explicitly adding a foreign_keys=[Cube.manga_target_pk] 
here, but that didn't work either.  

I initially created the constraint in my schema table with 

ALTER TABLE ONLY mangadatadb.cube
ADD CONSTRAINT manga_target_fk
FOREIGN KEY (manga_target_pk) REFERENCES mangasampledb.manga_target(pk)
ON UPDATE CASCADE ON DELETE CASCADE;

Can a relationship be created on a foreign key between a declarative base 
class and an automap base class?  

I tested out the relationships with an explicit declarative Base class for 
MangaTarget and everything works perfectly.  However, explicitly declaring 
all the tables in my sampledb schema is not really an option, 
unfortunately. I'm at a loss here.

If it should be possible, is there a procedure somewhere documented on how 
to get that working?

Thanks for any help.

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

Re: [sqlalchemy] connected using pyodbc; how to hook that to SA?

Indeed, the ibm_db list told me that testing with pyodbc was limited.
I'd skip pyodbc, but so far, it's the *only* package that is able to
connect me to the server, so I have to use it. Hopefully I can get
more information from ibm_db.

Adding the properties dbms_ver and dbms_name was a good idea. When I
do it in my iSeriesConnect() function (where I connect through
pyodbc), Python says that my connection object has no attribute
dbms_ver. I suppose I could subclass it, but it seems like this can't
be the right way to go--I don't know if the version is used for some
important check way off in the code somewhere, and if guessing values
will thus cause unforseen problems. Still, it's worth a try.

On 2/19/16, Simon King  wrote:
> I guess that's a symptom of the ibm_db_sa package not being very well
> tested with pyodbc
>
> I'm very confused by pyodbc's version numbers -
> https://pypi.python.org/pypi/pyodbc/3.0.10 suggests that version 3.0.10
> exists and was uploaded on 2015-04-29, but also says that the latest
> version is 2.1.9, which was uploaded on 2015-09-24. I've never used pyodbc
> so don't know what to make of that.
>
> I assume your error is coming from this line:
>
> https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/ibm_db_sa/base.py#L696
>
> ...which is in the base dialect, rather than the pyodbc-specific bit. Maybe
> you could hack around the problem by setting a "dbms_ver" attribute on the
> pyodbc connection that you are creating in your custom creator function.
> This is where it gets used:
>
> https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/ibm_db_sa/base.py#L481
>
> Simon
>
> On Fri, Feb 19, 2016 at 5:00 PM, Alex Hall  wrote:
>
>> That makes more sense, but as soon as I put "+pyodbc" in, I'm back to
>> last week's "pyodbc.Connection object has no attribute dbms_ver"
>> error. Pyodbc seems to be the problem, which is ironic--on its own,
>> pyodbc is the only way I've been able to talk to the server at all.
>> Add it to SA, though, and that attribute error appears.
>>
>> On 2/19/16, Simon King  wrote:
>> > According to
>> >
>> http://docs.sqlalchemy.org/en/rel_1_0/core/connections.html#registering-new-dialects
>> ,
>> > a dialect registered as "db2.pyodbc" should be specified in the URL as
>> > "db2+pyodbc://". Does that make any difference?
>> >
>> > On Fri, Feb 19, 2016 at 4:20 PM, Alex Hall  wrote:
>> >
>> >> Thanks. I tried both, and triedother variations including or excluding
>> >> the module name as a prefix (ibm_db_sa.db2.pyodbc://). In most cases,
>> >> I get:
>> >> sqlalchemy.exc.ArgumentError: could not parse RFC1738 URL from string
>> >> [my connection string]".
>> >>
>> >> If I don't get that, it's because I used a name that complains about
>> >> there being no attribute dbms_ver or server.version, depending on the
>> >> string.
>> >>
>> >> They don't make it easy, do they?
>> >>
>> >> On 2/19/16, Simon King  wrote:
>> >> > URI prefixes are defined in the setup.py for the ibm_db_sa package:
>> >> >
>> >> >
>> https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/setup.py
>> >> >
>> >> > I would guess that you want to end up with the DB2Dialect_pyodbc
>> class,
>> >> > which means you should use db2.pyodbc:// or ibm_db_sa.pyodbc://
>> >> >
>> >> > Simon
>> >> >
>> >> >
>> >> > On Fri, Feb 19, 2016 at 3:33 PM, Alex Hall 
>> wrote:
>> >> >
>> >> >> Thanks, that looks like what I'm looking for. I assume specifying
>> >> >> "ibm_db_sa://" for the string will let SA use the proper dialect?
>> >> >>
>> >> >> I'm now getting "pyodbc.Connection object has no attribute
>> >> >> server_info", in case anyone happens to know what that's about. I'm
>> >> >> getting nightmarish flashbacks to my "has no attribute" error last
>> >> >> week for the same object. But at least this is a different one;
>> >> >> I'll
>> >> >> count it as a good thing!
>> >> >>
>> >> >> On 2/19/16, Simon King  wrote:
>> >> >> > On Fri, Feb 19, 2016 at 2:38 PM, Alex Hall 
>> >> wrote:
>> >> >> >
>> >> >> >> As the subject says, I am connected to our iSeries through
>> straight
>> >> >> >> pyodbc. That seems to run perfectly. Now, is there a way to use
>> >> >> >> SA
>> >> >> >> with that connection? When I use "ibm_db_sa+pyodbc://..." I get
>> the
>> >> >> >> exact same error I was getting when using ibm_db directly. Using
>> >> >> >> pyodbc, I can specify the driver to be used, and I'm pretty sure
>> >> >> >> that's the key.
>> >> >> >>
>> >> >> >> Can I either use my pyodbc connection with SA and ibm_db_sa for
>> the
>> >> >> >> dialect, or specify the driver to SA directly? Thanks!
>> >> >> >>
>> >> >> >>
>> >> >> > You can pass a "creator" argument to create_engine if you want to
>> >> >> > create
>> >> >> > the connection yourself:
>> >> >> >
>> >> >> >
>> >> >>
>> >>
>>

Re: [sqlalchemy] connected using pyodbc; how to hook that to SA?

I guess that's a symptom of the ibm_db_sa package not being very well
tested with pyodbc

I'm very confused by pyodbc's version numbers -
https://pypi.python.org/pypi/pyodbc/3.0.10 suggests that version 3.0.10
exists and was uploaded on 2015-04-29, but also says that the latest
version is 2.1.9, which was uploaded on 2015-09-24. I've never used pyodbc
so don't know what to make of that.

I assume your error is coming from this line:

https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/ibm_db_sa/base.py#L696

...which is in the base dialect, rather than the pyodbc-specific bit. Maybe
you could hack around the problem by setting a "dbms_ver" attribute on the
pyodbc connection that you are creating in your custom creator function.
This is where it gets used:

https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/ibm_db_sa/base.py#L481

Simon

On Fri, Feb 19, 2016 at 5:00 PM, Alex Hall  wrote:

> That makes more sense, but as soon as I put "+pyodbc" in, I'm back to
> last week's "pyodbc.Connection object has no attribute dbms_ver"
> error. Pyodbc seems to be the problem, which is ironic--on its own,
> pyodbc is the only way I've been able to talk to the server at all.
> Add it to SA, though, and that attribute error appears.
>
> On 2/19/16, Simon King  wrote:
> > According to
> >
> http://docs.sqlalchemy.org/en/rel_1_0/core/connections.html#registering-new-dialects
> ,
> > a dialect registered as "db2.pyodbc" should be specified in the URL as
> > "db2+pyodbc://". Does that make any difference?
> >
> > On Fri, Feb 19, 2016 at 4:20 PM, Alex Hall  wrote:
> >
> >> Thanks. I tried both, and triedother variations including or excluding
> >> the module name as a prefix (ibm_db_sa.db2.pyodbc://). In most cases,
> >> I get:
> >> sqlalchemy.exc.ArgumentError: could not parse RFC1738 URL from string
> >> [my connection string]".
> >>
> >> If I don't get that, it's because I used a name that complains about
> >> there being no attribute dbms_ver or server.version, depending on the
> >> string.
> >>
> >> They don't make it easy, do they?
> >>
> >> On 2/19/16, Simon King  wrote:
> >> > URI prefixes are defined in the setup.py for the ibm_db_sa package:
> >> >
> >> >
> https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/setup.py
> >> >
> >> > I would guess that you want to end up with the DB2Dialect_pyodbc
> class,
> >> > which means you should use db2.pyodbc:// or ibm_db_sa.pyodbc://
> >> >
> >> > Simon
> >> >
> >> >
> >> > On Fri, Feb 19, 2016 at 3:33 PM, Alex Hall 
> wrote:
> >> >
> >> >> Thanks, that looks like what I'm looking for. I assume specifying
> >> >> "ibm_db_sa://" for the string will let SA use the proper dialect?
> >> >>
> >> >> I'm now getting "pyodbc.Connection object has no attribute
> >> >> server_info", in case anyone happens to know what that's about. I'm
> >> >> getting nightmarish flashbacks to my "has no attribute" error last
> >> >> week for the same object. But at least this is a different one; I'll
> >> >> count it as a good thing!
> >> >>
> >> >> On 2/19/16, Simon King  wrote:
> >> >> > On Fri, Feb 19, 2016 at 2:38 PM, Alex Hall 
> >> wrote:
> >> >> >
> >> >> >> As the subject says, I am connected to our iSeries through
> straight
> >> >> >> pyodbc. That seems to run perfectly. Now, is there a way to use SA
> >> >> >> with that connection? When I use "ibm_db_sa+pyodbc://..." I get
> the
> >> >> >> exact same error I was getting when using ibm_db directly. Using
> >> >> >> pyodbc, I can specify the driver to be used, and I'm pretty sure
> >> >> >> that's the key.
> >> >> >>
> >> >> >> Can I either use my pyodbc connection with SA and ibm_db_sa for
> the
> >> >> >> dialect, or specify the driver to SA directly? Thanks!
> >> >> >>
> >> >> >>
> >> >> > You can pass a "creator" argument to create_engine if you want to
> >> >> > create
> >> >> > the connection yourself:
> >> >> >
> >> >> >
> >> >>
> >>
> http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#custom-dbapi-connect-arguments
> >> >> >
> >> >> >
> >> >>
> >>
> http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#sqlalchemy.create_engine.params.creator
> >> >> >
> >> >> > Hope that helps,
> >> >> >
> >> >> > Simon
> >> >> >
> >> >> > --
> >> >> > 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.
> >> >> >
> >> >>
> >> >> --
> >> >> You received this message because you are subscribed to the Google
> >> Groups
> >> >>

Re: [sqlalchemy] connected using pyodbc; how to hook that to SA?

That makes more sense, but as soon as I put "+pyodbc" in, I'm back to
last week's "pyodbc.Connection object has no attribute dbms_ver"
error. Pyodbc seems to be the problem, which is ironic--on its own,
pyodbc is the only way I've been able to talk to the server at all.
Add it to SA, though, and that attribute error appears.

On 2/19/16, Simon King  wrote:
> According to
> http://docs.sqlalchemy.org/en/rel_1_0/core/connections.html#registering-new-dialects,
> a dialect registered as "db2.pyodbc" should be specified in the URL as
> "db2+pyodbc://". Does that make any difference?
>
> On Fri, Feb 19, 2016 at 4:20 PM, Alex Hall  wrote:
>
>> Thanks. I tried both, and triedother variations including or excluding
>> the module name as a prefix (ibm_db_sa.db2.pyodbc://). In most cases,
>> I get:
>> sqlalchemy.exc.ArgumentError: could not parse RFC1738 URL from string
>> [my connection string]".
>>
>> If I don't get that, it's because I used a name that complains about
>> there being no attribute dbms_ver or server.version, depending on the
>> string.
>>
>> They don't make it easy, do they?
>>
>> On 2/19/16, Simon King  wrote:
>> > URI prefixes are defined in the setup.py for the ibm_db_sa package:
>> >
>> > https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/setup.py
>> >
>> > I would guess that you want to end up with the DB2Dialect_pyodbc class,
>> > which means you should use db2.pyodbc:// or ibm_db_sa.pyodbc://
>> >
>> > Simon
>> >
>> >
>> > On Fri, Feb 19, 2016 at 3:33 PM, Alex Hall  wrote:
>> >
>> >> Thanks, that looks like what I'm looking for. I assume specifying
>> >> "ibm_db_sa://" for the string will let SA use the proper dialect?
>> >>
>> >> I'm now getting "pyodbc.Connection object has no attribute
>> >> server_info", in case anyone happens to know what that's about. I'm
>> >> getting nightmarish flashbacks to my "has no attribute" error last
>> >> week for the same object. But at least this is a different one; I'll
>> >> count it as a good thing!
>> >>
>> >> On 2/19/16, Simon King  wrote:
>> >> > On Fri, Feb 19, 2016 at 2:38 PM, Alex Hall 
>> wrote:
>> >> >
>> >> >> As the subject says, I am connected to our iSeries through straight
>> >> >> pyodbc. That seems to run perfectly. Now, is there a way to use SA
>> >> >> with that connection? When I use "ibm_db_sa+pyodbc://..." I get the
>> >> >> exact same error I was getting when using ibm_db directly. Using
>> >> >> pyodbc, I can specify the driver to be used, and I'm pretty sure
>> >> >> that's the key.
>> >> >>
>> >> >> Can I either use my pyodbc connection with SA and ibm_db_sa for the
>> >> >> dialect, or specify the driver to SA directly? Thanks!
>> >> >>
>> >> >>
>> >> > You can pass a "creator" argument to create_engine if you want to
>> >> > create
>> >> > the connection yourself:
>> >> >
>> >> >
>> >>
>> http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#custom-dbapi-connect-arguments
>> >> >
>> >> >
>> >>
>> http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#sqlalchemy.create_engine.params.creator
>> >> >
>> >> > Hope that helps,
>> >> >
>> >> > Simon
>> >> >
>> >> > --
>> >> > 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.
>> >> >
>> >>
>> >> --
>> >> 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.
>> >>
>> >
>> > --
>> > 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.
>> >
>>
>> --
>> 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.
>>

Re: [sqlalchemy] connected using pyodbc; how to hook that to SA?

According to
http://docs.sqlalchemy.org/en/rel_1_0/core/connections.html#registering-new-dialects,
a dialect registered as "db2.pyodbc" should be specified in the URL as
"db2+pyodbc://". Does that make any difference?

On Fri, Feb 19, 2016 at 4:20 PM, Alex Hall  wrote:

> Thanks. I tried both, and triedother variations including or excluding
> the module name as a prefix (ibm_db_sa.db2.pyodbc://). In most cases,
> I get:
> sqlalchemy.exc.ArgumentError: could not parse RFC1738 URL from string
> [my connection string]".
>
> If I don't get that, it's because I used a name that complains about
> there being no attribute dbms_ver or server.version, depending on the
> string.
>
> They don't make it easy, do they?
>
> On 2/19/16, Simon King  wrote:
> > URI prefixes are defined in the setup.py for the ibm_db_sa package:
> >
> > https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/setup.py
> >
> > I would guess that you want to end up with the DB2Dialect_pyodbc class,
> > which means you should use db2.pyodbc:// or ibm_db_sa.pyodbc://
> >
> > Simon
> >
> >
> > On Fri, Feb 19, 2016 at 3:33 PM, Alex Hall  wrote:
> >
> >> Thanks, that looks like what I'm looking for. I assume specifying
> >> "ibm_db_sa://" for the string will let SA use the proper dialect?
> >>
> >> I'm now getting "pyodbc.Connection object has no attribute
> >> server_info", in case anyone happens to know what that's about. I'm
> >> getting nightmarish flashbacks to my "has no attribute" error last
> >> week for the same object. But at least this is a different one; I'll
> >> count it as a good thing!
> >>
> >> On 2/19/16, Simon King  wrote:
> >> > On Fri, Feb 19, 2016 at 2:38 PM, Alex Hall 
> wrote:
> >> >
> >> >> As the subject says, I am connected to our iSeries through straight
> >> >> pyodbc. That seems to run perfectly. Now, is there a way to use SA
> >> >> with that connection? When I use "ibm_db_sa+pyodbc://..." I get the
> >> >> exact same error I was getting when using ibm_db directly. Using
> >> >> pyodbc, I can specify the driver to be used, and I'm pretty sure
> >> >> that's the key.
> >> >>
> >> >> Can I either use my pyodbc connection with SA and ibm_db_sa for the
> >> >> dialect, or specify the driver to SA directly? Thanks!
> >> >>
> >> >>
> >> > You can pass a "creator" argument to create_engine if you want to
> >> > create
> >> > the connection yourself:
> >> >
> >> >
> >>
> http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#custom-dbapi-connect-arguments
> >> >
> >> >
> >>
> http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#sqlalchemy.create_engine.params.creator
> >> >
> >> > Hope that helps,
> >> >
> >> > Simon
> >> >
> >> > --
> >> > 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.
> >> >
> >>
> >> --
> >> 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.
> >>
> >
> > --
> > 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.
> >
>
> --
> 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.
>

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

Re: [sqlalchemy] connected using pyodbc; how to hook that to SA?

Thanks. I tried both, and triedother variations including or excluding
the module name as a prefix (ibm_db_sa.db2.pyodbc://). In most cases,
I get:
sqlalchemy.exc.ArgumentError: could not parse RFC1738 URL from string
[my connection string]".

If I don't get that, it's because I used a name that complains about
there being no attribute dbms_ver or server.version, depending on the
string.

They don't make it easy, do they?

On 2/19/16, Simon King  wrote:
> URI prefixes are defined in the setup.py for the ibm_db_sa package:
>
> https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/setup.py
>
> I would guess that you want to end up with the DB2Dialect_pyodbc class,
> which means you should use db2.pyodbc:// or ibm_db_sa.pyodbc://
>
> Simon
>
>
> On Fri, Feb 19, 2016 at 3:33 PM, Alex Hall  wrote:
>
>> Thanks, that looks like what I'm looking for. I assume specifying
>> "ibm_db_sa://" for the string will let SA use the proper dialect?
>>
>> I'm now getting "pyodbc.Connection object has no attribute
>> server_info", in case anyone happens to know what that's about. I'm
>> getting nightmarish flashbacks to my "has no attribute" error last
>> week for the same object. But at least this is a different one; I'll
>> count it as a good thing!
>>
>> On 2/19/16, Simon King  wrote:
>> > On Fri, Feb 19, 2016 at 2:38 PM, Alex Hall  wrote:
>> >
>> >> As the subject says, I am connected to our iSeries through straight
>> >> pyodbc. That seems to run perfectly. Now, is there a way to use SA
>> >> with that connection? When I use "ibm_db_sa+pyodbc://..." I get the
>> >> exact same error I was getting when using ibm_db directly. Using
>> >> pyodbc, I can specify the driver to be used, and I'm pretty sure
>> >> that's the key.
>> >>
>> >> Can I either use my pyodbc connection with SA and ibm_db_sa for the
>> >> dialect, or specify the driver to SA directly? Thanks!
>> >>
>> >>
>> > You can pass a "creator" argument to create_engine if you want to
>> > create
>> > the connection yourself:
>> >
>> >
>> http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#custom-dbapi-connect-arguments
>> >
>> >
>> http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#sqlalchemy.create_engine.params.creator
>> >
>> > Hope that helps,
>> >
>> > Simon
>> >
>> > --
>> > 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.
>> >
>>
>> --
>> 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.
>>
>
> --
> 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.
>

-- 
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] Accessing data from an already executed query in a hybrid_property (using SQLAlchemy Transformer in the query)

Thanks for the quick response Mike! Happy to remind you about code that I'm 
guessing you wrote yourself once upon a time  I see that it was in the 
library all the way back in 0.7.

I didn't get the code working the way I'm expecting it to when I added your 
adaptations. First off it returns both the "SKU shipments" when I run the 
example. It should just return the latest. Am I missing something here?
I added *uselist=False, viewonly=True, *to the *_latest_sku_shipment 
*relationship 
but for obvious reasons it is not fixing the problem in itself (the warning 
"Multiple rows returned with uselist=False for eagerly-loaded attribute 
'Customer._latest_sku_shipment'" is printed to the terminal).

I've attached an updated version of my example.


On Friday, February 19, 2016 at 4:26:15 PM UTC+1, Mike Bayer wrote:
>
> In general, if you want objects to be populated as part of the normal 
> loading process there needs to be a relationship() to catch it.   This 
> can be achieved in this specific case like the following: 
>
> class CustomerLatestShipmentTransformer(hybrid.Comparator): 
>
>  @property 
>  def cte(self): 
>  def transform(q): 
>  subq = ( 
>  q.session.query( 
>  ProductSKUShipment.customer_id, 
>  ProductSKUShipment.shipment_date, 
>  ProductSKUShipment.order_no, 
>  ) 
>  .distinct(ProductSKUShipment.customer_id)  # Postgres 
> specific 
>  .order_by( 
>  ProductSKUShipment.customer_id, 
>  ProductSKUShipment.shipment_date.desc(), 
>  ) 
>  ) 
>  cte = subq.cte() 
>  return q.options( 
>  sa.orm.contains_eager('_latest_sku_shipment')).\ 
>  outerjoin(cte, cte.c.customer_id == Customer.id) 
>  return transform 
>
>
> class Customer(Base): 
>  __tablename__ = 'customer' 
>  id = sa.Column(sa.Integer, primary_key=True) 
>  no = sa.Column(sa.Text, nullable=False) 
>  name = sa.Column(sa.Text, nullable=False) 
>
>  def __repr__(self): 
>  return '<{no}: {name}>'.format(**vars(self)) 
>
>  # you'd want to make this into a full blown "latest element" 
>  # relationship if you need it to be accessed in any other way 
>  # besides the special query here 
>  _latest_sku_shipment = sa.orm.relationship( 
>  "ProductSKUShipment", lazy="noload") 
>
>  @hybrid.hybrid_property 
>  def latest_sku_shipment(self): 
>  return self._latest_sku_shipment 
>
>  @latest_sku_shipment.comparator 
>  def latest_sku_shipment(cls): 
>  return CustomerLatestShipmentTransformer(cls) 
>
> also thanks for reminding me about query.with_transformation()!   I had 
> zero recollection of this method or the recipe in hybrids!  it's like 
> I'm reading it for the first time, very strange. 
>
>
>
> On 02/19/2016 07:17 AM, Jacob Magnusson wrote: 
> > Hi, 
> > 
> > I have this case where I want to be able to access a hybrid_property 
> > that points to a related model which is further filtered using a cte 
> > (utilizing a Transformer 
> > <
> http://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html#building-transformers>)
>  
>
> > without having to trigger additional database requests. So what I'm 
> > trying to achieve looks something like this: 
> > 
> > | 
> > # Python code 
> > customers 
> > 
> =session.query(Customer).with_transformation(Customer.latest_sku_shipment.cte).all()
>  
>
> > 
> > # Jinja2 code 
> > {%forcustomer incustomers %} 
> > {{customer.name }} 
> > Latestshipment,{{customer.latest_sku_shipment.shipment_date }},was 
> > sent to customer on {{customer.latest_sku_shipment.apa }} 
> > {%endfor %} 
> > | 
> > 
> > Here's an example I've built that shows that it tries to access the 
> > regular property. Is there any way that it can get the data from the 
> > query that has already been executed? 
> > 
> > | 
> > importsqlalchemy assa 
> > fromsqlalchemy.ext.declarative importdeclarative_base 
> > fromsqlalchemy.ext importhybrid 
> > 
> > 
> > Base=declarative_base() 
> > Session=sa.orm.sessionmaker() 
> > 
> > 
> > classCustomerLatestShipmentTransformer(hybrid.Comparator): 
> > 
> > @property 
> > defcte(self): 
> > deftransform(q): 
> >  subq =( 
> >  q.session.query( 
> > ProductSKUShipment.customer_id, 
> > ProductSKUShipment.shipment_date, 
> > ProductSKUShipment.order_no, 
> > ) 
> > .distinct(ProductSKUShipment.customer_id)# Postgres specific 
> > .order_by( 
> > ProductSKUShipment.customer_id, 
> > ProductSKUShipment.shipment_date.desc(), 
> > ) 
> > ) 
> >  cte =subq.cte() 
> > returnq.outerjoin(cte,cte.c.customer_id ==Customer.id) 
> > returntransform 
> > 
> > 
> > classCustomer(Base): 
> >  __tablename__ ='customer' 
> >  id =sa.Column(sa.Integer,primary_key=True) 
> >

Re: [sqlalchemy] connected using pyodbc; how to hook that to SA?

URI prefixes are defined in the setup.py for the ibm_db_sa package:

https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/setup.py

I would guess that you want to end up with the DB2Dialect_pyodbc class,
which means you should use db2.pyodbc:// or ibm_db_sa.pyodbc://

Simon


On Fri, Feb 19, 2016 at 3:33 PM, Alex Hall  wrote:

> Thanks, that looks like what I'm looking for. I assume specifying
> "ibm_db_sa://" for the string will let SA use the proper dialect?
>
> I'm now getting "pyodbc.Connection object has no attribute
> server_info", in case anyone happens to know what that's about. I'm
> getting nightmarish flashbacks to my "has no attribute" error last
> week for the same object. But at least this is a different one; I'll
> count it as a good thing!
>
> On 2/19/16, Simon King  wrote:
> > On Fri, Feb 19, 2016 at 2:38 PM, Alex Hall  wrote:
> >
> >> As the subject says, I am connected to our iSeries through straight
> >> pyodbc. That seems to run perfectly. Now, is there a way to use SA
> >> with that connection? When I use "ibm_db_sa+pyodbc://..." I get the
> >> exact same error I was getting when using ibm_db directly. Using
> >> pyodbc, I can specify the driver to be used, and I'm pretty sure
> >> that's the key.
> >>
> >> Can I either use my pyodbc connection with SA and ibm_db_sa for the
> >> dialect, or specify the driver to SA directly? Thanks!
> >>
> >>
> > You can pass a "creator" argument to create_engine if you want to create
> > the connection yourself:
> >
> >
> http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#custom-dbapi-connect-arguments
> >
> >
> http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#sqlalchemy.create_engine.params.creator
> >
> > Hope that helps,
> >
> > Simon
> >
> > --
> > 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.
> >
>
> --
> 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.
>

-- 
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] connected using pyodbc; how to hook that to SA?

Thanks, that looks like what I'm looking for. I assume specifying
"ibm_db_sa://" for the string will let SA use the proper dialect?

I'm now getting "pyodbc.Connection object has no attribute
server_info", in case anyone happens to know what that's about. I'm
getting nightmarish flashbacks to my "has no attribute" error last
week for the same object. But at least this is a different one; I'll
count it as a good thing!

On 2/19/16, Simon King  wrote:
> On Fri, Feb 19, 2016 at 2:38 PM, Alex Hall  wrote:
>
>> As the subject says, I am connected to our iSeries through straight
>> pyodbc. That seems to run perfectly. Now, is there a way to use SA
>> with that connection? When I use "ibm_db_sa+pyodbc://..." I get the
>> exact same error I was getting when using ibm_db directly. Using
>> pyodbc, I can specify the driver to be used, and I'm pretty sure
>> that's the key.
>>
>> Can I either use my pyodbc connection with SA and ibm_db_sa for the
>> dialect, or specify the driver to SA directly? Thanks!
>>
>>
> You can pass a "creator" argument to create_engine if you want to create
> the connection yourself:
>
> http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#custom-dbapi-connect-arguments
>
> http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#sqlalchemy.create_engine.params.creator
>
> Hope that helps,
>
> Simon
>
> --
> 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.
>

-- 
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] Accessing data from an already executed query in a hybrid_property (using SQLAlchemy Transformer in the query)

In general, if you want objects to be populated as part of the normal 
loading process there needs to be a relationship() to catch it.   This 
can be achieved in this specific case like the following:


class CustomerLatestShipmentTransformer(hybrid.Comparator):

@property
def cte(self):
def transform(q):
subq = (
q.session.query(
ProductSKUShipment.customer_id,
ProductSKUShipment.shipment_date,
ProductSKUShipment.order_no,
)
.distinct(ProductSKUShipment.customer_id)  # Postgres 
specific

.order_by(
ProductSKUShipment.customer_id,
ProductSKUShipment.shipment_date.desc(),
)
)
cte = subq.cte()
return q.options(
sa.orm.contains_eager('_latest_sku_shipment')).\
outerjoin(cte, cte.c.customer_id == Customer.id)
return transform


class Customer(Base):
__tablename__ = 'customer'
id = sa.Column(sa.Integer, primary_key=True)
no = sa.Column(sa.Text, nullable=False)
name = sa.Column(sa.Text, nullable=False)

def __repr__(self):
return '<{no}: {name}>'.format(**vars(self))

# you'd want to make this into a full blown "latest element"
# relationship if you need it to be accessed in any other way
# besides the special query here
_latest_sku_shipment = sa.orm.relationship(
"ProductSKUShipment", lazy="noload")

@hybrid.hybrid_property
def latest_sku_shipment(self):
return self._latest_sku_shipment

@latest_sku_shipment.comparator
def latest_sku_shipment(cls):
return CustomerLatestShipmentTransformer(cls)

also thanks for reminding me about query.with_transformation()!   I had 
zero recollection of this method or the recipe in hybrids!  it's like 
I'm reading it for the first time, very strange.




On 02/19/2016 07:17 AM, Jacob Magnusson wrote:

Hi,

I have this case where I want to be able to access a hybrid_property
that points to a related model which is further filtered using a cte
(utilizing a Transformer
)
without having to trigger additional database requests. So what I'm
trying to achieve looks something like this:

|
# Python code
customers
=session.query(Customer).with_transformation(Customer.latest_sku_shipment.cte).all()

# Jinja2 code
{%forcustomer incustomers %}
{{customer.name }}
Latestshipment,{{customer.latest_sku_shipment.shipment_date }},was
sent to customer on {{customer.latest_sku_shipment.apa }}
{%endfor %}
|

Here's an example I've built that shows that it tries to access the
regular property. Is there any way that it can get the data from the
query that has already been executed?

|
importsqlalchemy assa
fromsqlalchemy.ext.declarative importdeclarative_base
fromsqlalchemy.ext importhybrid


Base=declarative_base()
Session=sa.orm.sessionmaker()


classCustomerLatestShipmentTransformer(hybrid.Comparator):

@property
defcte(self):
deftransform(q):
 subq =(
 q.session.query(
ProductSKUShipment.customer_id,
ProductSKUShipment.shipment_date,
ProductSKUShipment.order_no,
)
.distinct(ProductSKUShipment.customer_id)# Postgres specific
.order_by(
ProductSKUShipment.customer_id,
ProductSKUShipment.shipment_date.desc(),
)
)
 cte =subq.cte()
returnq.outerjoin(cte,cte.c.customer_id ==Customer.id)
returntransform


classCustomer(Base):
 __tablename__ ='customer'
 id =sa.Column(sa.Integer,primary_key=True)
no=sa.Column(sa.Text,nullable=False)
 name =sa.Column(sa.Text,nullable=False)

def__repr__(self):
return'<{no}: {name}>'.format(**vars(self))

@hybrid.hybrid_property
deflatest_sku_shipment(self):
# sess = sa.orm.object_session(self)
# return (
# sess.query(ProductSKUShipment)
# .filter_by(customer_id=self.id)
# .order_by(ProductSKUShipment.shipment_date.desc())
# .first()
# )
raiseRuntimeError('I want to be loaded from my query!')

@latest_sku_shipment.comparator
deflatest_sku_shipment(cls):
returnCustomerLatestShipmentTransformer(cls)


classProductSKUShipment(Base):
 __tablename__ ='productskushipment'

def__repr__(self):
return'<{sku} {order_no} ({shipment_date})>'.format(**vars(self))

 id =sa.Column(sa.Integer,primary_key=True)
 sku =sa.Column(sa.Text,unique=True)
 quantity =sa.Column(sa.Integer,nullable=False)
 order_no =sa.Column(sa.Text,nullable=False)
 shipment_date =sa.Column(sa.DateTime(timezone=True),nullable=False)
 customer_id =sa.Column(
 sa.Integer,
 sa.ForeignKey(Customer.id,ondelete='CASCADE'),
 nullable=False)
 customer =sa.orm.relationship(
Customer,
 backref=sa.orm.backref('sku_shipments',passive_deletes='all'),
)


customer =Customer(
no='123',
 name='My customer',
)
sku_shipment =ProductSKUShipment(

Re: [sqlalchemy] Weird SELECT when assigning to one-to-one relationship

2016-02-19 Thread Adrian

Thought something like that.. i did actually find that it's the backref 
causing it by stepping through tons of SA code ;)
So I guess setting it to None explicitly on creation is the correct way to 
avoid the SELECT?

-- 
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] Weird SELECT when assigning to one-to-one relationship

so this is uselist=False e.g. one-to-one, you make a Contribution with a 
non-present timetable entry, when you get to the flush(), it knows 
nothing about what timetable_entry objects might exist for this row.


Unsurprisingly then, if we do this:

contrib = Contribution()
s.add(contrib)
s.flush()
contrib.timetable_entry


that last line emits a SELECT.  Not surprising, right?   The assignment 
of "tte.contribution" similarly sets up the backref which necessarily 
needs to replace any previous timetable entry object, so it has to lazy 
load to see if there is one.


Stick a pdb into where it lazyloads and the stack trace should give 
clues about these (when you see "fire_replace_event", you see, ah, it's 
replacing an old value and it needs to find it):


> 
/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/strategies.py(559)_emit_lazyload()

-> q = session.query(self.mapper)._adapt_all_clauses()
(Pdb) where
  /home/classic/dev/sqlalchemy/test.py(47)()
-> tte.contribution = contrib

/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py(224)__set__()
-> instance_dict(instance), value, None)
  /home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py(807)set()
-> value = self.fire_replace_event(state, dict_, value, old, initiator)

/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py(829)fire_replace_event()
-> self._replace_token or self._init_append_or_replace_token())

/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py(1183)emit_backref_from_scalar_set_event()
-> passive=PASSIVE_NO_FETCH)

/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py(608)append()
-> self.set(state, dict_, value, initiator, passive=passive)
  /home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py(790)set()
-> state, dict_, passive=PASSIVE_ONLY_PERSISTENT | NO_AUTOFLUSH)
  /home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py(583)get()
-> value = self.callable_(state, passive)

/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/strategies.py(532)_load_for_state()
-> return self._emit_lazyload(session, state, ident_key, passive)
  (1)()
> 
/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/strategies.py(559)_emit_lazyload()

-> q = session.query(self.mapper)._adapt_all_clauses()
(Pdb)






On 02/19/2016 04:34 AM, Adrian wrote:

Check this testcase:
https://gist.github.com/ThiefMaster/913446490d0e4c31776d

When assigning an object to the relationship attribute a SELECT is sent, but
this does not happen when explicitly setting the attribute to None
before assigning
the object to it.

If the SELECT being issued is not a bug, is initializing the attribute
with an explicit
`None` the proper way to avoid it?

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


--
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] connected using pyodbc; how to hook that to SA?

On Fri, Feb 19, 2016 at 2:38 PM, Alex Hall  wrote:

> As the subject says, I am connected to our iSeries through straight
> pyodbc. That seems to run perfectly. Now, is there a way to use SA
> with that connection? When I use "ibm_db_sa+pyodbc://..." I get the
> exact same error I was getting when using ibm_db directly. Using
> pyodbc, I can specify the driver to be used, and I'm pretty sure
> that's the key.
>
> Can I either use my pyodbc connection with SA and ibm_db_sa for the
> dialect, or specify the driver to SA directly? Thanks!
>
>
You can pass a "creator" argument to create_engine if you want to create
the connection yourself:

http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#custom-dbapi-connect-arguments

http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#sqlalchemy.create_engine.params.creator

Hope that helps,

Simon

-- 
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] connected using pyodbc; how to hook that to SA?

As the subject says, I am connected to our iSeries through straight
pyodbc. That seems to run perfectly. Now, is there a way to use SA
with that connection? When I use "ibm_db_sa+pyodbc://..." I get the
exact same error I was getting when using ibm_db directly. Using
pyodbc, I can specify the driver to be used, and I'm pretty sure
that's the key.

Can I either use my pyodbc connection with SA and ibm_db_sa for the
dialect, or specify the driver to SA directly? Thanks!

-- 
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] Re: Accessing data from an already executed query in a hybrid_property (using SQLAlchemy Transformer in the query)

I would like to add that it would look much more clean in my book if I 
wouldn't have to resort to *session.query(Customer, ProductSKUShipment)*.

-- 
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] Re: Accessing data from an already executed query in a hybrid_property (using SQLAlchemy Transformer in the query)

I would like to add that it would look much more clean in my book if I 
wouldn't have to resort to *session.query(Customer, ProductSKUShipment)*.
.

On Friday, February 19, 2016 at 1:17:34 PM UTC+1, Jacob Magnusson wrote:
>
> Hi,
>
> I have this case where I want to be able to access a hybrid_property that 
> points to a related model which is further filtered using a cte (utilizing 
> a Transformer 
> )
>  
> without having to trigger additional database requests. So what I'm trying 
> to achieve looks something like this:
>
> # Python code
> customers = session.query(Customer).with_transformation(Customer.
> latest_sku_shipment.cte).all()
>
> # Jinja2 code
> {% for customer in customers %} 
> {{ customer.name }}
> Latest shipment, {{ customer.latest_sku_shipment.shipment_date }}, was 
> sent to customer on {{ customer.latest_sku_shipment.apa }}
> {% endfor %}
>
> Here's an example I've built that shows that it tries to access the 
> regular property. Is there any way that it can get the data from the query 
> that has already been executed?
>
> import sqlalchemy as sa
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.ext import hybrid
>
>
> Base = declarative_base()
> Session = sa.orm.sessionmaker()
>
>
> class CustomerLatestShipmentTransformer(hybrid.Comparator):
>
> @property
> def cte(self):
> def transform(q):
> subq = (
> q.session.query(
> ProductSKUShipment.customer_id,
> ProductSKUShipment.shipment_date,
> ProductSKUShipment.order_no,
> )
> .distinct(ProductSKUShipment.customer_id)  # Postgres 
> specific
> .order_by(
> ProductSKUShipment.customer_id,
> ProductSKUShipment.shipment_date.desc(),
> )
> )
> cte = subq.cte()
> return q.outerjoin(cte, cte.c.customer_id == Customer.id)
> return transform
>
>
> class Customer(Base):
> __tablename__ = 'customer'
> id = sa.Column(sa.Integer, primary_key=True)
> no = sa.Column(sa.Text, nullable=False)
> name = sa.Column(sa.Text, nullable=False)
>
> def __repr__(self):
> return '<{no}: {name}>'.format(**vars(self))
>
> @hybrid.hybrid_property
> def latest_sku_shipment(self):
> # sess = sa.orm.object_session(self)
> # return (
> # sess.query(ProductSKUShipment)
> # .filter_by(customer_id=self.id)
> # .order_by(ProductSKUShipment.shipment_date.desc())
> # .first()
> # )
> raise RuntimeError('I want to be loaded from my query!')
>
> @latest_sku_shipment.comparator
> def latest_sku_shipment(cls):
> return CustomerLatestShipmentTransformer(cls)
>
>
> class ProductSKUShipment(Base):
> __tablename__ = 'productskushipment'
>
> def __repr__(self):
> return '<{sku} {order_no} ({shipment_date})>'.format(**vars(self))
>
> id = sa.Column(sa.Integer, primary_key=True)
> sku = sa.Column(sa.Text, unique=True)
> quantity = sa.Column(sa.Integer, nullable=False)
> order_no = sa.Column(sa.Text, nullable=False)
> shipment_date = sa.Column(sa.DateTime(timezone=True), nullable=False)
> customer_id = sa.Column(
> sa.Integer,
> sa.ForeignKey(Customer.id, ondelete='CASCADE'),
> nullable=False)
> customer = sa.orm.relationship(
> Customer,
> backref=sa.orm.backref('sku_shipments', passive_deletes='all'),
> )
>
>
> customer = Customer(
> no='123',
> name='My customer',
> )
> sku_shipment = ProductSKUShipment(
> customer=customer,
> order_no='12345',
> sku='20090-100',
> quantity=1,
> shipment_date='2015-01-01 08:34',
> )
> sku_shipment_2 = ProductSKUShipment(
> customer=customer,
> order_no='12345',
> sku='10570-900',
> quantity=1,
> shipment_date='2016-02-19 13:03',
> )
>
> engine = sa.create_engine('postgresql://localhost/cachedproptest')
> Session.configure(bind=engine)
> Base.metadata.create_all(bind=engine)
> session 
> ...

-- 
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] Accessing data from an already executed query in a hybrid_property (using SQLAlchemy Transformer in the query)