Re: [sqlalchemy] distinct query
Did you try qry = session.query(AssetCategory).join(Asset).join(Shot).filter(Shot.id==1).distinct() qry = qry.filter(Shot.id==shot_id_of_interest) that generates SELECT DISTINCT "AssetCategory".id AS "AssetCategory_id" FROM "AssetCategory" JOIN "Asset" ON "AssetCategory".id = "Asset".category_id JOIN "Shot" ON "Shot".id = "Asset".shot_id WHERE "Shot".id = :id_1 -- Mike Conley -- 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: double clause in outer join
Silly mistake, this works fine now, thanks! On Mar 19, 12:53 am, Michael Bayer wrote: > On Mar 18, 2010, at 8:28 PM, ellonweb wrote: > > > > > Hi, > > I have a query object to which I'm performing the following join and > > filter: > > Q = Q.outerjoin(Table.history_loader) > > Q = Q.filter(TableHistory.tick == 123) > > Table.history_loader is a dynamic loader that maps the two tables > > based on their id property. > > > This produces the following SQL: > > LEFT OUTER JOIN table_history ON table.id = table_history.id WHERE > > table_history.tick = 123 > > > What I actually want is something like this: > > LEFT OUTER JOIN table_history ON table.id = table_history.id AND > > table_history.tick = 123 > > > What should I do to get this query generated? I've tried the > > following: > > Q = Q.outerjoin((Table.history_loader, TableHistory.tick == > > 123)) > > Q = Q.outerjoin((Table.history_loader, and_(TableHistory, > > TableHistory.tick == 123))) > > the tuple form accepts the target, then the onclause. Table.history_loader > is an onclause in itself you want query(Table).join((TableHistory, > and_(TableHistory.tick==123, TableHistory.id==Table.id)). > > > Q = Q.outerjoin((Table.history_loader, and_(TableHistory.id == > > Table.id, TableHistory.tick == 123))) > > All 3 of these generate the following error: > > AttributeError: 'BooleanClauseList' object has no attribute > > 'is_derived_from' > > > What should I be doing? > > Thanks in advance > > > -- > > 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 > > athttp://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] double clause in outer join
On Mar 18, 2010, at 8:28 PM, ellonweb wrote: > Hi, > I have a query object to which I'm performing the following join and > filter: >Q = Q.outerjoin(Table.history_loader) >Q = Q.filter(TableHistory.tick == 123) > Table.history_loader is a dynamic loader that maps the two tables > based on their id property. > > This produces the following SQL: > LEFT OUTER JOIN table_history ON table.id = table_history.id WHERE > table_history.tick = 123 > > What I actually want is something like this: > LEFT OUTER JOIN table_history ON table.id = table_history.id AND > table_history.tick = 123 > > What should I do to get this query generated? I've tried the > following: >Q = Q.outerjoin((Table.history_loader, TableHistory.tick == > 123)) >Q = Q.outerjoin((Table.history_loader, and_(TableHistory, > TableHistory.tick == 123))) the tuple form accepts the target, then the onclause. Table.history_loader is an onclause in itself you want query(Table).join((TableHistory, and_(TableHistory.tick==123, TableHistory.id==Table.id)). >Q = Q.outerjoin((Table.history_loader, and_(TableHistory.id == > Table.id, TableHistory.tick == 123))) > All 3 of these generate the following error: >AttributeError: 'BooleanClauseList' object has no attribute > 'is_derived_from' > > What should I be doing? > Thanks in advance > > -- > 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] double clause in outer join
Hi, I have a query object to which I'm performing the following join and filter: Q = Q.outerjoin(Table.history_loader) Q = Q.filter(TableHistory.tick == 123) Table.history_loader is a dynamic loader that maps the two tables based on their id property. This produces the following SQL: LEFT OUTER JOIN table_history ON table.id = table_history.id WHERE table_history.tick = 123 What I actually want is something like this: LEFT OUTER JOIN table_history ON table.id = table_history.id AND table_history.tick = 123 What should I do to get this query generated? I've tried the following: Q = Q.outerjoin((Table.history_loader, TableHistory.tick == 123)) Q = Q.outerjoin((Table.history_loader, and_(TableHistory, TableHistory.tick == 123))) Q = Q.outerjoin((Table.history_loader, and_(TableHistory.id == Table.id, TableHistory.tick == 123))) All 3 of these generate the following error: AttributeError: 'BooleanClauseList' object has no attribute 'is_derived_from' What should I be doing? Thanks in advance -- 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] AttributeError: 'cx_Oracle.LOB' object has no attribute 'decode'
that should be fixed in latest tip. get it from the download page.
chris e wrote:
> I am running into the following error running under mod_wsgi, and
> against an Oracle Database, using cx_Oracle
>
> I'm running the following query:
>
> result = select([TABLES.SYSTEM_CONFIG.c.value],
>
> TABLES.SYSTEM_CONFIG.c.key=='email_address').execute().fetchall()
>
>
> The table is defined as follows:
>
> SYSTEM_CONFIG = Table('system_config', bound_meta_data,
> Column('value', UnicodeText(),
> nullable=False),
> schema=schema, autoload=True)
>
> When the query runs I ocassionally get the following error:
>
> Module sqlalchemy.engine.base:1776 in fetchall
> Module sqlalchemy.engine.base:1663 in fetchone
> Module sqlalchemy.engine.base:1379 in __init__
> Module sqlalchemy.engine.base:1620 in _get_col
> Module sqlalchemy.databases.oracle:229 in process
> Module sqlalchemy.types:470 in process
> AttributeError: 'cx_Oracle.LOB' object has no attribute 'decode'
>
>
> Any suggestions as to what might be causing this?
>
> --
> 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] AttributeError: 'cx_Oracle.LOB' object has no attribute 'decode'
I am running into the following error running under mod_wsgi, and
against an Oracle Database, using cx_Oracle
I'm running the following query:
result = select([TABLES.SYSTEM_CONFIG.c.value],
TABLES.SYSTEM_CONFIG.c.key=='email_address').execute().fetchall()
The table is defined as follows:
SYSTEM_CONFIG = Table('system_config', bound_meta_data,
Column('value', UnicodeText(),
nullable=False),
schema=schema, autoload=True)
When the query runs I ocassionally get the following error:
Module sqlalchemy.engine.base:1776 in fetchall
Module sqlalchemy.engine.base:1663 in fetchone
Module sqlalchemy.engine.base:1379 in __init__
Module sqlalchemy.engine.base:1620 in _get_col
Module sqlalchemy.databases.oracle:229 in process
Module sqlalchemy.types:470 in process
AttributeError: 'cx_Oracle.LOB' object has no attribute 'decode'
Any suggestions as to what might be causing this?
--
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: "in_()" operator is not currently implemented for many-to-one-relations - alternatives?
Thanks that worked beautifully.
On a similar note, how would I match documents with only the tags that
I specify in the list? My naive attempt is:
for tag in tag_list:
session.query(Document).join(Document.tags).filter_by(tag=tag)
But that doesn't work.
On Mar 15, 10:54 am, "Michael Bayer" wrote:
> Stodgewrote:
> > I have two classes with a third table:
>
> > document_tags = Table('document_tags', metadata,
> > Column('document_id', Integer, ForeignKey('documents.id')),
> > Column('tag_id', Integer, ForeignKey('tags.id'))
> > )
>
> > class Document(Base):
> > __tablename__ = 'documents'
>
> > id = Column(Integer, primary_key=True)
> > title = Column(String)
> > filename = Column(String)
> > tags = relation('Tag', secondary=document_tags, backref='tags')
>
> > def __init__(self, title, filename):
> > self.title = title
> > self.filename = filename
>
> > class Tag(Base):
> > __tablename__ = 'tags'
>
> > id = Column(Integer, primary_key=True)
> > tag = Column(String)
>
> > def __init__(self, tag):
> > self.tag = tag
>
> > I want to find all documents with tags in a given list of tags:
>
> > documents =
> > session.query(Document).filter(Document.tags.in_(tag_list))
>
> > except I get the familiar message that the "in_()" operator is not
> > currently implemented for many-to-one-relations.
>
> > I've searched and found some alternatives but I can't get any to work.
> > Is there an easy example that will make this work? Thanks
>
> if the error message says "many-to-one" then that's a bug. Your relation
> is many-to-many.
>
> in this case the syntactically easiest method is to use any().
> Document.tags.any(Tag.id.in_([t.id for t in tag_list])).
>
> A join could be more performant, which would be:
>
> query.join(Document.tags).filter(Tag.id.in_([t.id for t in tag_list]))
>
>
>
> > --
> > 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] distinct query
Hello,
I am stuck here with a query, it't not too complicated I think, but I dont
get it...
I have three Class/table mappings:
Shots, Assets, which belong to a Shot (1:n) and AssetCategories, which are
owned by Assets (n:1)
The objective is:
For a given shot instance get all distinct AssetCategories.
To be sure I articulate myself correct: I want all asset categories for
one shot so that there are not doublets within the categories result.
I thought I could achieve this with distinct. How do I use joins with
distinct, or is this the wrong approach?
Thanks for having a look at this!
Here are the definitions:
from sqlalchemy import create_engine, Column,Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relation, backref
engine = create_engine("sqlite:///:memory:",echo=True)
Base=declarative_base()
class Asset(Base):
__tablename__="Asset"
id=Column(Integer,primary_key=True)
shot_id = Column(Integer, ForeignKey('Shot.id'))
shot=relation(Shot,backref=backref('assets',order_by=id))
category_id = Column(Integer, ForeignKey('AssetCategory.id'))
category=relation(AssetCategory,backref=backref('assets',order_by=id))
class AssetCategory(Base):
__tablename__="AssetCategory"
id=Column(Integer,primary_key=True)
class Shot(Base):
__tablename__="Shot"
id=Column(Integer, primary_key=True)
Base.metadata.create_all(engine)
Session=sessionmaker(bind=engine)
session = Session()
Regards
Sebastian
--
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: Calling a function after every new connection is created
Thanks, that's perfect. I knew it had to be in the API somewhere, but
I couldn't find it.
On Mar 18, 5:44 am, Michael Bayer wrote:
> On Mar 17, 2010, at 9:45 PM, chris e wrote:
>
>
>
> > Because of the way that we have our Oracle database setup, I have to
> > do the following to force every connection to use exact cursor
> > sharing.
>
> > dbapi = engine.dialect.dbapi
> > orig_connect = dbapi.connect
> > def exact_connect(*args, **kwargs) :
> > conn_obj = orig_connect(*args, **kwargs)
> > cursor = conn_obj.cursor()
> > cursor.execute('alter session set cursor_sharing = exact');
> > cursor.close()
> > return conn_obj
> > dbapi.connect = exact_connect
>
> > Is there a better way to do this? Is there a way to call a function
> > with the new connection every time one is created by the engine?
>
> sure -
> usehttp://www.sqlalchemy.org/docs/reference/sqlalchemy/interfaces.html?h...
>
>
>
> > --
> > 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
> > athttp://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] Problem with Foreign Key
Damn, your're right! Mea culpa :P
Thanks! Now it's working again
On Thu, Mar 18, 2010 at 6:27 PM, Michael Bayer wrote:
> masetto wrote:
> > Hi all,
> >
> > i am new to SQLAlchemy (simply wonderful!), and i'm writing some
> > python scripts to do some experiment.
> >
> > I've written a SINGLE python module with two classes which define two
> > different tables (declarative_base) with a simple relationship and a
> > single Foreign Key and everything WORKS fine as expected. Cool! :P
> >
> > Then, i've moved that two classes in two different python modules to
> > better organize my code but now i got the following error:
> > sqlalchemy.exc.NoReferencedTableError: Could not find table 'metadata'
> > with which to generate a foreign key
> >
> > I understood the error message but i can't found a way to resolve this
> > issue, can you help me?
> >
> > This is my "directory layout":'
>
>
> you need to share one declarative "Base" class for all of the classes that
> are related to each other, or alternatively at least a single "MetaData"
> for all tables that wish to reference foreign keys using strings.
> Anytime you specify options using strings to find something else, the
> relevant "base" has to be shared, i.e. declarative base if using strings
> in "relation()", and MetaData if using strings in ForeignKey().
>
>
>
>
>
>
>
> >
> > test.py - the main python module
> > SQLTableBuilder_Definition.py - class that define a table called
> > "Definition"
> > SQLTableBuilder_Metadata.py - class that define a table called
> > "Metadata"
> >
> > test.py:
> > ...
> > engine = create_engine('sqlite:///test.db3', echo=True,
> > encoding='utf-8' )
> >
> > import SQLTableBuilder_Metadata
> >
> > metadata_table = SQLTableBuilder_Metadata.MetadataClass.__table__
> > metadata = SQLTableBuilder_Metadata.Base.metadata
> > metadata.create_all(engine)
> >
> > import SQLTableBuilder_Definition
> > definitions_table =
> > SQLTableBuilder_Definition.DefinitionClass.__table__
> > metadata = SQLTableBuilder_Definition.Base.metadata
> > metadata.create_all(engine) <== My script explode here with the
> > following:
> >
> > Traceback (most recent call last):
> > File "test.py", line 82, in
> > metadata.create_all(engine)
> > ...
> > sqlalchemy.exc.NoReferencedTableError: Could not find table
> > 'metadata' with which to generate a foreign key
> >
> >
> > Session = sessionmaker(bind=engine)
> > Session.configure(bind=engine)
> > session = Session()
> >
> > ...
> > session.add(myObj)
> > ...
> >
> > SQLTableBuilder_Definition.py:
> >
> > Base = declarative_base()
> >
> > class DefinitionClass(Base):
> > __tablename__ = 'definitions'
> >
> > defId = Column(Integer, primary_key=True)
> > Id = Column(String)
> > classType = Column(String)
> > version = Column(String)
> >
> > metadataId = Column('metadataId', Integer,
> > ForeignKey('metadata.metadataId'))
> >
> > def __init__(self, node):
> > self.Id = node.get("id")[len(IdName):]
> > self.version = node.get("version")
> > self.classType = node.get("class")
> >
> > SQLTableBuilder_Metadata.py:
> > import SQLTableBuilder_Definition
> >
> > Base = declarative_base()
> >
> > class MetadataClass(Base):
> > __tablename__ = 'metadata'
> >
> > metadataId = Column(Integer, primary_key=True)
> > title = Column(String)
> >
> > defRef = relation(SQLTableBuilder_Definition.DefinitionClass,
> > backref="metadata")
> >
> > def __init__(self, node):
> > self.title = node
> >
> > If i remove relation and foreign key from the two classes, everything
> > works fine again.
> > I suppose that python can't find the metadata table (previously
> > created without error) from SQLTableBuilder_Definition.py, but how i
> > can "point him" in the right direction?
> >
> > Thanks for your attention.
> >
> > ps.
> print sqlalchemy.__version__
> > 0.6beta1
> >
> > --
> > 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.
>
>
--
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: confusion with outer join setup
On Mar 18, 12:24 pm, Jonathan Vanasco wrote: > let me simplify this , maybe it'll make sense to someone presented > differently: > > # do we need to restrict this within a date range ? > dates= [] > if date_start: > dates.append( class_a.timestamp_registered >= > date_start ) > if date_end: > dates.append( class_a.timestamp_registered <= > date_end ) > if sql_and: > dates.append( sql_and ) > if dates : > dates= sqlalchemy.sql.and_( *dates ) > > # generate the sql > sql= sqlalchemy.select( \ > [ > class_a_table.name+'.id', > 'nickname', > 'email_address_id', > 'email_address', > 'timestamp_registered', > ], > dates, > from_obj=[ class_a._osn_table_sa_stash.outerjoin( class_b_table ) ], > order_by=[ class_b_table.name +'.id'], > ) there's nothing present in these examples that would suggest that the literal value attached to a bind would be lost. Its not clear what "sql_and" is here. For further help you'd have to illustrate how your behavior can be reproduced exactly. -- 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] Problem with Foreign Key
masetto wrote:
> Hi all,
>
> i am new to SQLAlchemy (simply wonderful!), and i'm writing some
> python scripts to do some experiment.
>
> I've written a SINGLE python module with two classes which define two
> different tables (declarative_base) with a simple relationship and a
> single Foreign Key and everything WORKS fine as expected. Cool! :P
>
> Then, i've moved that two classes in two different python modules to
> better organize my code but now i got the following error:
> sqlalchemy.exc.NoReferencedTableError: Could not find table 'metadata'
> with which to generate a foreign key
>
> I understood the error message but i can't found a way to resolve this
> issue, can you help me?
>
> This is my "directory layout":'
you need to share one declarative "Base" class for all of the classes that
are related to each other, or alternatively at least a single "MetaData"
for all tables that wish to reference foreign keys using strings.
Anytime you specify options using strings to find something else, the
relevant "base" has to be shared, i.e. declarative base if using strings
in "relation()", and MetaData if using strings in ForeignKey().
>
> test.py - the main python module
> SQLTableBuilder_Definition.py - class that define a table called
> "Definition"
> SQLTableBuilder_Metadata.py - class that define a table called
> "Metadata"
>
> test.py:
> ...
> engine = create_engine('sqlite:///test.db3', echo=True,
> encoding='utf-8' )
>
> import SQLTableBuilder_Metadata
>
> metadata_table = SQLTableBuilder_Metadata.MetadataClass.__table__
> metadata = SQLTableBuilder_Metadata.Base.metadata
> metadata.create_all(engine)
>
> import SQLTableBuilder_Definition
> definitions_table =
> SQLTableBuilder_Definition.DefinitionClass.__table__
> metadata = SQLTableBuilder_Definition.Base.metadata
> metadata.create_all(engine) <== My script explode here with the
> following:
>
> Traceback (most recent call last):
> File "test.py", line 82, in
> metadata.create_all(engine)
> ...
> sqlalchemy.exc.NoReferencedTableError: Could not find table
> 'metadata' with which to generate a foreign key
>
>
> Session = sessionmaker(bind=engine)
> Session.configure(bind=engine)
> session = Session()
>
> ...
> session.add(myObj)
> ...
>
> SQLTableBuilder_Definition.py:
>
> Base = declarative_base()
>
> class DefinitionClass(Base):
> __tablename__ = 'definitions'
>
> defId = Column(Integer, primary_key=True)
> Id = Column(String)
> classType = Column(String)
> version = Column(String)
>
> metadataId = Column('metadataId', Integer,
> ForeignKey('metadata.metadataId'))
>
> def __init__(self, node):
> self.Id = node.get("id")[len(IdName):]
> self.version = node.get("version")
> self.classType = node.get("class")
>
> SQLTableBuilder_Metadata.py:
> import SQLTableBuilder_Definition
>
> Base = declarative_base()
>
> class MetadataClass(Base):
> __tablename__ = 'metadata'
>
> metadataId = Column(Integer, primary_key=True)
> title = Column(String)
>
> defRef = relation(SQLTableBuilder_Definition.DefinitionClass,
> backref="metadata")
>
> def __init__(self, node):
> self.title = node
>
> If i remove relation and foreign key from the two classes, everything
> works fine again.
> I suppose that python can't find the metadata table (previously
> created without error) from SQLTableBuilder_Definition.py, but how i
> can "point him" in the right direction?
>
> Thanks for your attention.
>
> ps.
print sqlalchemy.__version__
> 0.6beta1
>
> --
> 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: defer relation load in a query more than one relation away
Kent wrote:
> I should have mentioned that even when I was using lazyload() I was
> getting the same problem, but I think I corrected my problem with
> this:
>
>
> o=DBSession.query(Order).options(lazyload(Order.orderdetails,OrderDetail.product)).get(u'SALE35425')
>
> I think I understand better now: is it correct that you need to
> "chain" the relations, but calling lazyload() is really only marking a
> single relation as lazy. For example, in the above call, I need
> 'Order.orderdetails' in order to *reach* 'OrderDetail.product', but it
> is *only* 'OrderDetail.product' that is being marked as lazy.
> 'Order.orderdetails' lazy status is unaffected, even though it is in
> the call to lazyload().
>
> Is that accurate?
it is and you can get a shorthand if you use a string, I believe the
syntax is lazyload("orderdetails.product").
>
>
>
> On Mar 17, 4:05 pm, "Michael Bayer" wrote:
>> Kent wrote:
>> > Does defer only relate to ColumnProperty while lazyload relates to
>> > RelationProperty?
>>
>> > Apparently I should be using lazyload() instead of defer()?
>>
>> that is the case at the moment yes.
>>
>> But I can see that perhaps defer() should be dual-purposed here. the
>> "eager/lazy" terminology used to be much more prominent in our
>> documentation so its only recently that this kind of thing had a chance
>> of
>> getting confusing.
>>
>>
>>
>> > --
>> > 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.
>
>
--
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] Problem with Foreign Key
Hi all,
i am new to SQLAlchemy (simply wonderful!), and i'm writing some
python scripts to do some experiment.
I've written a SINGLE python module with two classes which define two
different tables (declarative_base) with a simple relationship and a
single Foreign Key and everything WORKS fine as expected. Cool! :P
Then, i've moved that two classes in two different python modules to
better organize my code but now i got the following error:
sqlalchemy.exc.NoReferencedTableError: Could not find table 'metadata'
with which to generate a foreign key
I understood the error message but i can't found a way to resolve this
issue, can you help me?
This is my "directory layout":
test.py - the main python module
SQLTableBuilder_Definition.py - class that define a table called
"Definition"
SQLTableBuilder_Metadata.py - class that define a table called
"Metadata"
test.py:
...
engine = create_engine('sqlite:///test.db3', echo=True,
encoding='utf-8' )
import SQLTableBuilder_Metadata
metadata_table = SQLTableBuilder_Metadata.MetadataClass.__table__
metadata = SQLTableBuilder_Metadata.Base.metadata
metadata.create_all(engine)
import SQLTableBuilder_Definition
definitions_table =
SQLTableBuilder_Definition.DefinitionClass.__table__
metadata = SQLTableBuilder_Definition.Base.metadata
metadata.create_all(engine) <== My script explode here with the
following:
Traceback (most recent call last):
File "test.py", line 82, in
metadata.create_all(engine)
...
sqlalchemy.exc.NoReferencedTableError: Could not find table
'metadata' with which to generate a foreign key
Session = sessionmaker(bind=engine)
Session.configure(bind=engine)
session = Session()
...
session.add(myObj)
...
SQLTableBuilder_Definition.py:
Base = declarative_base()
class DefinitionClass(Base):
__tablename__ = 'definitions'
defId = Column(Integer, primary_key=True)
Id = Column(String)
classType = Column(String)
version = Column(String)
metadataId = Column('metadataId', Integer,
ForeignKey('metadata.metadataId'))
def __init__(self, node):
self.Id = node.get("id")[len(IdName):]
self.version = node.get("version")
self.classType = node.get("class")
SQLTableBuilder_Metadata.py:
import SQLTableBuilder_Definition
Base = declarative_base()
class MetadataClass(Base):
__tablename__ = 'metadata'
metadataId = Column(Integer, primary_key=True)
title = Column(String)
defRef = relation(SQLTableBuilder_Definition.DefinitionClass,
backref="metadata")
def __init__(self, node):
self.title = node
If i remove relation and foreign key from the two classes, everything
works fine again.
I suppose that python can't find the metadata table (previously
created without error) from SQLTableBuilder_Definition.py, but how i
can "point him" in the right direction?
Thanks for your attention.
ps.
>>> print sqlalchemy.__version__
0.6beta1
--
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: confusion with outer join setup
let me simplify this , maybe it'll make sense to someone presented differently: # do we need to restrict this within a date range ? dates= [] if date_start: dates.append( class_a.timestamp_registered >= date_start ) if date_end: dates.append( class_a.timestamp_registered <= date_end ) if sql_and: dates.append( sql_and ) if dates : dates= sqlalchemy.sql.and_( *dates ) # generate the sql sql= sqlalchemy.select( \ [ class_a_table.name+'.id', 'nickname', 'email_address_id', 'email_address', 'timestamp_registered', ], dates, from_obj=[ class_a._osn_table_sa_stash.outerjoin( class_b_table ) ], order_by=[ class_b_table.name +'.id'], ) Variables used: class_a - mapped class class_a_table - reference to table class_a mapped to class_b_table - reference to table class_b mapped to -- 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: defer relation load in a query more than one relation away
I should have mentioned that even when I was using lazyload() I was getting the same problem, but I think I corrected my problem with this: o=DBSession.query(Order).options(lazyload(Order.orderdetails,OrderDetail.product)).get(u'SALE35425') I think I understand better now: is it correct that you need to "chain" the relations, but calling lazyload() is really only marking a single relation as lazy. For example, in the above call, I need 'Order.orderdetails' in order to *reach* 'OrderDetail.product', but it is *only* 'OrderDetail.product' that is being marked as lazy. 'Order.orderdetails' lazy status is unaffected, even though it is in the call to lazyload(). Is that accurate? On Mar 17, 4:05 pm, "Michael Bayer" wrote: > Kent wrote: > > Does defer only relate to ColumnProperty while lazyload relates to > > RelationProperty? > > > Apparently I should be using lazyload() instead of defer()? > > that is the case at the moment yes. > > But I can see that perhaps defer() should be dual-purposed here. the > "eager/lazy" terminology used to be much more prominent in our > documentation so its only recently that this kind of thing had a chance of > getting confusing. > > > > > -- > > 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] not-null constraint violation in 0.6beta1
On Sat, Mar 13, 2010 at 08:14:46PM -0500, Michael Bayer wrote: > > On Mar 13, 2010, at 6:45 PM, Christoph Ludwig wrote: > > > Hi, > > > > I have an application that used to work fine with SQLAlchemy > > 0.5.6. With 0.6beta1 I observe commit failures when I try to update > > references in a 1-to-many relationship and delete the then unreferences > > object. I did not see anything pertinent to this issue in the 0.6 > > migration guide. > > this is a bug, and the pertinent area of change is that described in > http://www.sqlalchemy.org/trac/wiki/06Migration#Many-to-oneEnhancements . > The first line there which mentions no longer fetching the "old" value is a > feature which needed to be partially rolled back to fix this, as what is > special about your test is that your foreign key is against a non-primary key > column (its only UNIQUE). When the "old" value is present in the current > session, it is in fact needed for the backref accounting to work correctly, > but for all our current tests this apparently has been relying upon the "old" > value identified by primary key and therefore available via the local > identity map. So if the relation can't use a simple get() it has to go > back to the database. > > The test is pending and the ticket is #1737, however I have already committed > the actual fix in r502f1a4f92d5 - the latest is available from mercurial or > the development links at http://www.sqlalchemy.org/download.html . thanks > for the full test case. > > a late "thank you" for the swift confirmation and fix! Cheers, 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.
Re: [sqlalchemy] Assertion Error
rajasekhar911 wrote:
> Hi guys
>
> I got this weird AsserstionError and KeyError. It says it is ignored.
> The class Credential is a mapped as a relation to the parent class.
>
> credential=relation(Credential, \
> primaryjoin=id == Credential.n_id,\
> foreign_keys=[Credential.n_id],\
> uselist=False,cascade='all, delete, delete-
> orphan')
>
> Exception AssertionError: AssertionError('State
> is not
> present in this identity map',) in InstanceState._cleanup of at 0x9f0e12c>> ignored
>
> Exception AssertionError: AssertionError('State
> is
> not present in this identity map',) in MutableAttrInstanceState._cleanup of
> >
> ignored
>
> Exception AssertionError: AssertionError('State
>
> is not present in this identity map',) in MutableAttrInstanceState._cleanup of
> >
> ignored
>
> Exception KeyError: ((,
> (u'6d73d41c-e4ad-a525-1f71-3cf6b1595f05',)),) in MutableAttrInstanceState._cleanup of
> >
> ignored
>
> is this just a warning or an actual exception..
> i tried googling , turned out nothing.
these are state exceptions upon teardown and are likely harmless, though
more information on the context which produces these would help
(especially: exact version of SQLAlchemy in use, steps to reproduce), as
I've never seen such exceptions actually get produced before.
>
> thnx in advance.
>
> --
> 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] Assertion Error
Hi guys
I got this weird AsserstionError and KeyError. It says it is ignored.
The class Credential is a mapped as a relation to the parent class.
credential=relation(Credential, \
primaryjoin=id == Credential.n_id,\
foreign_keys=[Credential.n_id],\
uselist=False,cascade='all, delete, delete-
orphan')
Exception AssertionError: AssertionError('State
is not
present in this identity map',) in > ignored
Exception AssertionError: AssertionError('State
is
not present in this identity map',) in >
ignored
Exception AssertionError: AssertionError('State
is not present in this identity map',) in >
ignored
Exception KeyError: ((,
(u'6d73d41c-e4ad-a525-1f71-3cf6b1595f05',)),) in >
ignored
is this just a warning or an actual exception..
i tried googling , turned out nothing.
thnx in advance.
--
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: ms sql server schema.sequence
Thanks a lot, this is the information I am looking for! -- 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] Calling a function after every new connection is created
On Mar 17, 2010, at 9:45 PM, chris e wrote:
> Because of the way that we have our Oracle database setup, I have to
> do the following to force every connection to use exact cursor
> sharing.
>
>dbapi = engine.dialect.dbapi
>orig_connect = dbapi.connect
>def exact_connect(*args, **kwargs) :
>conn_obj = orig_connect(*args, **kwargs)
>cursor = conn_obj.cursor()
>cursor.execute('alter session set cursor_sharing = exact');
>cursor.close()
>return conn_obj
>dbapi.connect = exact_connect
>
> Is there a better way to do this? Is there a way to call a function
> with the new connection every time one is created by the engine?
sure - use
http://www.sqlalchemy.org/docs/reference/sqlalchemy/interfaces.html?highlight=poollistener#sqlalchemy.interfaces.PoolListener
>
> --
> 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.
