[sqlalchemy] Re: Shouldn't MetaData.reflect() be able to reflect views, too?
was not aware that metadata.reflect() was doing views in 0.5, and this feature is fine if someone has time to work on it. (trac ticket is a good start) Below is a tiny diff that allows for reflection of views (so that our 0.5.7-version based unit test passes without changes again). Haven't tested beyond this. If you agree, I will attach this to a ticket which I will create on your wiki. 1870c1870 conn = None --- conn = bind.engine.contextual_connect() 1879a1880,1881 available.update(bind.engine.dialect.get_view_names(conn, schema=schema)) -- 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] Sql alchemy-Oracle Error
Hi
I am using sqlalchemy in my TurboGears application.
some of my classes has columns with Pickletype dataType.
these get converted to BLOB dataType in the database.
I was using mySql till recently and everything was working fine.
Now i am shifting to oracle. Tables are getting created properly.
(setup-app in tg project ran successfully). But when i try to query
a table having BLOB column , i get the following error.
Exception in thread Thread-22:
Traceback (most recent call last):
File /usr/lib/python2.4/threading.py, line 442, in __bootstrap
self.run()
File /svnee/trunk/src/core/services/task_service.py, line 76, in
check_calendar_tasks
for cal in conn.query(Task).\
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/orm/query.py, line 1361, in __iter__
return self._execute_and_instances(context)
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/orm/query.py, line 1364, in
_execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self._mapper_zero_or_none())
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/orm/session.py, line 754, in execute
return self.__connection(engine, close_with_result=True).execute(
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py, line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py, line 874, in
_execute_clauseelement
return self.__execute_context(context)
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py, line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py, line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py, line 931, in
_handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
DatabaseError: (DatabaseError) ORA-01036: illegal variable name/number
i did a normal select query from sqlplus:
select * from tasks;
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
These are my class and table definitions:
class Task(DeclarativeBase):
task_id = Column(Integer,Sequence('id_seq'), primary_key=True)
task_type = Column(Unicode(50), default=to_unicode('Task'))
name = Column(Unicode(256))
entity_id = Column(Unicode(256))
entity_name = Column(Unicode(50))
context = Column(PickleType)
params = Column(PickleType)
kw_params = Column(PickleType)
processors = Column(ImmutablePickleType)
_tablename_=tasks
TASK_ID Number
TASK_TYPEVarchar2
NAME Varchar2
ENTITY_IDVarchar2
ENTITY_NAME Varchar2
CONTEXT Blob
PARAMS Blob
KW_PARAMSBlob
PROCESSORS Blob
USER_NAME Varchar2
SUBMITTED_ON Date 7
REPEATING Number
anyone has any idea what's wrong?
please help .
Thanks
--
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] SQLAlchemy, ScopedSession and multi-threaded environment
Hi, I read the documentation about scoped sessions but l'm not sure to understand well what can be shared between threads and what cannot be. Can we share a ScopedSession instance between threads ? Do we have to share only the sessionmaker and re-create a ScoppedSession in each threads ? I have a multi-threaded script witch operate on a database with the following pattern: Regards, F. -- maker = sessionmaker(autoflush=True, autocommit=True) DBSession = scoped_session(maker) DeclarativeBase = declarative_base() metadata = DeclarativeBase.metadata ... class Action1(Thread): def __init__(self): def start(self): while True: some actions on database query flush etc... class Action2(Thread): def __init__(self): . def start(self): while True: some actions on the same database query flush etc... -- -- 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] Sql alchemy-Oracle Error
On Wed, May 5, 2010 at 7:42 AM, dhanil anupurath
dhanilanupur...@gmail.comwrote:
Hi
I am using sqlalchemy in my TurboGears application.
some of my classes has columns with Pickletype dataType.
these get converted to BLOB dataType in the database.
I was using mySql till recently and everything was working fine.
Now i am shifting to oracle. Tables are getting created properly.
(setup-app in tg project ran successfully). But when i try to query
a table having BLOB column , i get the following error.
Exception in thread Thread-22:
Traceback (most recent call last):
File /usr/lib/python2.4/threading.py, line 442, in __bootstrap
self.run()
File /svnee/trunk/src/core/services/task_service.py, line 76, in
check_calendar_tasks
for cal in conn.query(Task).\
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/orm/query.py, line 1361, in __iter__
return self._execute_and_instances(context)
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/orm/query.py, line 1364, in
_execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self._mapper_zero_or_none())
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/orm/session.py, line 754, in execute
return self.__connection(engine, close_with_result=True).execute(
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py, line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py, line 874, in
_execute_clauseelement
return self.__execute_context(context)
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py, line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py, line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py, line 931, in
_handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
DatabaseError: (DatabaseError) ORA-01036: illegal variable name/number
i did a normal select query from sqlplus:
select * from tasks;
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
These are my class and table definitions:
class Task(DeclarativeBase):
task_id = Column(Integer,Sequence('id_seq'), primary_key=True)
task_type = Column(Unicode(50), default=to_unicode('Task'))
name = Column(Unicode(256))
entity_id = Column(Unicode(256))
entity_name = Column(Unicode(50))
context = Column(PickleType)
params = Column(PickleType)
kw_params = Column(PickleType)
processors = Column(ImmutablePickleType)
_tablename_=tasks
TASK_ID Number
TASK_TYPEVarchar2
NAME Varchar2
ENTITY_IDVarchar2
ENTITY_NAME Varchar2
CONTEXT Blob
PARAMS Blob
KW_PARAMSBlob
PROCESSORS Blob
USER_NAME Varchar2
SUBMITTED_ON Date 7
REPEATING Number
Any chance you could send the query that's generating the python traceback?
You should be able to see the generated queries if you send echo=True in the
create_engine arguments.
--
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] Reflecting all tables in a database that creates objects with declarative objects
I am new to Sqlalchemy and ORM in general. I also have a slightly odd use case. My existing database is a set of very simple tables. Each table has the exact same layout/columns just different names, for different collections of information. So I looked through the Documentation and it seems to get this to work I would have to create a different class instance for each table, even though they have the same layout. Using reflection with autoload means I need to edit the code for every new table (could be dozens) with empty class definitions. The autoload per table to create objects where the columns were members of the class (ie mytable.mycolumn) This presents a maintenance issue I would like to avoid. So I tried using 'meta.reflect(bind=engine)' which does indeed read all my tables in without the maintenance issues. However it creates generic Table() classes with no interface additions for the columns. This makes my queries basic SQL with some added sqlalchemy calls to get the table and dereference the columns. Also not desirable. I believe this is due to the fact that Sqlalchemy chooses to modify the class definitions on reflection as opposed to class instances. Is there an easy way to flip this so that the result is a list of table objects that behave as a manually prepared/loaded/reflected object? I am guessing no, but wanted to check with the experts before giving up and using non-ORM straight SQL. Thanks. -- 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] SQLAlchemy, ScopedSession and multi-threaded environment
On May 5, 2010, at 4:20 AM, fv wrote: Hi, I read the documentation about scoped sessions but l'm not sure to understand well what can be shared between threads and what cannot be. Can we share a ScopedSession instance between threads ? Do we have to share only the sessionmaker and re-create a ScoppedSession in each threads ? The ScopedSession object is an interface to set of Session objects contained within a threading.local() object, and such is designed to be globally accessed by multiple threads. Each thread will ultimately talk to a separate Session that is local to the current thread. -- 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] Polymorphic on another table?
i have 4 tables, a, b, c, d.
a has one-to-many relation with b, b with one-to-one relationship with c, c
is a polymorphic on a.type, with d being one of the polymorphic types.
is there a way to implement this?
details:
this is what im trying to do in sqlalchemy:
a = Table('a', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'type', UnicodeText() ) )
b = Table('b', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'a_id', Integer(), ForeignKey('a.id') ) )
c = Table('c', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'b_id', Integer(), ForeignKey('b.id') ),
Column( 'class_id', Integer() ) )
d = Table('d', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'data', Integer() )
mappers
mapper( A, a )
mapper( B, b, properties={'a': relationship( A,
uselist=False,backref='b',
'c':relationship( C, uselist=False, backref='b') })
# Does a full join, does not work
mapper( C, c, polymorphic_on = a.c.type )
mapper( D, d, inherits=C, polymorphic_identity = D )
how can i change c to polymorphic on a, through the relationship? is there
a way to sort by d.data? (b-c/d is one-on-one).
--
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] Reflecting all tables in a database that creates objects with declarative objects
On May 5, 2010, at 4:43 PM, Chris Angove wrote:
I am new to Sqlalchemy and ORM in general. I also have a slightly odd
use case. My existing database is a set of very simple tables. Each
table has the exact same layout/columns just different names, for
different collections of information. So I looked through the
Documentation and it seems to get this to work I would have to create
a different class instance for each table, even though they have the
same layout. Using reflection with autoload means I need to edit the
code for every new table (could be dozens) with empty class
definitions. The autoload per table to create objects where the
columns were members of the class (ie mytable.mycolumn)
Since Python allows dynamic generation of classes, there are easy ways to deal
with this.
This presents a maintenance issue I would like to avoid. So I tried
using 'meta.reflect(bind=engine)' which does indeed read all my tables
in without the maintenance issues. However it creates generic Table()
classes with no interface additions for the columns. This makes my
queries basic SQL with some added sqlalchemy calls to get the table
and dereference the columns. Also not desirable.
I believe this is due to the fact that Sqlalchemy chooses to modify
the class definitions on reflection as opposed to class instances.
I am assuming there is a breakdown of terminology occuring here. The
reflection process produces instances of the Table object with contained
Columns. These are not classes, they are instances. The classes in use are
sqlalchemy.schema.Table and sqlalchemy.schema.Column, and these do not change
nor are they dynamically extended.There is also no dereferencing step I'm
aware of unless you are referring to the present of the Column objects on the
.c. collection. The table metadata produced by the MetaData object has no
knowledge of the ORM or the declarative extension, but the ORM builds upon
these constructs.
It sounds like you'd like a reflection process that produces declarative-bound
ORM classes, not Table instances. The recipe below achieves this, by
generating a new declarative class for each Table reflected by the metadata.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
def declared_classes_for_tables(Base, engine):
result = {}
Base.metadata.reflect(engine)
for table in Base.metadata.tables.values():
cls_ = type(str(table.name), (Base,), {'__table__':table})
result[cls_.__name__] = cls_
return result
# demo
if __name__ == '__main__':
engine = create_engine('sqlite://', echo=True)
metadata = MetaData()
for name in ('peas', 'carrots', 'celery', 'brocolli'):
Table(name, metadata,
Column('id', Integer, primary_key=True),
Column('data', String(50))
)
metadata.create_all(engine)
# now reflect the database into classes
Base = declarative_base()
classes = declared_classes_for_tables(Base, engine)
sess = sessionmaker(engine)()
sess.add_all([
classes['carrots'](data='carrot1'),
classes['peas'](data='pea1')
])
print sess.query(classes['carrots']).all()
print sess.query(classes['peas']).all()
--
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] Polymorphic on another table?
On May 5, 2010, at 5:01 PM, Timmy Chan wrote:
i have 4 tables, a, b, c, d.
a has one-to-many relation with b, b with one-to-one relationship with c, c
is a polymorphic on a.type, with d being one of the polymorphic types.
is there a way to implement this?
details:
this is what im trying to do in sqlalchemy:
a = Table('a', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'type', UnicodeText() ) )
b = Table('b', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'a_id', Integer(), ForeignKey('a.id') ) )
c = Table('c', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'b_id', Integer(), ForeignKey('b.id') ),
Column( 'class_id', Integer() ) )
d = Table('d', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'data', Integer() )
mappers
mapper( A, a )
mapper( B, b, properties={'a': relationship( A, uselist=False,backref='b',
'c':relationship( C, uselist=False, backref='b') })
# Does a full join, does not work
mapper( C, c, polymorphic_on = a.c.type )
mapper( D, d, inherits=C, polymorphic_identity = D )
how can i change c to polymorphic on a, through the relationship? is there
a way to sort by d.data? (b-c/d is one-on-one).
this mapping is incorrect. mapper(C) cannot be polymorphic on a table which
is not part of its mapping, and mapper(C) does not contain an inherits
keyword to that of A. Usually the polymorphic_on setting is on the
base-most mapper in the hierarchy and its not clear here which mapper you
intend for that to be.
--
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] Polymorphic on another table?
thx, is there a way to set that up, or something similar?
after giving it thought, maybe polymorphic_on isn't what i need. in some
sense, table A is similar to a generic container, and i want to constrain
the type of C depending on A's type column. is there a way to do this?
On Wed, May 5, 2010 at 5:15 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On May 5, 2010, at 5:01 PM, Timmy Chan wrote:
i have 4 tables, a, b, c, d.
a has one-to-many relation with b, b with one-to-one relationship with c, c
is a polymorphic on a.type, with d being one of the polymorphic types.
is there a way to implement this?
details:
this is what im trying to do in sqlalchemy:
a = Table('a', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'type', UnicodeText() ) )
b = Table('b', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'a_id', Integer(), ForeignKey('a.id') ) )
c = Table('c', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'b_id', Integer(), ForeignKey('b.id') ),
Column( 'class_id', Integer() ) )
d = Table('d', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'data', Integer() )
mappers
mapper( A, a )
mapper( B, b, properties={'a': relationship( A,
uselist=False,backref='b',
'c':relationship( C, uselist=False, backref='b') })
# Does a full join, does not work
mapper( C, c, polymorphic_on = a.c.type )
mapper( D, d, inherits=C, polymorphic_identity = D )
how can i change c to polymorphic on a, through the relationship? is
there a way to sort by d.data? (b-c/d is one-on-one).
this mapping is incorrect. mapper(C) cannot be polymorphic on a table
which is not part of its mapping, and mapper(C) does not contain an
inherits keyword to that of A. Usually the polymorphic_on setting is
on the base-most mapper in the hierarchy and its not clear here which mapper
you intend for that to be.
--
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.comsqlalchemy%2bunsubscr...@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] Polymorphic on another table?
what query do you want to see when you select C and D objects ?
On May 5, 2010, at 5:26 PM, Timmy Chan wrote:
thx, is there a way to set that up, or something similar?
after giving it thought, maybe polymorphic_on isn't what i need. in some
sense, table A is similar to a generic container, and i want to constrain
the type of C depending on A's type column. is there a way to do this?
On Wed, May 5, 2010 at 5:15 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On May 5, 2010, at 5:01 PM, Timmy Chan wrote:
i have 4 tables, a, b, c, d.
a has one-to-many relation with b, b with one-to-one relationship with c, c
is a polymorphic on a.type, with d being one of the polymorphic types.
is there a way to implement this?
details:
this is what im trying to do in sqlalchemy:
a = Table('a', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'type', UnicodeText() ) )
b = Table('b', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'a_id', Integer(), ForeignKey('a.id') ) )
c = Table('c', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'b_id', Integer(), ForeignKey('b.id') ),
Column( 'class_id', Integer() ) )
d = Table('d', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'data', Integer() )
mappers
mapper( A, a )
mapper( B, b, properties={'a': relationship( A,
uselist=False,backref='b',
'c':relationship( C, uselist=False, backref='b') })
# Does a full join, does not work
mapper( C, c, polymorphic_on = a.c.type )
mapper( D, d, inherits=C, polymorphic_identity = D )
how can i change c to polymorphic on a, through the relationship? is there
a way to sort by d.data? (b-c/d is one-on-one).
this mapping is incorrect. mapper(C) cannot be polymorphic on a table
which is not part of its mapping, and mapper(C) does not contain an
inherits keyword to that of A. Usually the polymorphic_on setting is on
the base-most mapper in the hierarchy and its not clear here which mapper you
intend for that to be.
--
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.
Re: [sqlalchemy] Polymorphic on another table?
C/D objects doesn't exist without A and B? maybe walk up the graph:
C.b.a.type
im not sure this schema is sensible, if it's really bad, please advise, im
not attached to it!
thax!
On Wed, May 5, 2010 at 5:28 PM, Michael Bayer mike...@zzzcomputing.comwrote:
what query do you want to see when you select C and D objects ?
On May 5, 2010, at 5:26 PM, Timmy Chan wrote:
thx, is there a way to set that up, or something similar?
after giving it thought, maybe polymorphic_on isn't what i need. in some
sense, table A is similar to a generic container, and i want to constrain
the type of C depending on A's type column. is there a way to do this?
On Wed, May 5, 2010 at 5:15 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On May 5, 2010, at 5:01 PM, Timmy Chan wrote:
i have 4 tables, a, b, c, d.
a has one-to-many relation with b, b with one-to-one relationship with c,
c is a polymorphic on a.type, with d being one of the polymorphic types.
is there a way to implement this?
details:
this is what im trying to do in sqlalchemy:
a = Table('a', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'type', UnicodeText() ) )
b = Table('b', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'a_id', Integer(), ForeignKey('a.id') ) )
c = Table('c', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'b_id', Integer(), ForeignKey('b.id') ),
Column( 'class_id', Integer() ) )
d = Table('d', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'data', Integer() )
mappers
mapper( A, a )
mapper( B, b, properties={'a': relationship( A,
uselist=False,backref='b',
'c':relationship( C, uselist=False, backref='b') })
# Does a full join, does not work
mapper( C, c, polymorphic_on = a.c.type )
mapper( D, d, inherits=C, polymorphic_identity = D )
how can i change c to polymorphic on a, through the relationship? is
there a way to sort by d.data? (b-c/d is one-on-one).
this mapping is incorrect. mapper(C) cannot be polymorphic on a table
which is not part of its mapping, and mapper(C) does not contain an
inherits keyword to that of A. Usually the polymorphic_on setting is
on the base-most mapper in the hierarchy and its not clear here which mapper
you intend for that to be.
--
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.comsqlalchemy%2bunsubscr...@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.comsqlalchemy%2bunsubscr...@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] Polymorphic on another table?
I meant SQL.if you want to do polymorphic_on based on a.type it would at
the very least need to issue:
SELECT c.*, a.type JOIN b ON c.b_id = b.id JOIN a ON b.a_id=a
which is an expensive way to get to where you're going. It can be done of
course but not through relation().
On May 5, 2010, at 5:36 PM, Timmy Chan wrote:
C/D objects doesn't exist without A and B? maybe walk up the graph:
C.b.a.type
im not sure this schema is sensible, if it's really bad, please advise, im
not attached to it!
thax!
On Wed, May 5, 2010 at 5:28 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
what query do you want to see when you select C and D objects ?
On May 5, 2010, at 5:26 PM, Timmy Chan wrote:
thx, is there a way to set that up, or something similar?
after giving it thought, maybe polymorphic_on isn't what i need. in some
sense, table A is similar to a generic container, and i want to constrain
the type of C depending on A's type column. is there a way to do this?
On Wed, May 5, 2010 at 5:15 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On May 5, 2010, at 5:01 PM, Timmy Chan wrote:
i have 4 tables, a, b, c, d.
a has one-to-many relation with b, b with one-to-one relationship with c, c
is a polymorphic on a.type, with d being one of the polymorphic types.
is there a way to implement this?
details:
this is what im trying to do in sqlalchemy:
a = Table('a', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'type', UnicodeText() ) )
b = Table('b', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'a_id', Integer(), ForeignKey('a.id') ) )
c = Table('c', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'b_id', Integer(), ForeignKey('b.id') ),
Column( 'class_id', Integer() ) )
d = Table('d', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'data', Integer() )
mappers
mapper( A, a )
mapper( B, b, properties={'a': relationship( A,
uselist=False,backref='b',
'c':relationship( C, uselist=False, backref='b') })
# Does a full join, does not work
mapper( C, c, polymorphic_on = a.c.type )
mapper( D, d, inherits=C, polymorphic_identity = D )
how can i change c to polymorphic on a, through the relationship? is
there a way to sort by d.data? (b-c/d is one-on-one).
this mapping is incorrect. mapper(C) cannot be polymorphic on a table
which is not part of its mapping, and mapper(C) does not contain an
inherits keyword to that of A. Usually the polymorphic_on setting is
on the base-most mapper in the hierarchy and its not clear here which mapper
you intend for that to be.
--
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.
--
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] Polymorphic on another table?
thx, that is what i am wondering. they are one-to-one, maybe thats good
enough for performance. so there's no easy way to do this.
On Wed, May 5, 2010 at 5:44 PM, Michael Bayer mike...@zzzcomputing.comwrote:
I meant SQL.if you want to do polymorphic_on based on a.type it would
at the very least need to issue:
SELECT c.*, a.type JOIN b ON c.b_id = b.id JOIN a ON b.a_id=a
which is an expensive way to get to where you're going. It can be done of
course but not through relation().
On May 5, 2010, at 5:36 PM, Timmy Chan wrote:
C/D objects doesn't exist without A and B? maybe walk up the
graph: C.b.a.type
im not sure this schema is sensible, if it's really bad, please advise, im
not attached to it!
thax!
On Wed, May 5, 2010 at 5:28 PM, Michael Bayer mike...@zzzcomputing.comwrote:
what query do you want to see when you select C and D objects ?
On May 5, 2010, at 5:26 PM, Timmy Chan wrote:
thx, is there a way to set that up, or something similar?
after giving it thought, maybe polymorphic_on isn't what i need. in some
sense, table A is similar to a generic container, and i want to constrain
the type of C depending on A's type column. is there a way to do this?
On Wed, May 5, 2010 at 5:15 PM, Michael Bayer
mike...@zzzcomputing.comwrote:
On May 5, 2010, at 5:01 PM, Timmy Chan wrote:
i have 4 tables, a, b, c, d.
a has one-to-many relation with b, b with one-to-one relationship with c,
c is a polymorphic on a.type, with d being one of the polymorphic types.
is there a way to implement this?
details:
this is what im trying to do in sqlalchemy:
a = Table('a', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'type', UnicodeText() ) )
b = Table('b', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'a_id', Integer(), ForeignKey('a.id') ) )
c = Table('c', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'b_id', Integer(), ForeignKey('b.id') ),
Column( 'class_id', Integer() ) )
d = Table('d', metadata,
Column( 'id', Integer(), primary_key=True ),
Column( 'data', Integer() )
mappers
mapper( A, a )
mapper( B, b, properties={'a': relationship( A,
uselist=False,backref='b',
'c':relationship( C, uselist=False, backref='b') })
# Does a full join, does not work
mapper( C, c, polymorphic_on = a.c.type )
mapper( D, d, inherits=C, polymorphic_identity = D )
how can i change c to polymorphic on a, through the relationship? is
there a way to sort by d.data? (b-c/d is one-on-one).
this mapping is incorrect. mapper(C) cannot be polymorphic on a table
which is not part of its mapping, and mapper(C) does not contain an
inherits keyword to that of A. Usually the polymorphic_on setting is
on the base-most mapper in the hierarchy and its not clear here which mapper
you intend for that to be.
--
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.comsqlalchemy%2bunsubscr...@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.comsqlalchemy%2bunsubscr...@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.comsqlalchemy%2bunsubscr...@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
Re: [sqlalchemy] PostgreSQL hstore custom type?
I was just considering something similar. Were you able to get far with this? On 04/07/2010 03:10 PM, Kyle Schaffrick wrote: On Wed, 7 Apr 2010 10:15:14 -0400 Michael Bayermike...@zzzcomputing.com wrote: Kyle Schaffrick wrote: Greetings, I'm looking into using PostgreSQL's hstore type in a SQLAlchemy project, and before I possibly reinvent the wheel I was wondering if anyone has/knows of an implementation of an hstore custom type for SQLA? I'm basically just interested in simply mapping a stand-alone attribute containing a python dict onto an hstore column, I don't require auto-magic storage of extra attributes directly added to the object a la examples/vertical. Mainly I want the hstore segregated into it's own attribute because I'd like to be able to expose expression language support for hstore's operators (?, -, ||, and so forth) to manipulate them server-side. Any pointers? start with 0.6, as we've expanded the capability for types to directly affect how various operators are rendered, as well as to drive the return type of expressions, which in turn allows that expression to have a say in its own usage in another expression, as well as affects result row behavior. I had planned on using 0.6 just to get the benefit of the new dialect+dbapi system. I did see the @compiles() idiom in some example code, is this what you're referring to? It looks like a great new feature. Have there been any thoughts on user-extensible reflection? Some way to say when you reflect a DB column of type foo, use this custom type class? If I got the right impression from skimming, dispatching reflected types is essentially a big cascade of if-statements at the moment. I don't really know how many DBMS's in SA's arsenal have extensible type systems, so not sure if it's worth the trouble. I assume you can just override column information piecemeal without having to give up reflection of columns with vanilla types. if hstore represents a python value that is mutable (which a dict would be), mixing in MutableType will cause the ORM to maintain a copy of the original version of the attribute in order to check for changes. Good information, thanks. I can see why this would need to be handled specially. -Kyle -- David Gardner Pipeline Tools Programmer Jim Henson Creature Shop dgard...@creatureshop.com -- 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] PostgreSQL hstore custom type?
On Wed, 5 May 2010 15:01:08 -0700
David Gardner dgard...@creatureshop.com wrote:
I was just considering something similar. Were you able to get far
with this?
Yes, actually I've got a pretty good start on it. The only obvious
thing missing right now is a Comparator implementation so that the
custom hstore operations are available to mapped classes and not just
to the SQL expression language.
I did want to have access to hstore operations outside of mapped
classes (i.e. just using SQL expression language) so I ended up
implementing it slightly differently than I had originally thought. It
required some hacks which I'm not sure how to do better.
Some thoughts...
1. While you can override existing operators that work on ColumnElements
without doing much funny business, if you want to add *new* operations
to it, the abstractions leak fairly badly. This seems to be because
operator definitions aren't delegated to the class representing the
type, which is suboptimal because I would think that the type of a
ColumnElement is what logically defines what operations are valid on
it. The result of this is that I have to create classes like
HStoreColumn, HStoreColumnElement, _HStoreDeleteFunction, and so on, so
that SQL expressions which are logically of type 'hstore' will have the
extended hstore operations available.
2. That expression operations on Foo.some_col and foo_table.c.some_col
take completely different paths in the implementation was slightly
surprising. I would have expected the former to be implemented in terms
of the latter, so that SQL expressions available on some column type are
automatically available on the descriptor of a class which maps to that
column. But I don't know, there might be good reasons for this. In any
case I'm trying to figure out how to write my Comparator for hstore
without repeating myself a lot.
Current implementation attached, with really hacky tests at the end :)
Comments welcome.
-Kyle
--
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.
import re
import sqlalchemy.types as satypes
import sqlalchemy.schema as saschema
import sqlalchemy.sql as sasql
import sqlalchemy.sql.expression as saexp
import sqlalchemy.sql.functions as safunc
import sqlalchemy.util as sautil
import sqlalchemy.dialects.postgresql as pgdialect
from sqlalchemy.exc import SQLAlchemyError
__all__ = [ 'HStoreSyntaxError', 'HStore', 'HStoreElement', 'pair',
'HStoreColumn' ]
# My best guess at the parsing rules of hstore literals, since no formal
# grammar is given. This may be overkill since the docs say that current output
# implementation always quotes keys and values, but gives no explicit guarantee
# that this behavior is dependable. This is mostly reverse engineered from PG's
# input parser behavior.
HSTORE_PAIR_RE = re.compile(r
(
(?Pkey [^ ] [^= ]* )# Unquoted keys
| (?Pkey_q ([^] | \\ . )* )# Quoted keys
)
[ ]* = [ ]*# Pair operator, optional adjoining whitespace
(
(?Pvalue [^ ] [^, ]* ) # Unquoted values
| (?Pvalue_q ([^] | \\ . )* ) # Quoted values
)
, re.VERBOSE)
HSTORE_DELIMITER_RE = re.compile(r
[ ]* , [ ]*
, re.VERBOSE)
class HStoreSyntaxError(SQLAlchemyError):
Indicates an error unmarshalling an hstore value.
def __init__(self, hstore_str, pos):
self.hstore_str = hstore_str
self.pos = pos
CTX = 20
hslen = len(hstore_str)
parsed_tail = hstore_str[ max(pos - CTX - 1, 0) : min(pos, hslen) ]
residual = hstore_str[ min(pos, hslen) : min(pos + CTX + 1, hslen) ]
if len(parsed_tail) CTX:
parsed_tail = '[...]' + parsed_tail[ 1 : ]
if len(residual) CTX:
residual = residual[ : -1 ] + '[...]'
super(HStoreSyntaxError, self).__init__(
After %r, could not parse residual at position %d: %r %
(parsed_tail, pos, residual))
def _parse_hstore(hstore_str):
Parse an hstore from it's literal string representation.
Attempts to approximate PG's hstore input parsing rules as closely as
possible. Although currently this is not strictly necessary, since the
current implementation of hstore's output syntax is stricter than what it
accepts as input, the documentation makes no guarantees that will always
be the case.
Throws HStoreSyntaxError if parsing fails.
result = {}
pos = 0
pair_match = HSTORE_PAIR_RE.match(hstore_str)
while pair_match is not None:
key = pair_match.group('key') or pair_match.group('key_q')
key = key.decode('string_escape')
value = pair_match.group('value') or
