[sqlalchemy] Automatic unique Many-To-Many Relationships

[sjoerd]

Hi,

Is there a way for SQLAlchemy to silently avoid duplicate entries in a 
Many-To-Many Association Table?
My current set-up;
class UserRole(db.Model):
__tablename__ = 'user_roles'

id = Column(Integer, primary_key=True)
rolename = Column(Unicode(80), unique=True)

UserUserRoleTable = Table('user_user_roles', db.Model.metadata,
Column('user_id', Integer, ForeignKey('users.id')),
Column('user_role_id', Integer, ForeignKey('user_roles.id')),
UniqueConstraint('user_id', 'user_role_id', name='uix_1')
)

class User(db.Model):
__tablename__ = 'users'

id = Column(Integer, primary_key=True)
roles = relationship(UserRole, secondary=lambda: UserUserRoleTable, 
backref=users)

Code::
r1 = UserRole('news_add')
db.session.add(r1)
r3 = UserRole('news_del')
db.session.add(r3)

me = Administrator()
me.fullname = 'Sjoerd Huisman'
me.roles.append(r1)
me.roles.append(r3)
me.roles.append(r1)
db.session.add(me)

Without the UniqueConstraint rows are the r1 is added twice. With the 
UniqueConstraint, there is a big error.

Thanks for any thoughts!

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/mNJa_CN1Id8J.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] zope.sqlalchemy - commit some objects on failure

[Paul Johnston]

Hi,

I'm using zope.sqlalchemy in a web application (actually ToscaWidgets not 
Zope) so each request is wrapped in a transaction. If the request succeeds 
the transaction is committed; if there is an error it is rolled back. This 
works great.

I have a log table where I log incoming XML to web callback methods (from 
eBay, 3DCart, etc.) Now, if there is an error what I want to happen is most 
things to be rolled back, but the log table still committed.

This has left me scratching my head a bit. Any ideas on an elegant way to 
do this?

Many thanks,

Paul


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/ZQATq5gFcgQJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Automatic unique Many-To-Many Relationships

[Michael Bayer]

On Jan 6, 2013, at 6:48 AM, sjo...@congressus.nl wrote:

 Hi,
 
 Is there a way for SQLAlchemy to silently avoid duplicate entries in a 
 Many-To-Many Association Table?
 My current set-up;
 class UserRole(db.Model):
 __tablename__ = 'user_roles'
 
 id = Column(Integer, primary_key=True)
 rolename = Column(Unicode(80), unique=True)
 
 UserUserRoleTable = Table('user_user_roles', db.Model.metadata,
 Column('user_id', Integer, ForeignKey('users.id')),
 Column('user_role_id', Integer, ForeignKey('user_roles.id')),
 UniqueConstraint('user_id', 'user_role_id', name='uix_1')
 )
 
 class User(db.Model):
 __tablename__ = 'users'
 
 id = Column(Integer, primary_key=True)
 roles = relationship(UserRole, secondary=lambda: UserUserRoleTable, 
 backref=users)
 

pretty simple just use a set() for the collection:

relationship(UserRole, secondary=..., collection_class=set)

http://docs.sqlalchemy.org/en/rel_0_8/orm/collections.html?highlight=collection_class#customizing-collection-access




 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To view this discussion on the web visit 
 https://groups.google.com/d/msg/sqlalchemy/-/mNJa_CN1Id8J.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] zope.sqlalchemy - commit some objects on failure

[Michael Bayer]

On Jan 6, 2013, at 12:10 PM, Paul Johnston wrote:

 Hi,
 
 I'm using zope.sqlalchemy in a web application (actually ToscaWidgets not 
 Zope) so each request is wrapped in a transaction. If the request succeeds 
 the transaction is committed; if there is an error it is rolled back. This 
 works great.
 
 I have a log table where I log incoming XML to web callback methods (from 
 eBay, 3DCart, etc.) Now, if there is an error what I want to happen is most 
 things to be rolled back, but the log table still committed.
 
 This has left me scratching my head a bit. Any ideas on an elegant way to do 
 this?

for this case you use a different Session, which you establish within the scope 
of the other one, add the state you want to it, and commit() it distinctly.   
Based on my limited experience with zope.transaction, this Session wouldn't 
even be using the zope.transaction extension so that it's own commit() method 
works normally.

