[sqlalchemy] Re: Need help with update + select query

[sector119]

product_t = Product.__table__
product_flavor_t = ProductFlavor.__table__
product_t_a = product_t.alias()

op.add_column(u'product', sa.Column('servings', sa.Integer(), nullable=True))
op.add_column(u'product', sa.Column('flavors_count', sa.Integer(), 
nullable=True))

servings = select([func.coalesce(func.avg(product_flavor_t.c.size), 0)]).\
   select_from(product_t_a.outerjoin(product_flavor_t)).\
   where(product_t.c.id == product_t_a.c.id)

flavors_count = select([func.count(product_flavor_t.c.id)]).\
select_from(product_t_a.outerjoin(product_flavor_t)).\
where(product_t.c.id == product_t_a.c.id)

op.execute(product_t.update().values(servings=servings, 
flavors_count=flavors_count))


op.alter_column(u'product', 'servings', nullable=False)
op.alter_column(u'product', 'flavors_count', nullable=False)

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Memory leak? with connection & insert

[Mike Bayer]

On 02/25/2016 07:56 PM, Randy Syring wrote:

Mike,

Just to be clear, I'm not doing any kind of selects.  I'm only inserting
records.  And these aren't large binary blobs of any kind, they are
rather small strings and ints.

I apologize in advance if I misunderstood your answer.


ah, well then that is odd, in your code example I assumed that whole 
yield_merchants() thing was doing a SELECT.


