date:20170523

Re: [sqlalchemy] many-to-many relationship : how to update items properly if there are duplicates entries ?

2017-05-23 Thread yoch . melka

Thank you Mike for this detailled response !

The UniqueObject recipe is very interesting, but not very suitable to my 
case because my webservice don't use the same session on each Post 
insertion.

Maybe a session.merge_all() method can help to improve performance in such 
cases by grouping the underlying SQL queries (only one SELECT and one 
INSERT) for all instances.


Le mardi 23 mai 2017 19:48:11 UTC+3, Mike Bayer a écrit :
>
> we don't have ON DUPLICATE KEY UPDATE for MySQL as of yet however there 
> is a PR that I will attend to at some point for possible 1.2 inclusion. 
>
> However, note you can just as well just emit the SQL string for ON 
> DUPLCIATE KEY UPDATE if you're only targeting MySQL.   Then just create 
> the object you need that corresponds to this row, then use 
> make_transient_to_detached() -> session.add() to make it happen (see 
> example below). 
>
> Theoretically, the functionality of session.merge() could provide an 
> alternate form that makes use of ON DUPLICATE KEY UPDATE automatically 
> however for the foreseeable future, you'd need to roll this yourself 
> once you make use of the SQL statement. 
>
> The traditional way to handle this is largely like what you have except 
> that client-side caching is used to make it more efficient.   One 
> example is 
> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/UniqueObject, 
> which caches per single object.   Other ways include just selecting a 
> whole batch of objects based on keys you know you will be working with 
> up front. 
>
>
> from sqlalchemy import * 
> from sqlalchemy.orm import * 
> from sqlalchemy.ext.declarative import declarative_base 
>
> Base = declarative_base() 
>
> post_tags = Table( 
>  "post_tags", Base.metadata, 
>  Column("postid", ForeignKey('post.id'), primary_key=True), 
>  Column("tagid", ForeignKey('tag.key'), primary_key=True) 
> ) 
>
>
> class Post(Base): 
>  __tablename__ = 'post' 
>
>  id = Column(Integer, primary_key=True) 
>  _tags = relationship('Tag', secondary='post_tags') 
>
>  @property 
>  def tags(self): 
>  return ';'.join(tag.key for tag in self._tags) 
>
>  @tags.setter 
>  def tags(self, s): 
>  lst = [Tag(key=tag) for tag in s.split(';')] 
>  self._tags = lst 
>
>
> class Tag(Base): 
>  __tablename__ = 'tag' 
>
>  key = Column(String(40), primary_key=True) 
>  count = Column(Integer) 
>
> e = create_engine("mysql://scott:tiger@localhost/test", echo=True) 
> Base.metadata.drop_all(e) 
> Base.metadata.create_all(e) 
> s = Session(e) 
>
>
> def make_tag(name): 
>  s.execute( 
>  "INSERT INTO tag (`key`, `count`) VALUES (:name, 1) " 
>  "ON DUPLICATE KEY UPDATE count = count + 1", 
>  {"name": name} 
>  ) 
>  tag_obj = Tag(key=name) 
>  make_transient_to_detached(tag_obj) 
>  return s.merge(tag_obj, load=False) 
>
>
> s.add(Post(_tags=[make_tag("tag_a")])) 
> s.commit() 
>
> s.add(Post(_tags=[make_tag("tag_a")])) 
> s.commit() 
>
> assert s.query(Tag).count() == 1 
>
>
>
> On 05/22/2017 09:59 AM, yoch@gmail.com  wrote: 
> > Hi, 
> > 
> > I'm trying to create a tag system with a many-to-many relationship 
> > approach, and I have problems with the updating phase. 
> > 
> > | 
> > classPost(Base): 
> >  __tablename__ ='post' 
> > 
> >  id =Column(Integer,primary_key=True) 
> >  _tags =relationship('Tag',secondary='post_tags') 
> > 
> > @property 
> > deftags(self): 
> > return';'.join(tag.key fortag inself._tags) 
> > 
> > @tags.setter 
> > deftags(self,s): 
> >  lst =[Tag(key=tag)fortag ins.split(';')] 
> > self._tags =lst 
> > 
> > classTag(Base): 
> >  __tablename__ ='tag' 
> > 
> >  key =Column(String(40),primary_key=True) 
> >  count =Column(Integer) 
> > | 
> > 
> > 
> > And the `post_tags` is defined as : 
> > 
> > | 
> > CREATE TABLE `post_tags`( 
> > `idpost`INT(10)UNSIGNED NOT NULL, 
> > `tag`VARCHAR(40)NOT NULL, 
> >   PRIMARY KEY (`idpost`,`tag`), 
> >   INDEX `FK_post_tags`(`tag`), 
> >   FOREIGN KEY (`tag`)REFERENCES `tag`(`key`), 
> >   FOREIGN KEY (`idpost`)REFERENCES `post`(`id`)ON DELETE CASCADE 
> > ); 
> > | 
> > 
> > 
> > The problem is that when adding tags that already exists in the `tag` 
> table 
> > 
> > | 
> > post.tags ='a'# tag 'a' already created 
> > | 
> > 
> > it produces this exception : sqlalchemy.orm.exc.FlushError: New instance 
> >  with identity key (, 
> > ('a',)) conflicts with persistent instance  
> > 
> > 
> > Ideally, I'd like to be able to produce a query like (MySQL) : 
> > 
> > | 
> > INSERT INTO tag (`key`,`count`)VALUES (%s,1)ON DUPLICATE KEY UPDATE 
> > count =count +1 
> > | 
> > 
> > Instead that, the way I found to do seems much less efficient : 
> > 
> > | 
> > @tags.setter 
> > deftags(self,s): 
> >  sess =object_session(self) 
> >  lst =[sess.merge(Tag(key=tag))fortag ins.split(';')] 
> > self._tags =lst 
> > | 
> > 
> > (and the counter is managed by a

Re: [sqlalchemy] many-to-many relationship : how to update items properly if there are duplicates entries ?

2017-05-23 Thread mike bayer

we don't have ON DUPLICATE KEY UPDATE for MySQL as of yet however there 
is a PR that I will attend to at some point for possible 1.2 inclusion.


However, note you can just as well just emit the SQL string for ON 
DUPLCIATE KEY UPDATE if you're only targeting MySQL.   Then just create 
the object you need that corresponds to this row, then use 
make_transient_to_detached() -> session.add() to make it happen (see 
example below).


Theoretically, the functionality of session.merge() could provide an 
alternate form that makes use of ON DUPLICATE KEY UPDATE automatically 
however for the foreseeable future, you'd need to roll this yourself 
once you make use of the SQL statement.


The traditional way to handle this is largely like what you have except 
that client-side caching is used to make it more efficient.   One 
example is 
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/UniqueObject, 
which caches per single object.   Other ways include just selecting a 
whole batch of objects based on keys you know you will be working with 
up front.



from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

post_tags = Table(
"post_tags", Base.metadata,
Column("postid", ForeignKey('post.id'), primary_key=True),
Column("tagid", ForeignKey('tag.key'), primary_key=True)
)


class Post(Base):
__tablename__ = 'post'

id = Column(Integer, primary_key=True)
_tags = relationship('Tag', secondary='post_tags')

@property
def tags(self):
return ';'.join(tag.key for tag in self._tags)

@tags.setter
def tags(self, s):
lst = [Tag(key=tag) for tag in s.split(';')]
self._tags = lst


class Tag(Base):
__tablename__ = 'tag'

key = Column(String(40), primary_key=True)
count = Column(Integer)

e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)


