Re: [sqlalchemy] info property in column and relationship
Then please go ahead. :) 2013/3/6 Michael Bayer : > .info on relationship is a new feature, that can be added. > > > On Mar 5, 2013, at 7:10 PM, Xin Wang wrote: > > Hi all, > > I can get info dict passed to Column() via `Model.field.info`, but with > relationship() I need to use `Model.field.property.info`. > > Not sure if it is an inconsistence, as both of them are instances of > `InstrumentedAttribute` when retrive from model. > > BR, > > Xin Wang > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] info property in column and relationship
.info on relationship is a new feature, that can be added. On Mar 5, 2013, at 7:10 PM, Xin Wang wrote: > Hi all, > > I can get info dict passed to Column() via `Model.field.info`, but with > relationship() I need to use `Model.field.property.info`. > > Not sure if it is an inconsistence, as both of them are instances of > `InstrumentedAttribute` when retrive from model. > > BR, > > Xin Wang > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] info property in column and relationship
Hi all, I can get info dict passed to Column() via `Model.field.info`, but with relationship() I need to use `Model.field.property.info`. Not sure if it is an inconsistence, as both of them are instances of `InstrumentedAttribute` when retrive from model. BR, Xin Wang -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Postgres custom composite types
On Tuesday, March 5, 2013 2:52:59 PM UTC-5, Jason wrote: > > > > On Tuesday, March 5, 2013 11:09:00 AM UTC-5, Michael Bayer wrote: >> >> >> On Mar 5, 2013, at 9:45 AM, Jason wrote: >> >> > Hello, >> > >> > I'm looking or some direction on how to implement the SQL syntax when >> querying Postgres' composite types. In case you're unfamiliar: the >> composite types in Postgres are a type that contains attributes which are >> regular SQL types. The attribute to type relationship is similar to the >> column to table relationship (except there are no constraints on >> attributes). A table column can be this composite type. Psycopg2 implements >> this by instantiating a namedtuple on retrieval or accepting a namedtuple >> on update/insert (you can also use your own custom class). The saving and >> retrieval isn't a problem with SQLAlchemy, I just pass through the >> namedtuple to/from Psycopg2. >> > >> > What I need to implement is the SQL syntax for querying composite type >> attributes. Postgres allows this by using the syntax >> "(table_name.column_name).attribute_name = 'bleh'" or just >> "(column_name).attribute_name = 'bleh'" when a table identifier is not >> required. I'm not sure how to go about this because the sql generation >> needs to change the way the column name is output, would this require >> subclassing the ColumnClause? I think I just need to know where to override >> the behaviour of generating the qualified column name in statements. >> >> you'd be doing ColumnElement here which is the more fundamental object. >> It's discussed here in "subclassing guidelines": >> http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#subclassing-guidelines >> >> >> if you're on 0.8 the integration here can be very smooth, using custom >> operators ( >> http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#types-operators) >> you could have an approach that looks like: >> mytable.c.column.attrs.attribute_name == "value". The "attrs" namespace >> would call into your custom ColumnElement. >> >> Since the custom operator API is a total blast to use here's a proof of >> concept: >> >> >> >> > Wow thats great, thanks! > > There is a problem with this when using declarative and a reflected base. > My Column's type get's overrwritten with NullType even though I've set > autoreload_replace=False when constructing the table. I am still using the > example ReflectedBase that was in the 0.7 documentation (because the new > DeferredReflection class in 0.8 raises a NoReferencedTableError when > configure_mappers() is called). Any idea how this reflected base is > overwriting the type?: > > I figured out the problem. It's not finding the table in the metadata (because it's schema qualified in metadata.tables) and so it creates it anew. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Postgres custom composite types
On Tuesday, March 5, 2013 11:09:00 AM UTC-5, Michael Bayer wrote: > > > On Mar 5, 2013, at 9:45 AM, Jason > > wrote: > > > Hello, > > > > I'm looking or some direction on how to implement the SQL syntax when > querying Postgres' composite types. In case you're unfamiliar: the > composite types in Postgres are a type that contains attributes which are > regular SQL types. The attribute to type relationship is similar to the > column to table relationship (except there are no constraints on > attributes). A table column can be this composite type. Psycopg2 implements > this by instantiating a namedtuple on retrieval or accepting a namedtuple > on update/insert (you can also use your own custom class). The saving and > retrieval isn't a problem with SQLAlchemy, I just pass through the > namedtuple to/from Psycopg2. > > > > What I need to implement is the SQL syntax for querying composite type > attributes. Postgres allows this by using the syntax > "(table_name.column_name).attribute_name = 'bleh'" or just > "(column_name).attribute_name = 'bleh'" when a table identifier is not > required. I'm not sure how to go about this because the sql generation > needs to change the way the column name is output, would this require > subclassing the ColumnClause? I think I just need to know where to override > the behaviour of generating the qualified column name in statements. > > you'd be doing ColumnElement here which is the more fundamental object. > It's discussed here in "subclassing guidelines": > http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#subclassing-guidelines > > > if you're on 0.8 the integration here can be very smooth, using custom > operators ( > http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#types-operators) > you could have an approach that looks like: > mytable.c.column.attrs.attribute_name == "value". The "attrs" namespace > would call into your custom ColumnElement. > > Since the custom operator API is a total blast to use here's a proof of > concept: > > > > Wow thats great, thanks! There is a problem with this when using declarative and a reflected base. My Column's type get's overrwritten with NullType even though I've set autoreload_replace=False when constructing the table. I am still using the example ReflectedBase that was in the 0.7 documentation (because the new DeferredReflection class in 0.8 raises a NoReferencedTableError when configure_mappers() is called). Any idea how this reflected base is overwriting the type?: class DeclarativeReflectedBase(object): _mapper_args = [] @classmethod def __mapper_cls__(cls, *args, **kw): """ Declarative will use this function in lieu of calling mapper() directly. Collect each series of arguments and invoke them when prepare() is called. """ cls._mapper_args.append((args, kw)) @classmethod def prepare(cls, engine): """Reflect all the tables and map !""" for args, kw in cls._mapper_args: class_ = args[0] print class_.__tablename__ class_.__table__ = table = Table( class_.__tablename__, cls.metadata, extend_existing=True, autoload_replace=False, autoload=True, autoload_with=engine) class_.__mapper__ = mapper(class_, table, **kw) Thanks, Jason -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] query of existing object won't refresh values (even when FOR UPDATE)
I had forgotten the danger of populate_existing() also because we make
it automatically issue a flush() for these purposes. Documentation note
sounds good.
On 3/5/2013 10:45 AM, Michael Bayer wrote:
populate_existing() blows away any pending changes on the object so
turning it on by default would be a surprise in a lot of cases.
typically if someone is working with FOR UPDATE they're already
programming a very specific section in a very careful manner, it's not
something that's done casually. I would think that it would be used to
select a row right at the start of a transaction, that is definitely not
already in the Session.
in my own experience, any time I've actually tried to work with
pessimistic locking my application ends up deadlocking at 2 AM, so I
can't say I have the background to really say how this method should be
used. At this point, a documentation note is certainly something doable
(because I would hope very much that anyone using this method has read
the documentation carefully).
On Mar 5, 2013, at 10:00 AM, Kent mailto:jkentbo...@gmail.com>> wrote:
By design, when a query() fetches an existing object, it doesn't
refresh the values unless populate_existing() is included with the
query. The documentation for populate_existing() states it isn't
meant for general purpose.
Occasionally, however, objects need to be selected FOR UPDATE,
with_lockmode('update'), to guarantee against timing problems with
concurrent database users, particularly when the record fetched is
used as a base for the update. For example, if I need to update a
record's quantity field by a delta of +5, it is extremely important
that I have the most recent Object.quantity datum; truly, that was the
reason I used with_lockmode('update') in the first place.
I get uneasy to think that the user needs to remember to invoke
populate_existing() when selecting a record FOR UPDATE to guard
against the possibility that there is a stale version of the instance
in the session.
I will likely add that to our framework's Query subclass, but thought
you might consider the implications here. Generally, sqla is extremely
optimistic regarding locks, but in the event when the user is
specifying with_lockmode('update'), we've left the realm of optimistic
locking and entered pessimistic, so it seems reasonable to consider
automatically enforcing populate_existing() when
with_lockmode('update') is used?
Something to consider; I'd be interested in your thoughts.
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to a topic in the
Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/I2ftUVJcAuo/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to
sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Postgres custom composite types
On Mar 5, 2013, at 9:45 AM, Jason wrote:
> Hello,
>
> I'm looking or some direction on how to implement the SQL syntax when
> querying Postgres' composite types. In case you're unfamiliar: the composite
> types in Postgres are a type that contains attributes which are regular SQL
> types. The attribute to type relationship is similar to the column to table
> relationship (except there are no constraints on attributes). A table column
> can be this composite type. Psycopg2 implements this by instantiating a
> namedtuple on retrieval or accepting a namedtuple on update/insert (you can
> also use your own custom class). The saving and retrieval isn't a problem
> with SQLAlchemy, I just pass through the namedtuple to/from Psycopg2.
>
> What I need to implement is the SQL syntax for querying composite type
> attributes. Postgres allows this by using the syntax
> "(table_name.column_name).attribute_name = 'bleh'" or just
> "(column_name).attribute_name = 'bleh'" when a table identifier is not
> required. I'm not sure how to go about this because the sql generation needs
> to change the way the column name is output, would this require subclassing
> the ColumnClause? I think I just need to know where to override the behaviour
> of generating the qualified column name in statements.
you'd be doing ColumnElement here which is the more fundamental object.It's
discussed here in "subclassing guidelines":
http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#subclassing-guidelines
if you're on 0.8 the integration here can be very smooth, using custom
operators
(http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#types-operators) you
could have an approach that looks like: mytable.c.column.attrs.attribute_name
== "value". The "attrs" namespace would call into your custom ColumnElement.
Since the custom operator API is a total blast to use here's a proof of concept:
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.types import UserDefinedType, to_instance
from sqlalchemy.sql.expression import ColumnElement
class PGCompositeElement(ColumnElement):
def __init__(self, base, attrname, type_):
self.base = base
self.attrname = attrname
self.type = to_instance(type_)
@compiles(PGCompositeElement)
def _compile_pgelem(element, compiler, **kw):
return "%s.%s" % (
compiler.process(element.base, **kw),
element.attrname
)
class _Namespace(object):
def __init__(self, comparator):
self.comparator = comparator
def __getattr__(self, key):
try:
type_ = self.comparator.type.typemap[key]
except KeyError:
raise KeyError(
"Type '%s' doesn't have an attribute: '%s'" %
(self.comparator.type, key))
return PGCompositeElement(
self.comparator.expr,
key,
type_)
class PGCompositeType(UserDefinedType):
def __init__(self, name, typemap):
self.name = name
self.typemap = typemap
class comparator_factory(UserDefinedType.Comparator):
@property
def attrs(self):
return _Namespace(self)
def get_col_spec(self):
return self.name
if __name__ == '__main__':
from sqlalchemy import String, Integer
from sqlalchemy.sql import table, column
mytype = PGCompositeType("mytype", {"attr1": String(50), "attr2": Integer})
t1 = table('mytable', column('mycolumn', mytype))
print t1.c.mycolumn.attrs.attr2 == 5
print t1.c.mycolumn.attrs.attr1 + "some string"
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] query of existing object won't refresh values (even when FOR UPDATE)
populate_existing() blows away any pending changes on the object so turning it
on by default would be a surprise in a lot of cases.
typically if someone is working with FOR UPDATE they're already programming a
very specific section in a very careful manner, it's not something that's done
casually. I would think that it would be used to select a row right at the
start of a transaction, that is definitely not already in the Session.
in my own experience, any time I've actually tried to work with pessimistic
locking my application ends up deadlocking at 2 AM, so I can't say I have the
background to really say how this method should be used. At this point, a
documentation note is certainly something doable (because I would hope very
much that anyone using this method has read the documentation carefully).
On Mar 5, 2013, at 10:00 AM, Kent wrote:
> By design, when a query() fetches an existing object, it doesn't refresh the
> values unless populate_existing() is included with the query. The
> documentation for populate_existing() states it isn't meant for general
> purpose.
>
> Occasionally, however, objects need to be selected FOR UPDATE,
> with_lockmode('update'), to guarantee against timing problems with concurrent
> database users, particularly when the record fetched is used as a base for
> the update. For example, if I need to update a record's quantity field by a
> delta of +5, it is extremely important that I have the most recent
> Object.quantity datum; truly, that was the reason I used
> with_lockmode('update') in the first place.
>
> I get uneasy to think that the user needs to remember to invoke
> populate_existing() when selecting a record FOR UPDATE to guard against the
> possibility that there is a stale version of the instance in the session.
>
> I will likely add that to our framework's Query subclass, but thought you
> might consider the implications here. Generally, sqla is extremely optimistic
> regarding locks, but in the event when the user is specifying
> with_lockmode('update'), we've left the realm of optimistic locking and
> entered pessimistic, so it seems reasonable to consider automatically
> enforcing populate_existing() when with_lockmode('update') is used?
>
> Something to consider; I'd be interested in your thoughts.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: query of existing object won't refresh values (even when FOR UPDATE)
I imagine this gets ugly when autoflush is disabled... perhaps that is why
it requires populate_existing()?
On Tuesday, March 5, 2013 10:00:36 AM UTC-5, Kent wrote:
>
> By design, when a query() fetches an existing object, it doesn't refresh
> the values unless populate_existing() is included with the query. The
> documentation for populate_existing() states it isn't meant for general
> purpose.
>
> Occasionally, however, objects need to be selected FOR UPDATE,
> with_lockmode('update'), to guarantee against timing problems with
> concurrent database users, particularly when the record fetched is used as
> a base for the update. For example, if I need to update a record's
> quantity field by a delta of +5, it is extremely important that I have the
> most recent Object.quantity datum; truly, that was the reason I used
> with_lockmode('update') in the first place.
>
> I get uneasy to think that the user needs to remember to invoke
> populate_existing() when selecting a record FOR UPDATE to guard against the
> possibility that there is a stale version of the instance in the session.
>
> I will likely add that to our framework's Query subclass, but thought you
> might consider the implications here. Generally, sqla is extremely
> optimistic regarding locks, but in the event when the user is specifying
> with_lockmode('update'), we've left the realm of optimistic locking and
> entered pessimistic, so it seems reasonable to consider automatically
> enforcing populate_existing() when with_lockmode('update') is used?
>
> Something to consider; I'd be interested in your thoughts.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] query of existing object won't refresh values (even when FOR UPDATE)
By design, when a query() fetches an existing object, it doesn't refresh
the values unless populate_existing() is included with the query. The
documentation for populate_existing() states it isn't meant for general
purpose.
Occasionally, however, objects need to be selected FOR UPDATE,
with_lockmode('update'), to guarantee against timing problems with
concurrent database users, particularly when the record fetched is used as
a base for the update. For example, if I need to update a record's
quantity field by a delta of +5, it is extremely important that I have the
most recent Object.quantity datum; truly, that was the reason I used
with_lockmode('update') in the first place.
I get uneasy to think that the user needs to remember to invoke
populate_existing() when selecting a record FOR UPDATE to guard against the
possibility that there is a stale version of the instance in the session.
I will likely add that to our framework's Query subclass, but thought you
might consider the implications here. Generally, sqla is extremely
optimistic regarding locks, but in the event when the user is specifying
with_lockmode('update'), we've left the realm of optimistic locking and
entered pessimistic, so it seems reasonable to consider automatically
enforcing populate_existing() when with_lockmode('update') is used?
Something to consider; I'd be interested in your thoughts.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Postgres custom composite types
Hello, I'm looking or some direction on how to implement the SQL syntax when querying Postgres' composite types. In case you're unfamiliar: the composite types in Postgres are a type that contains attributes which are regular SQL types. The attribute to type relationship is similar to the column to table relationship (except there are no constraints on attributes). A table column can be this composite type. Psycopg2 implements this by instantiating a namedtuple on retrieval or accepting a namedtuple on update/insert (you can also use your own custom class). The saving and retrieval isn't a problem with SQLAlchemy, I just pass through the namedtuple to/from Psycopg2. What I need to implement is the SQL syntax for querying composite type attributes. Postgres allows this by using the syntax "(table_name.column_name).attribute_name = 'bleh'" or just "(column_name).attribute_name = 'bleh'" when a table identifier is not required. I'm not sure how to go about this because the sql generation needs to change the way the column name is output, would this require subclassing the ColumnClause? I think I just need to know where to override the behaviour of generating the qualified column name in statements. Thanks, Jason -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