Running tens of thousands / millions of small INSERT statements 
shouldn't budge your memory use on the client side at all.   If you can 
try doing just the SQL part given a set of mock data, maybe that would 
help to isolate what might be happening.You're just doing 
cursor.execute() on a single connection, there's no state that 
SQLAlchemy keeps around nor any for psycopg2 per statement that would be 
growing memory.   Perhaps run through half of the loop and then use a 
tool like heapy (http://smira.ru/wp-content/uploads/2011/08/heapy.html) 
to see what kinds of objects are prevalent.






*Randy Syring*
Chief Executive Developer
Direct: 502.276.0459
Office: 812.285.8766
Level 12 

On 02/25/2016 07:46 PM, Mike Bayer wrote:



On 02/25/2016 06:31 PM, Randy Syring wrote:

I'm working on a project to parse through a large text file (1GB) of
records.  Once parsed, each record gets sent to the DB.  Due to the size
of the file, I've been working on a streaming/functional approach that
will keep my memory usage constant.

I've been able to simply take the DB out of the equation and parse
through all of the records and memory usage stays constant. But, as
soon as I bring SA into the picture, memory usage continues to climb
through the lifetime of the program.


Well as soon as you bring psycopg2, or really any DBAPI, into the
picture.   DBAPI drivers fully buffer both rows and columns. However,
this wouldn't lead to unbounded growth, only that memory would grow as
big as the biggest single result set you've fetched.



I originally started using the ORM, and thought the Session would be the
culprit, but have now drilled down deep enough into the problem that it
appears to be an issue even when using simple connections.

*using psycopg:

*
|
 connection =db.engine.connect().connection
withconnection.cursor()ascursor:
forcount,statement inenumerate(MEXChunker(mex_file).yield_merchants()):
forpc_data instatement.program_charges:
 insert_sql ="INSERT INTO stage.tsys_program_charges
(reporting_month,"\
"reporting_year, charge_amount, merchant_number, officer_code) "\
"VALUES (%s, %s, %s, %s, %s)"
 cursor.execute(insert_sql,pc_data)



I see you aren't using server side cursors, which is the minimum
required to not get psycopg2 to buffer rows as they are sent. But even
then, server side cursors don't have any impact on individual column
values being buffered - I'm not sure if these text fields are large
binary objects themselves, but the only DBAPI right now that supports
streaming of BLOB objects is cx_Oracle and SQLAlchemy also hides this
API.   For streaming of large objects in psycopg2, you have to use
this obscure Postgresql feature nobody uses called "large objects"
that requires use of a special table, that's described at
http://initd.org/psycopg/docs/usage.html#access-to-postgresql-large-objects.



|

The above, when ran, shows memory ("RES" in `top`) quickly climb and
then hold around 183K.  The resources module reports "max rss" at 182268
at the end of running the script.  Those memory numbers are just about
the same if I simply run the loop and keep the DB out of it.





*using SA

*
|
withdb.engine.begin()asconnection:
forcount,statement inenumerate(MEXChunker(mex_file).yield_merchants()):
forpc_data instatement.program_charges:
 insert_sql ="INSERT INTO stage.tsys_program_charges
(reporting_month,"\
"reporting_year, charge_amount, merchant_number, officer_code) "\
"VALUES (%s, %s, %s, %s, %s)"
 connection.execute(insert_sql,pc_data)
|

The above, when ran, shows memory usage climbing through the life of the
script.  "max rss" tops out at 323984.

I'd like to ultimately be able to use the ORM for this project, but if
even the simple inserts using SA don't result in constant memory, I
can't really more forward with that plan.

Thanks in advance for any help you can provide.


Basically, people use server side cursors for this, but I find those
to be troubling since they are temperamental and aren't platform
dependent.  If the size of your data is based on that there's a lot of
rows, I'd fetch it using windows at a time, e.g. an approach similar
to that described at
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/WindowedRangeQuery.


I've written routines that generalize this such that I can even pass
different chunks of data as I fetch them into individual worker
processes using a multiprocessing.Pool and I've written routines that
write data out to files and use heapq to sort them back (that job in

Re: [sqlalchemy] Memory leak? with connection & insert

[Randy Syring]

Mike,

Just to be clear, I'm not doing any kind of selects.  I'm only inserting 
records.  And these aren't large binary blobs of any kind, they are 
rather small strings and ints.


I apologize in advance if I misunderstood your answer.

*Randy Syring*
Chief Executive Developer
Direct: 502.276.0459
Office: 812.285.8766
Level 12 

On 02/25/2016 07:46 PM, Mike Bayer wrote:



On 02/25/2016 06:31 PM, Randy Syring wrote:

I'm working on a project to parse through a large text file (1GB) of
records.  Once parsed, each record gets sent to the DB.  Due to the size
of the file, I've been working on a streaming/functional approach that
will keep my memory usage constant.

I've been able to simply take the DB out of the equation and parse
through all of the records and memory usage stays constant. But, as
soon as I bring SA into the picture, memory usage continues to climb
through the lifetime of the program.


Well as soon as you bring psycopg2, or really any DBAPI, into the 
picture.   DBAPI drivers fully buffer both rows and columns. However, 
this wouldn't lead to unbounded growth, only that memory would grow as 
big as the biggest single result set you've fetched.




I originally started using the ORM, and thought the Session would be the
culprit, but have now drilled down deep enough into the problem that it
appears to be an issue even when using simple connections.

*using psycopg:

*
|
 connection =db.engine.connect().connection
withconnection.cursor()ascursor:
forcount,statement inenumerate(MEXChunker(mex_file).yield_merchants()):
forpc_data instatement.program_charges:
 insert_sql ="INSERT INTO stage.tsys_program_charges
(reporting_month,"\
"reporting_year, charge_amount, merchant_number, officer_code) "\
"VALUES (%s, %s, %s, %s, %s)"
 cursor.execute(insert_sql,pc_data)



I see you aren't using server side cursors, which is the minimum 
required to not get psycopg2 to buffer rows as they are sent. But even 
then, server side cursors don't have any impact on individual column 
values being buffered - I'm not sure if these text fields are large 
binary objects themselves, but the only DBAPI right now that supports 
streaming of BLOB objects is cx_Oracle and SQLAlchemy also hides this 
API.   For streaming of large objects in psycopg2, you have to use 
this obscure Postgresql feature nobody uses called "large objects" 
that requires use of a special table, that's described at 
http://initd.org/psycopg/docs/usage.html#access-to-postgresql-large-objects. 




|

The above, when ran, shows memory ("RES" in `top`) quickly climb and
then hold around 183K.  The resources module reports "max rss" at 182268
at the end of running the script.  Those memory numbers are just about
the same if I simply run the loop and keep the DB out of it.





*using SA

*
|
withdb.engine.begin()asconnection:
forcount,statement inenumerate(MEXChunker(mex_file).yield_merchants()):
forpc_data instatement.program_charges:
 insert_sql ="INSERT INTO stage.tsys_program_charges
(reporting_month,"\
"reporting_year, charge_amount, merchant_number, officer_code) "\
"VALUES (%s, %s, %s, %s, %s)"
 connection.execute(insert_sql,pc_data)
|

The above, when ran, shows memory usage climbing through the life of the
script.  "max rss" tops out at 323984.

I'd like to ultimately be able to use the ORM for this project, but if
even the simple inserts using SA don't result in constant memory, I
can't really more forward with that plan.

Thanks in advance for any help you can provide.


Basically, people use server side cursors for this, but I find those 
to be troubling since they are temperamental and aren't platform 
dependent.  If the size of your data is based on that there's a lot of 
rows, I'd fetch it using windows at a time, e.g. an approach similar 
to that described at 
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/WindowedRangeQuery. 



I've written routines that generalize this such that I can even pass 
different chunks of data as I fetch them into individual worker 
processes using a multiprocessing.Pool and I've written routines that 
write data out to files and use heapq to sort them back (that job in 
particular we had to read/write out a 4G XML file, where a DOM tree of 
such would run out of memory immediately, so we used all SAX 
parsing/streaming and heapq). IMO once you have things chunked, you 
can do anything with it.







*system info

*Python 2.7.6
SA 1.0.10 & SA 1.0.12
Ubuntu Linux 14.04

--
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 https://groups.google.com/group/sqlalchemy.

Re: [sqlalchemy] Memory leak? with connection & insert

[Mike Bayer]

On 02/25/2016 06:31 PM, Randy Syring wrote:

I'm working on a project to parse through a large text file (1GB) of
records.  Once parsed, each record gets sent to the DB.  Due to the size
of the file, I've been working on a streaming/functional approach that
will keep my memory usage constant.

I've been able to simply take the DB out of the equation and parse
through all of the records and memory usage stays constant.  But, as
soon as I bring SA into the picture, memory usage continues to climb
through the lifetime of the program.


Well as soon as you bring psycopg2, or really any DBAPI, into the 
picture.   DBAPI drivers fully buffer both rows and columns.   However, 
this wouldn't lead to unbounded growth, only that memory would grow as 
big as the biggest single result set you've fetched.




I originally started using the ORM, and thought the Session would be the
culprit, but have now drilled down deep enough into the problem that it
appears to be an issue even when using simple connections.

*using psycopg:

*
|
 connection =db.engine.connect().connection
withconnection.cursor()ascursor:
forcount,statement inenumerate(MEXChunker(mex_file).yield_merchants()):
forpc_data instatement.program_charges:
 insert_sql ="INSERT INTO stage.tsys_program_charges
(reporting_month,"\
"reporting_year, charge_amount, merchant_number, officer_code) "\
"VALUES (%s, %s, %s, %s, %s)"
 cursor.execute(insert_sql,pc_data)



I see you aren't using server side cursors, which is the minimum 
required to not get psycopg2 to buffer rows as they are sent.   But even 
then, server side cursors don't have any impact on individual column 
values being buffered - I'm not sure if these text fields are large 
binary objects themselves, but the only DBAPI right now that supports 
streaming of BLOB objects is cx_Oracle and SQLAlchemy also hides this 
API.   For streaming of large objects in psycopg2, you have to use this 
obscure Postgresql feature nobody uses called "large objects" that 
requires use of a special table, that's described at 
http://initd.org/psycopg/docs/usage.html#access-to-postgresql-large-objects. 




|

The above, when ran, shows memory ("RES" in `top`) quickly climb and
then hold around 183K.  The resources module reports "max rss" at 182268
at the end of running the script.  Those memory numbers are just about
the same if I simply run the loop and keep the DB out of it.





*using SA

*
|
withdb.engine.begin()asconnection:
forcount,statement inenumerate(MEXChunker(mex_file).yield_merchants()):
forpc_data instatement.program_charges:
 insert_sql ="INSERT INTO stage.tsys_program_charges
(reporting_month,"\
"reporting_year, charge_amount, merchant_number, officer_code) "\
"VALUES (%s, %s, %s, %s, %s)"
 connection.execute(insert_sql,pc_data)
|

The above, when ran, shows memory usage climbing through the life of the
script.  "max rss" tops out at 323984.

I'd like to ultimately be able to use the ORM for this project, but if
even the simple inserts using SA don't result in constant memory, I
can't really more forward with that plan.

Thanks in advance for any help you can provide.


Basically, people use server side cursors for this, but I find those to 
be troubling since they are temperamental and aren't platform dependent. 
 If the size of your data is based on that there's a lot of rows, I'd 
fetch it using windows at a time, e.g. an approach similar to that 
described at 
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/WindowedRangeQuery. 



I've written routines that generalize this such that I can even pass 
different chunks of data as I fetch them into individual worker 
processes using a multiprocessing.Pool and I've written routines that 
write data out to files and use heapq to sort them back (that job in 
particular we had to read/write out a 4G XML file, where a DOM tree of 
such would run out of memory immediately, so we used all SAX 
parsing/streaming and heapq).   IMO once you have things chunked, you 
can do anything with it.







*system info

*Python 2.7.6
SA 1.0.10 & SA 1.0.12
Ubuntu Linux 14.04

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
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] duck_type_collection(_AssociationDict) is None?

[Mike Bayer]

duck_type_collection isn't really public API and also the association 
proxy objects don't fully emulate the collections they behave as, the 
issue at 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3040/association-proxies-are-not-json 
talks about how it would be nice if these finally implemented ABCs fully 
but that seems to be a very complicated task, and so far noone has been 
willing to put any effort into solving this problem.




On 02/25/2016 07:10 PM, Gerald Thibault wrote:

Is this an oversight, or intentional?

Until I discovered duck_type_collection, I was using a custom function I
wrote that did basically the same exact thing, so I thought it was my
lucky day to find it already implemented in SQLAlchemy. But it looks
like it doesn't return the expected (by me at least, ha ha) results when
given an instance of sqlalchemy.ext.associationproxy._AssociationDict.

Is this working as intended?

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] duck_type_collection(_AssociationDict) is None?

