Re: [sqlalchemy] SQLAlchemy Access to Native Bulk Loaders

[James Fennell]

Hi Ben,

Have you checked out bulk operations?
https://docs.sqlalchemy.org/en/13/orm/persistence_techniques.html#bulk-operations

Bulk operations provide a DB-agnostic API for doing large numbers of
inserts and/or updates. The speed up you see, compared to session.add,
depends on your database and the database connection arguments. With
Postgres, I've needed to enable batch mode to see the fullest benefit:
https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#psycopg2-batch-mode

Personally I've seen huge performance impovements in my applications after
migrating big operations from session.add over to the bulk API.

Of course, as you say, you can do more low level SQL calls to get it even
faster, but then you run into a bunch of other issues.

James


On Sun, Apr 19, 2020, 12:46 PM Ben  wrote:

> I hope this is the right place for this... I need to load large files into
> my database. As I understand it, I can do this in one of two ways with
> SQLAlchemy Core: 1) Bring the data into Python and then write it out with
> the *add* method or, alternatively, 2) Use SQLAlchemy to issue a command
> to the DB to use it's native bulk loader to read data from my file. I would
> expect this second approach to be faster, to require less code, and to
> avoid issues such as trying to put too much in memory at one time. However,
> it is DB-vendor-specific (i.e. I believe the command I send to a MySQL DB
> will differ from that I send to a Postgres DB).
>
> So,
>
>- Do I properly understand SQLAlchemy's capabilities here or am I
>missing something?
>- If I do have this right, is generic access to bulk loaders something
>that is on the upgrade / new development list?
>
> Sorry if this isn't the right place for this.
> Thanks!
> Ben
>
> --
> 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/fd55ee9a-6918-4b60-88bb-961787e3c53e%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/CALDF6i1nvcNQQi46WqrqDyYk2RJa0EwRQVK%2B010VXwJ1Fk8i1w%40mail.gmail.com.

[sqlalchemy] How can I use bulk_save_objects when I know the primary key?

[James Fennell]

Hey all! I'm having a problem using the bulk_save_objects function.

*Background*: I'm working on an application that updates state in a
database from an external feed. The process is: I read the feed, convert
the data into SQLAlchemy objects which are detached from the session and
then call session.merge on each one. The merge can result in an insert or
an update, depending on if the entity has been seen before.

Given the number of objects I'm working with, this has turned out to be
very CPU intensive in production. I've profiled it, and most of the work is
in the merge operation. So, I want to use bulk_save_objects to speed things
up and in my case the tradeoffs (lack of cascading etc.) are 100% worth it.

*Problem*: I can't get bulk_save_objects to do updates; no matter what, it
tries to insert and this results in primary key constraint failures:

sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate
key value violates unique constraint "trip_pkey"


Having read the documentation, I'm pretty sure the problem relates to this
comment in the docs:

For each object, whether the object is sent as an INSERT or an UPDATE is
dependent on the same rules used by the Session

in
traditional operation; if the object has the InstanceState.key attribute
set, then the object is assumed to be “detached” and will result in an
UPDATE. Otherwise, an INSERT is used.


In all cases I'm not playing with the instance state. I'm essentially
manually stamping primary keys on detached objects, so I'm guessing
SQLAlchemy thinks it needs to insert? Any suggestions for how I can proceed?

Thanks!
James

-- 
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/CALDF6i2Mpw6EQk-q_%2Br2dm3%3Dxz0Mv5Sb_yCeMktq%3DgBf4uQYsA%40mail.gmail.com.

Re: [sqlalchemy] Re: How to check for table's existance without a separate query

[James Fennell]

>  However I cannot catch for this error, I can only catch for
"sqlalchemy.exc.ProgrammingError".

Why is that?


James

On Wed, Dec 4, 2019 at 6:03 PM Jonathan Vanasco 
wrote:

