[sqlalchemy] Do passive deletes apply to many to many relationships?

[Randy Syring]

I am trying to get SQLAlchemy to let my database's foreign keys "on delete 
cascade" do the cleanup on the association table between two objects. I 
have setup the cascade and passive_delete options on the relationship as 
seems appropriate from the docs. However, when a related object is loaded 
into the collection of a primary object and the primary object is deleted 
from the session, then SQLAlchemy issues a delete statement for the related 
object.

I have a code example to reproduce the problem at stackoverflow: 
http://stackoverflow.com/questions/23669198/passive-deletes-in-sqlalchemy-with-a-many-to-many-relationship-dont-prevent-del

Is there a way to configure SQLAlchemy to never emit a DELETE for a related 
object even when that object is loaded in a collection of an entity that is 
deleted from the session?

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] common table expressions for INSERT, UPDATE, and DELETE

[Michael Bayer]

On May 14, 2014, at 6:35 PM, Hunter Blanks  wrote:

> Hi,
> 
> Although SQLAlchemy supports PostgreSQL's common table expressions (i.e. WITH 
> statements*)  for SELECT, it does not yet seem to support them for INSERT, 
> UPDATE, or DELETE.
> 
> (1) Can anyone confirm that this is the case? Lest it help, I've attached 
> test cases for UPDATE and DELETE below.
> 
> (2) If this is the case, can anyone offer a guess for what it would involve 
> for me to add these features?

this is the case:

https://bitbucket.org/zzzeek/sqlalchemy/issue/2551/apparently-inserts-and-update-delete-can

it's doable, but not a particularly trivial add and the demand for this feature 
so far has been low.if you wanted to take a crack at it, it involves 
expanding the current SELECT part of the system appropriately and adding lots 
of tests to the test_cte suite.


-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How do you limit/specify the columns that are loaded via a relationship()?

[Seth]

Ok, thanks so much for your help.


On Wednesday, May 14, 2014 2:35:27 PM UTC-7, Michael Bayer wrote:
>
>
>
> there are deferred() columns that you can set on a mapper but there’s not 
> a mechanism right now to set deferred target attributes when the object is 
> loaded only from a specific relationship without the specific call within 
> the query().So unless you maybe ran those options into every Query 
> unconditionally, or something.  I’d look to see how big a deal it is to 
> just be explicit on this one.
>
>
>
> On May 14, 2014, at 1:39 PM, Seth > 
> wrote:
>
> Thanks Mike,
>
> But...is there no way to set this behavior directly on the 
> "relationship()"? The whole point there is to be "lazy" ;)
>
> Seth
>
>
>
> On Saturday, May 10, 2014 7:59:33 PM UTC-7, Michael Bayer wrote:
>>
>>
>> session.query(Parent).options(defaultload(“children”).load_only(“cheap_column"))
>>
>> or really if you want to cut out “expensive_column”
>>
>>
>> session.query(Parent).options(defaultload(“children”).defer(“expensive_column”))
>>
>> http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred
>>
>>
>>
>> On May 10, 2014, at 6:39 PM, Seth  wrote:
>>
>> Is there any way to get SQLAlchemy to only load specific columns through 
>> a relationship()?
>>
>> For example, with this scenario:
>>
>> class Parent(Base):
>> __tablename__ = 'parent'
>> id = Column(Integer, primary_key=True)
>> children = relationship("Child", lazy='joined')
>>
>> class Child(Base):
>> __tablename__ = 'child'
>> id = Column(Integer, primary_key=True)
>> parent_id = Column(Integer, ForeignKey('parent.id'))
>> cheap_column = Column(Unicode(10))
>> expensive_column = Column(LargeBinary)
>>
>> I'd like Child to get lazily joined when Parent is loaded, but only with 
>> Child's "cheap_column" and not it's "expensive_column".
>>
>> Thanks,
>> Seth
>>
>>
>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an 
>> email to sqlalchemy+...@googlegroups.com.
>> To post to this group, send email to sqlal...@googlegroups.com.
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/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 tosqlalchemy+...@googlegroups.com .
> To post to this group, send email to sqlal...@googlegroups.com
> .
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] common table expressions for INSERT, UPDATE, and DELETE

[Hunter Blanks]

Hi,

Although SQLAlchemy supports PostgreSQL's common table expressions (i.e. 
WITH statements*)  for SELECT, it does not yet seem to support them for 
INSERT, UPDATE, or DELETE.