[Gerald Thibault]

Is this an oversight, or intentional?

Until I discovered duck_type_collection, I was using a custom function I 
wrote that did basically the same exact thing, so I thought it was my lucky 
day to find it already implemented in SQLAlchemy. But it looks like it 
doesn't return the expected (by me at least, ha ha) results when given an 
instance of sqlalchemy.ext.associationproxy._AssociationDict. 

Is this working as intended?

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Memory leak? with connection & insert

[Randy Syring]

I'm working on a project to parse through a large text file (1GB) of 
records.  Once parsed, each record gets sent to the DB.  Due to the size of 
the file, I've been working on a streaming/functional approach that will 
keep my memory usage constant.  

I've been able to simply take the DB out of the equation and parse through 
all of the records and memory usage stays constant.  But, as soon as I 
bring SA into the picture, memory usage continues to climb through the 
lifetime of the program.

I originally started using the ORM, and thought the Session would be the 
culprit, but have now drilled down deep enough into the problem that it 
appears to be an issue even when using simple connections.



*using psycopg:*
connection = db.engine.connect().connection
with connection.cursor() as cursor:
for count, statement in enumerate(MEXChunker(mex_file).
yield_merchants()):
for pc_data in statement.program_charges:
insert_sql = "INSERT INTO stage.tsys_program_charges 
(reporting_month," \
 "reporting_year, charge_amount, 
merchant_number, officer_code) " \
 "VALUES (%s, %s, %s, %s, %s)"
cursor.execute(insert_sql, pc_data)

The above, when ran, shows memory ("RES" in `top`) quickly climb and then 
hold around 183K.  The resources module reports "max rss" at 182268 at the 
end of running the script.  Those memory numbers are just about the same if 
I simply run the loop and keep the DB out of it.



*using SA*
with db.engine.begin() as connection:
for count, statement in enumerate(MEXChunker(mex_file).
yield_merchants()):
for pc_data in statement.program_charges:
insert_sql = "INSERT INTO stage.tsys_program_charges 
(reporting_month," \
 "reporting_year, charge_amount, 
merchant_number, officer_code) " \
 "VALUES (%s, %s, %s, %s, %s)"
