[sqlalchemy] getting the actual sql used for a session.execute(sql,subs)
Hi All, How can I get the actual sql executed by a: session.execute(sql,subs) ? I tried turning on echo in the engine, but that just shows %s where the substitutions should happen. Is there any way to get the post-substitution sql? (so that you can spot bugs where there have been typos in the substitution names (:whatever, etc) in the sql) cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Collation and Column()
Thanks for your quick answer, On Feb 11, 1:32 am, Michael Bayer mike...@zzzcomputing.com wrote: if SQLite supports a COLLATE syntax, then sure we can accept patches for 0.6 /trunk. If you want instant gratification on sqlite just build yourself a UserDefinedType for now (again 0.6/trunk). The library I'm developing for still depends on 0.4.8+ so instant gratification is needed for much earlier versions. I'm thinking about adapting the SQLAlchemy types and injecting my private class. Any hooks I need to know about? A better solution? -Christoph -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: How to order_by relation by another join?
Thanks, Mike!
Your example indeed works, but unfortunately when I add inheritance,
mapper fails to generate proper (inherited) class:
(I've changed code a little, so it represents more what I'm trying to
do)
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite://', echo=True)
Base = declarative_base()
class Detail(Base):
__tablename__ = 'detail'
id = Column(Integer, primary_key=True)
sort = Column(Integer)
class Order(Base):
__tablename__ = 'order'
id = Column(Integer, primary_key=True)
class Item(Base):
__tablename__ = 'item'
id = Column(Integer, primary_key=True)
order_id = Column(Integer, ForeignKey('order.id'))
detail_id = Column(Integer, ForeignKey('detail.id'))
detail = relation(Detail, uselist=False, lazy=False)
order = relation(Order, uselist=False)
type = Column(String(20))
__mapper_args__ = { 'polymorphic_on' : type}
class ValueItem(Item):
__mapper_args__ = { 'polymorphic_identity' : 'quantity' }
value = Column('quantity_value', Numeric(15, 4))
class ErrorItem(Item):
__mapper_args__ = { 'polymorphic_identity' : 'error' }
value = Column('error_value', String(15, 4))
Order.items = relation(Item)
j = Item.__table__.join(Detail.__table__)
itemdetail = mapper(Item, j, non_primary=True)
Order.sorteditems = relation(itemdetail,
order_by=Detail.__table__.c.sort, viewonly=True)
metadata = Base.metadata
metadata.create_all(engine)
Session = scoped_session(sessionmaker(bind=engine))
order = Order(id=1)
Session.add(order)
detail = Detail(id=1, sort=1)
order.items.append(ValueItem(id=1, detail=detail))
Session.commit()
for order in Session.query(Order).all():
for item in order.sorteditems:
print item
for item in order.items:
print item
...
__main__.Item object at 0x881ddac
__main__.ValueItem object at 0x960da6c
Am I making a obvious mistake somewhere here?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Collation and Column()
On Feb 11, 2010, at 6:33 AM, Christoph Burgmer wrote: Thanks for your quick answer, On Feb 11, 1:32 am, Michael Bayer mike...@zzzcomputing.com wrote: if SQLite supports a COLLATE syntax, then sure we can accept patches for 0.6 /trunk. If you want instant gratification on sqlite just build yourself a UserDefinedType for now (again 0.6/trunk). The library I'm developing for still depends on 0.4.8+ so instant gratification is needed for much earlier versions. I'm thinking about adapting the SQLAlchemy types and injecting my private class. Any hooks I need to know about? A better solution? 0.4.8 you'd subclass TypeEngine. I don't think theres too much surprising going on there with types, plus sqlite accepts only unicode strings these days anyway so that's sort of handled too. -Christoph -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: How to order_by relation by another join?
On Feb 11, 2010, at 6:45 AM, Andrija Zarić wrote:
Thanks, Mike!
Your example indeed works, but unfortunately when I add inheritance,
mapper fails to generate proper (inherited) class:
(I've changed code a little, so it represents more what I'm trying to
do)
class ValueItem(Item):
__mapper_args__ = { 'polymorphic_identity' : 'quantity' }
value = Column('quantity_value', Numeric(15, 4))
class ErrorItem(Item):
__mapper_args__ = { 'polymorphic_identity' : 'error' }
value = Column('error_value', String(15, 4))
I'm assuming these are single-table inheritance mappers (I forgot about that
add the column trick..)
So yeah my solution was a quick hack and to continue in this way you'd have to
build non-primary mappers for each of ValueItem, ErrorItem that state
inherits for the original non-primary mapper, using the polymorphic
identities as well. It would still work.
If you don't care much about a high-scaling query you could ditch the secondary
mapper idea and order by a subquery, like
detail_alias = Detail.__table__.alias()
class Order(Base):
items = relation(Item,
order_by=select([detail_alias.c.id]).where(detail_alias.c.id==Item.__table__.c.detail_id).alias())
again somehting i haven't tried, but should work in theory.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite://', echo=True)
Base = declarative_base()
class Detail(Base):
__tablename__ = 'detail'
id = Column(Integer, primary_key=True)
sort = Column(Integer)
class Order(Base):
__tablename__ = 'order'
id = Column(Integer, primary_key=True)
class Item(Base):
__tablename__ = 'item'
id = Column(Integer, primary_key=True)
order_id = Column(Integer, ForeignKey('order.id'))
detail_id = Column(Integer, ForeignKey('detail.id'))
detail = relation(Detail, uselist=False, lazy=False)
order = relation(Order, uselist=False)
type = Column(String(20))
__mapper_args__ = { 'polymorphic_on' : type}
class ValueItem(Item):
__mapper_args__ = { 'polymorphic_identity' : 'quantity' }
value = Column('quantity_value', Numeric(15, 4))
class ErrorItem(Item):
__mapper_args__ = { 'polymorphic_identity' : 'error' }
value = Column('error_value', String(15, 4))
Order.items = relation(Item)
j = Item.__table__.join(Detail.__table__)
itemdetail = mapper(Item, j, non_primary=True)
Order.sorteditems = relation(itemdetail,
order_by=Detail.__table__.c.sort, viewonly=True)
metadata = Base.metadata
metadata.create_all(engine)
Session = scoped_session(sessionmaker(bind=engine))
order = Order(id=1)
Session.add(order)
detail = Detail(id=1, sort=1)
order.items.append(ValueItem(id=1, detail=detail))
Session.commit()
for order in Session.query(Order).all():
for item in order.sorteditems:
print item
for item in order.items:
print item
...
__main__.Item object at 0x881ddac
__main__.ValueItem object at 0x960da6c
Am I making a obvious mistake somewhere here?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: How to order_by relation by another join?
On 11 February 2010 14:26, Michael Bayer mike...@zzzcomputing.com wrote: I'm assuming these are single-table inheritance mappers (I forgot about that add the column trick..) So yeah my solution was a quick hack and to continue in this way you'd have to build non-primary mappers for each of ValueItem, ErrorItem that state inherits for the original non-primary mapper, using the polymorphic identities as well. It would still work. Thanks again! It's obvious now, of course. When I added other non-primary mappers, inheritance is working. I suppose declarative extension spoiled me... -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Collation and Column()
On Feb 11, 2:20 pm, Michael Bayer mike...@zzzcomputing.com wrote: 0.4.8 you'd subclass TypeEngine. I don't think theres too much surprising going on there with types, plus sqlite accepts only unicode strings these days anyway so that's sort of handled too. Thanks, that looks promising. I'll look into 0.6 SQLite later on. -Christoph -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] declaring relations on both sides of a join
Hi All,
Is this the right way to do this:
class Blog(Base):
__tablename__='blog_entry'
id = Column(Integer, primary_key=True)
date = Column('dated', Date, nullable=False)
title = Column(String(80))
entry = Column(Text())
owners_name = Column(ForeignKey('person.name'))
owner = relation('Person', back_populates=blogs)
class Person(Base):
__tablename__='person'
name = Column(String(80), primary_key=True)
blogs = relation('Blog', back_populates=owner)
...or is there something subtle that's going to come back and bite me?
The aim is to make it explicit when looking at one model what its
attributes are, rather than having to guess what other models (which are
often in other files, far from the reader's eye) have placed backrefs on it?
cheers,
Chris
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] getting the actual sql used for a session.execute(sql,subs)
On 2010-2-11 12:13, Chris Withers wrote: Hi All, How can I get the actual sql executed by a: session.execute(sql,subs) ? I tried turning on echo in the engine, but that just shows %s where the substitutions should happen. Is there any way to get the post-substitution sql? SQLAlchemy does not do the substitutions, the DB-API driver does that. So you'll need to look at your database driver to see if that supports logging of commands. You can make postgres log all its commands, perhaps that is an option for you. Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] declaring relations on both sides of a join
Chris Withers wrote:
Hi All,
Is this the right way to do this:
class Blog(Base):
__tablename__='blog_entry'
id = Column(Integer, primary_key=True)
date = Column('dated', Date, nullable=False)
title = Column(String(80))
entry = Column(Text())
owners_name = Column(ForeignKey('person.name'))
owner = relation('Person', back_populates=blogs)
class Person(Base):
__tablename__='person'
name = Column(String(80), primary_key=True)
blogs = relation('Blog', back_populates=owner)
...or is there something subtle that's going to come back and bite me?
The aim is to make it explicit when looking at one model what its
attributes are, rather than having to guess what other models (which are
often in other files, far from the reader's eye) have placed backrefs on
it?
that's the point of it, yup...enjoy the feature
cheers,
Chris
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Wednesday, February 10, 2010 6:30 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Warnings take a really long time / NotImplementedError
On Feb 10, 2010, at 6:05 PM, Jeff Peterson wrote:
It's touching a ton of tables, hundreds...if I had to guess I'd say every table
in the schema. The reasons for this are unknown to me, certainly all those
tables are not related specifically to the single view I am attempting to
reflect.
that shouldn't be possible on a single table reflect, if its a view. Views
have no foreign key metadata so it would have no reason to go anywhere else.
you'd have to provide more specifics in order for us to see how that might be
reproduced.
Seeing this for basicall(I think) everything in the Schema:
INFO [sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT a.index_name, a.column_name, b.uniqueness
FROM ALL_IND_COLUMNS a,
ALL_INDEXES b
WHERE
a.index_name = b.index_name
AND a.table_owner = b.table_owner
AND a.table_name = b.table_name
AND a.table_name = :table_name
AND a.table_owner = :schema
ORDER BY a.index_name, a.column_position
2010-02-11 11:01:43,410 INFO sqlalchemy.engine.base.Engine.0x...5bcc
{'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema': 'CRAR1APP'}
2010-02-11 11:01:43,410 INFO [sqlalchemy.engine.base.Engine.0x...5bcc]
{'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema': 'CRAR1APP'}
2010-02-11 11:01:43,413 INFO sqlalchemy.engine.base.Engine.0x...5bcc SELECT
column_name, data_type, data_length, data_precision, data_scale, nullable,
data_default FROM ALL_TAB_COLUMNS WHERE table_name = :table_name AND owner =
:owner ORDER BY column_id
2010-02-11 11:01:43,413 INFO [sqlalchemy.engine.base.Engine.0x...5bcc] SELECT
column_name, data_type, data_length, data_precision, data_scale, nullable,
data_default FROM ALL_TAB_COLUMNS WHERE table_name = :table_name AND owner =
:owner ORDER BY column_id
2010-02-11 11:01:43,414 INFO sqlalchemy.engine.base.Engine.0x...5bcc {'owner':
'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
2010-02-11 11:01:43,414 INFO [sqlalchemy.engine.base.Engine.0x...5bcc]
{'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
2010-02-11 11:01:43,421 INFO sqlalchemy.engine.base.Engine.0x...5bcc SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2010-02-11 11:01:43,421 INFO [sqlalchemy.engine.base.Engine.0x...5bcc] SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2010-02-11 11:01:43,421 INFO sqlalchemy.engine.base.Engine.0x...5bcc {'owner':
'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
2010-02-111:01:43,421 INFO [sqlalchemy.engine.base.Engine.0x...5bcc] {'owner':
'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
* Snip *
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
that SQL output is specific to 'table_name': 'CFA_CASH_FLOW_STATUS_TAB'
and 'table_name': 'CFA_CASH_FLOW_TAB'. that's two tables.
Jeff Peterson wrote:
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Wednesday, February 10, 2010 6:30 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Warnings take a really long time /
NotImplementedError
On Feb 10, 2010, at 6:05 PM, Jeff Peterson wrote:
It's touching a ton of tables, hundreds...if I had to guess I'd say every
table in the schema. The reasons for this are unknown to me, certainly
all those tables are not related specifically to the single view I am
attempting to reflect.
that shouldn't be possible on a single table reflect, if its a view.
Views have no foreign key metadata so it would have no reason to go
anywhere else. you'd have to provide more specifics in order for us to
see how that might be reproduced.
Seeing this for basicall(I think) everything in the Schema:
INFO [sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT a.index_name, a.column_name, b.uniqueness
FROM ALL_IND_COLUMNS a,
ALL_INDEXES b
WHERE
a.index_name = b.index_name
AND a.table_owner = b.table_owner
AND a.table_name = b.table_name
AND a.table_name = :table_name
AND a.table_owner = :schema
ORDER BY a.index_name, a.column_position
2010-02-11 11:01:43,410 INFO sqlalchemy.engine.base.Engine.0x...5bcc
{'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema': 'CRAR1APP'}
2010-02-11 11:01:43,410 INFO [sqlalchemy.engine.base.Engine.0x...5bcc]
{'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema': 'CRAR1APP'}
2010-02-11 11:01:43,413 INFO sqlalchemy.engine.base.Engine.0x...5bcc
SELECT column_name, data_type, data_length, data_precision, data_scale,
nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name = :table_name
AND owner = :owner ORDER BY column_id
2010-02-11 11:01:43,413 INFO [sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT column_name, data_type, data_length, data_precision, data_scale,
nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name = :table_name
AND owner = :owner ORDER BY column_id
2010-02-11 11:01:43,414 INFO sqlalchemy.engine.base.Engine.0x...5bcc
{'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
2010-02-11 11:01:43,414 INFO [sqlalchemy.engine.base.Engine.0x...5bcc]
{'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
2010-02-11 11:01:43,421 INFO sqlalchemy.engine.base.Engine.0x...5bcc
SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2010-02-11 11:01:43,421 INFO [sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2010-02-11 11:01:43,421 INFO sqlalchemy.engine.base.Engine.0x...5bcc
{'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
2010-02-111:01:43,421 INFO [sqlalchemy.engine.base.Engine.0x...5bcc]
{'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
* Snip *
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
Right, and there is that same code outputted for every table in the schema,
when reflecting that one view. What I posted was just the one snippet, it is
repeated over and over for each different table.
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com]
On Behalf Of Michael Bayer
Sent: Thursday, February 11, 2010 11:46 AM
To: sqlalchemy@googlegroups.com
Subject: RE: [sqlalchemy] Warnings take a really long time /
NotImplementedError
that SQL output is specific to 'table_name': 'CFA_CASH_FLOW_STATUS_TAB'
and 'table_name': 'CFA_CASH_FLOW_TAB'. that's two tables.
Jeff Peterson wrote:
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Wednesday, February 10, 2010 6:30 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Warnings take a really long time /
NotImplementedError
On Feb 10, 2010, at 6:05 PM, Jeff Peterson wrote:
It's touching a ton of tables, hundreds...if I had to guess I'd say
every
table in the schema. The reasons for this are unknown to me,
certainly
all those tables are not related specifically to the single view I am
attempting to reflect.
that shouldn't be possible on a single table reflect, if its a view.
Views have no foreign key metadata so it would have no reason to go
anywhere else. you'd have to provide more specifics in order for us
to
see how that might be reproduced.
Seeing this for basicall(I think) everything in the Schema:
INFO [sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT a.index_name, a.column_name, b.uniqueness
FROM ALL_IND_COLUMNS a,
ALL_INDEXES b
WHERE
a.index_name = b.index_name
AND a.table_owner = b.table_owner
AND a.table_name = b.table_name
AND a.table_name = :table_name
AND a.table_owner = :schema
ORDER BY a.index_name, a.column_position
2010-02-11 11:01:43,410 INFO sqlalchemy.engine.base.Engine.0x...5bcc
{'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema': 'CRAR1APP'}
2010-02-11 11:01:43,410 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
{'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema': 'CRAR1APP'}
2010-02-11 11:01:43,413 INFO sqlalchemy.engine.base.Engine.0x...5bcc
SELECT column_name, data_type, data_length, data_precision,
data_scale,
nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name =
:table_name
AND owner = :owner ORDER BY column_id
2010-02-11 11:01:43,413 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT column_name, data_type, data_length, data_precision,
data_scale,
nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name =
:table_name
AND owner = :owner ORDER BY column_id
2010-02-11 11:01:43,414 INFO sqlalchemy.engine.base.Engine.0x...5bcc
{'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
2010-02-11 11:01:43,414 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
{'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
2010-02-11 11:01:43,421 INFO sqlalchemy.engine.base.Engine.0x...5bcc
SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2010-02-11 11:01:43,421 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
[sqlalchemy] obtaining pid of forked process
Hi, sqlalchemy forks a process when it calls the db (in my case PostgreSQL, but I don't think it matters) using, for example from sqlalchemy.sql import text s = text(...) My question - is it possible to obtain the pid of this process at the python level in some fashion? The reason for this is that I want to plot a memory graph of the postgresql process, so it is handy to have the pid for this. I'm using Linux (Debian lenny) with pg 8.4.2 and sqla 0.5.7. Please cc me on any reply. Thanks. Regards, Faheem. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
I thought you were reflecting a view ? a table will fan out to all of its
constraints, yes.
Jeff Peterson wrote:
Right, and there is that same code outputted for every table in the
schema, when reflecting that one view. What I posted was just the one
snippet, it is repeated over and over for each different table.
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com]
On Behalf Of Michael Bayer
Sent: Thursday, February 11, 2010 11:46 AM
To: sqlalchemy@googlegroups.com
Subject: RE: [sqlalchemy] Warnings take a really long time /
NotImplementedError
that SQL output is specific to 'table_name': 'CFA_CASH_FLOW_STATUS_TAB'
and 'table_name': 'CFA_CASH_FLOW_TAB'. that's two tables.
Jeff Peterson wrote:
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Wednesday, February 10, 2010 6:30 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Warnings take a really long time /
NotImplementedError
On Feb 10, 2010, at 6:05 PM, Jeff Peterson wrote:
It's touching a ton of tables, hundreds...if I had to guess I'd say
every
table in the schema. The reasons for this are unknown to me,
certainly
all those tables are not related specifically to the single view I am
attempting to reflect.
that shouldn't be possible on a single table reflect, if its a view.
Views have no foreign key metadata so it would have no reason to go
anywhere else. you'd have to provide more specifics in order for us
to
see how that might be reproduced.
Seeing this for basicall(I think) everything in the Schema:
INFO [sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT a.index_name, a.column_name, b.uniqueness
FROM ALL_IND_COLUMNS a,
ALL_INDEXES b
WHERE
a.index_name = b.index_name
AND a.table_owner = b.table_owner
AND a.table_name = b.table_name
AND a.table_name = :table_name
AND a.table_owner = :schema
ORDER BY a.index_name, a.column_position
2010-02-11 11:01:43,410 INFO sqlalchemy.engine.base.Engine.0x...5bcc
{'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema': 'CRAR1APP'}
2010-02-11 11:01:43,410 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
{'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema': 'CRAR1APP'}
2010-02-11 11:01:43,413 INFO sqlalchemy.engine.base.Engine.0x...5bcc
SELECT column_name, data_type, data_length, data_precision,
data_scale,
nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name =
:table_name
AND owner = :owner ORDER BY column_id
2010-02-11 11:01:43,413 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT column_name, data_type, data_length, data_precision,
data_scale,
nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name =
:table_name
AND owner = :owner ORDER BY column_id
2010-02-11 11:01:43,414 INFO sqlalchemy.engine.base.Engine.0x...5bcc
{'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
2010-02-11 11:01:43,414 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
{'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
2010-02-11 11:01:43,421 INFO sqlalchemy.engine.base.Engine.0x...5bcc
SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2010-02-11 11:01:43,421 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner =
Re: [sqlalchemy] obtaining pid of forked process
Faheem Mitha wrote: Hi, sqlalchemy forks a process when it calls the db (in my case PostgreSQL, but I don't think it matters) using, for example from sqlalchemy.sql import text s = text(...) um, what ? there's no forking in SQLAlchemy. My question - is it possible to obtain the pid of this process at the python level in some fashion? The reason for this is that I want to plot a memory graph of the postgresql process, so it is handy to have the pid for this. I'm using Linux (Debian lenny) with pg 8.4.2 and sqla 0.5.7. Please cc me on any reply. Thanks. Regards, Faheem. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
That is the troubling part, I am reflecting a view, and yet it is still
touching all those tables in the DB for schema='CRAR1APP'
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com]
On Behalf Of Michael Bayer
Sent: Thursday, February 11, 2010 12:05 PM
To: sqlalchemy@googlegroups.com
Subject: RE: [sqlalchemy] Warnings take a really long time /
NotImplementedError
I thought you were reflecting a view ? a table will fan out to all of
its
constraints, yes.
Jeff Peterson wrote:
Right, and there is that same code outputted for every table in the
schema, when reflecting that one view. What I posted was just the
one
snippet, it is repeated over and over for each different table.
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com]
On Behalf Of Michael Bayer
Sent: Thursday, February 11, 2010 11:46 AM
To: sqlalchemy@googlegroups.com
Subject: RE: [sqlalchemy] Warnings take a really long time /
NotImplementedError
that SQL output is specific to 'table_name':
'CFA_CASH_FLOW_STATUS_TAB'
and 'table_name': 'CFA_CASH_FLOW_TAB'. that's two tables.
Jeff Peterson wrote:
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Wednesday, February 10, 2010 6:30 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Warnings take a really long time /
NotImplementedError
On Feb 10, 2010, at 6:05 PM, Jeff Peterson wrote:
It's touching a ton of tables, hundreds...if I had to guess I'd
say
every
table in the schema. The reasons for this are unknown to me,
certainly
all those tables are not related specifically to the single view I
am
attempting to reflect.
that shouldn't be possible on a single table reflect, if its a
view.
Views have no foreign key metadata so it would have no reason to
go
anywhere else. you'd have to provide more specifics in order for
us
to
see how that might be reproduced.
Seeing this for basicall(I think) everything in the Schema:
INFO [sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT a.index_name, a.column_name, b.uniqueness
FROM ALL_IND_COLUMNS a,
ALL_INDEXES b
WHERE
a.index_name = b.index_name
AND a.table_owner = b.table_owner
AND a.table_name = b.table_name
AND a.table_name = :table_name
AND a.table_owner = :schema
ORDER BY a.index_name, a.column_position
2010-02-11 11:01:43,410 INFO
sqlalchemy.engine.base.Engine.0x...5bcc
{'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema': 'CRAR1APP'}
2010-02-11 11:01:43,410 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
{'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema': 'CRAR1APP'}
2010-02-11 11:01:43,413 INFO
sqlalchemy.engine.base.Engine.0x...5bcc
SELECT column_name, data_type, data_length, data_precision,
data_scale,
nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name =
:table_name
AND owner = :owner ORDER BY column_id
2010-02-11 11:01:43,413 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT column_name, data_type, data_length, data_precision,
data_scale,
nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name =
:table_name
AND owner = :owner ORDER BY column_id
2010-02-11 11:01:43,414 INFO
sqlalchemy.engine.base.Engine.0x...5bcc
{'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
2010-02-11 11:01:43,414 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
{'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
2010-02-11 11:01:43,421 INFO
sqlalchemy.engine.base.Engine.0x...5bcc
SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2010-02-11 11:01:43,421 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
Jeff Peterson wrote:
That is the troubling part, I am reflecting a view, and yet it is still
touching all those tables in the DB for schema='CRAR1APP'
does the name of your view appear at all in ALL_CONS_COLUMNS.TABLE_NAME ?
that's the only way reflection of a view could get the name of a table to
reflect.if you turn on echo='debug' or set sqlalchemy.engine to
DEBUG level logging, you'd see all the rows returned from every query.
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com]
On Behalf Of Michael Bayer
Sent: Thursday, February 11, 2010 12:05 PM
To: sqlalchemy@googlegroups.com
Subject: RE: [sqlalchemy] Warnings take a really long time /
NotImplementedError
I thought you were reflecting a view ? a table will fan out to all of
its
constraints, yes.
Jeff Peterson wrote:
Right, and there is that same code outputted for every table in the
schema, when reflecting that one view. What I posted was just the
one
snippet, it is repeated over and over for each different table.
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com]
On Behalf Of Michael Bayer
Sent: Thursday, February 11, 2010 11:46 AM
To: sqlalchemy@googlegroups.com
Subject: RE: [sqlalchemy] Warnings take a really long time /
NotImplementedError
that SQL output is specific to 'table_name':
'CFA_CASH_FLOW_STATUS_TAB'
and 'table_name': 'CFA_CASH_FLOW_TAB'. that's two tables.
Jeff Peterson wrote:
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Wednesday, February 10, 2010 6:30 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Warnings take a really long time /
NotImplementedError
On Feb 10, 2010, at 6:05 PM, Jeff Peterson wrote:
It's touching a ton of tables, hundreds...if I had to guess I'd
say
every
table in the schema. The reasons for this are unknown to me,
certainly
all those tables are not related specifically to the single view I
am
attempting to reflect.
that shouldn't be possible on a single table reflect, if its a
view.
Views have no foreign key metadata so it would have no reason to
go
anywhere else. you'd have to provide more specifics in order for
us
to
see how that might be reproduced.
Seeing this for basicall(I think) everything in the Schema:
INFO [sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT a.index_name, a.column_name, b.uniqueness
FROM ALL_IND_COLUMNS a,
ALL_INDEXES b
WHERE
a.index_name = b.index_name
AND a.table_owner = b.table_owner
AND a.table_name = b.table_name
AND a.table_name = :table_name
AND a.table_owner = :schema
ORDER BY a.index_name, a.column_position
2010-02-11 11:01:43,410 INFO
sqlalchemy.engine.base.Engine.0x...5bcc
{'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema': 'CRAR1APP'}
2010-02-11 11:01:43,410 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
{'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema': 'CRAR1APP'}
2010-02-11 11:01:43,413 INFO
sqlalchemy.engine.base.Engine.0x...5bcc
SELECT column_name, data_type, data_length, data_precision,
data_scale,
nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name =
:table_name
AND owner = :owner ORDER BY column_id
2010-02-11 11:01:43,413 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT column_name, data_type, data_length, data_precision,
data_scale,
nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name =
:table_name
AND owner = :owner ORDER BY column_id
2010-02-11 11:01:43,414 INFO
sqlalchemy.engine.base.Engine.0x...5bcc
{'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
2010-02-11 11:01:43,414 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
{'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
2010-02-11 11:01:43,421 INFO
sqlalchemy.engine.base.Engine.0x...5bcc
SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
[sqlalchemy] how to set up a simple two-phase commit?
All,
I want to do a two-phase commit.
Published examples are more complex than what I need.
To put it another way, I don't want to use the ORM.
Here is what I want to do:
# This code does what I want - but is missing the two-phase
commit.
# All session code is commented out because I am unsure how to
configure it properly.
import sqlalchemy
mapEngine = sqlalchemy.create_engine('postgresql://
postgres:postg...@localhost:5432/sfmaps')
appEngine = sqlalchemy.create_engine('postgresql://
postgres:postg...@localhost:5432/mad')
# How can a _simply_ bind my connectables to a Session?
# The example from docs is more that I need:
Session.configure(binds={User:engine1, Account:engine2})
#Session = sqlalchemy.sessionmaker(twophase=True)
# of course this next line does not work - I hope to show my
intentions here
#Session.configure(binds={mapEngine, appEngine})
#session = Session()
mapConn = mapEngine.raw_connection()
appConn = appEngine.raw_connection()
mapCursor = mapConn.cursor()
appCursor = appConn.cursor()
# I need to do a lot of stuff like this
#- using different table names
#- using different db procs
mapCursor.execute('truncate table etl_test;')
mapCursor.execute(copy etl_test_staging to 'C:/temp/
etl_test_staging.copy';)
mapCursor.execute(copy etl_test from 'C:/temp/
etl_test_staging.copy';)
appCursor.callproc('_etl_test', ['testing...'])
# Once I have the magic incantations, I'll use session.commit()
instead of the connection.commit()
# session.commit()
mapConn.commit()
appConn.commit()
A similar thread
http://groups.google.com/group/sqlalchemy/browse_thread/thread/b9e42756eb2513b0/a372495356e4eb5e?lnk=raot
Regards
Paul
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] how to set up a simple two-phase commit?
PaulE wrote: All, I want to do a two-phase commit. Published examples are more complex than what I need. To put it another way, I don't want to use the ORM. Here is what I want to do: A similar thread http://groups.google.com/group/sqlalchemy/browse_thread/thread/b9e42756eb2513b0/a372495356e4eb5e?lnk=raot If you dont want to use the ORM or Session, you can use connections directly, using begin_twophase to get back a transaction, and prepare()/commit() on each transaction. begin_twophase is here: http://www.sqlalchemy.org/docs/reference/sqlalchemy/connections.html?highlight=begin_twophase#sqlalchemy.engine.base.Connection.begin_twophase it appears I need to add TwoPhaseTransaction to the docs but its like: engine1 = create_engine(...) engine2 = create_engine(...) conn1 = engine1.connect() conn2 = engine2.connect() trans1 = conn1.begin_twophase() trans2 = conn2.begin_twophase() trans1.prepare() trans2.prepare() trans1.commit() trans2.commit() conn1.close() conn2.close() the notes that Ants Aasma has in that thread are also significant. Regards Paul -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
The view name itself isn't but the names of all the tables that make up that
view are. So I guess that must be why.
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com]
On Behalf Of Michael Bayer
Sent: Thursday, February 11, 2010 12:59 PM
To: sqlalchemy@googlegroups.com
Subject: RE: [sqlalchemy] Warnings take a really long time /
NotImplementedError
Jeff Peterson wrote:
That is the troubling part, I am reflecting a view, and yet it is
still
touching all those tables in the DB for schema='CRAR1APP'
does the name of your view appear at all in ALL_CONS_COLUMNS.TABLE_NAME
?
that's the only way reflection of a view could get the name of a table
to
reflect.if you turn on echo='debug' or set sqlalchemy.engine to
DEBUG level logging, you'd see all the rows returned from every query.
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com]
On Behalf Of Michael Bayer
Sent: Thursday, February 11, 2010 12:05 PM
To: sqlalchemy@googlegroups.com
Subject: RE: [sqlalchemy] Warnings take a really long time /
NotImplementedError
I thought you were reflecting a view ? a table will fan out to all
of
its
constraints, yes.
Jeff Peterson wrote:
Right, and there is that same code outputted for every table in
the
schema, when reflecting that one view. What I posted was just the
one
snippet, it is repeated over and over for each different table.
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com]
On Behalf Of Michael Bayer
Sent: Thursday, February 11, 2010 11:46 AM
To: sqlalchemy@googlegroups.com
Subject: RE: [sqlalchemy] Warnings take a really long time /
NotImplementedError
that SQL output is specific to 'table_name':
'CFA_CASH_FLOW_STATUS_TAB'
and 'table_name': 'CFA_CASH_FLOW_TAB'. that's two tables.
Jeff Peterson wrote:
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Wednesday, February 10, 2010 6:30 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Warnings take a really long time /
NotImplementedError
On Feb 10, 2010, at 6:05 PM, Jeff Peterson wrote:
It's touching a ton of tables, hundreds...if I had to guess I'd
say
every
table in the schema. The reasons for this are unknown to me,
certainly
all those tables are not related specifically to the single
view I
am
attempting to reflect.
that shouldn't be possible on a single table reflect, if its a
view.
Views have no foreign key metadata so it would have no reason
to
go
anywhere else. you'd have to provide more specifics in order
for
us
to
see how that might be reproduced.
Seeing this for basicall(I think) everything in the Schema:
INFO [sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT a.index_name, a.column_name, b.uniqueness
FROM ALL_IND_COLUMNS a,
ALL_INDEXES b
WHERE
a.index_name = b.index_name
AND a.table_owner = b.table_owner
AND a.table_name = b.table_name
AND a.table_name = :table_name
AND a.table_owner = :schema
ORDER BY a.index_name, a.column_position
2010-02-11 11:01:43,410 INFO
sqlalchemy.engine.base.Engine.0x...5bcc
{'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema':
'CRAR1APP'}
2010-02-11 11:01:43,410 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
{'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema':
'CRAR1APP'}
2010-02-11 11:01:43,413 INFO
sqlalchemy.engine.base.Engine.0x...5bcc
SELECT column_name, data_type, data_length, data_precision,
data_scale,
nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name =
:table_name
AND owner = :owner ORDER BY column_id
2010-02-11 11:01:43,413 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT column_name, data_type, data_length, data_precision,
data_scale,
nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name =
:table_name
AND owner = :owner ORDER BY column_id
2010-02-11 11:01:43,414 INFO
sqlalchemy.engine.base.Engine.0x...5bcc
{'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
2010-02-11 11:01:43,414 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
{'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'}
2010-02-11 11:01:43,421 INFO
sqlalchemy.engine.base.Engine.0x...5bcc
SELECT
ac.constraint_name,
ac.constraint_type,
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
Jeff Peterson wrote:
The view name itself isn't but the names of all the tables that make up
that view are. So I guess that must be why.
It is only looking at the columns declared in your view - the Table
reflection logic doesn't actually look at the original definition of the
view (there is a function for that available but that's not what you're
using here).
I'm not familiar with what Oracle does here but if it places view columns
into ALL_CONS_COLUMNS corresponding to the table column they represent,
that would be the effect. But it seems strange that would be the case,
since there's no constraint on your view.
The other possibility is that you are actually reflecting tables somewhere
else.
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com]
On Behalf Of Michael Bayer
Sent: Thursday, February 11, 2010 12:59 PM
To: sqlalchemy@googlegroups.com
Subject: RE: [sqlalchemy] Warnings take a really long time /
NotImplementedError
Jeff Peterson wrote:
That is the troubling part, I am reflecting a view, and yet it is
still
touching all those tables in the DB for schema='CRAR1APP'
does the name of your view appear at all in ALL_CONS_COLUMNS.TABLE_NAME
?
that's the only way reflection of a view could get the name of a table
to
reflect.if you turn on echo='debug' or set sqlalchemy.engine to
DEBUG level logging, you'd see all the rows returned from every query.
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com]
On Behalf Of Michael Bayer
Sent: Thursday, February 11, 2010 12:05 PM
To: sqlalchemy@googlegroups.com
Subject: RE: [sqlalchemy] Warnings take a really long time /
NotImplementedError
I thought you were reflecting a view ? a table will fan out to all
of
its
constraints, yes.
Jeff Peterson wrote:
Right, and there is that same code outputted for every table in
the
schema, when reflecting that one view. What I posted was just the
one
snippet, it is repeated over and over for each different table.
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com]
On Behalf Of Michael Bayer
Sent: Thursday, February 11, 2010 11:46 AM
To: sqlalchemy@googlegroups.com
Subject: RE: [sqlalchemy] Warnings take a really long time /
NotImplementedError
that SQL output is specific to 'table_name':
'CFA_CASH_FLOW_STATUS_TAB'
and 'table_name': 'CFA_CASH_FLOW_TAB'. that's two tables.
Jeff Peterson wrote:
--
Jeffrey D Peterson
Webmaster
Crary Industries, Inc.
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Wednesday, February 10, 2010 6:30 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Warnings take a really long time /
NotImplementedError
On Feb 10, 2010, at 6:05 PM, Jeff Peterson wrote:
It's touching a ton of tables, hundreds...if I had to guess I'd
say
every
table in the schema. The reasons for this are unknown to me,
certainly
all those tables are not related specifically to the single
view I
am
attempting to reflect.
that shouldn't be possible on a single table reflect, if its a
view.
Views have no foreign key metadata so it would have no reason
to
go
anywhere else. you'd have to provide more specifics in order
for
us
to
see how that might be reproduced.
Seeing this for basicall(I think) everything in the Schema:
INFO [sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT a.index_name, a.column_name, b.uniqueness
FROM ALL_IND_COLUMNS a,
ALL_INDEXES b
WHERE
a.index_name = b.index_name
AND a.table_owner = b.table_owner
AND a.table_name = b.table_name
AND a.table_name = :table_name
AND a.table_owner = :schema
ORDER BY a.index_name, a.column_position
2010-02-11 11:01:43,410 INFO
sqlalchemy.engine.base.Engine.0x...5bcc
{'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema':
'CRAR1APP'}
2010-02-11 11:01:43,410 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
{'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema':
'CRAR1APP'}
2010-02-11 11:01:43,413 INFO
sqlalchemy.engine.base.Engine.0x...5bcc
SELECT column_name, data_type, data_length, data_precision,
data_scale,
nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name =
:table_name
AND owner = :owner ORDER BY column_id
2010-02-11 11:01:43,413 INFO
[sqlalchemy.engine.base.Engine.0x...5bcc]
SELECT column_name, data_type, data_length,
RE: [sqlalchemy] Warnings take a really long time / NotImplementedError
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Thursday, February 11, 2010 1:26 PM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Warnings take a really long time / NotImplementedError Jeff Peterson wrote: The view name itself isn't but the names of all the tables that make up that view are. So I guess that must be why. It is only looking at the columns declared in your view - the Table reflection logic doesn't actually look at the original definition of the view (there is a function for that available but that's not what you're using here). I'm not familiar with what Oracle does here but if it places view columns into ALL_CONS_COLUMNS corresponding to the table column they represent, that would be the effect. But it seems strange that would be the case, since there's no constraint on your view. The other possibility is that you are actually reflecting tables somewhere else. If I am it's not on purpose. ;) I was able to make one observation though...during my test, trying to get all the kinks worked out I setup 2 connection strings, 1) the schema owner (who has rights to everything) and 2) my limited user that only has select rights on certain views. When this happens, I am connected as the schema user. When connected as the limited user it's lightning fast (I commented out the create code in the lib, I can't create new tables as it sits but it'll reflect just fine). So, bottom line is, despite the strangeness, I guess I can, just not worry about it, at least for now. But it's clear that when it can't touch those tables it doesn't perform those commands. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Thursday, February 11, 2010 12:59 PM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Warnings take a really long time / NotImplementedError Jeff Peterson wrote: That is the troubling part, I am reflecting a view, and yet it is still touching all those tables in the DB for schema='CRAR1APP' does the name of your view appear at all in ALL_CONS_COLUMNS.TABLE_NAME ? that's the only way reflection of a view could get the name of a table to reflect.if you turn on echo='debug' or set sqlalchemy.engine to DEBUG level logging, you'd see all the rows returned from every query. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Thursday, February 11, 2010 12:05 PM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Warnings take a really long time / NotImplementedError I thought you were reflecting a view ? a table will fan out to all of its constraints, yes. Jeff Peterson wrote: Right, and there is that same code outputted for every table in the schema, when reflecting that one view. What I posted was just the one snippet, it is repeated over and over for each different table. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Thursday, February 11, 2010 11:46 AM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Warnings take a really long time / NotImplementedError that SQL output is specific to 'table_name': 'CFA_CASH_FLOW_STATUS_TAB' and 'table_name': 'CFA_CASH_FLOW_TAB'. that's two tables. Jeff Peterson wrote: -- Jeffrey D Peterson Webmaster Crary Industries, Inc. From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Wednesday, February 10, 2010 6:30 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Warnings take a really long time / NotImplementedError On Feb 10, 2010, at 6:05 PM, Jeff Peterson wrote: It's touching a ton of tables, hundreds...if I had to guess I'd say every table in the schema. The reasons for this are unknown to me, certainly all those tables are not related specifically to the single view I am attempting to reflect. that shouldn't be possible on a single table reflect, if its a view. Views have no foreign key metadata so it would have no reason to go anywhere else. you'd have to provide more specifics in order for us to see how that might be reproduced. Seeing this for basicall(I think) everything in the Schema: INFO
[sqlalchemy] Automatic prefix on all tables
Hi, First, many thanks to everyone who helped make SQLAlchemy such a great module. I'm currently using the declarative syntax and I would like to know weither it's possible or not to automatically add a prefix on all tables, without having to specify it on each and every table separately. That is, I would like to define the tables using __tablename__ = sometable, but the metadata to create the tables (and references in foreign keys, etc.) as someprefix_sometable. This seems similar to what .with_prefix() or column_prefix provide, though it would operate on tables instead of columns. I found this thread which seems to be similar to what I'm trying to achieve, but would prefer a definitive answer (that thread has been left unanswered) : http://groups.google.com/group/sqlalchemy/browse_thread/thread/caf89246e8ca/b66ff46a0eaeb543 Thank you for your help. Cheers, François. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Automatic prefix on all tables
F. Poirotte wrote: Hi, First, many thanks to everyone who helped make SQLAlchemy such a great module. I'm currently using the declarative syntax and I would like to know weither it's possible or not to automatically add a prefix on all tables, without having to specify it on each and every table separately. That is, I would like to define the tables using __tablename__ = sometable, but the metadata to create the tables (and references in foreign keys, etc.) as someprefix_sometable. This seems similar to what .with_prefix() or column_prefix provide, though it would operate on tables instead of columns. I found this thread which seems to be similar to what I'm trying to achieve, but would prefer a definitive answer (that thread has been left unanswered) : http://groups.google.com/group/sqlalchemy/browse_thread/thread/caf89246e8ca/b66ff46a0eaeb543 that thread has to do with copying tables which is not what you're looking for here. Usually I tell people to create a Table object using a def that passes in the new name. But since you want the table renamed using declarative + __tablename__, you'd need to use a metaclass: class RenameTables(DeclarativeMeta): def __init__(cls, classname, bases, dict_): if '__tablename__' in dict_: cls.__tablename__ = dict_['__tablename__'] = prefix_ + cls.__tablename__ return DeclarativeMeta.__init__(cls, classname, bases, dict_) Base = declarative_base(metaclass=RenameTables) class Usr(Base): __tablename__ = 'users' id = Column(Integer, primary_key = True) name = Column(String) you can also put RenameTables on individual classes with __metaclass__ if thats what you want. Thank you for your help. Cheers, François. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] pyodbc - sqlserver in mac os x
I wrote a (long) blog post on this for Leopard. I haven't had the chance to try it out on Snow Leopard. http://blog.singletoned.net/2009/07/connecting-to-ms-sql-server-from-python-on-mac-os-x-leopard/ If anyone can tell me how to create virtual machines of Snow Leopard, I'd be happy to try and set it up again and document it. (the fact that it might not work is what's stopping me upgrading) Also, any corrections would be greatly appreciated... Ed On 8 Feb 2010, at 19:02, Domingo Aguilera wrote: What you guys use to connect from mac os x to sql server using pyodbc ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en . -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Insert from Select Implentation
I've been having a clumsy hack at enabling myself to pass a select statement as a value to an insert statement. IE: sa.insert(mytable).values(myothertable.select()) I've got it working in that most basic case, but I'm struggling when the select statement has bindparams. The insert needs to take them, as well as (for some dialects) the column names of the select. The thing is I can work out how to get the bindparams from a compiled statement, and I can work out how to get the column names from a uncompiled statement, but I can't work out how to get both from one or the other. Any hints on this would be appreciated. Also, I slightly worried that there's an obvious reason why this hasn't been done before. Is there something stupid I'm missing? If anyone's tried patching SA to do this I would greatly appreciate any advice. Thanks Ed -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