the net effect of this is that your log operations occur on a different 
connection.   I use this pattern for log tables extensively.



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] custom __init__ methods not being invoked

[RM]

I have a class which inherits from Base. My class has a metaclass which 
inherits from DeclarativeMeta. Among other things, the metaclass adds an 
__init__ method to the class dictionary. When I instantiate an instance of 
my class directly, my __init__ method is invoked, but if I use the ORM to 
retrieve an instance, my __init__ method is not invoked. 

A metaclass serves better than a mixin for what I am trying to accomplish. 
However, I did experiment with a mixin and saw the same behavior as 
described above.

Any ideas? Many thanks.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/oDj_bHNvP7EJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] How to handle 'sub-commits'?

[Ken Lareau]

Michael,

Thanks for the response, see further questions/issues below...

On Fri, Jan 4, 2013 at 8:41 AM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Jan 3, 2013, at 10:18 PM, Ken Lareau wrote:

 I recently (today) ran into an issue that has me perplexed as to how to
 resolve it,
 so I'm asking here to see if anyone can shed some insight.  Hopefully I
 can ex-
 plain it clearly enough to make me not sound completely incompetent...

 I currently have an application that during it's run starts a session via
 SQLAlchemy
 to one of our databases and keeps it available until the program exits.
 During this
 time it does multiple changes (primarily inserts and updates) to the
 database, but
 of course nothing is actually written to the database until a commit() is
 done.  The
 problem is that there are times when I have a single change that must be
 available
 in the database immediately due to external resources needing to access to
 that
 updated/new information.



 for this use case you use a distinct transaction, which means a different
 Session object.  You commit() that Session when you need this short-term
 data to be exposed.


Okay, this is what I suspected and feared. :)  Creating new sessions isn't
much of an
issue, and I came up with a class to manage this for me before realizing my
problem
is going to end up being much deeper...

My current library that uses SQLAlchemy was based off a single session...
so in a
file called meta.py I had the following line:

Session = scoped_session(sessionmaker())

(swiped from an old Pylons example).  In my __init__.py file, I did:

from tagopsdb.database.meta import Session

and then in when initializing my connection to the database, simply did:

Session.configure(bind=engine)

From all other areas of the library, I simple re-used the aforementioned
import, then
actually directly used Session, such as:

Session.add(obj)

or

Session.commit()

Now... this may be very poor usage of it, though I'm trying to improve my
under-
standing and utilize SQLAlchemy better.  Of course, with the sudden need
for more
than one session, I'm finding that I'm running into an issue.  Specifically:

1) To make it easy to find the correct session, I'm using a dictionary
which I
pass around instead of Session... but referencing a given session is a
bit
clunky - 'sessions.current[name]' is quite a bit less succinct than
the
use of just 'Session', and while I could just assign a given entry to a
shorter
name, that just seems to add to the mistake. :)

2) All the methods in my library currently expect the session to be
'Session';
that changes with the need for multiple sessions, and it means either I
will now need to explicitly pass the session into every method, or find
a
way to have the session automatically determined... which may not be
possible or reasonable.

Given the above, I am open to alternative suggestions, especially if someone
has solved this problem already. :)  It looks like I will need the multiple
session
solution sooner rather than later, which means I have to find some way to
solve
this quandary of mine.


 So this leads to the question: is there any way to do an 'isolated' commit
 from
 within a session and if so, how is it done?  As an alternative, is there a
 way to
 use temporary new sessions to accomplish the same thing?  My current use
 in my application is I have a 'Session = scoped_session(sessionmaker())'
 line
 in a module which I import wherever I need it (essentially as a singleton)
 to be
 able to access the same session throughout the code.  This would of course
 need to change, at least with an application requiring such 'sub commits'.


 Depending on the database in use, using low isolation levels can have the
 effect that other transactions can view dirty reads as the transaction
 proceeds, but this is obviously an all-or-nothing thing.When I need
 certain resources exposed during a long running transaction, I transfer
 that data to a different Session and commit() those changes distinctly.

 My current needs would tend to use the short transactions for things that
are mostly
isolated from anything going on in the longer running (main) transaction,
though I do
suspect I might need what you mention in your last sentence, but might you
be able
to refer me to an example of how it would work, perchance?

Once again, thanks for help.

- Ken



  --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.




-- 
- Ken Lareau

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group,