connection.execute(insert_sql, pc_data)

The above, when ran, shows memory usage climbing through the life of the 
script.  "max rss" tops out at 323984.

I'd like to ultimately be able to use the ORM for this project, but if even 
the simple inserts using SA don't result in constant memory, I can't really 
more forward with that plan.

Thanks in advance for any help you can provide.



*system info*Python 2.7.6
SA 1.0.10 & SA 1.0.12
Ubuntu Linux 14.04

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Need help with update + select query

[sector119]

Postgresql doesn't support this yet, so I have to use separate query all 
aggregates (( 

 SET (servings, flavors_count) = (
SELECT coalesce(avg(f.size), 0),
   count(f.id)
FROM product p LEFT OUTER JOIN product_flavor f ON p.id = f.product_id
WHERE product.id = p.id

четверг, 25 февраля 2016 г., 15:19:11 UTC+2 пользователь sector119 написал:
>
> Hello,
>
> Can some one help me with that query? I get AttributeError: servings
> I expect that sqlalchemy use update from select for that query or it's not 
> possible and I must use select(...).as_scalar() for every updated column?
>
> s = 
> select([func.coalesce(func.avg(product_flavor_t.c.size).label('servings'), 
> 0),
> func.count().label('flavors_count')]).\
> where(and_(product_flavor_t.c.product_id == product_t.c.id, 
> product_flavor_t.c.quantity > 0))
>
> op.execute(product_t.update().values(servings=s.c.servings, 
> flavors_count=s.c.flavors_count))
>
> Thanks!
>

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Need help with update + select query

[sector119]

s = select([func.coalesce(func.avg(product_flavor_t.c.size), 0).label('f1'),
func.count(product_flavor_t.c.id).label('f2')]).\
select_from(product_t.outerjoin(product_flavor_t))
op.execute(product_t.update().values(servings=s.c.f1, flavors_count=s.c.f2))


I tried that, but it doesn't work as expected ((


I got UPDATE without SELECT :

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "f1" does 
not exist
LINE 1: UPDATE product SET servings=f1, flavors_count=f2



And PostgreSQL log file contains:


statement: ALTER TABLE product ADD COLUMN servings INTEGER
statement: ALTER TABLE product ADD COLUMN flavors_count INTEGER


statement: UPDATE product SET servings=f1, flavors_count=f2
ERROR:  column "f1" does not exist at character 29
STATEMENT:  UPDATE product SET servings=f1, flavors_count=f2

statement: ROLLBACK


-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Question about the other property of backref

[Jonathan Vanasco]

On Thursday, February 25, 2016 at 5:05:25 AM UTC-5, Simon King wrote:
 

> I think it's a matter of personal preference. Some people like to see all 
> the attributes of a class defined as part of the class definition itself, 
> in which case they'll need to use 2 relationship definitions with the 
> back_populates argument. Others prefer a terser syntax with less 
> duplication, so they use the backref argument.
>

Exactly.

Another use-case though, is that you start your project with backref -- and 
you honestly do prefer it.  Eventually you have 100 tables that are highly 
relational, and backref becomes a bit of a liability when editing the code 
because you don't know if there is an 'incoming' relationship or not.  If 
you're using back_populates, sqlalchemy makes sure you define the 
association on each side.  slowly switching from backref to back_populates 
has really reduced the number of errors we make.

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] TypeDecorator and Query.get()

[Jonathan Rogers]

I'm using the Enum recipe from 
http://techspot.zzzeek.org/2011/01/14/the-enum-recipe/

I have a class mapped to table which has a single enum column as its 
primary key. Querying using EnumSymbol instances for the column values 
mostly works fine. However, If I use Query.get(pk_val) where "pk_val" is an 
EnumSymbol instance, I get "InvalidRequestError: Incorrect number of values 
in identifier to formulate primary key for query.get()...". Digging into 
SQLAlchemy, I figured out that the problem is that EnumSymbol is iterable, 
confusing util.to_list(), which is used by Query.get(). I don't need 
EnumSymbols to be iterable so I'll just remove the __iter__ method. I'm not 
sure if this should be considered a bug in the recipe.

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Interaction between joined table inheritance and subquery correlate

[Mike Bayer]

On 02/24/2016 08:24 PM, Andrew Wansley wrote:

Hey y'all,

I'm trying to understand the interaction between joined table
inheritance and a correlated subquery.

The query seems to work as I expect if the same table doesn't have a
joined table subclass, or if I circumvent the polymorphic code by
querying Parent.__table__ directly, but not if I query the declarative
class directly.

Roughly, we want to query some kind of user action, joining in the most
immediately preceding event for the user who performed that action. We
select the event with a correlated subquery.

See the attached file for an isolated test case.


you did a fantastic job giving me exactly the kind of test case I'm 
looking for (minus having to install "arrow" :)  )  but yeah this 
doesn't work as it should.  I'm not a fan of across-the-board 
with_polymorphic at the mapper level because it generates atrocious 
queries, so you aren't doing it "wrong" but you might want to consider 
using ad-hoc with_polymorphic() when you need it.


Anyway I can give you two workarounds and a bug report, bug report is 
here: 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3662/correlation-fails-with-with_polymorphic


workarounds are as follows:

# workaround one - use Core correlate_except() to grab every
# possible table that's not UserEvent
q_with_declarative_two = (
session.query(Parent.id, UserEvent.id)
.join(User, Parent.user_id == User.id)
.join(UserEvent, and_(
UserEvent.user_id == User.id,
UserEvent.time == session.query(
func.max(UserEvent.time)
).filter(UserEvent.user_id == User.id)
 .filter(UserEvent.time <= Parent.time)
 .as_scalar().correlate_except(UserEvent)
))
)

# workaround two - use ad-hoc with_polymorphic to turn off
# the mapper-level polymoprhic

from sqlalchemy.orm import with_polymorphic
parent_poly = with_polymorphic(Parent, [], Parent.__table__)

q_with_declarative_three = (
session.query(parent_poly.id, UserEvent.id)
.join(User, parent_poly.user_id == User.id)
.join(UserEvent, and_(
UserEvent.user_id == User.id,
UserEvent.time == session.query(
func.max(UserEvent.time)
).filter(UserEvent.user_id == User.id)
 .filter(UserEvent.time <= parent_poly.time)
 .correlate(parent_poly).correlate(User)
 .as_scalar()
))
)

I'll have to find time to look into the issue here, these 
with_polymorphic things represent the absolute most confusing bugs to 
work out, there may be a 1.0-level fix that can come out but I'd have to 
look and see how big a change is needed.








(Tested on SqlAlchemy 1.0.12 with Postgres 9.3.5 as well as Sqlite 2.6.0)

I'm just as happy to hear "You're doing it wrong! Here's how to re-write
your query" as I am to a clear explanation of why this difference exists..

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Need help with update + select query

[Simon King]

On Thu, Feb 25, 2016 at 1:19 PM, sector119  wrote:

> Hello,
>
> Can some one help me with that query? I get AttributeError: servings
> I expect that sqlalchemy use update from select for that query or it's not
> possible and I must use select(...).as_scalar() for every updated column?
>
> s =
> select([func.coalesce(func.avg(product_flavor_t.c.size).label('servings'),
> 0),
> func.count().label('flavors_count')]).\
> where(and_(product_flavor_t.c.product_id == product_t.c.id,
> product_flavor_t.c.quantity > 0))
>
> op.execute(product_t.update().values(servings=s.c.servings,
> flavors_count=s.c.flavors_count))
>
>
Your select statement looks something like this:

SELECT coalesce(avg(product_flavor.size) as servings), 0),
   count(*) as flavors_count