(1) Can anyone confirm that this is the case? Lest it help, I've attached 
test cases for UPDATE and DELETE below.

(2) If this is the case, can anyone offer a guess for what it would involve 
for me to add these features?

I know that in most cases, people are able to work around this by passing 
around aliased select() clauses – but it sure would be nice to have the use 
of CTE's, too. When viewing the SQL, they do make for better legibility, 
and they can also help with query optimization.

Thanks much,

Hunter Blanks

* Syntax can be found in PostgreSQL's documentation for 
INSERT
, UPDATE , and 
DELETE .


"""
(Failing) test cases for UPDATE and DELETE statements with PostgreSQL's
common table expressions.
"""

import sqlalchemy
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import select
from sqlalchemy.sql.expression import exists


SRC_TABLE_ROWS = [
{'id': i, 'name': name}
for i, name in enumerate(
['zero', 'one', 'two', 'three', 'four', 'five']
)
]

DST_TABLE_ROWS = [{'id': i, 'name': 'x'} for i in range(6)]


def create_tmp_tables(connection):
""" Creates temporary tables for the test case. """
metadata = sqlalchemy.MetaData()

tmp_src_table = sqlalchemy.Table(
'tmp_src_table', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(255), nullable=False),
prefixes=['TEMPORARY']
)

tmp_dst_table = sqlalchemy.Table(
'tmp_dst_table', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(255), nullable=False),
prefixes=['TEMPORARY']
)

metadata.create_all(connection)

connection.execute(tmp_src_table.insert(), SRC_TABLE_ROWS)
connection.execute(tmp_dst_table.insert(), DST_TABLE_ROWS)

return tmp_src_table, tmp_dst_table



def test_cte_update(connection):
"""
Tests an UPDATE statement using a CTE.

Args:

- connection: SQLAlchemy connection with permissions to create
  temporary tables
"""
src_table, dst_table = create_tmp_tables(connection)

even_rows = src_table.select().where(
src_table.c.id % 2 == 0
).cte('even_rows')

# Verify working CTE
result = connection.execute(even_rows.select())

update = dst_table.update().values(
name=even_rows.c.name
).where(
dst_table.c.id == even_rows.c.id
)

connection.execute(update)


def test_cte_delete(connection):
"""
Tests an DELETE statement using a CTE.

Args:

- connection: SQLAlchemy connection with permissions to create
  temporary tables
"""
src_table, dst_table = create_tmp_tables(connection)

even_rows = src_table.select().where(
src_table.c.id % 2 == 0
).cte('even_rows')

# Verify working CTE
result = connection.execute(even_rows.select())

delete_even_rows = dst_table.delete().where(
exists().where(
dst_table.c.id == even_rows.c.id
)
)

connection.execute(delete_even_rows)

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How do you limit/specify the columns that are loaded via a relationship()?

[Michael Bayer]

there are deferred() columns that you can set on a mapper but there's not a 
mechanism right now to set deferred target attributes when the object is loaded 
only from a specific relationship without the specific call within the query(). 
   So unless you maybe ran those options into every Query unconditionally, or 
something.  I'd look to see how big a deal it is to just be explicit on this 
one.



On May 14, 2014, at 1:39 PM, Seth  wrote:

> Thanks Mike,
> 
> But...is there no way to set this behavior directly on the "relationship()"? 
> The whole point there is to be "lazy" ;)
> 
> Seth
> 
> 
> 
> On Saturday, May 10, 2014 7:59:33 PM UTC-7, Michael Bayer wrote:
> session.query(Parent).options(defaultload("children").load_only("cheap_column"))
> 
> or really if you want to cut out "expensive_column"
> 
> session.query(Parent).options(defaultload("children").defer("expensive_column"))
> 
> http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred
> 
> 
> 
> On May 10, 2014, at 6:39 PM, Seth  wrote:
> 
>> Is there any way to get SQLAlchemy to only load specific columns through a 
>> relationship()?
>> 
>> For example, with this scenario:
>> 
>> class Parent(Base):
>> __tablename__ = 'parent'
>> id = Column(Integer, primary_key=True)
>> children = relationship("Child", lazy='joined')
>> 
>> class Child(Base):
>> __tablename__ = 'child'
>> id = Column(Integer, primary_key=True)
>> parent_id = Column(Integer, ForeignKey('parent.id'))
>> cheap_column = Column(Unicode(10))
>> expensive_column = Column(LargeBinary)
>> 
>> I'd like Child to get lazily joined when Parent is loaded, but only with 
>> Child's "cheap_column" and not it's "expensive_column".
>> 
>> Thanks,
>> Seth
>> 
>> 
>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an 
>> email to sqlalchemy+...@googlegroups.com.
>> To post to this group, send email to sqlal...@googlegroups.com.
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/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 tosqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How do you limit/specify the columns that are loaded via a relationship()?

[Seth]

Thanks Mike,

But...is there no way to set this behavior directly on the 
"relationship()"? The whole point there is to be "lazy" ;)

Seth