def make_tag(name):
s.execute(
"INSERT INTO tag (`key`, `count`) VALUES (:name, 1) "
"ON DUPLICATE KEY UPDATE count = count + 1",
{"name": name}
)
tag_obj = Tag(key=name)
make_transient_to_detached(tag_obj)
return s.merge(tag_obj, load=False)


s.add(Post(_tags=[make_tag("tag_a")]))
s.commit()

s.add(Post(_tags=[make_tag("tag_a")]))
s.commit()

assert s.query(Tag).count() == 1



On 05/22/2017 09:59 AM, yoch.me...@gmail.com wrote:

Hi,

I'm trying to create a tag system with a many-to-many relationship 
approach, and I have problems with the updating phase.


|
classPost(Base):
 __tablename__ ='post'

 id =Column(Integer,primary_key=True)
 _tags =relationship('Tag',secondary='post_tags')

@property
deftags(self):
return';'.join(tag.key fortag inself._tags)

@tags.setter
deftags(self,s):
 lst =[Tag(key=tag)fortag ins.split(';')]
self._tags =lst

classTag(Base):
 __tablename__ ='tag'

 key =Column(String(40),primary_key=True)
 count =Column(Integer)
|


And the `post_tags` is defined as :

|
CREATE TABLE `post_tags`(
`idpost`INT(10)UNSIGNED NOT NULL,
`tag`VARCHAR(40)NOT NULL,
  PRIMARY KEY (`idpost`,`tag`),
  INDEX `FK_post_tags`(`tag`),
  FOREIGN KEY (`tag`)REFERENCES `tag`(`key`),
  FOREIGN KEY (`idpost`)REFERENCES `post`(`id`)ON DELETE CASCADE
);
|