FROM product_flavor

Notice that neither of the columns selected are labelled "servings", so
when you try to access "s.c.servings" later, you get an AttributeError.

Try moving the ".label('servings')" so that it applies to the result of
coalesce(), not avg().

Simon

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] update instance.relation.attr in instance.attr "set event listener"

[Simon King]

Er, ok. There are simpler ways to avoid autoflush

https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/DisableAutoflush

for example:

session = sqlalchemy.orm.object_session(mapper)
with session.no_autoflush:
target.product.quantity += (value - oldvalue)

...but that still doesn't fix your underlying problem, which is that you
are assigning an unexpected object to your "label" property. I guess
FieldStorage comes from your web framework, and you need to extract the
actual value from that before assigning it to your mapped object.

Simon

On Thu, Feb 25, 2016 at 1:24 PM, sector119  wrote:

> Thanks a lot, I add
>
> @event.listens_for(ProductFlavor, 'after_update')
> def quantity_before_update_listener(mapper, connection, target):
> quantity =
> select([func.coalesce(func.sum(ProductFlavor.__table__.c.quantity),
> 0)]).where(
> ProductFlavor.__table__.c.product_id == Product.__table__.c.id)
> connection.execute(
> Product.__table__.update().where(Product.__table__.c.id ==
> target.product_id).values(quantity=quantity))
>
>
> четверг, 25 февраля 2016 г., 11:52:50 UTC+2 пользователь Simon King
> написал:
>>
>> On Wed, Feb 24, 2016 at 9:51 PM, sector119  wrote:
>>
>>> Hello!
>>>
>>> I have two models, Product and ProductFlavor with one-to-many
>>> relationship
>>> And I have a listener, which I want to update Product.quantity on
>>> ProductFlavor.quantity change:
>>>
>>> @event.listens_for(ProductFlavor.quantity, 'set')
>>> def quantity_set(target, value, oldvalue, initiator):
>>> if value != oldvalue:
>>> target.product.quantity += (value - oldvalue)
>>>
>>>
>>> But I get the following error:
>>>
>>>
>>> ProgrammingError: (raised as a result of Query-invoked autoflush;
>>> consider using a session.no_autoflush block if this flush is occurring
>>> prematurely)
>>>
>>> (psycopg2.ProgrammingError) can't adapt type 'instance' [SQL: 'UPDATE
>>> product_flavor SET label=%(label)s WHERE product_flavor.id =
>>> %(product_flavor_id)s'] [parameters: {'product_flavor_id': 4, 'label':
>>> FieldStorage('label', u'42bbebd1f7ba46b58d3d4b794b4b890e.png')}]
>>>
>>> What I'm doing wrong?
>>>
>>
>> It looks like you're assigning a non-string to your "label" column. This
>> isn't directly related to your attribute listener - the error would happen
>> even without the attribute listener when you called session.flush() or
>> session.commit(). The attribute listener is just causing the flush to
>> happen earlier presumably because "target.product" has not yet been loaded
>> from the database.
>>
>> Simon
>>
>>
>> --
> 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 https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Can I make bulk update through association proxy?

[Simon King]