On Saturday, May 10, 2014 7:59:33 PM UTC-7, Michael Bayer wrote:
>
>
> session.query(Parent).options(defaultload(“children”).load_only(“cheap_column"))
>
> or really if you want to cut out “expensive_column”
>
>
> session.query(Parent).options(defaultload(“children”).defer(“expensive_column”))
>
> http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred
>
>
>
> On May 10, 2014, at 6:39 PM, Seth > 
> wrote:
>
> Is there any way to get SQLAlchemy to only load specific columns through a 
> relationship()?
>
> For example, with this scenario:
>
> class Parent(Base):
> __tablename__ = 'parent'
> id = Column(Integer, primary_key=True)
> children = relationship("Child", lazy='joined')
>
> class Child(Base):
> __tablename__ = 'child'
> id = Column(Integer, primary_key=True)
> parent_id = Column(Integer, ForeignKey('parent.id'))
> cheap_column = Column(Unicode(10))
> expensive_column = Column(LargeBinary)
>
> I'd like Child to get lazily joined when Parent is loaded, but only with 
> Child's "cheap_column" and not it's "expensive_column".
>
> Thanks,
> Seth
>
>
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com .
> To post to this group, send email to sqlal...@googlegroups.com
> .
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Concurrent raw sql execution in a multi-threaded application: Connection is busy with results for another hstmt error

[Sylvester Steele]

Correct, that is what I did. Except, I do a conn.close() at the end rather
than a del.


On Tue, May 13, 2014 at 4:10 PM, Michael Bayer wrote:

>
> On May 13, 2014, at 2:23 PM, Sylvester Steele 
> wrote:
>
>
> ODBC connection pooling setting did not matter. After the above change,
> code is running in both cases (ODBC connection pooling on or pooling off).
>
> Let me know if this is an issue and you need more info.
>
>
> OK so I’m guessing you did something like this:
>
> conn = engine.connect()
> raw_connection = conn.connection.connection
>
> del conn   # conn goes out of scope
>
>
>
> so yeah, that will break like that.   What you can do is stick with
> “conn.connection” at least, that’s the so-called “Connection Fairy” which
> will keep the DBAPI connection checked out until it goes out of scope.
>
>
>
>  --
> 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/hRyAeD0xWv0/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 http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Create database with sqlalchemy > 0.8.0 and postgres

[Tony Locke]

I've had a look at this and submitted a patch that should make autocommit 
work for the pg8000 Sqlalchemy dialect in the same way as it does for the 
psycopg2 dialect https://github.com/zzzeek/sqlalchemy/pull/88.

On Friday, 9 May 2014 17:50:43 UTC+1, Tony Locke wrote:
>
> Hi, the pg8000 driver has an autocommit mode, activated using the boolean 
> 'autocommit' attribute of the DBAPI connection. For example:
>
> import pg8000conn = pg8000.connect(user="postgres", password="C.P.Snow")
> conn.autocommit = Truecur = conn.cursor()cur.execute("CREATE DATABASE 
> qux")conn.autocommit = Falsecursor.close()conn.close()
>
> I'm not sure if the SQLAlchemy driver for pg8000 supports using:
>
> conn.execution_options(“AUTOCOMMIT”)
>
> I'll investigate...
>
> Cheers,
>
> Tony.
>
>
>
> On Wednesday, 7 May 2014 01:08:00 UTC+1, Michael Bayer wrote:
>>
>>
>> On May 6, 2014, at 6:09 PM, Michael Costello  
>> wrote:
>>
>> Hello.
>>
>> Setup:
>>   python 2.7.6
>>   postgres 9.3.4
>>   sqlalchemy 0.9.4 (also, 0.8.4)
>>   pg8000 1.9.8
>>
>> I am attempting to create a database using sqlalchemy with the above 
>> tools and the following code:
>>
>> from sqlalchemy import create_engine
>>
>> dburl = "postgresql+pg8000://user:pas...@db.foo.com:5432/postgres"
>>
>> engine = create_engine(dburl)
>>
>> conn = engine.connect()
>> conn.execute("COMMIT")
>> conn.execute("CREATE DATABASE qux")
>> conn.close()
>>
>> but I receive the following error:
>>
>> sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('ERROR', '25001', 
>> 'CREATE DATABASE cannot run inside a transaction block') 'CREATE DATABASE 
>> qux' ()
>>
>> However, running the same code against the same database but using 
>> sqlalchemy version 0.8.0 works.
>>
>> Is there something I can do to get 0.9.4 to work for me?
>>
>>
>>
>> I can’t imagine why that would work differently on 0.8.0 because the 
>> transactional behavior is the same on the SQLAlchemy side.  
>>
>> Running this test with the latest pg8000 1.9.8:
>>
>> from sqlalchemy import create_engine
>>
>> e = create_engine("postgresql+pg8000://scott:tiger@localhost/test", 
>> echo=True)
>> conn = e.connect()
>> conn.execute("COMMIT")
>> conn.execute("create database foo")
>>
>> output on 0.9.4:
>>
>> sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('ERROR', '25001', 
>> 'CREATE DATABASE cannot run inside a transaction block') 'create database 
>> foo' ()
>>
>> output on 0.8.0:
>>
>> sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('ERROR', '25001', 
>> 'CREATE DATABASE cannot run inside a transaction block') 'create database 
>> foo' ()
>>
>> output on 0.8.4:
>>
>> sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('ERROR', '25001', 
>> 'CREATE DATABASE cannot run inside a transaction block') 'create database 
>> foo' ()
>>
>>
>> etc.
>>
>> so i think perhaps your pg8000 version has changed.
>>
>> To achieve this you should use psycopg2 and use psycopg2’s “autocommit” 
>> mode.  See 
>> http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#postgresql-isolation-leveland
>>  
>> http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#psycopg2-transaction-isolation-level;
>>  
>> with psycopg2 you can use 
>> conn.execution_options(“AUTOCOMMIT”).execute(“CREATE DATABASE qux”).
>>
>>
>>
>>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Advice for Method to consistently import XML into SQLAlchemy

[Sayth Renshaw]

Hi

Looking for some guidance and advice on using xml as an update source for 
my to be data web project. If I am consistently going to be updating data 
into the database from XML files what is a good method?


Should I be creating a Sax parser as in this example 
http://iamtgc.com/importing-xml-into-a-database-with-python-and-sqlalchemy/ 
. 
To be clear the XML I am importing from is not basic, using from that 
example my xml would like more like. So i would need to filter the values 
out that I want which I have acheived using xmltodict.


  
The Consumer
M. Gira
  
  
The Wind-Up Bird Chronicle
Haruki Murakami
  
  

Copies are available here 
http://old.racingnsw.com.au/Site/_content/racebooks/20140515GOSF0.xml













Or should I be attempting to filter and convert the XML to json format and 
import into SQLAlchemy.

Or other, searching around I cannot, strangely find one and only one way to 
do it. It seems to be a mish mosh of good luck, well from perspective of 
someone looking to implement this for the first time. Probably painfully 
obvious to those who have done it before.

Anyway thank you for your time.

Sayth

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.