Re: [sqlalchemy] Connection pooling uses connections rather than sessions

[Anupama Goparaju]

Thanks a lot for the inputs!

On Wednesday, December 9, 2020 at 9:08:51 PM UTC-7 Mike Bayer wrote:

> Oracle describes SessionPool at:
>
>
> https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#connpool
>
> This pool describes the connection lifecycle as first calling 
> pool.acquire(), and then pool.release(connection), however the good news is 
> that if connection.close() is called normally, that also does a pool 
> release per their documentation, meaning the connection is transparently 
> pooled once acquired.
>
> SQLAlchemy allows the means by which a connection is acquired to be 
> customized using the "creator" hook as well as the newer "do_connect" hook:
>
>
> https://docs.sqlalchemy.org/en/13/core/engines.html?highlight=creator#sqlalchemy.create_engine.params.creator
>
>
> https://docs.sqlalchemy.org/en/13/core/events.html#sqlalchemy.events.DialectEvents.do_connect
>
> the difference between "creator" and "do_connect" is mostly that the 
> latter hook passes through all the connection arguments whereas the first 
> one does not."creator" is quicker to demonstrate as below since we are 
> given a complete source of connections to start.   
>
> Then to disable SQLAlchemy's pooling, following the guidelines at 
> https://docs.sqlalchemy.org/en/13/core/pooling.html#switching-pool-implementations
>  
> we want to use NullPool.   This pool will use the creator() function to get 
> new connections, and when the connection is to be released, it calls 
> connection.close(), just like we want.
>
> so we put those together and we get:
>
> import cx_Oracle
> from sqlalchemy import create_engine
> from sqlalchemy.pool import NullPool
>
> pool = cx_Oracle.SessionPool(
> user="scott", password="tiger", dsn="oracle1120", min=2, max=5, 
> increment=1
> )
>
> engine = create_engine("oracle://", creator=pool.acquire, 
> poolclass=NullPool)
>
> with engine.connect() as conn:
> print(conn.scalar("select 1 FROM dual"))
>
>
> that will give you the Engine which you then use with SQLAlchemy's Session 
> normally.
>
> Another way this could be done would be to make a SQLAlchemy pool 
> implementation that wraps cx_Oracle's SessionPool.   That could be 
> ultimately nicer to use and would be able to call acquire() / release() 
> directly without the need to use NullPool, but would be more work to 
> implement up front.
>
>
>
> On Wed, Dec 9, 2020, at 7:52 PM, Anupama Goparaju wrote:
>
> Hi.
>
> I would like to know how to use a cx-oracle SessionPool with SQLAlchemy.
> We are leveraging the Session object of SQLAlchemy but we would like to 
> use the driver level pooling instead of SQLAlchemy pool.
> Please advise.
>
> Thanks,
> Anupama
>
> On Monday, October 3, 2011 at 12:23:58 PM UTC-6 Mike Bayer wrote:
>
>
> On Oct 3, 2011, at 2:20 PM, Michael Bayer wrote:
>
> > 
> > 3. Use pool events to emit commands when connections are checked out or 
> checked in. If you need to emit some SQL or cx_oracle commands on the DBAPI 
> connection upon checkout or checkin, the Engine provides pool events which 
> accomplish this. They work with NullPool as well as the regular QueuePool.
> > 
> > http://www.sqlalchemy.org/docs/core/events.html#connection-pool-events
>
>
> in 0.6 you'd use PoolListener, same idea just older API:
>
>
> http://www.sqlalchemy.org/docs/06/core/interfaces.html#connection-pool-events
>
>
>
>
> -- 
> 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+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/e9e325d2-ac0e-47da-870f-6985d0e210c4n%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/1ab0ebc5-1d1a-45bd-9059-63eddf2e5a48n%40googlegroups.com.

Re: [sqlalchemy] Setting the FK not performed when relationship is declared

[Nikola Radovanovic]

Yes, my bad; it should state: # at this moment, child.parent_id 
 is None. What is strange, when added single_parent=True 
to the relationship - it then worked as I would expected. I guess its some 
of my misunderstanding of how relationship works. In case needed, I will 
set working sample, this one was just extracted from more complicated code.

Thanks!

On Thursday, December 10, 2020 at 11:00:40 AM UTC+1 Simon King wrote:

> I can't see anything obviously wrong with what you've written (you
> said "child.id is None", but I assume you meant child.parent_id). Can
> you provide a runnable example?
>
> Simon
>
> On Thu, Dec 10, 2020 at 8:27 AM Nikola Radovanovic  
> wrote:
> >
> > Hi,
> > I have a FK in child pointing to parent table. Also, there is a 
> relationship towards parent from children. I would like to create children 
> and set FK to parent explicitly, without previously loading the parent 
> object. After commit, FK of parent in child table is None. In case I remove 
> relationship from child to parent, FK is set properly.
> >
> > Here is the simplified code:
> >
> > class Child(Base):
> > __tablename__ = 'children'
> > id = Column(Integer, primary_key=True)
> > parent_id = Column(Integer, ForeignKey('parent.id', 
> ondelete="CASCADE"), nullable=True)
> >
> > parent = relationship("Parent", uselist=False) ## <-- when removing 
> this, it works
> >
> > class Parent(Base):
> > __tablename__ = 'parent'
> > id = Column(Integer, primary_key=True)
> >
> > child = Child(parent_id=3)
> > session.add(child)
> > session.commit()
> >
> > # at this moment, child.id is None and not 3., although parent with id 
> ==3 exists in the DB. DB is Postgres 12.
> >
> > I have tried different loading techniques, but nothing worked.
> >
> > Can someone please point me into the right direction?
> >
> > Thank you in advance.
> >
> > Kindest regards
> >
> > --
> > 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+...@googlegroups.com.
> > To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/4704d9db-2725-416f-9ab4-65077209b8f5n%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/0c78d1b8-58c9-475b-8355-17af30c7b146n%40googlegroups.com.

Re: [sqlalchemy] Setting the FK not performed when relationship is declared

[Simon King]

I can't see anything obviously wrong with what you've written (you
said "child.id is None", but I assume you meant child.parent_id). Can
you provide a runnable example?

Simon

On Thu, Dec 10, 2020 at 8:27 AM Nikola Radovanovic  wrote:
>
> Hi,
> I have a FK in child pointing to parent table. Also, there is a relationship 
> towards parent from children. I would like to create children and set FK to 
> parent explicitly, without previously loading the parent object. After 
> commit, FK of parent in child table is None. In case I remove relationship 
> from child to parent, FK is set properly.
>
> Here is the simplified code:
>
> class Child(Base):
> __tablename__ = 'children'
> id = Column(Integer, primary_key=True)
> parent_id = Column(Integer, ForeignKey('parent.id', ondelete="CASCADE"), 
> nullable=True)
>
>parent = relationship("Parent", uselist=False) ## <-- when removing this, 
> it works
>
> class Parent(Base):
> __tablename__ = 'parent'
> id = Column(Integer, primary_key=True)
>
> child = Child(parent_id=3)
> session.add(child)
> session.commit()
>
> # at this moment, child.id is None and not 3., although parent with id ==3 
> exists in the DB. DB is Postgres 12.
>
> I have tried different loading techniques, but nothing worked.
>
> Can someone please point me into the right direction?
>
> Thank you in advance.
>
> Kindest regards
>
> --
> 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/4704d9db-2725-416f-9ab4-65077209b8f5n%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/CAFHwexfBrrXhbRZHg7abuPXL4nYYeVM43zR2CyDL9e5G0NpjGw%40mail.gmail.com.

[sqlalchemy] Setting the FK not performed when relationship is declared

[Nikola Radovanovic]

Hi,
I have a FK in child pointing to parent table. Also, there is a 
relationship towards parent from children. I would like to create children 
and set FK to parent explicitly, without previously loading the parent 
object. After commit, FK of parent in child table is None. In case I remove 
relationship from child to parent, FK is set properly.

Here is the simplified code:

class Child(Base):
__tablename__ = 'children'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parent.id', 
ondelete="CASCADE"), nullable=True)

   parent = relationship("Parent", uselist=False) ## <-- when removing 
this, it works

class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)

child = Child(parent_id=3)
session.add(child)
session.commit()

# at this moment, child.id is None and not 3., although parent with id ==3 
exists in the DB. DB is Postgres 12.

I have tried different loading techniques, but nothing worked.

Can someone please point me into the right direction?

Thank you in advance.

Kindest regards

-- 
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/4704d9db-2725-416f-9ab4-65077209b8f5n%40googlegroups.com.