On Thu, Feb 25, 2016 at 12:01 PM, Piotr Dobrogost <
p...@2016.groups.google.dobrogost.net> wrote:

> On Thursday, February 25, 2016 at 11:10:36 AM UTC+1, Simon King wrote
>>
>>
>> I can't think of a way you could do this with objects you've already
>> loaded into memory. Perhaps you could use Query.update to issue the
>> appropriate SQL directly to the database?
>>
>>
>> http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html#sqlalchemy.orm.query.Query.update
>>
>
> Thanks for your reply.
> My reasoning is that if it's possible for one object (and it is) it should
> be possible for multiple objects as well.
> It seems to use Query.update I would need to filter/select related objects
> (those accessible through association proxy) directly and this is
> inconvenient as I would like to treat them as part of the primary objects
> and be able to filter/select them for update "through" primary objects by
> means of association proxy.
>

Maybe I'm not understanding your question properly. The return value from
query.all() is a plain python list. You're asking for it to return a
different kind of object, that wraps the underlying list and allows you to
specify arbitrary operations that should be applied to each object in that
list? I guess I could imagine a complicated class that might support
something like that, but I don't think it exists at the moment, and it
would seem like a lot of work just to avoid a simple loop...

Simon

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] update instance.relation.attr in instance.attr "set event listener"

[sector119]

Thanks a lot, I add 

@event.listens_for(ProductFlavor, 'after_update')
def quantity_before_update_listener(mapper, connection, target):
quantity = 
select([func.coalesce(func.sum(ProductFlavor.__table__.c.quantity), 
0)]).where(
ProductFlavor.__table__.c.product_id == Product.__table__.c.id)
connection.execute(
Product.__table__.update().where(Product.__table__.c.id == 
target.product_id).values(quantity=quantity))


четверг, 25 февраля 2016 г., 11:52:50 UTC+2 пользователь Simon King написал:
>
> On Wed, Feb 24, 2016 at 9:51 PM, sector119  > wrote:
>
>> Hello!
>>
>> I have two models, Product and ProductFlavor with one-to-many relationship
>> And I have a listener, which I want to update Product.quantity on 
>> ProductFlavor.quantity change:
>>
>> @event.listens_for(ProductFlavor.quantity, 'set')
>> def quantity_set(target, value, oldvalue, initiator):
>> if value != oldvalue:
>> target.product.quantity += (value - oldvalue)
>>
>>
>> But I get the following error:
>>
>>
>> ProgrammingError: (raised as a result of Query-invoked autoflush; 
>> consider using a session.no_autoflush block if this flush is occurring 
>> prematurely) 
>>
>> (psycopg2.ProgrammingError) can't adapt type 'instance' [SQL: 'UPDATE 
>> product_flavor SET label=%(label)s WHERE product_flavor.id = 
>> %(product_flavor_id)s'] [parameters: {'product_flavor_id': 4, 'label': 
>> FieldStorage('label', u'42bbebd1f7ba46b58d3d4b794b4b890e.png')}]
>>
>> What I'm doing wrong?
>>
>
> It looks like you're assigning a non-string to your "label" column. This 
> isn't directly related to your attribute listener - the error would happen 
> even without the attribute listener when you called session.flush() or 
> session.commit(). The attribute listener is just causing the flush to 
> happen earlier presumably because "target.product" has not yet been loaded 
> from the database.
>
> Simon
>
>
>

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Need help with update + select query

[sector119]

Hello,

Can some one help me with that query? I get AttributeError: servings
I expect that sqlalchemy use update from select for that query or it's not 
possible and I must use select(...).as_scalar() for every updated column?

s = 
select([func.coalesce(func.avg(product_flavor_t.c.size).label('servings'), 
0),
func.count().label('flavors_count')]).\
where(and_(product_flavor_t.c.product_id == product_t.c.id, 
product_flavor_t.c.quantity > 0))

op.execute(product_t.update().values(servings=s.c.servings, 
flavors_count=s.c.flavors_count))

Thanks!

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Can I make bulk update through association proxy?

[Piotr Dobrogost]

On Thursday, February 25, 2016 at 11:10:36 AM UTC+1, Simon King wrote
>
>
> I can't think of a way you could do this with objects you've already 
> loaded into memory. Perhaps you could use Query.update to issue the 
> appropriate SQL directly to the database?
>
>
> http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html#sqlalchemy.orm.query.Query.update
>

Thanks for your reply.
My reasoning is that if it's possible for one object (and it is) it should 
be possible for multiple objects as well.
It seems to use Query.update I would need to filter/select related objects 
(those accessible through association proxy) directly and this is 
inconvenient as I would like to treat them as part of the primary objects 
and be able to filter/select them for update "through" primary objects by 
means of association proxy.

Regards,
Piotr

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] sqlalchemy core only to build dynamic query using multiple optional parameters and curl for the same

[Shan Tala]

Hi I have raised the query on stackoverflow with details. Please guide.

http://stackoverflow.com/questions/35625498/sqlalchemy-core-build-dynamic-query

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Question about the other property of backref

[尤立宇]

Thanks!

This is exactly what I was looking for.

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] I wish to know how I use table ALIAS in alchemy core

[Simon King]

On Thu, Feb 25, 2016 at 9:43 AM, Krishnakant  wrote:

> Hello,
> I have a query where there are 2 alias for a single table.
> This is because the table contains a self referencing foreign key.
> the table is (groupcode integer primary key, groupname text, subgroupof
> integer foreign key references groupcode).
> Now let's say I wish to have a 2 column query with groups and their
> respective subgroups, I need to join the table to itself making 2 aliases.
> I know the raw query but need to do it through sqlalchemy core.
> I don't use ORM for my project.and need this in the expression language.
>
>
Something like this perhaps:

import sqlalchemy as sa
md = sa.MetaData()
t = sa.Table(
't', md,
sa.Column('groupcode', sa.Integer, primary_key=True),
sa.Column('groupname', sa.Text()),
sa.Column('subgroupof', sa.ForeignKey('t.groupcode')),
)

subgroup = t.alias('subgroup')
j = t.join(subgroup, subgroup.c.subgroupof == t.c.groupcode)
print sa.select([t.c.groupcode, subgroup.c.groupcode]).select_from(j)


Output:

SELECT t.groupcode, subgroup.groupcode
FROM t JOIN t AS subgroup ON subgroup.subgroupof = t.groupcode


Hope that helps,

Simon

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Can I make bulk update through association proxy?

[Simon King]

On Thu, Feb 25, 2016 at 9:03 AM, Piotr Dobrogost <
p...@2016.groups.google.dobrogost.net> wrote:

> On Wednesday, February 24, 2016 at 2:41:43 PM UTC+1, Piotr Dobrogost wrote:
>>
>> Hi!
>>
>> Let's say I have a model Text with attribute "values" which is
>> association proxy.
>> I can update single object like this:
>> text = session.query(Text).one()
>> text.values.update(...)
>>
>> How can I update multiple objects with the same value without manually
>> looping over result of a query?
>> texts = session.query(Text).all()
>> texts???values???.update(...)
>>
>
> Any hints?
>
>
I can't think of a way you could do this with objects you've already loaded
into memory. Perhaps you could use Query.update to issue the appropriate
SQL directly to the database?

http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html#sqlalchemy.orm.query.Query.update

Simon

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Question about the other property of backref

[Simon King]

On Thu, Feb 25, 2016 at 7:53 AM, 尤立宇  wrote:

> Thanks for your response.
>
> Do you consider using `backref` only on one of the class bad practice?
>
> I'm curious because automatically creating descriptors seems possible to
> me, and I'm wondering when it happens.
>
> As documentation states so:
>
> Remember, when the backref keyword is used on a single relationship, it’s
> exactly the same as if the above two relationships were created
> individually using back_populates on each.
>
> ref: http://docs.sqlalchemy.org/en/latest/orm/backref.html
>
> There is also a half-a-year-old stackoverflow question about it:
>
>
> http://stackoverflow.com/questions/32617371/how-to-force-creation-of-backref-instrumentedattributes-using-sqlalchemy
>
>
>
I think it's a matter of personal preference. Some people like to see all
the attributes of a class defined as part of the class definition itself,
in which case they'll need to use 2 relationship definitions with the
back_populates argument. Others prefer a terser syntax with less
duplication, so they use the backref argument.

If you want to trigger the creation of all "pending" backref attributes, I
think you can call sqlalchemy.orm.configure_mappers:

http://docs.sqlalchemy.org/en/rel_1_0/orm/mapping_api.html#sqlalchemy.orm.configure_mappers

Normally this gets called automatically when you start querying the
database, but in certain instances you may want to call it explicitly.

Simon

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] update instance.relation.attr in instance.attr "set event listener"

[Simon King]

On Wed, Feb 24, 2016 at 9:51 PM, sector119  wrote:

> Hello!
>
> I have two models, Product and ProductFlavor with one-to-many relationship
> And I have a listener, which I want to update Product.quantity on
> ProductFlavor.quantity change:
>
> @event.listens_for(ProductFlavor.quantity, 'set')
> def quantity_set(target, value, oldvalue, initiator):
> if value != oldvalue:
> target.product.quantity += (value - oldvalue)
>
>
> But I get the following error:
>
>
> ProgrammingError: (raised as a result of Query-invoked autoflush; consider
> using a session.no_autoflush block if this flush is occurring prematurely)
>
> (psycopg2.ProgrammingError) can't adapt type 'instance' [SQL: 'UPDATE
> product_flavor SET label=%(label)s WHERE product_flavor.id =
> %(product_flavor_id)s'] [parameters: {'product_flavor_id': 4, 'label':
> FieldStorage('label', u'42bbebd1f7ba46b58d3d4b794b4b890e.png')}]
>
> What I'm doing wrong?
>

It looks like you're assigning a non-string to your "label" column. This
isn't directly related to your attribute listener - the error would happen
even without the attribute listener when you called session.flush() or
session.commit(). The attribute listener is just causing the flush to
happen earlier presumably because "target.product" has not yet been loaded
from the database.

Simon

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] I wish to know how I use table ALIAS in alchemy core

[Krishnakant]

Hello,
I have a query where there are 2 alias for a single table.
This is because the table contains a self referencing foreign key.
the table is (groupcode integer primary key, groupname text, subgroupof 
integer foreign key references groupcode).
Now let's say I wish to have a 2 column query with groups and their 
respective subgroups, I need to join the table to itself making 2 aliases.

I know the raw query but need to do it through sqlalchemy core.
I don't use ORM for my project.and need this in the expression language.
Happy hacking.
Krishnakant.

--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] prefix_with for queries with that eager load relationships

[Daniel Kraus]