The problem is that when adding tags that already exists in the `tag` table

|
post.tags ='a'# tag 'a' already created
|

it produces this exception : sqlalchemy.orm.exc.FlushError: New instance 
 with identity key (, 
('a',)) conflicts with persistent instance 



Ideally, I'd like to be able to produce a query like (MySQL) :

|
INSERT INTO tag (`key`,`count`)VALUES (%s,1)ON DUPLICATE KEY UPDATE 
count =count +1

|

Instead that, the way I found to do seems much less efficient :

|
@tags.setter
deftags(self,s):
 sess =object_session(self)
 lst =[sess.merge(Tag(key=tag))fortag ins.split(';')]
self._tags =lst
|

(and the counter is managed by a trigger on `post_tags` INSERT)

Is there another way to do that properly and efficiently ?

--
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 - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an

Re: [sqlalchemy] When is session.close required?

2017-05-23 Thread mike bayer




On 05/23/2017 09:55 AM, 'Brian Candler' via sqlalchemy wrote:

Hello,

I have a question about when "close" should or should not be called on a 
session.


At http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html it 
says:


"When the transactional state is completed after a rollback or commit, 
the Session releases all Transaction and Connection resources, and goes 
back to the “begin” state, which will again invoke new Connection and 
Transaction objects as new requests to emit SQL statements are received."


 From this description, given that the session releases its resources, 
it seems that there's no need to call "close" explicitly on the session, 
whether or not you want to re-use the session object or not.  There is 
also some example code, which doesn't invoke session.close().


session  =  Session()
try:
 ...
 # commit. The pending changes above
 # are flushed via flush(), the Transaction
 # is committed, the Connection object closed
 # and discarded, the underlying DBAPI connection
 # returned to the connection pool.
 session.commit()
except:
 # on rollback, the same closure of state
 # as that of commit proceeds.
 session.rollback()
 raise



fixed in 7fc7492d86f6e5ca105743a184cd07190e9f9b28  / 
f830fff2583b3e23f72fdc3768be22f64e2212d1


https://bitbucket.org/zzzeek/sqlalchemy/commits/7fc7492d86f6




However, under 
http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it 
there's a different example, this time explicitly calling close() on the 
session:


def  run_my_program():
 session  =  Session()
 try:
 ThingOne().go(session)
 ThingTwo().go(session)

 session.commit()
 except:
 session.rollback()
 raise
 finally:
 session.close()