>
> Personally, I would handle the check like this:
>
> ERRORS_UNDEFINED_TABLE = (psycopg2.errors.UndefinedTable, )
>
> try:
> res = conn.execute(stmt)
> except sa.exc.ProgrammingError as err:
> if isinstance(err.orig, ERRORS_UNDEFINED_TABLE):
> print('Table does not exist')
> raise
>
>
> This would allow you to update the `ERRORS_UNDEFINED_TABLE` tuple in a
> central place, and allow you to more easily catch this situation in other
> databases if needed.  I've used this technique in a few projects that are
> built for postgres, but support mysql and use sqlite for some tests.
>
>
> On Wednesday, December 4, 2019 at 5:52:15 PM UTC-5, Zsolt Ero wrote:
>>
>> Thanks. So is the following code correct for psycopg2 specific scenario?
>>
>> try:
>> res = conn.execute(stmt)
>> except sa.exc.ProgrammingError as err:
>> if isinstance(err.orig, psycopg2.errors.UndefinedTable):
>> print('Table does not exist')
>> else:
>> raise err
>>
> --
> 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/f936b2c1-bdcf-4372-8943-00a31615d70a%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/CALDF6i11hXvsywy0nAA86nSpet0h%3DGRDCZHxq6saEsyOdQA6cQ%40mail.gmail.com.

Re: [sqlalchemy] Large RAM usage in bulk_insert_mappings

[James Fennell]

Because the memory spike was so bad (the application usually runs at 250mb
RAM, and it went up to a GB during this process), I was able to find the
problem by running htop and using print statements to discover where in the
execution the Python code was when the RAM spike happened.

I unfortunately don't have any advice on actual good tools for tracking RAM
usage in Python programs but would to leave to hear if others do.



On Thu, Nov 14, 2019 at 12:41 PM Soumaya Mauthoor <
soumaya.mauth...@gmail.com> wrote:

> What did you use to profile memory usage? I've recently been investigating
> memory usage when loading data using memory_profiler and would be
> interested to find out about the best approach
>
> On Thu, 14 Nov 2019, 17:16 James Fennell,  wrote:
>
>> Hi all,
>>
>> Just sharing some perf insights into the bulk operation function
>> bulk_insert_mappings.
>>
>> I was recently debugging a SQL Alchemy powered web app that was crashing
>> due to out of memory issues on a small Kubernetes node. It turned out to be
>> "caused" by an over optimistic invocation of bulk_insert_mappings.
>> Basically I'm reading a CSV file with ~500,000 entries into a list of
>> dictionaries, and then passing it into the bulk_insert_mappings function at
>> once. It turned out the SQL Alchemy work was using 750mb of RAM, which was
>> enough to OOM the small node the web app was running on.
>>
>> A simple workaround is to split the list of 500,000 entries into chunks
>> of 1000 entries each, and then call bulk_insert_mappings on each chunk.
>> When I do this, the extra memory usage is not even noticeable. But also, it
>> seems that this chunked approach is actually faster! I might benchmark that
>> to quantify that.
>>
>> Thought it was interesting. I wonder would it be worth adding to the docs
>> on bulk_insert_mappings? Given that function is motivated by performance,
>> it seems it might be relevant.
>>
>> James
>>
>>
>>
>>
>> --
>> 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/CALDF6i1sx_QmW1v1jvopT-iWZqmSGmJ7JpJi10egNnbE0D7FMQ%40mail.gmail.com
>> <https://groups.google.com/d/msgid/sqlalchemy/CALDF6i1sx_QmW1v1jvopT-iWZqmSGmJ7JpJi10egNnbE0D7FMQ%40mail.gmail.com?utm_medium=email_source=footer>
>> .
>>
> --
> 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/CAN14jWTdFQ7tSyhr71H_wCX_JXT58S40Q4MN7qte6pE2N-%2BOLw%40mail.gmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/CAN14jWTdFQ7tSyhr71H_wCX_JXT58S40Q4MN7qte6pE2N-%2BOLw%40mail.gmail.com?utm_medium=email_source=footer>
> .
>

-- 
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/CALDF6i3KeKteJ8aV5XHM-g%2BMsAZdRoAnGqLfCxVETfj8vsDTSg%40mail.gmail.com.

