Re: [sqlalchemy] how add comment before query statement?
2011/12/12 Michael Bayer mike...@zzzcomputing.com we have select.prefix_with() which can stick it right after the SELECT, if that worksotherwise if it really has to be the first thing would need to work in some @compiles tricks. then as far as Query I thought we had added something for this but apparently not, you'd have to subclass that too for the moment... :/ Hello. I have related question. I need add sql_cache keywords into select query. There is way to do this on sql layer via prefixes keyword argument to select class or via method .prefix_with of the same select class. But I have orm.Query object... And I can't find way to add prefix on it. SA-0.6.7 On Dec 12, 2011, at 3:56 AM, lestat wrote: For our postgresql cluster we need sometime append comment before query statement. E.g. q = Comment.query.all() SELECT ... FROM comment How append comment like this? /*NO LOAD BALANCE*/ SELECT ... FROM comment I try change q.statement, but can't find right solution. -- 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] MySQL significant order in set list of update command
2011/12/11 Michael Bayer mike...@zzzcomputing.com My impression was Postgresql handles this much more nicely which is confirmed by this post: http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ the usual way this kind of thing is done is via declaring local variables in the SQL statement, and an example of doing such using MySQL's syntax is in that linked article - MySQL will let you assign to the variable in the WHERE clause so they do update t set a=b, b=@temp where (@temp:=a) is not null. This basically gets at the value of a before it changes, which is what PG does in any case. None of this has much to do with SQLAlchemy, though. you'd pretty much need to use a plain string or @compiles elements to produce that SQL within SQLAlchemy. I know about correct behavior of postgresql in this task. And I was very surprising when found such weird behavior of mysql. Is this a bug in mysql? What tell sql standard about such operations? PS For now I made switch of column values in client code, by doing select for update ... and update each row of result. -- 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] MySQL significant order in set list of update command
Hi, All. I have found unexpected behaviour of MySQL DB. In update command order of operations in set list is significant. I need to switch values of 2 rows. Better see example: mysql create temporary table sw_test (a integer not null, b integer not null, dummy integer); Query OK, 0 rows affected (0.23 sec) mysql insert into sw_test (a, b) values (1, 2), (4, 3); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql update sw_test set a=b, b=a where ab; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from sw_test; +---+---+---+ | a | b | dummy | +---+---+---+ | 2 | 2 | NULL | | 4 | 3 | NULL | +---+---+---+ 2 rows in set (0.00 sec) Here update lost values of one row... but if we write: mysql update sw_test set dummy=a, a=b, b=dummy, dummy=null where ba; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from sw_test; +---+---+---+ | a | b | dummy | +---+---+---+ | 2 | 2 | NULL | | 3 | 4 | NULL | +---+---+---+ 2 rows in set (0.00 sec) Very ugly but what else? Is there a way to forse order of columns in SA update clause? PS: mysql-5.1.56 sqlalchemt-0.6.7 -- 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] looks like bug ses.query(data).update()
I try to update counter for omr object ang got following:
Traceback (most recent call last):
File /home/vugluskr/tmp/z/sa.py, line 56, in module
main()
File /home/vugluskr/tmp/z/sa.py, line 52, in main
q2.update({data.cnt: data.cnt + 1})
File /usr/lib/python2.6/site-packages/sqlalchemy/orm/query.py,
line 2184, in update
difference(to_evaluate))
File /usr/lib/python2.6/site-packages/sqlalchemy/orm/state.py,
line 257, in expire_attributes
impl = self.manager[key].impl
KeyError: 'counter'
Tests script and full output in attaches.
Maybe I going wrong way and SA have more simpler way awailable to make
query like:
update tbl set counter = counter + 1 where ...
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:PRAGMA table_info(data)
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:()
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:
CREATE TABLE data (
idnr INTEGER NOT NULL,
counter INTEGER NOT NULL,
PRIMARY KEY (idnr)
)
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:()
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:BEGIN
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:INSERT INTO data (counter) VALUES
(?)
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:(0,)
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:BEGIN
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:SELECT data.counter AS
data_counter, data.idnr AS data_idnr
FROM data
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:()
DEBUG:sqlalchemy.engine.base.Engine.0x...9b4c:Col ('data_counter', 'data_idnr')
DEBUG:sqlalchemy.engine.base.Engine.0x...9b4c:Row (0, 1)
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:UPDATE data SET
counter=(data.counter + ?) WHERE data.idnr = ?
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:(1, 1)
Traceback (most recent call last):
File /home/vugluskr/tmp/z/sa.py, line 56, in module
main()
File /home/vugluskr/tmp/z/sa.py, line 52, in main
q2.update({data.cnt: data.cnt + 1})
File /usr/lib/python2.6/site-packages/sqlalchemy/orm/query.py, line 2184,
in update
difference(to_evaluate))
File /usr/lib/python2.6/site-packages/sqlalchemy/orm/state.py, line 257, in
expire_attributes
impl = self.manager[key].impl
KeyError: 'counter'
#!/usr/bin/env python
#-*- coding:utf-8 -*-
#import pdb
import os, sys
import time
import logging
#import re
#import errno
#import locale
#import pprint
# mutable inputs
from sqlalchemy import *
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base
decl = declarative_base()
class data(decl):
__tablename__ = 'data'
idnr= Column(Integer, primary_key=True)
cnt = Column('counter', Integer, nullable=False, default=0)
def __repr__(self):
return 'tbl.%s(idnr=%s, cnt=%s)' % (
self.__class__.__name__, self.idnr, self.cnt)
def main():
logging.basicConfig()
logging.getLogger().setLevel(logging.DEBUG)
log = logging.getLogger('sqlalchemy.engine')
log.setLevel(logging.DEBUG)
eng = create_engine('sqlite://')
sm = orm.sessionmaker(bind=eng, autoflush=False, autocommit=False)
decl.metadata.create_all(eng)
ses = sm()
e = data()
ses.add(e)
ses.commit()
ses.close()
ses = sm()
q = ses.query(data)
for e in q:
q2 = ses.query(data).filter(data.idnr==e.idnr)
q2.update({data.cnt: data.cnt + 1})
ses.commit()
if __name__ == '__main__':
main()
Re: [sqlalchemy] looks like bug ses.query(data).update()
2010/9/30 Michael Bayer mike...@zzzcomputing.com:
this is a bug , created at http://www.sqlalchemy.org/trac/ticket/1935 , and a
patch which fixes this issue is there. will try to get this committed
soon.
Thanks, patch fix issue.
I try to update counter for omr object ang got following:
Traceback (most recent call last):
File /home/vugluskr/tmp/z/sa.py, line 56, in module
main()
File /home/vugluskr/tmp/z/sa.py, line 52, in main
q2.update({data.cnt: data.cnt + 1})
File /usr/lib/python2.6/site-packages/sqlalchemy/orm/query.py,
line 2184, in update
difference(to_evaluate))
File /usr/lib/python2.6/site-packages/sqlalchemy/orm/state.py,
line 257, in expire_attributes
impl = self.manager[key].impl
KeyError: 'counter'
Tests script and full output in attaches.
Maybe I going wrong way and SA have more simpler way awailable to make
query like:
update tbl set counter = counter + 1 where ...
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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 attribute changes?
Hello. Is sqlachemy allow to check for changes on particallar mapped attribute? Receive it's old value? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Some weir(for me) behavior of one-to-one relation.
I have 2 tables with one-to-one relation, and I got some unexpected
behaviour from sqlalchemy.
In attach there is demonstration script and its log.
If I try to add object into table right for already existing key I
expect error on DB level, telling about violation of unique
constraint, but sqlalchemy before try to insert new row, set relation
key to NULL for already existing object...
Is it right?
How can I avoid such behavior?
if you are replacing an object reference with another one, then yes SQLA will
null out the key for the old one first, since you have removed it from its
parent by replacing it. If you dont want the NULL allowed, the usual
approach is to have the left_idnr column be NOT NULL - the database then does
the work of disallowing the operation to proceed. There is a setting for
passive_deletes, 'all', which disallows the nulling out of the foreign key,
but that only applies to a cascading deletion scenario which is not the case
here.
For one-to-ones I usually set the foreign key on the child as the primary key
as well. You can see me asking about this (since a DBA gave me some
resistance about it recently) here:
http://stackoverflow.com/questions/2967450/foreign-key-constraints-on-primary-key-columns-issues
I try to change schema(updated example in attach, it drop
AssertionException), to use foreign key as primary key for child
table, but it doesn't want to work in this configuration at all.
Please give me link on sqlalchemy documentation, where I can read
about such usage of primary key.
There is only one place in sqlalchemy documentation where I see such
usage of primary key
http://www.sqlalchemy.org/docs/05/reference/ext/declarative.html?highlight=declarative#joined-table-inheritance
but this is not my case.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
#!/usr/bin/env python
#-*- coding:utf-8 -*-
#import pdb
import os, sys
import time
import logging
#import re
#import errno
#import locale
#import pprint
# mutable inputs
from sqlalchemy import *
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base
decl = declarative_base()
class left(decl):
__tablename__ = 'left'
idnr= Column(Integer, primary_key=True)
data= Column(String(32), nullable=False)
rel = orm.relation('right', uselist=False,
backref=orm.backref('left'),
passive_updates=True, passive_deletes=True, lazy=True, cascade='all')
class right(decl):
__tablename__ = 'right'
#idnr= Column(Integer, primary_key=True)
left_idnr = Column(Integer, ForeignKey(left.idnr, onupdate='CASCADE', ondelete='CASCADE'), primary_key=True, unique=True)
data= Column(String(32), nullable=False)
def main():
logging.basicConfig()
log = logging.getLogger('sqlalchemy.engine')
log.setLevel(logging.DEBUG)
eng = create_engine('sqlite://')
sm = orm.sessionmaker(bind=eng, autoflush=False, autocommit=False)
decl.metadata.create_all(eng)
ses = sm()
l = left()
l.data = 'abc'
ses.add(l)
r = right()
r.left = l
r.data = 'cde'
ses.add(r)
ses.commit()
l_idnr = l.idnr
ses.close()
ses = sm()
l = ses.query(left).get(l_idnr)
r2 = right()
r2.left = l
r2.data = '012'
ses.flush()
ses.commit()
if __name__ == '__main__':
main()
[sqlalchemy] Some weir(for me) behavior of one-to-one relation.
Hello.
I have 2 tables with one-to-one relation, and I got some unexpected
behaviour from sqlalchemy.
In attach there is demonstration script and its log.
If I try to add object into table right for already existing key I
expect error on DB level, telling about violation of unique
constraint, but sqlalchemy before try to insert new row, set relation
key to NULL for already existing object...
Is it right?
How can I avoid such behavior?
PS sqlalchemy version is 0.5.8
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
INFO:sqlalchemy.engine.base.Engine.0x...deac:PRAGMA table_info(left)
INFO:sqlalchemy.engine.base.Engine.0x...deac:()
INFO:sqlalchemy.engine.base.Engine.0x...deac:PRAGMA table_info(right)
INFO:sqlalchemy.engine.base.Engine.0x...deac:()
INFO:sqlalchemy.engine.base.Engine.0x...deac:
CREATE TABLE left (
idnr INTEGER NOT NULL,
data VARCHAR(32) NOT NULL,
PRIMARY KEY (idnr)
)
INFO:sqlalchemy.engine.base.Engine.0x...deac:()
INFO:sqlalchemy.engine.base.Engine.0x...deac:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...deac:
CREATE TABLE right (
idnr INTEGER NOT NULL,
left_idnr INTEGER,
data VARCHAR(32) NOT NULL,
PRIMARY KEY (idnr),
FOREIGN KEY(left_idnr) REFERENCES left (idnr) ON DELETE CASCADE ON
UPDATE CASCADE,
UNIQUE (left_idnr)
)
INFO:sqlalchemy.engine.base.Engine.0x...deac:()
INFO:sqlalchemy.engine.base.Engine.0x...deac:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...deac:BEGIN
INFO:sqlalchemy.engine.base.Engine.0x...deac:INSERT INTO left (data) VALUES
(?)
INFO:sqlalchemy.engine.base.Engine.0x...deac:['abc']
INFO:sqlalchemy.engine.base.Engine.0x...deac:INSERT INTO right (left_idnr,
data) VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine.0x...deac:[1, 'cde']
INFO:sqlalchemy.engine.base.Engine.0x...deac:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...deac:BEGIN
INFO:sqlalchemy.engine.base.Engine.0x...deac:SELECT left.idnr AS left_idnr,
left.data AS left_data
FROM left
WHERE left.idnr = ?
INFO:sqlalchemy.engine.base.Engine.0x...deac:[1]
DEBUG:sqlalchemy.engine.base.Engine.0x...deac:Col ('left_idnr', 'left_data')
DEBUG:sqlalchemy.engine.base.Engine.0x...deac:Row (1, u'abc')
INFO:sqlalchemy.engine.base.Engine.0x...deac:BEGIN
INFO:sqlalchemy.engine.base.Engine.0x...deac:SELECT left.idnr AS left_idnr,
left.data AS left_data
FROM left
WHERE left.idnr = ?
INFO:sqlalchemy.engine.base.Engine.0x...deac:[1]
DEBUG:sqlalchemy.engine.base.Engine.0x...deac:Col ('left_idnr', 'left_data')
DEBUG:sqlalchemy.engine.base.Engine.0x...deac:Row (1, u'abc')
INFO:sqlalchemy.engine.base.Engine.0x...deac:SELECT right.idnr AS right_idnr,
right.left_idnr AS right_left_idnr, right.data AS right_data
FROM right
WHERE ? = right.left_idnr
INFO:sqlalchemy.engine.base.Engine.0x...deac:[1]
DEBUG:sqlalchemy.engine.base.Engine.0x...deac:Col ('right_idnr',
'right_left_idnr', 'right_data')
DEBUG:sqlalchemy.engine.base.Engine.0x...deac:Row (1, 1, u'cde')
INFO:sqlalchemy.engine.base.Engine.0x...deac:UPDATE right SET left_idnr=?
WHERE right.idnr = ?
INFO:sqlalchemy.engine.base.Engine.0x...deac:[None, 1]
INFO:sqlalchemy.engine.base.Engine.0x...deac:INSERT INTO right (left_idnr,
data) VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine.0x...deac:[1, '012']
INFO:sqlalchemy.engine.base.Engine.0x...deac:COMMIT
#!/usr/bin/env python
#-*- coding:utf-8 -*-
#import pdb
import os, sys
import time
import logging
#import re
#import errno
#import locale
#import pprint
# mutable inputs
from sqlalchemy import *
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base
decl = declarative_base()
class left(decl):
__tablename__ = 'left'
idnr= Column(Integer, primary_key=True)
data= Column(String(32), nullable=False)
rel = orm.relation('right', uselist=False,
backref=orm.backref('left'),
passive_updates=True, passive_deletes=True, lazy=True, cascade='all')
class right(decl):
__tablename__ = 'right'
idnr= Column(Integer, primary_key=True)
left_idnr = Column(Integer, ForeignKey(left.idnr, onupdate='CASCADE', ondelete='CASCADE'), unique=True)
data= Column(String(32), nullable=False)
def main():
logging.basicConfig()
log = logging.getLogger('sqlalchemy.engine')
log.setLevel(logging.DEBUG)
eng = create_engine('sqlite://')
sm = orm.sessionmaker(bind=eng, autoflush=False, autocommit=False)
decl.metadata.create_all(eng)
ses = sm()
l = left()
l.data = 'abc'
ses.add(l)
r = right()
r.left = l
r.data = 'cde'
ses.add(r)
ses.commit()
l_idnr = l.idnr
ses.close()
ses = sm()
l = ses.query(left).get(l_idnr)
r2 = right()
r2.left
[sqlalchemy] Joined Table Inheritance
Hello.
Is there any way I can use some expression for polymorphic_on mapper
attribute?
I have tried some ways, but not got what I want.
# --- my tries
import sqlalchemy as sa
from sqlalchemy import *
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base
_decl = declarative_base()
class generic(_decl):
__tablename__ = 'generic'
idnr = Column(Integer, primary_key=True)
type = Column(String(32), nullable=False)
data = Column(String(128))
discriminator = orm.column_property(case(value=type, whens={'abc':type,
'cde':type}, else_='generic').label('discriminator'))
__mapper_args__ = {'polymorphic_on': type,
'polymorphic_identity':'generic'}
#__mapper_args__ = {'polymorphic_on': discriminator,
'polymorphic_identity':'generic'}
#__mapper_args__ = {'polymorphic_on': case(value=type,
whens={'abc':type, 'cde':type}, else_='generic'),
'polymorphic_identity':'generic'}
class type_abc(generic):
__tablename__ = 'abc'
generic_idnr = Column(Integer, ForeignKey(generic.idnr,
onupdate='CASCADE', ondelete='CASCADE'), primary_key=True)
abc = Column(Integer, nullable=False)
__mapper_args__ = {'polymorphic_identity':'abc'}
class type_cde(generic):
__tablename__ = 'cde'
generic_idnr = Column(Integer, ForeignKey(generic.idnr,
onupdate='CASCADE', ondelete='CASCADE'), primary_key=True)
cde = Column(Integer, nullable=False)
__mapper_args__ = {'polymorphic_identity':'cde'}
def main():
engine = create_engine('sqlite:///:memory:', echo=True)
generic.metadata.create_all(engine)
sm = orm.sessionmaker(bind=engine)
ses = sm()
a = type_cde()
a.cde = 1
ses.add(a)
a = type_abc()
a.abc = 2
ses.add(a)
#pdb.set_trace()
a = generic()
a.type = 'zzz'
a.data = 'a vot tak?'
ses.add(a)
ses.commit()
if __name__ == '__main__':
main()
# code end
It works only with real column instance...
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: Joined Table Inheritance
Or maybe there is way to extend _polymorphic_map on application side? I am already have type field into DB, but I don't need separate class for earch possible type value. For such values I want use generic class. Without this, I must add one more type field. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
