Re: [sqlalchemy] Re: using SQLA in distributed, concurrent, or asynchronous contexts?
From my understanding, this should be safe with Twisted: - SqlAlchemy engine operations, where you don't use the ORM and encapsulate every ansync chunk in a transaction, and within a thread - SqlAlchemy orm operations, where every async chunk is encapsulated in it's own session, and within a thread. I'm not sure how familiar you are with twisted, so i'm going to go real basic here... In the way you'd likely use it, Twisted works on the concept of async 'deferreds'. You sort of play Choose your own adventure and add in Callbacks or Errbacks to your miscellaneous functions. Processing a request wouldn't be: def process_request(): hit_api blocked process_results it would be more like (and I'm butchering this syntax): def process_request(): d = deferred( get_api ).addCallback(got_api) d.deferToThread() def get_api() block def got_api(): process data You'd also probably use a DeferredList, which lets you queue up 2dozen items in a batch at once. it's rad. Anyways, you wouldn't want to use a SqlAlchemy session that persists throughout the callback chain. Because everything is so anync , you're virtually guaranteed that the Session will not match the datastore. SqlAlchemy also has a blocking interface to the datastore, so IIRC, you not have issues with all the sessions, but you end up blocking the main twisted thread. so the popular workaround(s) are to do both: 1- defer to thread (which most people would do anyways) 2- open/close multiple sessions (which are cheap). basically , instead of approaching a task in twisted as a single action of multiple components, think of it like a website , with each action as a new page request. your web session is the same, but your SqlAlchemy Session changes on each request. Anyways, I would probably look more at Celery for what you're looking to accomplish. Celery is super simple to learn and maintain; On a learning-curve scale of 1-10, I'd probably put celery at 1, gevent at 5, and twisted at 10. -- 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. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] contains_eager and order_by
On Oct 7, 2013, at 6:06 AM, Julien Cigar jci...@ulb.ac.be wrote:
Hello,
I have the following query:
entity = orm.with_polymorphic(
Content, [Event, News]
)
q = db.Session.query(entity)
The Event class has a country property:
orm.mapper(Event, table['event'], inherits = Content,
polymorphic_identity = _get_type_id('event'),
properties = {
'country' : orm.relationship(
Country, lazy = 'joined'
)
})
I would like to order my query above with .order_by(Country.name)
So I tried:
q = q.outerjoin(Event.country).options(
orm.contains_eager(Event.country)
).order_by(
Country.name
)
but SQLAlchemy complains with:
ArgumentError: Can't find property 'country' on any entity
specified in this Query. Note the full path from root
(Mapper|Content|content) to target entity must be specified.
you need to state all work with Event in terms of the with_polymorphic
construct you've created:
outerjoin(entity.Event.country)
this is documented about midway through
http://docs.sqlalchemy.org/en/rel_0_8/orm/inheritance.html#with-polymorphic ,
though maybe a little buried.
signature.asc
Description: Message signed with OpenPGP using GPGMail
[sqlalchemy] Descending sorted index on PostgreSQL
According to the
documentationhttp://docs.sqlalchemy.org/en/rel_0_8/core/constraints.html?highlight=desc#functional-indexes,
the following index definition:
Index('ix_table_column_desc', table.c.column.desc())
should render in PostgreSQL to:
CREATE INDEX ix_table_column_desc
ON table
USING btree
(modified DESC);
but instead the DESC is omitted and I only get:
CREATE INDEX ix_table_column_desc
ON table
USING btree
(modified);
Am I missing some detail here? Should I create a ticket about this issue?
Thanks,
--Pedro.
--
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.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Descending sorted index on PostgreSQL
there's no USING btree generated by the CreateIndex construct unless you
specify postgresql_using which I do not see in your code.
here's a test using both desc() and using which works as advertised:
from sqlalchemy import *
t = Table('t', MetaData(), Column('column', String(50)))
ix = Index('ix_table_column_desc', t.c.column.desc(), postgresql_using='btree')
e = create_engine(postgresql://scott:tiger@localhost/test, echo=True)
c = e.connect()
tr = c.begin()
t.create(c)
output:
2013-10-07 14:05:28,064 INFO sqlalchemy.engine.base.Engine select version()
2013-10-07 14:05:28,064 INFO sqlalchemy.engine.base.Engine {}
2013-10-07 14:05:28,065 INFO sqlalchemy.engine.base.Engine select
current_schema()
2013-10-07 14:05:28,065 INFO sqlalchemy.engine.base.Engine {}
2013-10-07 14:05:28,067 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-10-07 14:05:28,067 INFO sqlalchemy.engine.base.Engine
CREATE TABLE t (
column VARCHAR(50)
)
2013-10-07 14:05:28,067 INFO sqlalchemy.engine.base.Engine {}
2013-10-07 14:05:28,069 INFO sqlalchemy.engine.base.Engine CREATE INDEX
ix_table_column_desc ON t USING btree (column DESC)
2013-10-07 14:05:28,069 INFO sqlalchemy.engine.base.Engine {}
On Oct 7, 2013, at 1:37 PM, Pedro Romano pmcn...@gmail.com wrote:
According to the documentation, the following index definition:
Index('ix_table_column_desc', table.c.column.desc())
should render in PostgreSQL to:
CREATE INDEX ix_table_column_desc
ON table
USING btree
(modified DESC);
but instead the DESC is omitted and I only get:
CREATE INDEX ix_table_column_desc
ON table
USING btree
(modified);
Am I missing some detail here? Should I create a ticket about this issue?
Thanks,
--Pedro.
--
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.
For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Descending sorted index on PostgreSQL
Thanks for quick and comprehensive reply Michael.
I apologise for having been lazy and not providing an example that
illustrated the issue. The examples of rendered SQL were what I got from
the PgAdminIII inspection of the index (which I didn't even edit
correctly!), so I guess PostgreSQL 9.2 creates btree indexes by default if
the type of index is omitted.
I am trying to create the index in a 'mapper_configured' event listener of
a declarative model mixin (not sure if this is the right place, but it was
the only one I could find where I could get the information I needed, i.e.
the local table from the mapper of the model that included the mixin, using
'propagate=True' in the event listener), and for some weird reason, when
creating the index there, I get the behaviour I described, even when using
postgresql_using='btree'.
Evidently when running your example, the index SQL is rendered as
advertised, so this will definitely require a better investigation of the
issue to try to understand what is happening in my case. When I find what
really is going on I'll come back.
Thanks again for your help.
--Pedro.
On Monday, 7 October 2013 19:06:47 UTC+1, Michael Bayer wrote:
there's no USING btree generated by the CreateIndex construct unless you
specify postgresql_using which I do not see in your code.
here's a test using both desc() and using which works as advertised:
from sqlalchemy import *
t = Table('t', MetaData(), Column('column', String(50)))
ix = Index('ix_table_column_desc', t.c.column.desc(),
postgresql_using='btree')
e = create_engine(postgresql://scott:tiger@localhost/test, echo=True)
c = e.connect()
tr = c.begin()
t.create(c)
output:
2013-10-07 14:05:28,064 INFO sqlalchemy.engine.base.Engine select version()
2013-10-07 14:05:28,064 INFO sqlalchemy.engine.base.Engine {}
2013-10-07 14:05:28,065 INFO sqlalchemy.engine.base.Engine select
current_schema()
2013-10-07 14:05:28,065 INFO sqlalchemy.engine.base.Engine {}
2013-10-07 14:05:28,067 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-10-07 14:05:28,067 INFO sqlalchemy.engine.base.Engine
CREATE TABLE t (
column VARCHAR(50)
)
2013-10-07 14:05:28,067 INFO sqlalchemy.engine.base.Engine {}
2013-10-07 14:05:28,069 INFO sqlalchemy.engine.base.Engine CREATE INDEX
ix_table_column_desc ON t USING btree (column DESC)
2013-10-07 14:05:28,069 INFO sqlalchemy.engine.base.Engine {}
On Oct 7, 2013, at 1:37 PM, Pedro Romano pmc...@gmail.com javascript:
wrote:
According to the
documentationhttp://docs.sqlalchemy.org/en/rel_0_8/core/constraints.html?highlight=desc#functional-indexes,
the following index definition:
Index('ix_table_column_desc', table.c.column.desc())
should render in PostgreSQL to:
CREATE INDEX ix_table_column_desc
ON table
USING btree
(modified DESC);
but instead the DESC is omitted and I only get:
CREATE INDEX ix_table_column_desc
ON table
USING btree
(modified);
Am I missing some detail here? Should I create a ticket about this issue?
Thanks,
--Pedro.
--
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+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy.
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.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Descending sorted index on PostgreSQL
is it possible the index you see is due to an index=True on the Column
elsewhere, and the one inside mapper_configured isn't being invoked?
mapper_configured won't necessarily have been called if you haven't started
using ORM functionality, calling configure_mappers() will guarantee it as long
as that class has been mapped ahead of that call.
On Oct 7, 2013, at 5:09 PM, Pedro Romano pmcn...@gmail.com wrote:
Thanks for quick and comprehensive reply Michael.
I apologise for having been lazy and not providing an example that
illustrated the issue. The examples of rendered SQL were what I got from the
PgAdminIII inspection of the index (which I didn't even edit correctly!), so
I guess PostgreSQL 9.2 creates btree indexes by default if the type of index
is omitted.
I am trying to create the index in a 'mapper_configured' event listener of a
declarative model mixin (not sure if this is the right place, but it was the
only one I could find where I could get the information I needed, i.e. the
local table from the mapper of the model that included the mixin, using
'propagate=True' in the event listener), and for some weird reason, when
creating the index there, I get the behaviour I described, even when using
postgresql_using='btree'.
Evidently when running your example, the index SQL is rendered as advertised,
so this will definitely require a better investigation of the issue to try to
understand what is happening in my case. When I find what really is going on
I'll come back.
Thanks again for your help.
--Pedro.
On Monday, 7 October 2013 19:06:47 UTC+1, Michael Bayer wrote:
there's no USING btree generated by the CreateIndex construct unless you
specify postgresql_using which I do not see in your code.
here's a test using both desc() and using which works as advertised:
from sqlalchemy import *
t = Table('t', MetaData(), Column('column', String(50)))
ix = Index('ix_table_column_desc', t.c.column.desc(),
postgresql_using='btree')
e = create_engine(postgresql://scott:tiger@localhost/test, echo=True)
c = e.connect()
tr = c.begin()
t.create(c)
output:
2013-10-07 14:05:28,064 INFO sqlalchemy.engine.base.Engine select version()
2013-10-07 14:05:28,064 INFO sqlalchemy.engine.base.Engine {}
2013-10-07 14:05:28,065 INFO sqlalchemy.engine.base.Engine select
current_schema()
2013-10-07 14:05:28,065 INFO sqlalchemy.engine.base.Engine {}
2013-10-07 14:05:28,067 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-10-07 14:05:28,067 INFO sqlalchemy.engine.base.Engine
CREATE TABLE t (
column VARCHAR(50)
)
2013-10-07 14:05:28,067 INFO sqlalchemy.engine.base.Engine {}
2013-10-07 14:05:28,069 INFO sqlalchemy.engine.base.Engine CREATE INDEX
ix_table_column_desc ON t USING btree (column DESC)
2013-10-07 14:05:28,069 INFO sqlalchemy.engine.base.Engine {}
On Oct 7, 2013, at 1:37 PM, Pedro Romano pmc...@gmail.com wrote:
According to the documentation, the following index definition:
Index('ix_table_column_desc', table.c.column.desc())
should render in PostgreSQL to:
CREATE INDEX ix_table_column_desc
ON table
USING btree
(modified DESC);
but instead the DESC is omitted and I only get:
CREATE INDEX ix_table_column_desc
ON table
USING btree
(modified);
Am I missing some detail here? Should I create a ticket about this issue?
Thanks,
--Pedro.
--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
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.
For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Descending sorted index on PostgreSQL
Thanks for the tip, Michael. I've started a more thorough investigation of
the issue, but it's been inconclusive so far: none of the obvious
suspected. I'll be sure to post here if I find anything interesting.
--Pedro.
On Monday, 7 October 2013 23:01:31 UTC+1, Michael Bayer wrote:
is it possible the index you see is due to an index=True on the Column
elsewhere, and the one inside mapper_configured isn't being invoked?
mapper_configured won't necessarily have been called if you haven't
started using ORM functionality, calling configure_mappers() will guarantee
it as long as that class has been mapped ahead of that call.
On Oct 7, 2013, at 5:09 PM, Pedro Romano pmc...@gmail.com javascript:
wrote:
Thanks for quick and comprehensive reply Michael.
I apologise for having been lazy and not providing an example that
illustrated the issue. The examples of rendered SQL were what I got from
the PgAdminIII inspection of the index (which I didn't even edit
correctly!), so I guess PostgreSQL 9.2 creates btree indexes by default if
the type of index is omitted.
I am trying to create the index in a 'mapper_configured' event listener of
a declarative model mixin (not sure if this is the right place, but it was
the only one I could find where I could get the information I needed, i.e.
the local table from the mapper of the model that included the mixin, using
'propagate=True' in the event listener), and for some weird reason, when
creating the index there, I get the behaviour I described, even when using
postgresql_using='btree'.
Evidently when running your example, the index SQL is rendered as
advertised, so this will definitely require a better investigation of the
issue to try to understand what is happening in my case. When I find what
really is going on I'll come back.
Thanks again for your help.
--Pedro.
On Monday, 7 October 2013 19:06:47 UTC+1, Michael Bayer wrote:
there's no USING btree generated by the CreateIndex construct unless
you specify postgresql_using which I do not see in your code.
here's a test using both desc() and using which works as advertised:
from sqlalchemy import *
t = Table('t', MetaData(), Column('column', String(50)))
ix = Index('ix_table_column_desc', t.c.column.desc(),
postgresql_using='btree')
e = create_engine(postgresql://scott:tiger@localhost/test, echo=True)
c = e.connect()
tr = c.begin()
t.create(c)
output:
2013-10-07 14:05:28,064 INFO sqlalchemy.engine.base.Engine select
version()
2013-10-07 14:05:28,064 INFO sqlalchemy.engine.base.Engine {}
2013-10-07 14:05:28,065 INFO sqlalchemy.engine.base.Engine select
current_schema()
2013-10-07 14:05:28,065 INFO sqlalchemy.engine.base.Engine {}
2013-10-07 14:05:28,067 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
2013-10-07 14:05:28,067 INFO sqlalchemy.engine.base.Engine
CREATE TABLE t (
column VARCHAR(50)
)
2013-10-07 14:05:28,067 INFO sqlalchemy.engine.base.Engine {}
2013-10-07 14:05:28,069 INFO sqlalchemy.engine.base.Engine CREATE INDEX
ix_table_column_desc ON t USING btree (column DESC)
2013-10-07 14:05:28,069 INFO sqlalchemy.engine.base.Engine {}
On Oct 7, 2013, at 1:37 PM, Pedro Romano pmc...@gmail.com wrote:
According to the
documentationhttp://docs.sqlalchemy.org/en/rel_0_8/core/constraints.html?highlight=desc#functional-indexes,
the following index definition:
Index('ix_table_column_desc', table.c.column.desc())
should render in PostgreSQL to:
CREATE INDEX ix_table_column_desc
ON table
USING btree
(modified DESC);
but instead the DESC is omitted and I only get:
CREATE INDEX ix_table_column_desc
ON table
USING btree
(modified);
Am I missing some detail here? Should I create a ticket about this issue?
Thanks,
--Pedro.
--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
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+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy.
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
Re: [sqlalchemy] Re: using SQLA in distributed, concurrent, or asynchronous contexts?
On Mon, Oct 7, 2013 at 12:01 PM, Jonathan Vanasco jonat...@findmeon.com wrote: so the popular workaround(s) are to do both: 1- defer to thread (which most people would do anyways) 2- open/close multiple sessions (which are cheap). basically , instead of approaching a task in twisted as a single action of multiple components, think of it like a website , with each action as a new page request. your web session is the same, but your SqlAlchemy Session changes on each request. There's a 3. 3- open a single request-scope session, and carry it through your async tasks. You'll have to be super-careful not to use any thread-local state, always use the request-scope session, but that's relatively easy with most frameworks. Probably the easiest way would be to instantiate an explicit session with a session_maker, and then carry it implicitly in your callbacks as a closure. This is even easier on web frameworks, that support this kind of stuff built-in since it's so very common. But, adapting, got_api: def process_request(): d = deferToThread( get_api, session_maker() ).addCallback(got_api) def get_api(session) # use session block def got_api(): process data At first sight, this doesn't look all that better, but when you start chaining deferreds, it will be. -- 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. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Rewriting input/output values on columns with core
Hello there, I see that Column() has default, onupdate, and server_default to be used when the value for that column is not present when inserting and updating... What would be the best way of rewriting a value that is present when inserting or updating? Similarly, is there any easy way of rewriting the value of a column when selecting? Basically it would be great if we could specify input/output callables with Column() objects... If such functionality is not there, is there any gotcha in trying to extend column()? How do you recommend we do it? Thank you very much, g. -- 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. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Re: using SQLA in distributed, concurrent, or asynchronous contexts?
On Monday, October 7, 2013 8:27:34 PM UTC-4, Klauss wrote: 3- open a single request-scope session, and carry it through your async tasks. I left that out, because it brings up the problem where your long-running session blocks the database (via transactions) or if you're in some 'autocommit' mode, you end up with a session very much out of sync. -- 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. For more options, visit https://groups.google.com/groups/opt_out.
