[sqlalchemy] Re: using with_polymorphic() on joined tables
Thanks for the feedback it ended up being really helpful. I think we are going to try a dev branch using joined table inheritance and at least do some testing, because at this point there are only three or four subtypes that we currently know about. Also in our new schema the data will be more normalized, so we may end up doing more joins, but in our app we will probably be selecting about a 1/3 the number of rows. Michael Bayer wrote: > 100K rows is not that much. the number of joins you are building into > your schema however will be cumbersome, both from a performance standpoint > as well as a day-to-day working with the database point of view (i.e., > selecting rows from the SQL shell, writing non-ORM scripts, etc.) I tend > towards joined table inheritance when there are going to be a lot of > subtypes, and I know that there will be many more subtypes introduced as > we move along. If a table is meant to hold just a couple of subtypes and > not much variability in that structure is anticipated, i might look into > single table (or combining joined and single) just to reduce complexity. > > > >> Michael Bayer wrote: >> >>> yeah. OK we only have limited support for that concept right now >>> using of_type(), which currently only supports one type, not a list. >>> so it would be >>> query(Company).join(Company.employees.of_type(Engineer)). In theory >>> of_type() could support a list, but that isnt built right now. So to >>> really get the full SQL you're looking for you'd have to go "old >>> school" and use the table objects here, like: >>> >>> session >>> .query >>> (Company >>> ).select_from >>> (company_table.join(employees_table).outerjoin(engineers_table, >>> ).outerjoin(managers_table, )).. >>> >>> you'll actually get a better query from the above since it won't wrap >>> the engineer/manager stuff in a subquery. >>> >>> >>> >>> On Jun 10, 2009, at 8:38 PM, David Gardner wrote: >>> >>> >>> I was wondering if there was a way to use with_polymorphic() on a joined table. For instance using the Company->Employees relationship in the example on http://www.sqlalchemy.org/docs/05/mappers.html#mapping-class-inheritance-hierarchies if I wanted to query for a company, and eagerload the employees and eagerly join the engineers and managers tables I would think to do something like: session.query(Company).join(Company.employees).\ options(contains_eager(Company.employees)).\ with_polymorphic([Engineer, Manager]).\ filter(Company.name=='test').first() >> -- >> David Gardner >> Pipeline Tools Programmer >> Jim Henson Creature Shop >> dgard...@creatureshop.com >> >> >> >> > > > > > > -- 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 sqlalchemy@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: Relation Bug in SA?
Thank you, Bobby and Michael. This seems to work now.
I'm still waiting for your book, Michael. Hope it comes soon!
A.
On Jun 12, 5:29 pm, "Michael Bayer" wrote:
> the "foreign keys" argument as yet is not propagated to the backref (the
> primaryjoin is), I should look into fixing that, but for now use
> backref=backref('owner', primaryjoin=pj, foreign_keys=[the foreign keys]).
>
>
>
> Affect wrote:
>
> > Hello:
>
> > When I use the foreign_keys argument to the relation function in the
> > mapper of SA, I get the following error:
>
> > ArgumentError: Could not determine relation direction for primaryjoin
> > condition 'drm_owners.owner_id = drm_contract_royalties.contract_id',
> > on relation Royalty.owner. Specify the 'foreign_keys' argument to
> > indicate which columns on the relation are foreign.
>
> > This error only shows when the 'backref' argument is specified and not
> > otherwise! Is this a bug in alchemy or am I missing something?
>
> > Here's the mapper conf:
> > ===
> > mapper(Royalty, royalties_table)
> > mapper(Owner, owners_table,
> > properties = {
> > 'works': relation(Work, backref='owner'),
> > 'royalty': relation(Royalty,
>
> > primaryjoin=owners_table.c.owner_id==royalties_table.c.contract_id,
> > foreign_keys=[royalties_table.c.contract_id],
> > backref='owner')
> > })
>
> > So, if I remove the 'backref' argument from the call to 'relation',
> > the relation works, but of course I lose the backref 'owner'.
>
> > Thanks!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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: Relation Bug in SA?
the "foreign keys" argument as yet is not propagated to the backref (the
primaryjoin is), I should look into fixing that, but for now use
backref=backref('owner', primaryjoin=pj, foreign_keys=[the foreign keys]).
Affect wrote:
>
> Hello:
>
> When I use the foreign_keys argument to the relation function in the
> mapper of SA, I get the following error:
>
> ArgumentError: Could not determine relation direction for primaryjoin
> condition 'drm_owners.owner_id = drm_contract_royalties.contract_id',
> on relation Royalty.owner. Specify the 'foreign_keys' argument to
> indicate which columns on the relation are foreign.
>
> This error only shows when the 'backref' argument is specified and not
> otherwise! Is this a bug in alchemy or am I missing something?
>
> Here's the mapper conf:
> ===
> mapper(Royalty, royalties_table)
> mapper(Owner, owners_table,
> properties = {
> 'works': relation(Work, backref='owner'),
> 'royalty': relation(Royalty,
>
> primaryjoin=owners_table.c.owner_id==royalties_table.c.contract_id,
> foreign_keys=[royalties_table.c.contract_id],
> backref='owner')
> })
>
> So, if I remove the 'backref' argument from the call to 'relation',
> the relation works, but of course I lose the backref 'owner'.
>
> Thanks!
>
>
> >
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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: Relation Bug in SA?
The error is complaining about the backref ("on relation
Royalty.owner"), so it makes sense that it would go away if you remove
the backref.
The error says that you need to specify foreign_keys for the backref,
so you should try that. i.e, change
backref='owner'
to
backref=backref('owner', foreign_keys = [owners_table.c.owner_id])
On Fri, Jun 12, 2009 at 12:31 PM, Affect wrote:
>
> Hello:
>
> When I use the foreign_keys argument to the relation function in the
> mapper of SA, I get the following error:
>
> ArgumentError: Could not determine relation direction for primaryjoin
> condition 'drm_owners.owner_id = drm_contract_royalties.contract_id',
> on relation Royalty.owner. Specify the 'foreign_keys' argument to
> indicate which columns on the relation are foreign.
>
> This error only shows when the 'backref' argument is specified and not
> otherwise! Is this a bug in alchemy or am I missing something?
>
> Here's the mapper conf:
> ===
> mapper(Royalty, royalties_table)
> mapper(Owner, owners_table,
> properties = {
> 'works': relation(Work, backref='owner'),
> 'royalty': relation(Royalty,
>
> primaryjoin=owners_table.c.owner_id==royalties_table.c.contract_id,
> foreign_keys=[royalties_table.c.contract_id],
> backref='owner')
> })
>
> So, if I remove the 'backref' argument from the call to 'relation',
> the relation works, but of course I lose the backref 'owner'.
>
> Thanks!
>
>
> >
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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] Relation Bug in SA?
Hello:
When I use the foreign_keys argument to the relation function in the
mapper of SA, I get the following error:
ArgumentError: Could not determine relation direction for primaryjoin
condition 'drm_owners.owner_id = drm_contract_royalties.contract_id',
on relation Royalty.owner. Specify the 'foreign_keys' argument to
indicate which columns on the relation are foreign.
This error only shows when the 'backref' argument is specified and not
otherwise! Is this a bug in alchemy or am I missing something?
Here's the mapper conf:
===
mapper(Royalty, royalties_table)
mapper(Owner, owners_table,
properties = {
'works': relation(Work, backref='owner'),
'royalty': relation(Royalty,
primaryjoin=owners_table.c.owner_id==royalties_table.c.contract_id,
foreign_keys=[royalties_table.c.contract_id],
backref='owner')
})
So, if I remove the 'backref' argument from the call to 'relation',
the relation works, but of course I lose the backref 'owner'.
Thanks!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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: @synonym_for lose the docstrings
try this patch:
Index: lib/sqlalchemy/ext/declarative.py
===
--- lib/sqlalchemy/ext/declarative.py (revision 6051)
+++ lib/sqlalchemy/ext/declarative.py (working copy)
@@ -639,8 +639,9 @@
prop = synonym('col', descriptor=property(_read_prop, _write_prop))
"""
+
def decorate(fn):
-return _orm_synonym(name, map_column=map_column, descriptor=fn)
+return util.update_wrapper(_orm_synonym(name,
map_column=map_column, descriptor=fn), fn)
return decorate
def comparable_using(comparator_factory):
@@ -661,7 +662,7 @@
"""
def decorate(fn):
-return comparable_property(comparator_factory, fn)
+return
util.update_wrapper(comparable_property(comparator_factory, fn), fn)
return decorate
def _declarative_constructor(self, **kwargs):
Angri wrote:
>
> I think that patch which you would like to provide could add
> functools.wraps decorator to the decorator defined in synonym_for().
> See http://docs.python.org/library/functools.html#functools.wraps
>
> --
> Anton Gritsay
> http://angri.ru
>
> On 12 ÉÀÎ, 22:20, Christophe de VIENNE wrote:
>> Hi,
>>
>> I noticed that when I use the @synonym_for decorator, my function
>> docstring
>> get lost.
>>
>> I got lost in SA code around the attributes.register_descriptor
>> function,
>> which is one of the steps I guess the doc is not copied (along with the
>> decorator function itself), and cannot propose a patch.
>>
>> I workaround the issue by doing the plain synonym declaration and using
>> the
>> sphinx specific docstrings for class attributes, but it would be really
>> great if it could just work.
>>
>> And thanks for the awesome tool SqlAlchemy is : it is a constant source
>> of
>> amazement to me.
>>
>> Regards,
>>
>> Christophe
> >
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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: Logging facilities of SQLAlchemy
General wrote:
>
> First issue is _should_log_* properties. What for are they exist?
> Standard lib's logging module allows to fine-tune levels per-logger or
> per-handler. But with those strange props such simple code works not
> as it should:
>
> engine_logger = sqlalchemy.log.instance_logger(engine)
> engine_logger.setLevel(logging.INFO)
>
> If engine was created with echo=False there will be no logging output
> (of course I haven't forgot to set up the handler). But if I call
> instance_logger() again _after_ setting level to logger, output will
> appear because instance_logger will set _should_log_info=True to
> engine and only after that engine start to actually write INFO to log.
> I can not understand the meaning of _should_log_* props.
unfortunately the logging module included with Python adds significant
performance overhead even if no handlers are configured. We would like
to be able to have fine-grained logging available in our library, but at
the same time when the logging is disabled for significant latency to not
be added to the application. Take a look at the source of logging.debug()
- a logger with a few levels of hierarchy and no handlers configured will
have about three method calls of overhead. Multiply that by, for
example, a log statement for each row received by RowProxy, and a log
statement for each column/result processor in the ORM, and it adds up to
many dozens of method calls per row. The primary hindrance to speed in
Python is function calls - perhaps projects like Unladen Swallow will
improve this but for now, Python application profiling is almost a linear
function w.r.t number of method calls. So we can either remove all the
log.debug() statements we have and just not have the capability available,
or gate them within a conditional as we've done.
>
> Second question is about putting the query parameters to the log. Now
> there are two log records for each query - first for the query itself
> (with placeholders instead of actual data) and the second for the
> query params. It makes processing log records very difficult. I think
> code which logs queries should look like
>
> self.engine.logger.info(statement, {'parameters': parameters})
>
> instead of
>
> self.engine.logger.info(statement)
> self.engine.logger.info(repr(parameters))
>
this is fine and I may consider implementing this in 0.6. Though have
you tried DEBUG level sqlalchemy.engine ? the result set processing
necessarily needs to be implemented as distinct log lines, so you'd still
have the problem of associating many log lines with one record. there's
a connection proxy that can be used for more elaborate schemes.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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: @synonym_for lose the docstrings
I think that patch which you would like to provide could add functools.wraps decorator to the decorator defined in synonym_for(). See http://docs.python.org/library/functools.html#functools.wraps -- Anton Gritsay http://angri.ru On 12 июн, 22:20, Christophe de VIENNE wrote: > Hi, > > I noticed that when I use the @synonym_for decorator, my function docstring > get lost. > > I got lost in SA code around the attributes.register_descriptor function, > which is one of the steps I guess the doc is not copied (along with the > decorator function itself), and cannot propose a patch. > > I workaround the issue by doing the plain synonym declaration and using the > sphinx specific docstrings for class attributes, but it would be really > great if it could just work. > > And thanks for the awesome tool SqlAlchemy is : it is a constant source of > amazement to me. > > Regards, > > Christophe --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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] Logging facilities of SQLAlchemy
Hello all.
I think that logging in SQLAlchemy should be a bit more straightforward.
First issue is _should_log_* properties. What for are they exist?
Standard lib's logging module allows to fine-tune levels per-logger or
per-handler. But with those strange props such simple code works not
as it should:
engine_logger = sqlalchemy.log.instance_logger(engine)
engine_logger.setLevel(logging.INFO)
If engine was created with echo=False there will be no logging output
(of course I haven't forgot to set up the handler). But if I call
instance_logger() again _after_ setting level to logger, output will
appear because instance_logger will set _should_log_info=True to
engine and only after that engine start to actually write INFO to log.
I can not understand the meaning of _should_log_* props.
Second question is about putting the query parameters to the log. Now
there are two log records for each query - first for the query itself
(with placeholders instead of actual data) and the second for the
query params. It makes processing log records very difficult. I think
code which logs queries should look like
self.engine.logger.info(statement, {'parameters': parameters})
instead of
self.engine.logger.info(statement)
self.engine.logger.info(repr(parameters))
This will logicaly join the query text and parameters in one log
record. It is very simple to implement logging formatter which will
concatenate query text with repr(parameters) string to implement
previous behaviour. Such approach brings an ability to process queries
from log records (in custom formatter) as simple and efficient as
possible.
--
Anton Gritsay
http://angri.ru
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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] @synonym_for lose the docstrings
Hi, I noticed that when I use the @synonym_for decorator, my function docstring get lost. I got lost in SA code around the attributes.register_descriptor function, which is one of the steps I guess the doc is not copied (along with the decorator function itself), and cannot propose a patch. I workaround the issue by doing the plain synonym declaration and using the sphinx specific docstrings for class attributes, but it would be really great if it could just work. And thanks for the awesome tool SqlAlchemy is : it is a constant source of amazement to me. Regards, Christophe --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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: Can I coerce strings into Unicode?
allen.fowler wrote:
>
>
>
> On Jun 12, 6:00 am, Gunnlaugur Briem wrote:
>> The engine's conversion to unicode doesn't happen when you assign the
>> property, it happens when the underlying database operation is
>> committed, and arrives in the python object's property only after
>> roundtripping through the database.
>>
>
> OK, i see.
>
> Are there any shortcuts for installing a "filter" on the object to
> mandate (and force if possible) UTF-8 on all incoming property
> assignments?
I use this
class _coerce_from_utf8(TypeDecorator):
def process_bind_param(self, value, dialect):
if isinstance(value, str):
value = value.decode('utf-8')
return value
class UTF8(_coerce_from_utf8):
"""A Unicode type which coerces from utf-8."""
impl = sa.Unicode
class UTF8Text(_coerce_from_utf8):
"""A Unicode type which coerces from utf-8."""
impl = sa.UnicodeText
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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: Can I coerce strings into Unicode?
On Jun 12, 6:00 am, Gunnlaugur Briem wrote: > The engine's conversion to unicode doesn't happen when you assign the > property, it happens when the underlying database operation is > committed, and arrives in the python object's property only after > roundtripping through the database. > OK, i see. Are there any shortcuts for installing a "filter" on the object to mandate (and force if possible) UTF-8 on all incoming property assignments? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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: Merging and PGArray
Ok we'll have to ensure this doesn't break anything, that line doesn't seem to be covered in the current unit tests Roel van Os wrote: > Hi Michael, > > On 12-06-2009 16:22, Michael Bayer wrote: >> this is likely a bug. try out the patch below (against the latest 0.5 >> release): >> > > After applying the patch it works perfectly. Thanks a lot! > > Regards, > Roel > >> Index: lib/sqlalchemy/orm/state.py >> === >> --- lib/sqlalchemy/orm/state.py (revision 6049) >> +++ lib/sqlalchemy/orm/state.py (working copy) >> @@ -111,8 +111,8 @@ >> return None >> elif hasattr(impl, 'get_collection'): >> return impl.get_collection(self, dict_, x, >> passive=passive) >> -elif isinstance(x, list): >> -return x >> +#elif isinstance(x, list): >> +#return x >> else: >> return [x] >> >> >> >> >> Roel van Os wrote: >> >>> Hi all, >>> >>> In my program I'm using PGArray to store a list of strings in the >>> database (defined as text[] in the schema). >>> >>> When I use Session.merge to create a copy of an object in the current >>> session, the list is converted to a single string (the first from the >>> list) in the copy. I've placed an example below. dont_load True or >>> False >>> doesn't make a difference. >>> >>> I've tested with SQLAlchemy 0.5.4 and 0.4.6. >>> >>> Any idea what the problem might be and what I can do about it? >>> >>> Thanks, >>> Roel van Os >>> >>> Example code: >>> >>> #!/usr/bin/env python >>> from sqlalchemy import * >>> from sqlalchemy.sql import * >>> from sqlalchemy.orm import * >>> from sqlalchemy.ext.declarative import declarative_base >>> from sqlalchemy.databases.postgres import PGArray >>> >>> Base = declarative_base() >>> >>> class TestClass(Base): >>> __tablename__ = 'testclass' >>> >>> id = Column(Integer, primary_key=True) >>> test_array = Column(PGArray(Text)) >>> >>> dburl = 'postgres://:x...@/' >>> engine = create_engine(dburl, convert_unicode=True, echo=False, >>> pool_recycle=60) >>> Session = sessionmaker(bind=engine) >>> Base.metadata.create_all(engine) >>> >>> # Create a test object >>> s1 = Session() >>> o1 = TestClass(test_array=['1', '2', '3']) >>> s1.save(o1) >>> s1.commit() >>> o1_id = o1.id >>> s1.close() >>> >>> # Load the test object >>> s2 = Session() >>> o2 = s2.query(TestClass).get(o1_id) >>> print o2.test_array >>> assert len(o2.test_array) == 3 >>> >>> # Merge the object into another session >>> s3 = Session() >>> o3 = s3.merge(o2, dont_load=True) >>> >>> # Should print the same as above, but prints "1" >>> print o3.test_array >>> assert len(o3.test_array) == 3 >>> >>> >>> >> >> >> > >> > > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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: Merging and PGArray
Hi Michael, On 12-06-2009 16:22, Michael Bayer wrote: > this is likely a bug. try out the patch below (against the latest 0.5 > release): > After applying the patch it works perfectly. Thanks a lot! Regards, Roel > Index: lib/sqlalchemy/orm/state.py > === > --- lib/sqlalchemy/orm/state.py (revision 6049) > +++ lib/sqlalchemy/orm/state.py (working copy) > @@ -111,8 +111,8 @@ > return None > elif hasattr(impl, 'get_collection'): > return impl.get_collection(self, dict_, x, passive=passive) > -elif isinstance(x, list): > -return x > +#elif isinstance(x, list): > +#return x > else: > return [x] > > > > > Roel van Os wrote: > >> Hi all, >> >> In my program I'm using PGArray to store a list of strings in the >> database (defined as text[] in the schema). >> >> When I use Session.merge to create a copy of an object in the current >> session, the list is converted to a single string (the first from the >> list) in the copy. I've placed an example below. dont_load True or False >> doesn't make a difference. >> >> I've tested with SQLAlchemy 0.5.4 and 0.4.6. >> >> Any idea what the problem might be and what I can do about it? >> >> Thanks, >> Roel van Os >> >> Example code: >> >> #!/usr/bin/env python >> from sqlalchemy import * >> from sqlalchemy.sql import * >> from sqlalchemy.orm import * >> from sqlalchemy.ext.declarative import declarative_base >> from sqlalchemy.databases.postgres import PGArray >> >> Base = declarative_base() >> >> class TestClass(Base): >> __tablename__ = 'testclass' >> >> id = Column(Integer, primary_key=True) >> test_array = Column(PGArray(Text)) >> >> dburl = 'postgres://:x...@/' >> engine = create_engine(dburl, convert_unicode=True, echo=False, >> pool_recycle=60) >> Session = sessionmaker(bind=engine) >> Base.metadata.create_all(engine) >> >> # Create a test object >> s1 = Session() >> o1 = TestClass(test_array=['1', '2', '3']) >> s1.save(o1) >> s1.commit() >> o1_id = o1.id >> s1.close() >> >> # Load the test object >> s2 = Session() >> o2 = s2.query(TestClass).get(o1_id) >> print o2.test_array >> assert len(o2.test_array) == 3 >> >> # Merge the object into another session >> s3 = Session() >> o3 = s3.merge(o2, dont_load=True) >> >> # Should print the same as above, but prints "1" >> print o3.test_array >> assert len(o3.test_array) == 3 >> >> >> > > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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: Merging and PGArray
this is likely a bug. try out the patch below (against the latest 0.5 release): Index: lib/sqlalchemy/orm/state.py === --- lib/sqlalchemy/orm/state.py (revision 6049) +++ lib/sqlalchemy/orm/state.py (working copy) @@ -111,8 +111,8 @@ return None elif hasattr(impl, 'get_collection'): return impl.get_collection(self, dict_, x, passive=passive) -elif isinstance(x, list): -return x +#elif isinstance(x, list): +#return x else: return [x] Roel van Os wrote: > > Hi all, > > In my program I'm using PGArray to store a list of strings in the > database (defined as text[] in the schema). > > When I use Session.merge to create a copy of an object in the current > session, the list is converted to a single string (the first from the > list) in the copy. I've placed an example below. dont_load True or False > doesn't make a difference. > > I've tested with SQLAlchemy 0.5.4 and 0.4.6. > > Any idea what the problem might be and what I can do about it? > > Thanks, > Roel van Os > > Example code: > > #!/usr/bin/env python > from sqlalchemy import * > from sqlalchemy.sql import * > from sqlalchemy.orm import * > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.databases.postgres import PGArray > > Base = declarative_base() > > class TestClass(Base): > __tablename__ = 'testclass' > > id = Column(Integer, primary_key=True) > test_array = Column(PGArray(Text)) > > dburl = 'postgres://:x...@/' > engine = create_engine(dburl, convert_unicode=True, echo=False, > pool_recycle=60) > Session = sessionmaker(bind=engine) > Base.metadata.create_all(engine) > > # Create a test object > s1 = Session() > o1 = TestClass(test_array=['1', '2', '3']) > s1.save(o1) > s1.commit() > o1_id = o1.id > s1.close() > > # Load the test object > s2 = Session() > o2 = s2.query(TestClass).get(o1_id) > print o2.test_array > assert len(o2.test_array) == 3 > > # Merge the object into another session > s3 = Session() > o3 = s3.merge(o2, dont_load=True) > > # Should print the same as above, but prints "1" > print o3.test_array > assert len(o3.test_array) == 3 > > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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: using with_polymorphic() on joined tables
David Gardner wrote: > > OK I think that would work for me. > > I have another question in regards to joined table inheritance and > performance. > At work we are planning to restructure our database schema, and what we > are considering doing is > creating an adjacency list, of objects using joined table inheritance. > We are thinking there would be at least four subtypes, > where each would be a subclass of the previous (B inherits from A, and C > inherits from B). We are doing it this way because > every object could overload a value from its parent (probably won't 90% > of the time). > We would probably want to eagerly load children two levels deep, and at > the lowest level an object would make up a join of four tables. > > The table will be populated with the rows from two existing tables one > with 11,000 rows the other with 40,000 rows, so presumably in the > near future the table would have well over 100,000 rows. > > How bad do you think the performance would be? Is this something that is > just too crazy to try? 100K rows is not that much. the number of joins you are building into your schema however will be cumbersome, both from a performance standpoint as well as a day-to-day working with the database point of view (i.e., selecting rows from the SQL shell, writing non-ORM scripts, etc.) I tend towards joined table inheritance when there are going to be a lot of subtypes, and I know that there will be many more subtypes introduced as we move along. If a table is meant to hold just a couple of subtypes and not much variability in that structure is anticipated, i might look into single table (or combining joined and single) just to reduce complexity. > > Michael Bayer wrote: >> yeah. OK we only have limited support for that concept right now >> using of_type(), which currently only supports one type, not a list. >> so it would be >> query(Company).join(Company.employees.of_type(Engineer)). In theory >> of_type() could support a list, but that isnt built right now. So to >> really get the full SQL you're looking for you'd have to go "old >> school" and use the table objects here, like: >> >> session >> .query >> (Company >> ).select_from >> (company_table.join(employees_table).outerjoin(engineers_table, >> ).outerjoin(managers_table, )).. >> >> you'll actually get a better query from the above since it won't wrap >> the engineer/manager stuff in a subquery. >> >> >> >> On Jun 10, 2009, at 8:38 PM, David Gardner wrote: >> >> >>> I was wondering if there was a way to use with_polymorphic() on a >>> joined >>> table. >>> >>> For instance using the Company->Employees relationship in the >>> example on >>> http://www.sqlalchemy.org/docs/05/mappers.html#mapping-class-inheritance-hierarchies >>> >>> if I wanted to query for a company, and eagerload the employees and >>> eagerly join the engineers and managers tables I would think to do >>> something like: >>> >>> session.query(Company).join(Company.employees).\ >>>options(contains_eager(Company.employees)).\ >>>with_polymorphic([Engineer, Manager]).\ >>>filter(Company.name=='test').first() >>> >>> >>> >>> > > > -- > 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 sqlalchemy@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] Merging and PGArray
Hi all, In my program I'm using PGArray to store a list of strings in the database (defined as text[] in the schema). When I use Session.merge to create a copy of an object in the current session, the list is converted to a single string (the first from the list) in the copy. I've placed an example below. dont_load True or False doesn't make a difference. I've tested with SQLAlchemy 0.5.4 and 0.4.6. Any idea what the problem might be and what I can do about it? Thanks, Roel van Os Example code: #!/usr/bin/env python from sqlalchemy import * from sqlalchemy.sql import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.databases.postgres import PGArray Base = declarative_base() class TestClass(Base): __tablename__ = 'testclass' id = Column(Integer, primary_key=True) test_array = Column(PGArray(Text)) dburl = 'postgres://:x...@/' engine = create_engine(dburl, convert_unicode=True, echo=False, pool_recycle=60) Session = sessionmaker(bind=engine) Base.metadata.create_all(engine) # Create a test object s1 = Session() o1 = TestClass(test_array=['1', '2', '3']) s1.save(o1) s1.commit() o1_id = o1.id s1.close() # Load the test object s2 = Session() o2 = s2.query(TestClass).get(o1_id) print o2.test_array assert len(o2.test_array) == 3 # Merge the object into another session s3 = Session() o3 = s3.merge(o2, dont_load=True) # Should print the same as above, but prints "1" print o3.test_array assert len(o3.test_array) == 3 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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: InvalidRequestError
chingi wrote: > > Hello, > > In my application I have AJAX function which sends around 20 > requests every 5 seconds to Server to update my web page. > > But few requests fail to get values because of > "invalidRequestError : The transaction is inactive due to a rollback > in a subtransaction and should be closed" > > After investigtion I found that "SessionTransaction" class which is > responsible for creating sessions is not thread safe and i guess that > this is creating the problem. > > Please help me to sort out this issue . Any Help is appreciated. this is correct, Session and its internals are documented as non-threadsafe. To manage sessions on a per thread basis, you should be using the ScopedSession class as described in http://www.sqlalchemy.org/docs/05/session.html#contextual-thread-local-sessions . --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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: Can I coerce strings into Unicode?
The engine's conversion to unicode doesn't happen when you assign the
property, it happens when the underlying database operation is
committed, and arrives in the python object's property only after
roundtripping through the database.
In [50]: m1.body = 'new - NOT unicode'
In [51]: m1.body
Out[51]: 'new - NOT unicode'
In [52]: session.add(m1)
In [53]: m1.body
Out[53]: 'new - NOT unicode'
In [54]: session.commit()
2009-06-12 09:46:18,430 INFO sqlalchemy.engine.base.Engine.0x...aa70
BEGIN
2009-06-12 09:46:18,431 INFO sqlalchemy.engine.base.Engine.0x...aa70
INSERT INTO message (body) VALUES (?)
2009-06-12 09:46:18,431 INFO sqlalchemy.engine.base.Engine.0x...aa70
['new - NOT unicode']
2009-06-12 09:46:18,432 INFO sqlalchemy.engine.base.Engine.0x...aa70
COMMIT
In [55]: m1.body
2009-06-12 09:46:22,803 INFO sqlalchemy.engine.base.Engine.0x...aa70
BEGIN
2009-06-12 09:46:22,804 INFO sqlalchemy.engine.base.Engine.0x...aa70
SELECT message.body AS message_body
FROM message
WHERE message.body = ?
2009-06-12 09:46:22,805 INFO sqlalchemy.engine.base.Engine.0x...aa70
['new - NOT unicode']
Out[55]: u'new - NOT unicode'
So if you want to rely on the engine's coercion to unicode, you have
to go through the engine.
But really, you ought to take care of unicode encoding issues yourself
before passing text data to SQLAlchemy. The engine cannot know where
your 8-bit strings come from, and just assumes that they are encoded
in UTF-8 (or whatever other encoding you set it to). You are better
equipped to know what encoding to expect your string input to be in
(and it may vary, depending on your input). If you don't know, find
out. (Or specify and assert.)
Cheers,
- Gulli
On Jun 12, 6:20 am, "allen.fowler" wrote:
> Anybody?
>
> On Jun 4, 1:13 am, AF wrote:
>
> > Hello,
>
> > I'm using sqlite and "convert_unicode = True "on the engine.
>
> > How can I force coerce string based object attributes in to unicode?
> > (I had thought "convert_unicode = True" would do this)
>
> > Here is what I am seeing...
>
> > Setup code:
> > engine = create_engine('sqlite:///:memory:', echo=True,
> > convert_unicode=True)
> > Session = sessionmaker(bind=engine)
> > session = Session()
> > metadata = MetaData()
> > m1 = message(u'message body 1')
>
> > Now, in ipython:
>
> > In [1]: session.add(m1)
>
> > In [2]: m1.body
> > Out[2]: u'message body 1'
>
> > In [3]: m1.body = u'new - unicode'
>
> > In [4]: m1.body
> > Out[4]: u'new - unicode'
>
> > In [5]: m1.body = 'new - NOT unicode'
>
> > In [6]: m1.body
> > Out[6]: 'new - NOT unicode'
>
> > In [7]: unicode(m1.body)
> > Out[7]: u'new - NOT unicode'
>
> > Output line 6 is the problem.
>
> > Ideally, I'd like to see output lines 6 & 7 be the same.
>
> > Am I doing something wrong?
>
> > Thank you,
> > Allen
>
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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
-~--~~~~--~~--~--~---
