Re: [sqlalchemy] Re: calling stored procedure 1000 times in a row - system impact?

[Mike Bayer]

you likely want to use cx_oracle directly so that you can use callproc():

https://cx-oracle.readthedocs.io/en/latest/user_guide/plsql_execution.html


to access the cx_oracle cursor see the guidelines at 
https://docs.sqlalchemy.org/en/13/core/connections.html#calling-stored-procedures

you might get better results if you devise a stored proc that can accept 
multiple sets of parameters at once.  the cx_Oracle people are the best people 
to talk about regarding things like this, they actually work for Oracle: 
https://github.com/oracle/python-cx_Oracle/issues



On Wed, Oct 21, 2020, at 2:39 PM, Terrence-Monroe: Brannon wrote:
> 
> 
> On Wednesday, October 21, 2020 at 1:11:06 PM UTC-4 Terrence-Monroe: Brannon 
> wrote:
>> 
>> Question 2 - If we adopt a naive approach of opening a transaction, making 
>> 1000 calls to this stored proc that does simple inserts, and then commiting 
>> to database at the end of the 1000 calls to the stored proc, does this 
>> represent a bottleneck on database/computer resources of some sort, such as:
>> a - will multiple threads will be created for this?
>> 
>  
> I can answer this myself. Each separate call to the stored proc is sent to 
> the database immediately. So, there is no need for 1000 threads to remain 
> open until the commit is issued. The database does not actually commit the 
> data until the commit (if we are operating in that mode), but by no means is 
> Python/SQLAlchemy holding the transactions in limbo until the commit call.
> 
> 

> --
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> 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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/3a39a538-9aae-4ac5-995b-8135956c69b7n%40googlegroups.com
>  
> .

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/23d22c12-0de0-4030-9304-9fbe3deb50c3%40www.fastmail.com.

[sqlalchemy] Re: calling stored procedure 1000 times in a row - system impact?

[Steven James]

Question 1:
I don't think there is a good fancy way of doing this built in to 
SQLAlchemy. With your constraint of using a stored proc for inserts (we 
have a similar constraint where I work), one way around the 
multiple-command overhead would be to do a bulk insert to a temporary 
"parameters" table, and then use another stored proc that will send those 
params to your original proc one set at a time, or will implement a more 
efficient bulk "insert from select." Error handling can become complex if 
there are instances where your parameters to the insert function could be 
rejected by the stored procedures.

Some engines will also let you send multiple commands in a "BEGIN ... END" 
sql block, but I have not tested this with Oracle and there is always a 
limit to how large a command like that can be.

Question 2: 
a / b : A single transaction will not span threads or connections in a 
pool. Your described approach would use a single thread with a single 
connection on the client side.
c: The only real overhead here is the 1000 separate db calls, especially if 
they go across a network. There is some overhead to each command sent to 
the db server. 1000 calls is a lot but might not be too many, so before you 
go through any optimization, do some performance testing to make sure you 
need it.

On Wednesday, 21 October 2020 at 13:11:06 UTC-4 thequie...@gmail.com wrote:

> In our application we are using SQLAlchemy to retrieve data from our 
> Oracle database. However, we have a policy that any INSERTS to the Oracle 
> database are done via stored procedures. We have approximately 1000 rows 
> (where each row  consists of 3-4 simple scalars such as numbers or strings) 
> that we need to insert.
>
> Question 1 - is there a sophisticated approach of creating an array of 
> these 1000 items and submitting this collection to Oracle in one shot? If 
> so, are there any references on this?
>
> Question 2 - If we adopt a naive approach of opening a transaction, making 
> 1000 calls to this stored proc that does simple inserts, and then commiting 
> to database at the end of the 1000 calls to the stored proc, does this 
> represent a bottleneck on database/computer resources of some sort, such as:
> a - will multiple threads will be created for this?
> b - presuming the default of 20 pooled connections to the database 
> , how many 
> connections from this pool will be used for the 1000 calls to the 
> inserting-stored-procedure before we commit?
> c - Would synchronous calls of this stored procedure pose a serious 
> performance/system-overhead issue? 
>
>
>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d40accbf-efb2-464d-85f7-f8c75e9134can%40googlegroups.com.

[sqlalchemy] Re: calling stored procedure 1000 times in a row - system impact?

[Terrence-Monroe: Brannon]

On Wednesday, October 21, 2020 at 1:11:06 PM UTC-4 Terrence-Monroe: Brannon 
wrote:

>
> Question 2 - If we adopt a naive approach of opening a transaction, making 
> 1000 calls to this stored proc that does simple inserts, and then commiting 
> to database at the end of the 1000 calls to the stored proc, does this 
> represent a bottleneck on database/computer resources of some sort, such as:
> a - will multiple threads will be created for this?
>
>  
I can answer this myself. Each separate call to the stored proc is sent to 
the database immediately. So, there is no need for 1000 threads to remain 
open until the commit is issued. The database does not actually commit the 
data until the commit (if we are operating in that mode), but by no means 
is Python/SQLAlchemy holding the transactions in limbo until the commit 
call.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/3a39a538-9aae-4ac5-995b-8135956c69b7n%40googlegroups.com.

[sqlalchemy] calling stored procedure 1000 times in a row - system impact?

[Terrence-Monroe: Brannon]

In our application we are using SQLAlchemy to retrieve data from our Oracle 
database. However, we have a policy that any INSERTS to the Oracle database 
are done via stored procedures. We have approximately 1000 rows (where each 
row  consists of 3-4 simple scalars such as numbers or strings) that we 
need to insert.

Question 1 - is there a sophisticated approach of creating an array of 
these 1000 items and submitting this collection to Oracle in one shot? If 
so, are there any references on this?

Question 2 - If we adopt a naive approach of opening a transaction, making 
1000 calls to this stored proc that does simple inserts, and then commiting 
to database at the end of the 1000 calls to the stored proc, does this 
represent a bottleneck on database/computer resources of some sort, such as:
a - will multiple threads will be created for this?
b - presuming the default of 20 pooled connections to the database 
, how many connections 
from this pool will be used for the 1000 calls to the 
inserting-stored-procedure before we commit?
c - Would synchronous calls of this stored procedure pose a serious 
performance/system-overhead issue? 




-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/24223156-4e69-4f14-9991-935e858e99d5n%40googlegroups.com.

Re: [sqlalchemy] NEED HELP! Using Hybrid property in SQLAlchemy filter throws error

[Simon King]

The "expression" part of a hybrid property is used whenever you write
"FinishedGoodsParent.balance". It operates in the context of the
class, not a single instance, and it needs to return an SQL expression
that can be used inside a larger query.

In your version, you are trying to iterate over "cls.children", but
that's not possible because "cls.children" is not a list. Hybrid
properties that work across relationships can be a bit difficult to
think about. The expression that you return needs to access another
table, so you need to consider how the query will join to that table:

https://docs.sqlalchemy.org/en/13/orm/extensions/hybrid.html#working-with-relationships

In your case, you actually need to perform an aggregating function
(sum) on the related table. The easiest way to do that would be to
follow the correlated subquery example from the docs:

https://docs.sqlalchemy.org/en/13/orm/extensions/hybrid.html#correlated-subquery-relationship-hybrid

Something like this:

@balance.expression
def balance(cls):
return select([sa.func.sum(FinishedGoodsChild.qty)]).\
where(FinishedGoodsChild.parent_id==cls.id).\
label('balance')

You probably need something a bit more complicated than that - I
didn't understand the join condition between parent and child in your
example so I made up the "parent_id" column.

Hope that helps,

Simon

On Tue, Oct 20, 2020 at 4:57 PM Padam Sethia  wrote:
>
> Hello ,
>
> I'm having an issue with Hybrid methods - I still don't understand them 
> enough properly . So I have a parent and child many to many relationship
>
>
> This is the child model
>
> class FinishedGoodsChild(TimestampMixin, db.Model):
>   id = db.Column(db.Integer, primary_key=True)
>   hold_qty = db.Column(db.Float, default=0)
>   pen_qty = db.Column(db.Float, default=0)
>   qty = db.Column(db.Float, nullable=False, default=0)
>   sku = db.Column(db.String(40), nullable=False, default='')
>   size = db.relationship('SizeMaster',   
> secondary='size_goods_child', passive_deletes=True,   
> backref='size_goods_child', lazy='joined')
>   size_id = db.Column(db.Integer, nullable=False, default=None)
>
> This is the Parent model
>
>
> class FinishedGoodsChild(TimestampMixin, db.Model):
>   id = db.Column(db.Integer, primary_key=True)
>   qty =   db.Column(db.Float, default=0)
>   balance = db.Column(db.Float)
>   children = db.relationship('FinishedGoodsChild',   
> passive_deletes=True, secondary='goods_child_sizes', 
> backref='goods_child_sizes', lazy='joined')
>
>
> No I need to filter by the sum of the children qty
>
>
> Here is the hybrid property that I have set up , but throws not implemented 
> error
>
>
> @hybrid_property
> def balance(self):
>return sum(acc.qty for acc in self.children) @balance.expression
> def balance(cls):
>return sum(acc.qty for acc in cls.children)
>
> Help is much appreciated thanks!
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> ---
> 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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/ba1add47-9497-4b92-8cb8-926e03c958a5n%40googlegroups.com.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexfvqQs6c%2BtR20pkojBt4JjToWB7gxW7U9O4Pb_gxpxC3A%40mail.gmail.com.