[sqlalchemy] Mapping and updating tables with no primary key
Hi all. Possibly a real basic question but one I seem to have gone
round in circles with and failed to find an answer for so far.
I have an (Oracle) db with some tables similar to this. I'll create a
simple in-memory sqlite db just to illustrate.
from sqlalchemy import *
db = create_engine('sqlite://')
metadata = MetaData(db)
account_ids_table = Table('account_ids', metadata,
Column('account_id', Integer, primary_key=True),
Column('username', String(20)))
account_stuff_table = Table('account_stuff', metadata,
Column('account_id', Integer,
ForeignKey('account_ids.account_id')),
Column('credit', Numeric))
metadata.create_all()
# Load up a couple of rows.
account_ids_table.insert().execute(
{'account_id': 1, 'username': 'andyh'},
{'account_id': 2, 'username': 'richo'})
account_stuff_table.insert().execute(
{'account_id': 1, 'credit': 0},
{'account_id': 2, 'credit': 100.5})
# Okay. Now create a session, something to map to and join
class Account(object):
pass
# Create a mapper based on the two tables just joined via account_ids
mapper(Account, join(account_ids_table, account_stuff_table))
session = create_session()
ac = session.query(Account).filter_by(account_id=1).first()
# And then modify and get an error...
ac.credit = 10
session.flush()
When the flush executes I get the error:
class 'sqlalchemy.exceptions.ConcurrentModificationError': Updated
rowcount 2 does not match number of objects updated 1
because it's trying to execute the sql: UPDATE account_stuff SET
credit=?
because I assume account_stuff has no primary key (updates to
account_ids specify a where clause).
How can I get the above to work? I assume the join in the mapper needs
to specify something else? Is it possible?
Thanks
Andy Hird
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: NULLS FIRST/LAST support?
Hello, Michael Bayer said the following on 09.08.2007 22:53: OK just to double check, the syntax looks like: SELECT * FROM sometable ORDER BY foo NULLS FIRST SELECT * FROM sometable ORDER BY foo DESC NULLS LAST Yes. ? i.e. is DESC/ASC before the NULLS part ? or doesn't matter ? It does matter according to Firebird Null Guide: http://www.firebirdsql.org/manual/nullguide-sorts.html -- Oleg --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: NULLS FIRST/LAST support?
OK ive added ticket #723 for this, it will go into 0.4xx. On Aug 10, 2007, at 5:13 AM, Oleg Deribas wrote: Hello, Michael Bayer said the following on 09.08.2007 22:53: OK just to double check, the syntax looks like: SELECT * FROM sometable ORDER BY foo NULLS FIRST SELECT * FROM sometable ORDER BY foo DESC NULLS LAST Yes. ? i.e. is DESC/ASC before the NULLS part ? or doesn't matter ? It does matter according to Firebird Null Guide: http://www.firebirdsql.org/manual/nullguide-sorts.html -- Oleg --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: new session docs !
On Aug 10, 2007, at 5:18 AM, Alexandre CONRAD wrote: It sounds to me that the session is global and needs to be cleared everytime. Which I think is wrong (or SessionContext works differently). I think a *new* session should be created and attached to every new request. The session is then deleted automaticly when the request ends, rather than shared from a global obect (the Pylons' model) and cleared (which is not thread-safe as I now understand). hi alexandre - in fact, either approach will work. you can close() the existing session (i say close() now because it also removes any transactions in progress), or you can replace with an entirely new session. both have the same effect. the clear at the end model seems to have been more popular for some reason. also, im not sure if i put an explicit hook onto ScopedSession to set a new session so that should definitely be added. - mike --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapping and updating tables with no primary key
On Aug 10, 2007, at 3:18 AM, Andy Hird wrote: because it's trying to execute the sql: UPDATE account_stuff SET credit=? because I assume account_stuff has no primary key (updates to account_ids specify a where clause). How can I get the above to work? I assume the join in the mapper needs to specify something else? Is it possible? one interesting thing here is that i think you've found the oldest bug in SQLAlchemy ever. so thats fixed in the trunk / 0.3 branch, the bug being that it was trying to issue an UPDATE on a table which has no primary keys. but that doesn't fix your problem...because if you use the latest SQLAlchemy in the trunk or 0.3 branch, now it will just skip the account_stuff table altogether. you just have to put primary_key=True on the account_stuff.account_id column, and then it will work. now, if theres a reason you can't have a primary key on account_stuff.account_id, id be interested to hear what that is. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unit of work and optimistic locking
Michael Bayer wrote: SQLAlchemy has always supported this feature using the version_id_col keyword argument to mapper(), which is described at: http://www.sqlalchemy.org/docs/04/ sqlalchemy_orm.html#docstrings_sqlalchemy.orm_modfunc_mapper Argh, just a little bit too far in the hierarchy. Hmmm... searching the wiki for version_id_col shows up a few hits, but certainly I wouldn't have guessed looking for that. I did look up for optimistic locking, maybe adding those words somewhere can help to future users. When an UPDATE or DELETE cannot locate the row with the correct version ID, a sqlalchemy.exceptions.ConcurrentModificationError is thrown and the transaction is rolled back. Cool. In the unit test you provide the version_id_col parameter is specified manually. Does SQLAlchemy do automatic reflection on the table and use a version_id column if it already exists, without having to specify it on the code? Some time ago I started with a few Ruby on rails tutorials and I ended up with mixed feelings about the way everything there seems automatic if you name it with the correct words. It looks cool because you write less, but I ended up forgetting that stuff was being done behind my back. PS. http://www.sqlalchemy.org/trac/wiki/SqlSoup throws a system message error at the end of the page. -- Rastertech España S.A. Grzegorz Adam Hankiewicz /Jefe de Producto TeraVial/ C/ Perfumería 21. Nave I. Polígono industrial La Mina 28770 Colmenar Viejo. Madrid (España) Tel. +34 918 467 390 (Ext.17) *·* Fax +34 918 457 889 [EMAIL PROTECTED] *·* www.rastertech.es http://www.rastertech.es/ --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Problem with subquery
Er, where is it you're not supposed to use .c? The code in MikeB's
example seems to have .c in every possible location. How do you
access a column without .c?
On 8/10/07, Jeronimo [EMAIL PROTECTED] wrote:
Excelent ! It works perfectly !!
Thank you very much Michael. I was going crazy trying to figure how to
move
subquery to the from clause.
On Aug 9, 8:06 pm, Michael Bayer [EMAIL PROTECTED] wrote:
OK sorry, i didn't look carefully enough. when you use a scalar
subquery, you shouldn't access the c attribute on it. I hadn't
really realized that and maybe i should add an exception for that.
when you access the c attribute, you're treating it like another
relation to be selected from, so it gets stuck into the from clause.
but here, its really just a column expression; so you don't join
against one of the subqueries' columns, the subquery IS the thing to
be compared against. so heres the full testcase:
create table node(id integer, parent_id integer, type_id integer);
insert into node(1,NULL,1);
insert into node values(1,NULL,1);
insert into node values(2,1,1);
insert into node values(3,1,2);
insert into node values(4,1,1);
SELECT node.id, node.parent_id, node.type_id
FROM node
WHERE node.id = (SELECT max(n1.id) FROM node AS n1 WHERE n1.type_id =
node.type_id);
from sqlalchemy import *
meta = MetaData()
node_table = Table('nodes', meta,
Column('id', Integer),
Column('parent_id', Integer),
Column('type_id', Integer),
)
meta.bind = create_engine('sqlite://', echo=True)
meta.create_all()
node_table.insert().execute(id=1, type_id=1)
node_table.insert().execute(id=1, type_id=1)
node_table.insert().execute(id=2, parent_id=1, type_id=1)
node_table.insert().execute(id=2, parent_id=1, type_id=1)
node_table.insert().execute(id=3, parent_id=1, type_id=2)
node_table.insert().execute(id=4, parent_id=1, type_id=1)
n1 = node_table.alias('n1')
sub_query = select([func.max(n1.c.id)],
(node_table.c.type_id==n1.c.type_id), scalar=True)
print
node_table.select(node_table.c.id==sub_query).execute().fetchall()
--
Mike Orr [EMAIL PROTECTED]
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Unit of work and optimistic locking
On Aug 10, 2007, at 8:32 AM, Grzegorz Adam Hankiewicz wrote:
Is there any documentation about this or do sqlalchemy applications
deal
with concurrent writes in another way? If the unit of work tracked
those
internal version numbers the flushing would fail and an exception
raised, but I haven't seen anything about this in the docs so far.
HI Grzegorz:
SQLAlchemy has always supported this feature using the
version_id_col keyword argument to mapper(), which is described at:
http://www.sqlalchemy.org/docs/04/
sqlalchemy_orm.html#docstrings_sqlalchemy.orm_modfunc_mapper
the versioning pattern here should probably receive a full-fledged
example in the mappers.html document, however.
Even if you dont have a version_id_col set up, SQLAlchemy will still
check the updated rowcount on UPDATE and DELETE that it matches the
expected results.
an example from the unit tests looks something like:
version_table = Table('version_test', MetaData(testbase.db),
Column('id', Integer, Sequence('version_test_seq'),
primary_key=True ),
Column('version_id', Integer, nullable=False),
Column('value', String(40), nullable=False)
)
class Foo(object):pass
mapper(Foo, version_table,
version_id_col=version_table.c.version_id)
When an UPDATE or DELETE cannot locate the row with the correct
version ID, a sqlalchemy.exceptions.ConcurrentModificationError is
thrown and the transaction is rolled back.
Hope this helps,
- mike
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Unit of work and optimistic locking
On Aug 10, 1:04 pm, Grzegorz Adam Hankiewicz [EMAIL PROTECTED] wrote: Cool. In the unit test you provide the version_id_col parameter is specified manually. Does SQLAlchemy do automatic reflection on the table and use a version_id column if it already exists, without having to specify it on the code? telling the mapper which column you'd like to be the versioning column, if any, is always a manual on mapper(). SQLAlchemy never assumes anything based on naming conventions, unless required by a specific database implementation (such as PG's naming of tablename_id_seq for SERIAL sequences). --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: new session docs !
On 8/10/07, Alexandre CONRAD [EMAIL PROTECTED] wrote: Okay Mike, about scoped_session(), I think I got it right this time. I was trying to get inspired from SAContext, how it's beeing wrapped around with Pylons. Since SAContext has not yet upgraded to SA 0.4, maybe I was just getting inspired from some different mechanism. Or SAContext it doing it wrong. SAContext says you have to call the clear() method on your session on each new request. *Important:* Put this line at the beginning of your base controller's .__call__ method (myapp/lib/base.py):: model.sac.session.clear() This erases any stray session data left from the previous request in this thread. Otherwise you may get random errors or corrupt data. Or del model.sac.session_context.current if you prefer. It sounds to me that the session is global and needs to be cleared everytime. Which I think is wrong (or SessionContext works differently). I think a *new* session should be created and attached to every new request. The session is then deleted automaticly when the request ends, rather than shared from a global obect (the Pylons' model) and cleared (which is not thread-safe as I now understand). sac.session is not a global attribute, it's a property that returns sac.session_context.current. sac,session_context is a SessionContext, which manages its .current property to provide a session local to the current thread and application. After del sac.session_context.current, SessionContext automatically creates a new session at the next access; this is a feature of SessionContext. sac.session.clear() resets the thread-local session in place, discarding any remnants of its previous use. Both statements do effectively the same thing, but I'm told that del sac.session_context.current is more computationally efficient. In SQLAlchemy 0.4, SessionContext is superceded by scoped_session, which has a different API. Assuming 'Session = scoped_session(sessionmaker(...))', 'Session()' is the equivalent to 'session_context.current'. MikeB says the preferred way to reset a session in 0.4 is 'session.close()'. This does the same as 'session.clear()' but also releases any network connections or other resources that are being held. *If* SAContext replaces .session_context with .session_factory (equivalent to 'Session' above), the .session property would be redefined to return self.session_factory()'. Then you'd put this in your controller method: model.sac.session.close() *after* the superclass call. I'm thinking about adding convenience methods sac.start_request() / sac.end_request() to avoid any confusion, and also to isolate the controller from changes in the session code. But SAContext will not be upgraded until SQLAlchemy 0.4 beta comes out, because the SQLAlchemy API is changing too quickly for me to keep up with. If session.close() exists in SQLAlchemy 0.3, I didn't know about it. -- Mike Orr [EMAIL PROTECTED] --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to dynamically create where-clauses
On Fri, Aug 10, 2007 at 02:51:51PM +0200, Hermann Himmelbauer wrote: My web-application, that is in front of SQLAlchemy has various input-fields, which should act as a where-clause (with like()), a query looks e.g. like this: session.query(MyObj).filter(table1.c.input1.like(inputfield_1+'%'), table1.c.input2.like(inputfield_2+'%'), ...) The problem is that if such an input field is empty, it should not be included in the query. If I rule out empty fields manually, I have 2^n if-clauses (I think), so I need to dynamically create the where-clauses in some way. What would be the best way to accomplish this? You are on the right track with filter() already. Consider this: result = session.query(MyObj) if 'name' in params: result=result.filter_by(name=params['name']) if 'min_age' in params: result=result.filter_by(age=params['min_age']) ... Works well in my Pylons (a web framework) applications in search/query forms. Cheers Christoph --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Problem with subquery
Excelent ! It works perfectly !!
Thank you very much Michael. I was going crazy trying to figure how to
move
subquery to the from clause.
On Aug 9, 8:06 pm, Michael Bayer [EMAIL PROTECTED] wrote:
OK sorry, i didn't look carefully enough. when you use a scalar
subquery, you shouldn't access the c attribute on it. I hadn't
really realized that and maybe i should add an exception for that.
when you access the c attribute, you're treating it like another
relation to be selected from, so it gets stuck into the from clause.
but here, its really just a column expression; so you don't join
against one of the subqueries' columns, the subquery IS the thing to
be compared against. so heres the full testcase:
create table node(id integer, parent_id integer, type_id integer);
insert into node(1,NULL,1);
insert into node values(1,NULL,1);
insert into node values(2,1,1);
insert into node values(3,1,2);
insert into node values(4,1,1);
SELECT node.id, node.parent_id, node.type_id
FROM node
WHERE node.id = (SELECT max(n1.id) FROM node AS n1 WHERE n1.type_id =
node.type_id);
from sqlalchemy import *
meta = MetaData()
node_table = Table('nodes', meta,
Column('id', Integer),
Column('parent_id', Integer),
Column('type_id', Integer),
)
meta.bind = create_engine('sqlite://', echo=True)
meta.create_all()
node_table.insert().execute(id=1, type_id=1)
node_table.insert().execute(id=1, type_id=1)
node_table.insert().execute(id=2, parent_id=1, type_id=1)
node_table.insert().execute(id=2, parent_id=1, type_id=1)
node_table.insert().execute(id=3, parent_id=1, type_id=2)
node_table.insert().execute(id=4, parent_id=1, type_id=1)
n1 = node_table.alias('n1')
sub_query = select([func.max(n1.c.id)],
(node_table.c.type_id==n1.c.type_id), scalar=True)
print
node_table.select(node_table.c.id==sub_query).execute().fetchall()
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Using SA to move data between databases
On Thursday 09 August 2007 13:04:44 Paul Johnston wrote: Hi, A little update; Also, in the same direction, complete copy of some database seems to consist of (at least) 3 stages: 1 recreate/remove the old one if it exists 2 copy structure 3 copy data 3 is your copy loop, which is independent of db type; 2 is the autoload, which does depend on db-dialect; i hope most of it can move into the SA-dialects themselves. how about 1? it also does depend badly on db-dialect. see http://dbcook.svn.sourceforge.net/viewvc/*checkout*/dbcook/trunk/dbcook/usage/sa_engine_defs.py e.g. for sqlite, recreate mean 'rm -f file'; for postgres it means 'dropdb url' + 'createdb url'; for mssql it is even more tricky... btw: why is the 'text_as_varchar=1' considered only if it is in url (see mssql.py create_connect_args()) and not if it is in the connect_args argument to create_engine()? svil --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Using SA to move data between databases
Hi, wouldn't this be working equivalent? it also copes with empty tables.. Yep, I like yours better. Thanks! i'm Wondering if all the unicode strings (at least table/column names) should be converted back into plain strings as they have been before autoload reflecting them from database. Well, some databases do support unicode identifier names, some don't. I'd say don't do any conversion for now; if someone is faced with migrating tables with unicode names to a database that doesn't support it, well, let them sweat that one :-) Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: new session docs !
Okay Mike, about scoped_session(), I think I got it right this time. I was trying to get inspired from SAContext, how it's beeing wrapped around with Pylons. Since SAContext has not yet upgraded to SA 0.4, maybe I was just getting inspired from some different mechanism. Or SAContext it doing it wrong. SAContext says you have to call the clear() method on your session on each new request. *Important:* Put this line at the beginning of your base controller's .__call__ method (myapp/lib/base.py):: model.sac.session.clear() This erases any stray session data left from the previous request in this thread. Otherwise you may get random errors or corrupt data. Or del model.sac.session_context.current if you prefer. It sounds to me that the session is global and needs to be cleared everytime. Which I think is wrong (or SessionContext works differently). I think a *new* session should be created and attached to every new request. The session is then deleted automaticly when the request ends, rather than shared from a global obect (the Pylons' model) and cleared (which is not thread-safe as I now understand). Or I just don't get it. :) Anyway, all seems to be working fine for me now. ps: again, congrats with the new docs and the new SA04, they're great! Regards, Alex --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Using SA to move data between databases
On Thursday 09 August 2007 13:04:44 Paul Johnston wrote:
Hi,
A little update; this code handles the case where columns have a
key attribute:
model = __import__(sys.argv[1])
if sys.argv[2] == 'copy':
seng = create_engine(sys.argv[3])
deng = create_engine(sys.argv[4])
for tbl in model.metadata.table_iterator():
print tbl
mismatch = {}
for col in tbl.c:
if col.key != col.name:
mismatch[col.name] = col.key
def rewrite(x, mismatch):
x = dict(x)
for m in mismatch:
x[mismatch[m]] = x[m]
return x
deng.execute(tbl.insert(), [rewrite(x, mismatch) for x in
seng.execute(tbl.select())])
are u sure about the rewrite() part?
x will contain both .key and .name with same values on them...
wouldn't this be working equivalent? it also copes with empty tables..
---
def copy( metadata, src_engine, dst_engine, echo =False ):
for tbl in metadata.table_iterator():
if echo: print tbl
data = [ dict( (col.key, x[ col.name]) for col in tbl.c)
for x in src_engine.execute( tbl.select()) ]
if data:
dst_engine.execute( tbl.insert(), data)
if __name__ == '__main__':
arg_model = sys.argv[1]
model = import__( arg_model )
copy( model.metadata,
src_engine= create_engine( sys.argv[2]),
dst_engine= create_engine( sys.argv[3]),
)
http://dbcook.svn.sourceforge.net/viewvc/*checkout*/dbcook/trunk/dbcook/misc/copydata.py
there is also copyall.py (at same place) that does all in once
(autoload + copydata):
$ python copyall.py postgres://[EMAIL PROTECTED]/db1 sqlite:///db2
===
i'm Wondering if all the unicode strings (at least table/column names)
should be converted back into plain strings as they have been before
autoload reflecting them from database.
svil
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Using SA to move data between databases
Hi, 1 recreate/remove the old one if it exists how about 1? it also does depend badly on db-dialect. Personally, I'd do this step manually. Not sure I quite trust a script that has the potential to drop database btw: why is the 'text_as_varchar=1' considered only if it is in url (see mssql.py create_connect_args()) and not if it is in the connect_args argument to create_engine()? Fair question, and the short answer is because that's all I needed. We did have a discussion about unifying create_engine args and URL params, but it turns out there are a few gotchas. We could allow specification in both places - is this important to you? Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Unit of work and optimistic locking
Hi. I've been reading the documentation on the unit of work at http://www.sqlalchemy.org/docs/04/sqlalchemy_orm_session.html and I wonder how this can be merged with what I've seen called as optimistic locking. By optimistic locking I understand the possibility of tracking if two users accessing the same row in a table. The basic example is process A accessing a row and reading a version column. At the same time process B accesses the same row and same version number, but is faster and writes to the row the updated information along with an increased version number. When the process A tries to update the same row the version number doesn't match and the transaction fails. Here's where the unit of work would help tracking changes to different rows in different tables which are logically part of the same object or transaction from the point of view of the user. Is there any documentation about this or do sqlalchemy applications deal with concurrent writes in another way? If the unit of work tracked those internal version numbers the flushing would fail and an exception raised, but I haven't seen anything about this in the docs so far. -- Rastertech España S.A. Grzegorz Adam Hankiewicz /Jefe de Producto TeraVial/ C/ Perfumería 21. Nave I. Polígono industrial La Mina 28770 Colmenar Viejo. Madrid (España) Tel. +34 918 467 390 (Ext.17) *·* Fax +34 918 457 889 [EMAIL PROTECTED] *·* www.rastertech.es http://www.rastertech.es/ --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] How to dynamically create where-clauses
Hi, My web-application, that is in front of SQLAlchemy has various input-fields, which should act as a where-clause (with like()), a query looks e.g. like this: session.query(MyObj).filter(table1.c.input1.like(inputfield_1+'%'), table1.c.input2.like(inputfield_2+'%'), ...) The problem is that if such an input field is empty, it should not be included in the query. If I rule out empty fields manually, I have 2^n if-clauses (I think), so I need to dynamically create the where-clauses in some way. What would be the best way to accomplish this? Best Regards, Hermann -- [EMAIL PROTECTED] GPG key ID: 299893C7 (on keyservers) FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to dynamically create where-clauses
I haven't tried it with your specific example, but I'm pretty sure the theory should work just the same. The first thing you need to do is create a list of the clause objects. The other thing you'll need is a way to associate the input names with the column names (either make them the same or use a dictionary or something): (this code is untested / pseudocode) clauseList = [] for input in formInputs: if not input == None: clauseList.append( table1.c[ input.name ].like(input.value +'%') ) OR clauseList = [ table1.c[ input.name ].like(input.value+'%') for input in formInputs if not input == None ] after that, you use the * operator on the query: session.query(MyObj).filter( *clauseList ) (If you are unaware, is the same thing as calling session.query(MyObj).filter( clauseList[0], clauseList[1], ... , clauseList[n]), only you don't have to know the exact length. Hope that answered your question -Dave On Aug 10, 8:51 am, Hermann Himmelbauer [EMAIL PROTECTED] wrote: Hi, My web-application, that is in front of SQLAlchemy has various input-fields, which should act as a where-clause (with like()), a query looks e.g. like this: session.query(MyObj).filter(table1.c.input1.like(inputfield_1+'%'), table1.c.input2.like(inputfield_2+'%'), ...) The problem is that if such an input field is empty, it should not be included in the query. If I rule out empty fields manually, I have 2^n if-clauses (I think), so I need to dynamically create the where-clauses in some way. What would be the best way to accomplish this? Best Regards, Hermann -- [EMAIL PROTECTED] GPG key ID: 299893C7 (on keyservers) FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Problem with subquery
On Aug 10, 1:56 pm, Mike Orr [EMAIL PROTECTED] wrote: Er, where is it you're not supposed to use .c? The code in MikeB's example seems to have .c in every possible location. How do you access a column without .c? ive clarified this in 0.4's behavior. lets take a query like this: select([users.c.id]) which is: select id from users. if we do like a join against some other table on it, like this: s = select([users.c.id]) select([addresses.c.user_id, s.c.id], addresses.c.user_id==s.c.id) which is: select addresses.user_id, id from addreses, (select id from users) where addresses.user_id=id we use the c attribute on the selectable s to get at the id column. this is because s is just another relation (i.e. selectable), with its own list of columns, just like a regular table. But if we want to use s in a column expression, like this: select * from addresses where user_id=(select id from users where name='ed') we are comparing a scalar column user_id to another scalar value. the subquery is *required* by SQL to return a single, scalar result. its invalid to say: select * from addresses where user_id=(select id, name from users where name='ed') and it would also be invalid (according to SQL) if the subquery returned multiple rows. so whats really happening is, the subquery acts like a column expression itself. this is what a scalar subquery is. so in 0.4, you say: s = s.as_scalar() and its no longer a Select object, its actually a _ScalarSelect. and it no longer has a c attribute..because its specifically a column expression now, and not a relation/selectable like it was before. SQLAlchemy has been pretty casual about this kind of thing, like is it a relation, is it scalar, etc. but Ive tried to clarify these roles more explicitly in 0.4. it will still work in 0.4 if you don't say as_scalar(), in fact. but if you've specifically declared a subquery as scalar, it *is* its own column, and you no longer get at a c attribute. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Using SA to move data between databases
btw: why is the 'text_as_varchar=1' considered only if it is in url (see mssql.py create_connect_args()) and not if it is in the connect_args argument to create_engine()? Fair question, and the short answer is because that's all I needed. We did have a discussion about unifying create_engine args and URL params, but it turns out there are a few gotchas. We could allow specification in both places - is this important to you? not really important, we dig it out already, but it would be more sane if power(connect_args) = power(url_args), that is, connect_args is the more general/powerful/ way, and url allows a subset (or all) of connect_args items; and not vice versa -- connect_args is the programatical way, so that should do _everything_.. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Using SA to move data between databases
i'm Wondering if all the unicode strings (at least table/column names) should be converted back into plain strings as they have been before autoload reflecting them from database. Well, some databases do support unicode identifier names, some don't. I'd say don't do any conversion for now; if someone is faced with migrating tables with unicode names to a database that doesn't support it, well, let them sweat that one :-) hmmm. i'll probably put that as some option, as my model's table/column names are never unicode, but once they go into db, all gets unicoded there. so i'm not sure if after some migration the model will match the database... e.g. sqlite turns everything into unicode and hence does not care if unicode or not - so it's all ok there; but once db-structure migrates into something that _does_ care about unicode or not, trouble trouble.. is this unicodeing everything a sqlite specific behaviour? de-unicoding it then should go into sqlite-dialect specific reflection then. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