[sqlalchemy] Large RAM usage in bulk_insert_mappings

[James Fennell]

Hi all,

Just sharing some perf insights into the bulk operation function
bulk_insert_mappings.

I was recently debugging a SQL Alchemy powered web app that was crashing
due to out of memory issues on a small Kubernetes node. It turned out to be
"caused" by an over optimistic invocation of bulk_insert_mappings.
Basically I'm reading a CSV file with ~500,000 entries into a list of
dictionaries, and then passing it into the bulk_insert_mappings function at
once. It turned out the SQL Alchemy work was using 750mb of RAM, which was
enough to OOM the small node the web app was running on.

A simple workaround is to split the list of 500,000 entries into chunks of
1000 entries each, and then call bulk_insert_mappings on each chunk. When I
do this, the extra memory usage is not even noticeable. But also, it seems
that this chunked approach is actually faster! I might benchmark that to
quantify that.

Thought it was interesting. I wonder would it be worth adding to the docs
on bulk_insert_mappings? Given that function is motivated by performance,
it seems it might be relevant.

James

-- 
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/CALDF6i1sx_QmW1v1jvopT-iWZqmSGmJ7JpJi10egNnbE0D7FMQ%40mail.gmail.com.

Re: [sqlalchemy] Re: Query last relation created and attributes

[James Fennell]

I think Mike's suggestion was to construct the raw SQL string you want,
then reverse engineer to get the correct SQL Alchemy code, which you can
then use with your different models. For complicated SQL logic I think this
is a good practice in general.

You current question seems like a general SQL question rather than
something specific to SQL Alchemy. After you've the SQL, we could discuss
the reverse engineering.

On Mon, May 13, 2019, 10:37 AM Scheck David  the problem is that I can't use SQL for this because this is a mixins that
> I use for several objects (tables) because they all have this status
> structure... yes it's quite difficult to do :
>
> right now I'm on this stage, but still don't work
> self.session.query(Object).outerjoin(ObjectStatus).outerjoin(Status)\
> .group_by(Object).with_entities(Object,
> func.max(Status.datum).label("status_datum")).subquery()
>
> self.session.query(Object).join((sub, sub.c.id == Status.id))\
> .filter(sub.c.statustype_id > 50)
>
> Le lun. 13 mai 2019 à 16:26, Mike Bayer  a
> écrit :
>
>> Hi -
>>
>> I was hoping someone could jump in on this.
>>
>> do you know the SQL that you want to emit?  E.g. plain SQL string.   I
>> can help you do that part.
>>
>>
>> On Mon, May 13, 2019 at 9:11 AM Scheck David  wrote:
>> >
>> > I think I'm near but I can't finish :
>> >
>> > for all the structure : https://dpaste.de/fek5#L
>> >
>> > and here my query :
>> >
>> > self.session.query(Object).outerjoin(ObjectStatus).outerjoin(Status)\
>> > .group_by(Object).with_entities(Object,
>> func.max(Status.datum).label("status_datum")).subquery()
>> >
>> > self.session.query(Object).join((sub, sub.c.id == Status.id))\
>> > .filter(sub.c.statustype_id > 50)
>> >
>> > but status type not reachable.
>> >
>> >
>> > --
>> > 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 post to this group, send email to sqlalchemy@googlegroups.com.
>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>> > To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/fe2da543-2710-4b44-9f88-e68c5e35faea%40googlegroups.com
>> .
>> > For more options, visit https://groups.google.com/d/optout.
>>
>> --
>> 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 a topic in the
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit
>> https://groups.google.com/d/topic/sqlalchemy/Cju-PF7mV58/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/CA%2BRjkXF3d_pffLqFLYpSNw80E%3D_2ULhh0pTiqBG-F1dFjA9r1Q%40mail.gmail.com
>> .
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
> --
>
> With kindest regards,
>
>
> *David SCHECK*
>
> PRESIDENT/DEVELOPER
>
> [image: Signature Logo Sphax Bleu-01.png]
>
> Phone: +32 4 87 86 70 12
> Visit our website ! https://www.sphax.org
>
> --
> 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 post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAOPG6scR1rWJTBN%3DEArX2buPqiVsGaPiQq4wYGU58Renqae0kA%40mail.gmail.com
> 
> .
> For more options, visit https://groups.google.com/d/optout.
>

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