On Tuesday, 23 February 2016 23:16:25 UTC+8, Mike Bayer wrote:
>
>
> On 02/23/2016 04:00 AM, Daniel Kraus wrote: 
> > Here is a simple script to demonstrate the error: 
> > 
> > https://gist.github.com/dakra/0424086f5837d722bc58 
>
> the joinedload() case "works", as long as you don't use LIMIT or OFFSET, 
> as there's no subquery: 
>
> SELECT SQL_CALC_FOUND_ROWS users.id AS users_id, users.name AS 
> users_name, addresses_1.id AS addresses_1_id, addresses_1.email_address 
> AS addresses_1_email_address, addresses_1.user_id AS addresses_1_user_id 
> FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = 
> addresses_1.user_id 
>
> but looking at the docs for the purpose of found_rows, it would only be 
> used with a LIMIT.   Therefore it's not really valid to use this 
> function with joined eager loading of a collection because it only works 
> at the top level of the query and a joined eager load is going to return 
> more rows than there are actual entities.   If OTOH you are only 
> retrieving a many-to-one via joined eager load, this should all work 
> totally fine, and even in the case of LIMIT I don't think a subquery is 
> applied for simple many-to-one relationships. 
>
> So subqueryload is the only practical option when you need collection 
> eager loading plus the found rows feature with limit.   In this case you 
> definitely don't want this emitted in the subquery because even if it 
> were accepted it would mess up your found_rows().   Longer term solution 
> here would be to provide flags to the query.prefix_with() method to 
> indicate prefixes that should always move to the outside of the query as 
> well as prefixes that should not be passed along to subqueryloaders and 
> other transformations. 
>
> Here's a found_rows modifier that will anticipate a subqueryload and 
> erase any _prefixes() nested: 
>
> from sqlalchemy.orm.interfaces import MapperOption 
> from sqlalchemy.sql import visitors 
>
>
> class FoundRows(MapperOption): 
>  def process_query(self, query): 
>  query._prefixes = "SQL_CALC_FOUND_ROWS", 
>
>  def process_query_conditionally(self, query): 
>  # when subqueryload calls upon loader options, it is passing 
>  # the fully contructed query w/ the original query already 
>  # embedded as a core select() object.  So we will modify the 
>  # select() after the fact. 
>
>  def visit_select(select): 
>  select._prefixes = () 
>
>  # this can be more hardcoded, but here we're searching throughout 
>  # all select() objects and erasing their _prefixes 
>  for from_ in query._from_obj: 
>  visitors.traverse( 
>  from_, {}, {"select": visit_select}) 
>
>
> users = s.query(User).options(FoundRows(), 
> subqueryload(User.addresses)).limit(3).all() 
> row_count = s.execute('SELECT FOUND_ROWS()').scalar() 
>
> print(users, row_count) 
>
>

Thanks, this does remove the 'SQL_CALC_FOUND_ROWS' from the subquery,
but when I query like you above with option subqueryload, sqlalchemy fires 
_2_ queries,
the first one having SQL_CALC_FOUND_ROWS and the second one not, so
s.execute('SELECT FOUND_ROWS()') only returns the found rows for that 
second query:

--- cut ---
[...INSERTs...]
2016-02-25 17:20:02,625 INFO sqlalchemy.engine.base.Engine SELECT 
SQL_CALC_FOUND_ROWS users.id AS users_id, users.name AS users_name 
FROM users 
 LIMIT %(param_1)s
2016-02-25 17:20:02,625 INFO sqlalchemy.engine.base.Engine {'param_1': 1}
2016-02-25 17:20:02,627 INFO sqlalchemy.engine.base.Engine SELECT 
addresses.id AS addresses_id, addresses.email_address AS 
addresses_email_address, addresses.user_id AS addresses_user_id, 
anon_1.users_id AS anon_1_users_id 
FROM (SELECT users.id AS users_id 
FROM users 
 LIMIT %(param_1)s) AS anon_1 INNER JOIN addresses ON anon_1.users_id = 
addresses.user_id ORDER BY anon_1.users_id
2016-02-25 17:20:02,627 INFO sqlalchemy.engine.base.Engine {'param_1': 1}
2016-02-25 17:20:02,628 INFO sqlalchemy.engine.base.Engine SELECT 
FOUND_ROWS()
2016-02-25 17:20:02,628 INFO sqlalchemy.engine.base.Engine {}
[<__main__.User object at 0x7f66e19a61d0>] 2
--- cut ---


But looking more into it, SQL_CALC_FOUND_ROWS seems to be slower in most 
cases anyway. See:
https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
https://mariadb.atlassian.net/browse/MDEV-4592  (The related issue is open 
since 2009).


So now generate the query and to get the row count I have:
--- cut ---
if model_query.statement._group_by_clause.clauses:
# if there's a GROUP BY we count the slow way:
# SELECT count(*) FROM (SELECT ... FROM Model ... )
row_count = 
model_query.limit(None).offset(None).order_by(None).count()
else:
# Remove limit, offset and order by from query and
# SELECT count(DISTINCT Model.id) FROM Model ...
count_query = 

[sqlalchemy] Re: Can I make bulk update through association proxy?

[Piotr Dobrogost]

On Wednesday, February 24, 2016 at 2:41:43 PM UTC+1, Piotr Dobrogost wrote:
>
> Hi!
>
> Let's say I have a model Text with attribute "values" which is association 
> proxy.
> I can update single object like this:
> text = session.query(Text).one()
> text.values.update(...)
>
> How can I update multiple objects with the same value without manually 
> looping over result of a query?
> texts = session.query(Text).all()
> texts???values???.update(...)
>

Any hints? 

Regards,
Piotr
 

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.