So my question is, what does session.close() do that commit/rollback 
does not?


close() will:

1. expunge all objects currently still associated with that Session to 
no longer be associated with it


2. safely dispose of any remaining SessionTransaction objects associated 
with the Session.


We had a user who required #2 because they built an "after_commit()" 
handler that threw errors and would cause the state of the 
SessionTransaction to be invalid 
(https://bitbucket.org/zzzeek/sqlalchemy/issues/3974/inconsistent-session-state-after-raising).


Therefore, if you're building production code and at the end of your 
transaction block, you absolutely want everything to start fresh 
regardless of how badly things blew up the last time, call session.close().







It's also not entirely clear to me if a session object can be reused 
after it has been closed. 
  At http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.close it says:


"If this session were created with autocommit=False, a new transaction 
is immediately begun. Note that this new transaction does not use any 
connection resources until they are first needed."


So it sounds to me like a closed session *can* be re-used.  Is that correct?

Related to this is calling scoped_session.remove. 
  At http://docs.sqlalchemy.org/en/latest/orm/contextual.html#unitofwork-contextual it says:


 web  request  ends   ->  # the registry is instructed to
  # remove the Session
  Session.remove()


As I understand it, this calls "close" on the underlying session *and* 
removes it from the registry, so you get a fresh Session next time. 
  However if the framework already does a commit/rollback, why not just 
allow the registry to retain the same session object?


Thanks,

Brian.

--
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 - 
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] When is session.close required?

2017-05-23 Thread 'Brian Candler' via sqlalchemy

Hello,

I have a question about when "close" should or should not be called on a 
session.

At http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html it 
says:

"When the transactional state is completed after a rollback or commit, the 
Session releases all Transaction and Connection resources, and goes back to 
the “begin” state, which will again invoke new Connection and Transaction 
objects as new requests to emit SQL statements are received."

>From this description, given that the session releases its resources, it 
seems that there's no need to call "close" explicitly on the session, 
whether or not you want to re-use the session object or not.  There is also 
some example code, which doesn't invoke session.close().  

session = Session()try:
...
# commit.  The pending changes above
# are flushed via flush(), the Transaction
# is committed, the Connection object closed
# and discarded, the underlying DBAPI connection
# returned to the connection pool.
session.commit()except:
# on rollback, the same closure of state
# as that of commit proceeds.
session.rollback()
raise


However, under 
http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it
 
there's a different example, this time explicitly calling close() on the 
session:

def run_my_program():
session = Session()
try:
ThingOne().go(session)
ThingTwo().go(session)

session.commit()
except:
session.rollback()
raise
finally:
session.close()


So my question is, what does session.close() do that commit/rollback does 
not?

It's also not entirely clear to me if a session object can be reused after 
it has been closed. 
 At 
http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.close
 
it says:

"If this session were created with autocommit=False, a new transaction is 
immediately begun. Note that this new transaction does not use any 
connection resources until they are first needed."

So it sounds to me like a closed session *can* be re-used.  Is that correct?

Related to this is calling scoped_session.remove. 
 At 
http://docs.sqlalchemy.org/en/latest/orm/contextual.html#unitofwork-contextual 
it says:

web request ends  -> # the registry is instructed to
 # remove the Session
 Session.remove()


As I understand it, this calls "close" on the underlying session *and* 
removes it from the registry, so you get a fresh Session next time. 
 However if the framework already does a commit/rollback, why not just 
allow the registry to retain the same session object?

Thanks,

Brian.

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

Re: [sqlalchemy] many-to-many relationship : how to update items properly if there are duplicates entries ?

Re: [sqlalchemy] many-to-many relationship : how to update items properly if there are duplicates entries ?

Re: [sqlalchemy] When is session.close required?

[sqlalchemy] When is session.close required?

4 matches

Site Navigation

Mail list logo

Footer information