Re: [sqlalchemy] Possible regression?

[James Fennell]

Thanks for the explanation Mike! Seeing it now, I actually think there’s a 
decent reason to want the current backerefs:

My understanding is that with session.merge in SQL Alchemy it’s possible to 
draw a very clean line between entities that are persisted (or about to be 
persisted on the next flush) and entities which will never be persisted. This 
is owing to the design choice whereby SQL Alchemy doesn’t persist the entity 
you pass into the merge; instead, that is kept alone and a new entity is 
created.

With this in mind, there are two ways to see Lyla’s example.

One way: as soon as the tassel_thread was related to the persisted my_head 
(persisted because of the line my_head=session.merge(my_head)) then 
tassel_thread should be seen as in the session already. In this view, the merge 
is necessary and possibly error-prone, as here.

Another way: instead of assigning my_head=session.merge(my_head), keep the 
unpersisted head around with say persisted_head = session.merge(my_head). Then 
relating the new tassel_thread to my_head won’t add it to the session. To get a 
record into the DB, then do a session.merge on it - everything works correctly 
this way.


In both cases, there is the idea of a persisted object graph and a distinct 
unpersisted object graph. Once you relate a new entity to something in the 
persisted object graph, it becomes persistent. 

-- 
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 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: Possible regression?

[James Fennell]

It seems to be related to the cascades happening recursively. The merge 
cascade goes from the tassel thread to the head, and then again down from 
the head to the tassel thread - which is kind of strange, I would expect 
the cascades to only visit each node in the object graph at most once. The 
second cascade is when the second tassel thread is created.

There are a couple of ways that I can get this to work:

   - Instead of setting head when creating the tassel thread, set head_id. 
   This avoids the first cascade being triggered.
   - Add cascade=None on the relationship in the Head to avoid the second 
   cascade being triggered. However the merge operation on the tassel thread 
   now returns a warning that the "add operation along 'Head.tassel_threads' 
   will not proceed"
   - The your second merge, instead of merging in the tassel_thread, merge 
   in the head again.

Btw, I'm using SQL Alchemy 1.2.16 and the thing you're seeing is still 
repro-able.

On Sunday, 28 April 2019 08:56:40 UTC-4, lyla...@gmail.com wrote:
>
> Hi!
>
> I recently came across some confusing behavior in relations and cascading 
> using sqllite, and I was hoping that I might get some help explaining what 
> the behavior is here. I put together a minimum failing script here. I'm 
> trying to commit one instance of each of two classes, but what ends up 
> happening is that I commit two copies of the many part of a one-to-many 
> relation. I suspect that this has something to do with cascading, but I 
> found a bug report for similar behavior 
> that
>  
> claims to have been fixed several years ago, and I'm wondering if there was 
> some kind of regression? I'm running SQLAlchemy 1.3.1 on Ubuntu and I'm 
> still using sqllite at this stage of development. 
>
> from sqlalchemy import create_engine
> from sqlalchemy.orm import scoped_session, sessionmaker
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import Column, Integer, String, Text, ForeignKey
> from sqlalchemy.orm import relationship
>
> import os
>
> db_engine = create_engine('sqlite:///sample.db', convert_unicode=True)
> db_session = scoped_session(sessionmaker(autocommit=False,
>  autoflush=False,
>  bind=db_engine))
>
> Base = declarative_base()
> Base.query = db_session.query_property()
>
> class Head(Base):
> __tablename__ = 'head'
> id = Column(String, primary_key=True)
> tassel_threads = relationship("TasselThread", back_populates="head")
> def __init__(self, id):
> self.id=id
>
> class TasselThread(Base):
> __tablename__ = 'tassel_thread'
> id = Column(Integer, primary_key=True)
> head_id = Column(Integer, ForeignKey('head.id'), nullable=False)
> head = relationship("Head", back_populates="tassel_threads")
> def __init__(self, head):
> self.head = head
>
> def init_db():
> Base.metadata.create_all(bind=db_engine)
>
>
> def do_db_work():
>
> my_head = Head(id="foobar")
> my_head = db_session.merge(my_head)
> db_session.commit()
>
> my_tassel_thread = TasselThread(head=my_head)
> db_session.merge(my_tassel_thread)
> db_session.commit()
>
>
> if os.path.exists("sample_data.db"):
> os.remove("sample_data.db")
> init_db()
> do_db_work()
> a = db_session.query(TasselThread).all()
> print(len(a))
> # output: 2, should be 1
>
> Thanks for any help you might be able to provide!
>
> -Lyla Fischer
>

