[sqlalchemy] Re: howto Sqlalchemy atomic transaction ??
Model
class VDCTemplates(DeclarativeBase):
__tablename__='cd_vdc_templates'
id = Column(Unicode(50), primary_key=True)
vdc_id=Column(Unicode(50), ForeignKey('cd_vdc.id',
ondelete=CASCADE))
template_id=Column(Unicode(50),
ForeignKey('cd_account_templates.id', ondelete=CASCADE))
account_id=Column(Unicode(50), ForeignKey('cd_accounts.id',
ondelete=CASCADE))
code
==
acc_template=self.create_template_data()
DBSession.add(acc_template)
vdc_template = VDCTemplates()
vdc_template.account_id = account_id
vdc_template.vdc_id = vdc_id
vdc_template.template_id = acc_template.id
DBSession.add(vdc_template)
* Getting IntegrityError error
(IntegrityError) (1452, 'Cannot add or update a child row: a foreign
key constraint fails (`my_cms_ee/cd_vdc_templates`, CONSTRAINT
`cd_vdc_templates_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES
`cd_account_templates` (`id`) ON DELETE CASCADE)') u'INSERT INTO
cd_vdc_templates (id, vdc_id, template_id, account_id) VALUES (%s, %s,
%s, %s)' ['6ae8e571-ebce-6977-150d-09f7127acb5b', 'ab977753-
de54-6e3b-3d7c-644d12f66d63', '8e0ce099-4ba7-2e01-304c-edc18803fbce',
'b56bc9e3-288b-a98f-a974-b67f90970122']
* Its working, if i put transaction.commit() after
DBSession.add(acc_template), but that not atomic.
* Have any way to make it atomic ???
--
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] Re: howto Sqlalchemy atomic transaction ??
DBSession.flush()
after the DBSession.add call might be enough.
On Mon, Nov 21, 2011 at 12:48 PM, sajuptpm sajup...@gmail.com wrote:
Model
class VDCTemplates(DeclarativeBase):
__tablename__='cd_vdc_templates'
id = Column(Unicode(50), primary_key=True)
vdc_id=Column(Unicode(50), ForeignKey('cd_vdc.id',
ondelete=CASCADE))
template_id=Column(Unicode(50),
ForeignKey('cd_account_templates.id', ondelete=CASCADE))
account_id=Column(Unicode(50), ForeignKey('cd_accounts.id',
ondelete=CASCADE))
code
==
acc_template=self.create_template_data()
DBSession.add(acc_template)
vdc_template = VDCTemplates()
vdc_template.account_id = account_id
vdc_template.vdc_id = vdc_id
vdc_template.template_id = acc_template.id
DBSession.add(vdc_template)
* Getting IntegrityError error
(IntegrityError) (1452, 'Cannot add or update a child row: a foreign
key constraint fails (`my_cms_ee/cd_vdc_templates`, CONSTRAINT
`cd_vdc_templates_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES
`cd_account_templates` (`id`) ON DELETE CASCADE)') u'INSERT INTO
cd_vdc_templates (id, vdc_id, template_id, account_id) VALUES (%s, %s,
%s, %s)' ['6ae8e571-ebce-6977-150d-09f7127acb5b', 'ab977753-
de54-6e3b-3d7c-644d12f66d63', '8e0ce099-4ba7-2e01-304c-edc18803fbce',
'b56bc9e3-288b-a98f-a974-b67f90970122']
* Its working, if i put transaction.commit() after
DBSession.add(acc_template), but that not atomic.
* Have any way to make it atomic ???
--
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.
--
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] is there a problem in $ and % in a query using engine.execute?
Hello all. I am trying to write some code which after creating tables and related views trys to create the stored procedures (plpgsql). code goes some thing like this engine.execute(create or replace function addRecord(f1 text, f2 t1.fieldname%type ) returns bit as $$ ... begin ... end; $$ language plpgsql) When this gets executed I get the error that indicates dict object is not indexable. Is this some thing to do wiht the use of % or $ sign in the query? Note that although I wrote the code on more than one line, in my code its a single line statement. All my views get created with same syntax of engine.execute() but not stored procedure creation code. Any suggestion? Happy hacking. Krishnakant. -- 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] is there a problem in $ and % in a query using engine.execute?
try escaping the % sign as in %%. this is psycopg2's behavior. On Nov 21, 2011, at 1:27 PM, Krishnakant Mane wrote: Hello all. I am trying to write some code which after creating tables and related views trys to create the stored procedures (plpgsql). code goes some thing like this engine.execute(create or replace function addRecord(f1 text, f2 t1.fieldname%type ) returns bit as $$ ... begin ... end; $$ language plpgsql) When this gets executed I get the error that indicates dict object is not indexable. Is this some thing to do wiht the use of % or $ sign in the query? Note that although I wrote the code on more than one line, in my code its a single line statement. All my views get created with same syntax of engine.execute() but not stored procedure creation code. Any suggestion? Happy hacking. Krishnakant. -- 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. -- 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] Get a contains_eager collection to follow order_by
On Nov 21, 2011, at 12:12 AM, Yap Sok Ann wrote:
Is it possible to get a contains_eager collection to follow the
order_by defined in the relationship? It seems like with eager
loading, the order_by defined will just be ignored (which I think make
sense, just wondering if there is a better way than manual sorting).
you can say:
query =
query.options(contains_eager('phones')).order_by(*Contact.phones.property.order_by)
in that regard, you could generalize:
def my_contains_eager(query, prop):
return query.options(contains_eager(prop)).order_by(*prop.property.order_by)
query = my_contains_eager(query, Contact.phones)
Here's some sample code to illustrate:
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import contains_eager, relationship, Session
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, Unicode
Base = declarative_base()
class Contact(Base):
__tablename__ = 'contacts'
id = Column(Integer, primary_key=True)
name = Column(Unicode, nullable=False)
phones = relationship('Phone', back_populates='contact',
order_by='Phone.id')
class Phone(Base):
__tablename__ = 'phones'
id = Column(Integer, primary_key=True)
number = Column(Unicode, nullable=False)
contact_id = Column(Integer, ForeignKey('contacts.id'),
nullable=False)
contact = relationship('Contact', back_populates='phones')
engine = create_engine('sqlite:///:memory:')
engine.echo = True
Base.metadata.create_all(engine)
session = Session(bind=engine)
c = Contact(name=u'Stan Marsh')
c.phones.append(Phone(number=u'999'))
c.phones.append(Phone(number=u'456'))
session.add(c)
session.commit()
session.expire(c)
c = session.query(Contact).one()
# [999, 456]
print [x.number for x in c.phones]
session.expire(c)
query =
session.query(Contact).outerjoin(Contact.phones).order_by(Phone.number)
# If commented, print [999, 456]. Otherwise, print [456, 999]
query = query.options(contains_eager('phones'))
c = query.one()
print [x.number for x in c.phones]
--
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.
--
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] Translating T-SQL UPDATE FROM to SQLAlchemy
I'm working on translating some existing MS-dialect SQL over to SQLAlchemy(0.6.7) and I've run into a statement that's giving me a little trouble: UPDATE version SET doc =document.doc FROM document, version WHERE version.elnumber = document.elnumber AND version.elnumber = ? AND version.version = ? From what I could tell from the SA documentation there is no support for the nonstandard 'from' clause in updates in the expression language and that you should use correlated updates instead. However, the 'doc' column is of type 'image' which is invalid in subqueries according to an error message from my database driver. Is there anything I can do in SA's expression language or will I have to use a hand-written statement in this case? -- 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] Translating T-SQL UPDATE FROM to SQLAlchemy
On Nov 21, 2011, at 2:38 PM, Andrew Buza wrote: I'm working on translating some existing MS-dialect SQL over to SQLAlchemy(0.6.7) and I've run into a statement that's giving me a little trouble: UPDATE version SET doc =document.doc FROM document, version WHERE version.elnumber = document.elnumber AND version.elnumber = ? AND version.version = ? From what I could tell from the SA documentation there is no support for the nonstandard 'from' clause in updates in the expression language and that you should use correlated updates instead. However, the 'doc' column is of type 'image' which is invalid in subqueries according to an error message from my database driver. Is there anything I can do in SA's expression language or will I have to use a hand-written statement in this case? I can tell you that there are two trac tickets regarding this functionality, and this patch in particular has a @compiles recipe that does the basic idea: http://www.sqlalchemy.org/trac/attachment/ticket/1944/enhance2.py The various patches that need to be reconclied/tested are at: http://www.sqlalchemy.org/trac/ticket/1944 http://www.sqlalchemy.org/trac/ticket/2166 A key thing holding back the entire feature being built in is that Oracle has similar functionality but implements it entirely differently. This is a common issue Oracle introduces by doing things in a totally nonstandard way (like CONNECT BY...). That said, the straight string or @compiles approach will allow this to function now in a rudimental way. @compiles is documented at: http://www.sqlalchemy.org/docs/core/compiler.html -- 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] map several objects at once ?
Hi list,
In my use case I have
groups that can include other groups (many to many)
groups can also include users (many to many)
users can 'have feelings' to other users (many to many)
What I want to achieve is :
for a given group, recursively find its sub users
for each of those users, I need the list of the users they ('like' | 'love'
| 'hate' ... filter on this criteria)
we discussed the recursive point here :
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg24742.html
so I manage to have a query that when executed returns a list of all sub
users (of class User)
searched group is the starting point (Group object)
content_q = searched_group.get_all_users()
of course, I can iterate on the result of this query, and then create a
dict of list (or whatever)
all_users = set(content_q.all())
result = {}
for user in all_users:
result['user.id']=[]
for other in user.my_feelings(feeling='love'):
result['user.id'].append((other.id, other.name))
Still as all those tables join, I have a feeling this could be accomplished
in a single query
maybe not ?
thanks for any idea
NiL
--
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/-/3TrJBLp82toJ.
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] Translating T-SQL UPDATE FROM to SQLAlchemy
On Nov 21, 2011, at 2:47 PM, Michael Bayer wrote: The various patches that need to be reconclied/tested are at: http://www.sqlalchemy.org/trac/ticket/1944 http://www.sqlalchemy.org/trac/ticket/2166 I've added a new patch that is good for inclusion in 0.7.4 to ticket #2166: http://www.sqlalchemy.org/trac/attachment/ticket/2166/2166.patch I'll be adding some documentation and doing more testing, the patch as is does the basic job for MSSQL, MySQL and Postgresql. I'm just going to leave Oracle out of it for now since I don't think the update multi table use case is nearly as prominent as it apparently is with PG (which is where we get the most requests for this feature) or the other backends. You can try/test this patch out now if you're so motivated or pull it down from https://bitbucket.org/zzzeek/sqlalchemy_2166. -- 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.
