Re: [sqlalchemy] Oracle use_ansi=False inner join problem on inline views
Kent jkentbo...@gmail.com wrote: Mike, When using use_ansi=False for Oracle (8) in conjunction with joinedload-ing an inline view property, SQLAlchemy-0.8.7 renders an inner join instead of an outer join. This has been fixed in SQLAlchemy-0.9.0, but, as I'm not prepared for the migration yet, I was hoping and failing to find the bug ticket and hopefully a patch. Do you know when/where this was fixed and whether the fix would be patch'able in 0.7 or at least 0.8? The attached script runs on 0.9.0+ but the assertion fails on 0.8.7. The only difference in SQL output is the outer join (+): SELECT products.productid AS products_productid, anon_1.productid AS anon_1_productid, anon_1.siteid AS anon_1_siteid, anon_1.qty AS anon_1_qty FROM products, (SELECT inventory.productid AS productid, inventory.siteid AS siteid, sum(inventory.qty) AS qty FROM inventory GROUP BY inventory.productid, inventory.siteid) anon_1 WHERE anon_1.productid(+) = products.productid ORDER BY anon_1.siteid Interestingly, use-ansi=True correctly renders LEFT OUTER JOIN in 0.8.7 but it fails to render as an outer join with use-ansi=False. Thanks for you time and exceptional software, Kent no specific fixes are logged, however http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1 refers to a very large change in how the ORM decides to join things.That would cause some kinds of joinedloads to render differently , which would impact how (+) comes out as well, but i wouldn’t think it would have the effect that the missing (+) is the only change, it would be more than that. So I have no better idea than you, so the method I’d do is just to git bisect (http://git-scm.com/docs/git-bisect) through revisions until you find the fix. If it’s a big merge revision, I can look into it to find something specific, but if you can get me a rev ID that would be a good start. -- 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] Oracle use_ansi=False inner join problem on inline views
Mike,
When using use_ansi=False for Oracle (8) in conjunction with joinedload-ing
an inline view property, SQLAlchemy-0.8.7 renders an inner join instead of
an outer join. This has been fixed in SQLAlchemy-0.9.0, but, as I'm not
prepared for the migration yet, I was hoping and failing to find the bug
ticket and hopefully a patch. Do you know when/where this was fixed and
whether the fix would be patch'able in 0.7 or at least 0.8?
The attached script runs on 0.9.0+ but the assertion fails on 0.8.7.
The only difference in SQL output is the outer join (+):
SELECT products.productid AS products_productid, anon_1.productid AS
anon_1_productid, anon_1.siteid AS anon_1_siteid, anon_1.qty AS anon_1_qty
FROM products, (SELECT inventory.productid AS productid, inventory.siteid
AS siteid, sum(inventory.qty) AS qty
FROM inventory GROUP BY inventory.productid, inventory.siteid) anon_1
WHERE anon_1.productid*(+)* = products.productid ORDER BY anon_1.siteid
Interestingly, use-ansi=True correctly renders LEFT OUTER JOIN in 0.8.7
but it fails to render as an outer join with use-ansi=False.
Thanks for you time and exceptional software,
Kent
--
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.
from sqlalchemy import *
from sqlalchemy.orm import *
eng_url = 'oracle://kent:kent@localhost:1521/xe?use_ansi=False'
engine = create_engine(eng_url, echo=True)
metadata = MetaData(engine)
Session = sessionmaker(bind=engine)
products_table = Table(products, metadata,
Column(productid, Unicode(255), primary_key=True),
)
inventory_table = Table(inventory, metadata,
Column(inventoryid, Integer, primary_key=True),
Column(productid, Unicode(255), ForeignKey('products.productid'), nullable=False),
Column(siteid, Unicode(255), nullable=False),
Column(qty, Integer, nullable=False),
)
def repr_attrs(obj, *attrs):
return '%s: ' % obj.__class__.__name__ + \
' '.join('{0[%s]}=[{1[%s]}]' % (i,i) for i in range(len(attrs)))\
.format(attrs, map(obj.__dict__.get, attrs)) +
class Base(object):
def __init__(self, session, **attrs):
self.__dict__.update(attrs)
session.add(self)
class SiteStockLevel(object):
def __repr__(self):
return repr_attrs(self,'productid','siteid','qty')
class Product(Base):
def __repr__(self):
return repr_attrs(self,'productid')
class Inventory(Base):
pass
sitestocklevels_view = select([
inventory_table.c.productid,
inventory_table.c.siteid,
func.sum(inventory_table.c.qty).label('qty')],
group_by=[inventory_table.c.productid, inventory_table.c.siteid]).alias('sitestocklevels')
mapper(Inventory, inventory_table)
mapper(Product, products_table,
properties={
'sitestocklevels': relationship(SiteStockLevel,
primaryjoin=sitestocklevels_view.c.productid==products_table.c.productid,
order_by=sitestocklevels_view.c.siteid,
viewonly=True),
})
mapper(SiteStockLevel, sitestocklevels_view,
primary_key=[sitestocklevels_view.c.productid, sitestocklevels_view.c.siteid])
metadata.create_all()
try:
sess = Session()
Product(sess, productid=u'SKUA')
Product(sess, productid=u'SKUB')
sess.commit()
Inventory(sess, inventoryid=1, productid=u'SKUA', siteid=u'S1', qty=1)
Inventory(sess, inventoryid=2, productid=u'SKUA', siteid=u'S1', qty=2)
Inventory(sess, inventoryid=3, productid=u'SKUA', siteid=u'S1', qty=3)
Inventory(sess, inventoryid=4, productid=u'SKUA', siteid=u'S2', qty=1)
sess.commit()
allproducts = sess.query(Product).options(joinedload(Product.sitestocklevels)).all()
assert len(allproducts) == 2
finally:
metadata.drop_all()
Re: [sqlalchemy] Oracle use_ansi=False inner join problem on inline views
So, in 0.7 expression.py has a non-public, underscored class _FromGrouping instead of the public class FromGrouping That should be just fine to use, correct? On Wednesday, January 21, 2015 at 3:56:12 PM UTC-5, Michael Bayer wrote: wow that is awful, how often do I fix a full blown bug, even write tests for it, and don’t put anything in the changelog, no bug report or anything. You can probably patch it to 0.8. Not sure about 0.7, but if 0.7 doesn’t have FromGrouping there should still be some similar concept that can be tested for here.I’m not maintaining either except for security issues back to 0.8. there’s a new website section coming soon that will show this stuff. Kent jkent...@gmail.com javascript: wrote: Here it is: commit 85368d25ed158c85bd19f4a63400884ab1cda26a Author: Mike Bayer m... Date: Sat Jun 8 18:54:14 2013 -0400 get nested joins to render on oracle 8 Sounds like the right commit notes. You still maintaining 0.8? Should that change be patchable in 0.7? On Wednesday, January 21, 2015 at 1:13:21 PM UTC-5, Michael Bayer wrote: Kent jkent...@gmail.com wrote: Mike, When using use_ansi=False for Oracle (8) in conjunction with joinedload-ing an inline view property, SQLAlchemy-0.8.7 renders an inner join instead of an outer join. This has been fixed in SQLAlchemy-0.9.0, but, as I'm not prepared for the migration yet, I was hoping and failing to find the bug ticket and hopefully a patch. Do you know when/where this was fixed and whether the fix would be patch'able in 0.7 or at least 0.8? The attached script runs on 0.9.0+ but the assertion fails on 0.8.7. The only difference in SQL output is the outer join (+): SELECT products.productid AS products_productid, anon_1.productid AS anon_1_productid, anon_1.siteid AS anon_1_siteid, anon_1.qty AS anon_1_qty FROM products, (SELECT inventory.productid AS productid, inventory.siteid AS siteid, sum(inventory.qty) AS qty FROM inventory GROUP BY inventory.productid, inventory.siteid) anon_1 WHERE anon_1.productid(+) = products.productid ORDER BY anon_1.siteid Interestingly, use-ansi=True correctly renders LEFT OUTER JOIN in 0.8.7 but it fails to render as an outer join with use-ansi=False. Thanks for you time and exceptional software, Kent no specific fixes are logged, however http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1 refers to a very large change in how the ORM decides to join things. That would cause some kinds of joinedloads to render differently , which would impact how (+) comes out as well, but i wouldn’t think it would have the effect that the missing (+) is the only change, it would be more than that. So I have no better idea than you, so the method I’d do is just to git bisect (http://git-scm.com/docs/git-bisect) through revisions until you find the fix. If it’s a big merge revision, I can look into it to find something specific, but if you can get me a rev ID that would be a good start. -- 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+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.com javascript:. 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.
Re: [sqlalchemy] Oracle use_ansi=False inner join problem on inline views
Here it is: commit 85368d25ed158c85bd19f4a63400884ab1cda26a Author: Mike Bayer m... Date: Sat Jun 8 18:54:14 2013 -0400 get nested joins to render on oracle 8 Sounds like the right commit notes. You still maintaining 0.8? Should that change be patchable in 0.7? On Wednesday, January 21, 2015 at 1:13:21 PM UTC-5, Michael Bayer wrote: Kent jkent...@gmail.com javascript: wrote: Mike, When using use_ansi=False for Oracle (8) in conjunction with joinedload-ing an inline view property, SQLAlchemy-0.8.7 renders an inner join instead of an outer join. This has been fixed in SQLAlchemy-0.9.0, but, as I'm not prepared for the migration yet, I was hoping and failing to find the bug ticket and hopefully a patch. Do you know when/where this was fixed and whether the fix would be patch'able in 0.7 or at least 0.8? The attached script runs on 0.9.0+ but the assertion fails on 0.8.7. The only difference in SQL output is the outer join (+): SELECT products.productid AS products_productid, anon_1.productid AS anon_1_productid, anon_1.siteid AS anon_1_siteid, anon_1.qty AS anon_1_qty FROM products, (SELECT inventory.productid AS productid, inventory.siteid AS siteid, sum(inventory.qty) AS qty FROM inventory GROUP BY inventory.productid, inventory.siteid) anon_1 WHERE anon_1.productid(+) = products.productid ORDER BY anon_1.siteid Interestingly, use-ansi=True correctly renders LEFT OUTER JOIN in 0.8.7 but it fails to render as an outer join with use-ansi=False. Thanks for you time and exceptional software, Kent no specific fixes are logged, however http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1 refers to a very large change in how the ORM decides to join things. That would cause some kinds of joinedloads to render differently , which would impact how (+) comes out as well, but i wouldn’t think it would have the effect that the missing (+) is the only change, it would be more than that. So I have no better idea than you, so the method I’d do is just to git bisect (http://git-scm.com/docs/git-bisect) through revisions until you find the fix. If it’s a big merge revision, I can look into it to find something specific, but if you can get me a rev ID that would be a good start. -- 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 use_ansi=False inner join problem on inline views
Hmmm 0.7 is missing expression.FromGrouping... I imagine that is a big deal, isn't it, like not really patchable? On Wednesday, January 21, 2015 at 3:11:29 PM UTC-5, Kent wrote: Here it is: commit 85368d25ed158c85bd19f4a63400884ab1cda26a Author: Mike Bayer m... Date: Sat Jun 8 18:54:14 2013 -0400 get nested joins to render on oracle 8 Sounds like the right commit notes. You still maintaining 0.8? Should that change be patchable in 0.7? On Wednesday, January 21, 2015 at 1:13:21 PM UTC-5, Michael Bayer wrote: Kent jkent...@gmail.com wrote: Mike, When using use_ansi=False for Oracle (8) in conjunction with joinedload-ing an inline view property, SQLAlchemy-0.8.7 renders an inner join instead of an outer join. This has been fixed in SQLAlchemy-0.9.0, but, as I'm not prepared for the migration yet, I was hoping and failing to find the bug ticket and hopefully a patch. Do you know when/where this was fixed and whether the fix would be patch'able in 0.7 or at least 0.8? The attached script runs on 0.9.0+ but the assertion fails on 0.8.7. The only difference in SQL output is the outer join (+): SELECT products.productid AS products_productid, anon_1.productid AS anon_1_productid, anon_1.siteid AS anon_1_siteid, anon_1.qty AS anon_1_qty FROM products, (SELECT inventory.productid AS productid, inventory.siteid AS siteid, sum(inventory.qty) AS qty FROM inventory GROUP BY inventory.productid, inventory.siteid) anon_1 WHERE anon_1.productid(+) = products.productid ORDER BY anon_1.siteid Interestingly, use-ansi=True correctly renders LEFT OUTER JOIN in 0.8.7 but it fails to render as an outer join with use-ansi=False. Thanks for you time and exceptional software, Kent no specific fixes are logged, however http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1 refers to a very large change in how the ORM decides to join things. That would cause some kinds of joinedloads to render differently , which would impact how (+) comes out as well, but i wouldn’t think it would have the effect that the missing (+) is the only change, it would be more than that. So I have no better idea than you, so the method I’d do is just to git bisect (http://git-scm.com/docs/git-bisect) through revisions until you find the fix. If it’s a big merge revision, I can look into it to find something specific, but if you can get me a rev ID that would be a good start. -- 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 use_ansi=False inner join problem on inline views
try it! Kent jkentbo...@gmail.com wrote: So, in 0.7 expression.py has a non-public, underscored class _FromGrouping instead of the public class FromGrouping That should be just fine to use, correct? On Wednesday, January 21, 2015 at 3:56:12 PM UTC-5, Michael Bayer wrote: wow that is awful, how often do I fix a full blown bug, even write tests for it, and don’t put anything in the changelog, no bug report or anything. You can probably patch it to 0.8. Not sure about 0.7, but if 0.7 doesn’t have FromGrouping there should still be some similar concept that can be tested for here.I’m not maintaining either except for security issues back to 0.8. there’s a new website section coming soon that will show this stuff. Kent jkent...@gmail.com wrote: Here it is: commit 85368d25ed158c85bd19f4a63400884ab1cda26a Author: Mike Bayer m... Date: Sat Jun 8 18:54:14 2013 -0400 get nested joins to render on oracle 8 Sounds like the right commit notes. You still maintaining 0.8? Should that change be patchable in 0.7? On Wednesday, January 21, 2015 at 1:13:21 PM UTC-5, Michael Bayer wrote: Kent jkent...@gmail.com wrote: Mike, When using use_ansi=False for Oracle (8) in conjunction with joinedload-ing an inline view property, SQLAlchemy-0.8.7 renders an inner join instead of an outer join. This has been fixed in SQLAlchemy-0.9.0, but, as I'm not prepared for the migration yet, I was hoping and failing to find the bug ticket and hopefully a patch. Do you know when/where this was fixed and whether the fix would be patch'able in 0.7 or at least 0.8? The attached script runs on 0.9.0+ but the assertion fails on 0.8.7. The only difference in SQL output is the outer join (+): SELECT products.productid AS products_productid, anon_1.productid AS anon_1_productid, anon_1.siteid AS anon_1_siteid, anon_1.qty AS anon_1_qty FROM products, (SELECT inventory.productid AS productid, inventory.siteid AS siteid, sum(inventory.qty) AS qty FROM inventory GROUP BY inventory.productid, inventory.siteid) anon_1 WHERE anon_1.productid(+) = products.productid ORDER BY anon_1.siteid Interestingly, use-ansi=True correctly renders LEFT OUTER JOIN in 0.8.7 but it fails to render as an outer join with use-ansi=False. Thanks for you time and exceptional software, Kent no specific fixes are logged, however http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1 refers to a very large change in how the ORM decides to join things. That would cause some kinds of joinedloads to render differently , which would impact how (+) comes out as well, but i wouldn’t think it would have the effect that the missing (+) is the only change, it would be more than that. So I have no better idea than you, so the method I’d do is just to git bisect (http://git-scm.com/docs/git-bisect) through revisions until you find the fix. If it’s a big merge revision, I can look into it to find something specific, but if you can get me a rev ID that would be a good start. -- 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+...@googlegroups.com. To post to this group, send email to sqlal...@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. -- 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] SQL join between two tables from two databases
Simon, thanks for your response. Let me wrap my head around this and try it
out.
Brian
On 21 January 2015 at 04:59, Simon King si...@simonking.org.uk wrote:
You don't need to convert it to a Table object, but you probably do
need to add 'schema': 'whatever' to the __table_args__ dictionary.
In answer to your second question, I very much doubt you can use
query.join() with 2 DB connections. query.join() simply adds an SQL
JOIN clause to the query that is eventually sent to the database -
there's no way of making that work with 2 separate connections.
As an alternative, I think it should be possible to put the tables
that exist in a separate schema in a separate SQLAlchemy MetaData
(they'd need to use a separate declarative Base class). The MetaData
can hold the default schema for the tables, and I *think* you should
be able to use tables from different MetaData in query.join(). (I
haven't tested this though).
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative/basic_use.html#accessing-the-metadata
http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#sqlalchemy.schema.MetaData
http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#metadata-describing
Hope that helps,
Simon
On Wed, Jan 21, 2015 at 9:09 AM, Brian Glogower bglogo...@ifwe.co wrote:
Hi Michael,
Do I need to redefined mapped class ssh_host_keys as a Table object?
ssh_host_keys = Table('ssh_host_keys', metadata,
Column('hostname', VARCHAR(30), primary_key=True),
Column('pub', VARCHAR(1600)),
Column('sha256', CHAR(64)),
Column('priv', VARCHAR(2000)),
schema='keys',
mysql_engine='InnoDB'
)
Do I need to convert mapped class 'Host' to a Table object as well? I
would
prefer not to touch this class, since it is part of a separate module,
but
if needed, it is possible.
class Host(Base):
__tablename__ = 'hosts'
__table_args__ = {'mysql_engine': 'InnoDB'}
id = Column(u'HostID', INTEGER(), primary_key=True)
hostname = Column(String(length=30))
Can you please give an example how to use schema with a query.join(),
for my
scenario (two sessions, one for each DB connection)?
Thanks,
Brian
On 20 January 2015 at 16:12, Michael Bayer mike...@zzzcomputing.com
wrote:
Jonathan Vanasco jonat...@findmeon.com wrote:
On Tuesday, January 20, 2015 at 5:34:27 PM UTC-5, Brian Glogower
wrote:
Thanks for the idea. Do you have an example?
I don't have a personal example handle, but from the docs...
http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#using-literal-sql
session.query(User).from_statement(
... text(SELECT * FROM users where name=:name)).
\
... params(name='ed').all()
[User(name='ed', fullname='Ed Jones', password='f8s7ccs')]
So you should be able to do something like:
query = Session.query(Host)\
.from_statement(
sqlaclhemy.text(SELECT hostname, sha256 FROM
DATABASE1.hosts
LEFT JOIN DATABASE2.ssh_host_keys ssh_host_keys ON
ssh_host_keys.hostname ==
hosts.hostname)
)
why is text() needed here?these could be the Table objects set up
with
“schema=‘schema name’” to start with, then you’d just do the join with
query.join().
--
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.
--
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
Re: [sqlalchemy] Oracle use_ansi=False inner join problem on inline views
wow that is awful, how often do I fix a full blown bug, even write tests for it, and don’t put anything in the changelog, no bug report or anything. You can probably patch it to 0.8. Not sure about 0.7, but if 0.7 doesn’t have FromGrouping there should still be some similar concept that can be tested for here.I’m not maintaining either except for security issues back to 0.8. there’s a new website section coming soon that will show this stuff. Kent jkentbo...@gmail.com wrote: Here it is: commit 85368d25ed158c85bd19f4a63400884ab1cda26a Author: Mike Bayer m... Date: Sat Jun 8 18:54:14 2013 -0400 get nested joins to render on oracle 8 Sounds like the right commit notes. You still maintaining 0.8? Should that change be patchable in 0.7? On Wednesday, January 21, 2015 at 1:13:21 PM UTC-5, Michael Bayer wrote: Kent jkent...@gmail.com wrote: Mike, When using use_ansi=False for Oracle (8) in conjunction with joinedload-ing an inline view property, SQLAlchemy-0.8.7 renders an inner join instead of an outer join. This has been fixed in SQLAlchemy-0.9.0, but, as I'm not prepared for the migration yet, I was hoping and failing to find the bug ticket and hopefully a patch. Do you know when/where this was fixed and whether the fix would be patch'able in 0.7 or at least 0.8? The attached script runs on 0.9.0+ but the assertion fails on 0.8.7. The only difference in SQL output is the outer join (+): SELECT products.productid AS products_productid, anon_1.productid AS anon_1_productid, anon_1.siteid AS anon_1_siteid, anon_1.qty AS anon_1_qty FROM products, (SELECT inventory.productid AS productid, inventory.siteid AS siteid, sum(inventory.qty) AS qty FROM inventory GROUP BY inventory.productid, inventory.siteid) anon_1 WHERE anon_1.productid(+) = products.productid ORDER BY anon_1.siteid Interestingly, use-ansi=True correctly renders LEFT OUTER JOIN in 0.8.7 but it fails to render as an outer join with use-ansi=False. Thanks for you time and exceptional software, Kent no specific fixes are logged, however http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1 refers to a very large change in how the ORM decides to join things.That would cause some kinds of joinedloads to render differently , which would impact how (+) comes out as well, but i wouldn’t think it would have the effect that the missing (+) is the only change, it would be more than that. So I have no better idea than you, so the method I’d do is just to git bisect (http://git-scm.com/docs/git-bisect) through revisions until you find the fix. If it’s a big merge revision, I can look into it to find something specific, but if you can get me a rev ID that would be a good start. -- 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.
[sqlalchemy] Sharding, query_chooser and lazy select
Hello, I'm using sharding and it works fine except for the relationship. In my mapper, i define a relationship with lazy mode set to select. When I want to access to this relationship, my query_chooser implementation is called and I want to retrieve the parent instance but I don't know if it's possible and if yes, how to do it . I need to access to the parent instance because the shard_id to use is stored into it. Thanks in advance -- 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] SQL join between two tables from two databases
Hi Michael,
Do I need to redefined mapped class ssh_host_keys as a Table object?
ssh_host_keys = Table('ssh_host_keys', metadata,
Column('hostname', VARCHAR(30), primary_key=True),
Column('pub', VARCHAR(1600)),
Column('sha256', CHAR(64)),
Column('priv', VARCHAR(2000)),
schema='keys',
mysql_engine='InnoDB'
)
Do I need to convert mapped class 'Host' to a Table object as well? I would
prefer not to touch this class, since it is part of a separate module, but
if needed, it is possible.
class Host(Base):
__tablename__ = 'hosts'
__table_args__ = {'mysql_engine': 'InnoDB'}
id = Column(u'HostID', INTEGER(), primary_key=True)
hostname = Column(String(length=30))
Can you please give an example how to use schema with a query.join(), for
my scenario (two sessions, one for each DB connection)?
Thanks,
Brian
On 20 January 2015 at 16:12, Michael Bayer mike...@zzzcomputing.com wrote:
Jonathan Vanasco jonat...@findmeon.com wrote:
On Tuesday, January 20, 2015 at 5:34:27 PM UTC-5, Brian Glogower wrote:
Thanks for the idea. Do you have an example?
I don't have a personal example handle, but from the docs...
http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#using-literal-sql
session.query(User).from_statement(
... text(SELECT * FROM users where name=:name)).
\
... params(name='ed').all()
[User(name='ed', fullname='Ed Jones', password='f8s7ccs')]
So you should be able to do something like:
query = Session.query(Host)\
.from_statement(
sqlaclhemy.text(SELECT hostname, sha256 FROM DATABASE1.hosts
LEFT JOIN DATABASE2.ssh_host_keys ssh_host_keys ON ssh_host_keys.hostname
== hosts.hostname)
)
why is text() needed here?these could be the Table objects set up with
“schema=‘schema name’” to start with, then you’d just do the join with
query.join().
--
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.
Re: [sqlalchemy] Sharding, query_chooser and lazy select
Julien Meyer julien.mey...@gmail.com wrote: Hello, I'm using sharding and it works fine except for the relationship. In my mapper, i define a relationship with lazy mode set to select. When I want to access to this relationship, my query_chooser implementation is called and I want to retrieve the parent instance but I don't know if it's possible and if yes, how to do it . I need to access to the parent instance because the shard_id to use is stored into it. tricky, not much support for that. the only thing passed from parent to the actual query are the “load options”. If you made your own MapperOption like this: session.query(Parent).options(ShardId(“some shard)) that ShardId option would be applied to the lazy loader query as well. maybe you could subclass Query so that the ShardId option is applied automatically. the option itself would be like: from sqlalchemy.orm.interfaces import MapperOption class ShardId(MapperOption): propagate_to_loaders = True # the Query will send it to lazy loaders also def __init__(self, shard_id): self.shard_id = shard_id def process_query(self, query): Apply a modification to the given :class:`.Query`.” # apply criteria here if needed def process_query_conditionally(self, query): # same thing, but called only in a lazy loader might be a nifty way to do the sharding overall, if it works out maybe we should add this to the docs. Thanks in advance -- 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.
Re: [sqlalchemy] SQL join between two tables from two databases
You don't need to convert it to a Table object, but you probably do
need to add 'schema': 'whatever' to the __table_args__ dictionary.
In answer to your second question, I very much doubt you can use
query.join() with 2 DB connections. query.join() simply adds an SQL
JOIN clause to the query that is eventually sent to the database -
there's no way of making that work with 2 separate connections.
As an alternative, I think it should be possible to put the tables
that exist in a separate schema in a separate SQLAlchemy MetaData
(they'd need to use a separate declarative Base class). The MetaData
can hold the default schema for the tables, and I *think* you should
be able to use tables from different MetaData in query.join(). (I
haven't tested this though).
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative/basic_use.html#accessing-the-metadata
http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#sqlalchemy.schema.MetaData
http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#metadata-describing
Hope that helps,
Simon
On Wed, Jan 21, 2015 at 9:09 AM, Brian Glogower bglogo...@ifwe.co wrote:
Hi Michael,
Do I need to redefined mapped class ssh_host_keys as a Table object?
ssh_host_keys = Table('ssh_host_keys', metadata,
Column('hostname', VARCHAR(30), primary_key=True),
Column('pub', VARCHAR(1600)),
Column('sha256', CHAR(64)),
Column('priv', VARCHAR(2000)),
schema='keys',
mysql_engine='InnoDB'
)
Do I need to convert mapped class 'Host' to a Table object as well? I would
prefer not to touch this class, since it is part of a separate module, but
if needed, it is possible.
class Host(Base):
__tablename__ = 'hosts'
__table_args__ = {'mysql_engine': 'InnoDB'}
id = Column(u'HostID', INTEGER(), primary_key=True)
hostname = Column(String(length=30))
Can you please give an example how to use schema with a query.join(), for my
scenario (two sessions, one for each DB connection)?
Thanks,
Brian
On 20 January 2015 at 16:12, Michael Bayer mike...@zzzcomputing.com wrote:
Jonathan Vanasco jonat...@findmeon.com wrote:
On Tuesday, January 20, 2015 at 5:34:27 PM UTC-5, Brian Glogower wrote:
Thanks for the idea. Do you have an example?
I don't have a personal example handle, but from the docs...
http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#using-literal-sql
session.query(User).from_statement(
... text(SELECT * FROM users where name=:name)).
\
... params(name='ed').all()
[User(name='ed', fullname='Ed Jones', password='f8s7ccs')]
So you should be able to do something like:
query = Session.query(Host)\
.from_statement(
sqlaclhemy.text(SELECT hostname, sha256 FROM DATABASE1.hosts
LEFT JOIN DATABASE2.ssh_host_keys ssh_host_keys ON ssh_host_keys.hostname
==
hosts.hostname)
)
why is text() needed here?these could be the Table objects set up with
“schema=‘schema name’” to start with, then you’d just do the join with
query.join().
--
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.
--
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] Permanent before_execute Dialect Event
there’s a lot of places you could set that up. The dialect has an initialize(Connection) hook called, you could do the listen on connection.engine at that point. Lycovian mfwil...@gmail.com wrote: Given a custom dialect how can I associate a before_execute event listener for all Engine and Connection objects that use the dialect? I've been able to create listeners for this in a script but haven't been able to make this listener permanent in my dialect. -- 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.