-- 
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 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: What is the standard/best way to merge an object graph into the session?

[James Fennell]

Okay let me answer my own question. The problem is that my parent-child 
relationship does not have the delete-orphan cascade. So when I set the new 
children, the old child_2 loses its parent (as is expected, because it's no 
longer a child) and then there's an error because the DB has a not null 
constraint on the parent_pk coming from nullable=False.

I guess the moral of the story is that parent_pk being non-nullable 
essentially requires delete-orphan.

-- 
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 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: What is the standard/best way to merge an object graph into the session?

[James Fennell]

Oooo the problem is not what I thought.

The problem is that in my 'new data' there is no new_child_2. This is an 
expected case, as sometimes children disappear, so will update the post.

-- 
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 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] What is the standard/best way to merge an object graph into the session?

[James Fennell]

I have a parent child relationship which I construct from a data feed. At 
the time of constructing the object graph I don't have access to the 
primary keys of the entities, so I build up the object graph by using the 
relationship attributes. My understanding was that I could perform a 
session.merge to get the new state of the whole object graph into the 
database, but when I try do this I get an exception.

Sample code that reproduces the problem I encounter:

from sqlalchemy import Column, Integer, ForeignKey, create_engine, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship

Base = declarative_base()

PARENT_PK = 5
CHILD_1_PK = 6
CHILD_2_PK = 7


class Parent(Base):
__tablename__ = 'parent'

pk = Column(Integer, primary_key=True)
data = Column(String)

children = relationship(
'Child',
back_populates='parent'
)


class Child(Base):
__tablename__ = 'child'

pk = Column(Integer, primary_key=True)
parent_pk = Column(Integer, ForeignKey('parent.pk'), nullable=False)
data = Column(String)

parent = relationship(
'Parent',
back_populates='children'
)


engine = create_engine('sqlite:///temp.db')
session_factory = sessionmaker(bind=engine, autoflush=False)
Session = scoped_session(session_factory)
session = Session()
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

# Put some data in the database from some previous feed update
parent = Parent(pk=PARENT_PK, data='First')
child_1 = Child(pk=CHILD_1_PK, data='First child')
child_2 = Child(pk=CHILD_2_PK)
parent.children = [child_1, child_2]

session.add(parent)

session.commit()

# New data in the new feed update
new_parent = Parent(pk=PARENT_PK, data='Second')
new_child_1 = Child(pk=CHILD_1_PK, data='Second child')
new_parent.children = [new_child_1]

session.merge(new_parent)

session.commit()


Exception:

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint 
failed: child.parent_pk

[SQL: UPDATE child SET parent_pk=? WHERE child.pk = ?]

[parameters: (None, 7)]

(Background on this error at: http://sqlalche.me/e/gkpj)


Manually setting new_child_1.parent_pk before the merge doesn't do anything 
as the relationship takes precedence. To avoid the exception I need to do 
something like:

new_child_1.parent_pk = PARENT_PK
del new_parent.children
del new_child_1.parent


Is there an easier way to use session.merge for a graph - or a more 
standard method? Or do I always have to do some 'post processing' to strip 
out the relationships before using it?  


-- 
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 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.