[sqlalchemy] Using a non-string in sqlalchemy
Hi everyone, I am a newbie and I must be overlooking a simple thing here. But how do I typecast a string to the type needed by a mapped class, which is identified at run time? I have a class, class Record(object): pass which I map to one of different tables identified at run time. I get strings like this col, val = raw_input(), raw_input() (my actual application uses HTML form, but this was for testing) which I try to use to insert a new record, like: rec = Record() setattr(rec, col, val) This works if col is a string (the column subclassing SQLAlchemy's String) but not otherwise. I can identify the type of the column as coltype = rec.c[col].type I probably can write a big if elif else to identify the type and do the typecasting. e.g.: if isinstance(coltype, Integer): val = int(val) but is there a canonical solution already there? Regards, Muhammad --~--~-~--~~~---~--~~ 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 a non-string in sqlalchemy
On 8/6/07, malkarouri [EMAIL PROTECTED] wrote: rec = Record() setattr(rec, col, val) This works if col is a string (the column subclassing SQLAlchemy's String) but not otherwise. I can identify the type of the column as I think most people just use a higher-level forms api. -Jonathan --~--~-~--~~~---~--~~ 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 a non-string in sqlalchemy
On Aug 6, 2007, at 8:29 AM, malkarouri wrote: Hi everyone, I am a newbie and I must be overlooking a simple thing here. But how do I typecast a string to the type needed by a mapped class, which is identified at run time? I have a class, class Record(object): pass which I map to one of different tables identified at run time. I get strings like this col, val = raw_input(), raw_input() (my actual application uses HTML form, but this was for testing) which I try to use to insert a new record, like: rec = Record() setattr(rec, col, val) This works if col is a string (the column subclassing SQLAlchemy's String) but not otherwise. I can identify the type of the column as coltype = rec.c[col].type I probably can write a big if elif else to identify the type and do the typecasting. e.g.: if isinstance(coltype, Integer): val = int(val) but is there a canonical solution already there? for parsing HTML form data into Python objects most people use FormEncode: http://formencode.org/ --~--~-~--~~~---~--~~ 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] Reverse Foreign Key
Hello, I have a medium sized python project that deals with SQLalchemy to talk to a database. The thing about this project is that it doesn't define a database layout itself. It is a project that you include in other projects to provide additional database functionality. One of the more important features is creating data entry forms for your tables (using Toscawidgets). Toscawidgets forms are expensive to create over and over, so we use a caching system to save execution time. This works great unless there is a foreign key in the table. Because the widget is cached, it is not privy to the new information unless it is reformed or explicitly told there is new information. Currently, the solution is to query the database every time the page is reloaded so that the widget stays current. I would like to convert to an event driven system (re-populate the widgets only when necessary) but have hit a snag. In order to update the correct widgets, I would need to create a list of tables that rely on the table being added to/updated/deleted from. I played around with the column objects but was unable to find a way to do what I needed. I thought about using the backref mapper-property system but not everybody defines them so I might miss a few fields doing it that way. if anybody has done something like this before or has any insight, I would appreciate it. --~--~-~--~~~---~--~~ 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: Migrate: want to take over the project ?
I am very interested in this, but I don't have time to take over the project. I would be willing to work with other people to help get it working with the latest version of SA. This project is very important to the codebase I am working on right now and IMHO this is a feature that SA needs to compete with other database tools. Has anyone taken a look at Evan's branch to see how close to complete it is? He said that it should remove all the monkey patching and doesn't require any changes to SA. If that is true it may not require too much work to finish it off. Where is the latest version of the code? It seems that: http://erosson.com/migrate/trac/ is down right now. -Allen PS. I cc'ed the SA group in case there are migrate users that are not on the new migrate mailing list. On Aug 3, 11:31 am, Michael Bayer [EMAIL PROTECTED] wrote: If someone is willing to step up and take over the project in earnest, producing a polished version either of Evan's branch or a new version, with plenty of unittests and documentation, as well as providing ongoing support, fixes, enhancements, I will integrate Migrate into SQLAlchemy itself where it will be available as part of the distribution, including whatever reasonable hooks are required within schema.py etc. to make it work the most smoothly. --~--~-~--~~~---~--~~ 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] Atomic update error
Hi!
Can somebody point me why this fails?
meta = MetaData(bind=sqlite:///:memory:)
blocks = Table('blocks', meta,
... Column('id', Integer, primary_key=True, autoincrement=True),
... Column('lines', Integer),
... Column('lastline', Integer),
... )
blocks.create()
blocks.update().execute(lines = blocks.c.lines + 1)
Traceback (most recent call last):
File stdin, line 1, in module
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.3.10-py2.5.egg/sqlalchemy/sql.py,
line 1207, in execute
return self.compile(bind=self.bind,
parameters=compile_params).execute(*multiparams, **params)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.3.10-py2.5.egg/sqlalchemy/sql.py,
line 1097, in execute
return e.execute_compiled(self, *multiparams, **params)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.3.10-py2.5.egg/sqlalchemy/engine/base.py,
line 780, in execute_compiled
return connection.execute_compiled(compiled, *multiparams, **params)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.3.10-py2.5.egg/sqlalchemy/engine/base.py,
line 568, in execute_compiled
self._execute_raw(context)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.3.10-py2.5.egg/sqlalchemy/engine/base.py,
line 581, in _execute_raw
self._execute(context)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.3.10-py2.5.egg/sqlalchemy/engine/base.py,
line 599, in _execute
raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (InterfaceError) Error binding parameter
0 - probably unsupported type. u'UPDATE blocks SET lines=blocks.lines +
?' [sqlalchemy.sql._BinaryExpression object at 0x847e20c]
Seems like a bug, but may be I'm doing something wrong?
--
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: Atomic update error
On Aug 6, 2007, at 3:22 PM, Paul Colomiets wrote:
Hi!
Can somebody point me why this fails?
meta = MetaData(bind=sqlite:///:memory:)
blocks = Table('blocks', meta,
... Column('id', Integer, primary_key=True, autoincrement=True),
... Column('lines', Integer),
... Column('lastline', Integer),
... )
blocks.create()
blocks.update().execute(lines = blocks.c.lines + 1)
cant put SQL expressions inside of execute(); those are literal bind
params only. put them in values:
blocks.update(values={'lines':blocks.c.lines+1}).execute()
or
blocks.update(values={blocks.c.lines:blocks.c.lines+1}).execute()
--~--~-~--~~~---~--~~
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 a non-string in sqlalchemy
On Aug 6, 5:35 pm, malkarouri [EMAIL PROTECTED] wrote: On Aug 6, 4:36 pm, Michael Bayer [EMAIL PROTECTED] wrote: for parsing HTML form data into Python objects most people use FormEncode: http://formencode.org/ Thanks a lot for the suggestion. FormEncode looks great and I will definitely be using it. Still, as I identify my object (column) classes at run-time, I need to find a mapping between sqlalchemy types and python types. I guess I will build a dictionary with the types I need. we do have a trac ticket for this idea, associating python types with SQL types, which we havent yet decided how we'd like to do. theres a bigger ticket for a rewrite of the types system which would probably address this issue. --~--~-~--~~~---~--~~ 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: Reverse Foreign Key
On Aug 6, 1:38 pm, Dave Marsh [EMAIL PROTECTED] wrote: Currently, the solution is to query the database every time the page is reloaded so that the widget stays current. I would like to convert to an event driven system (re-populate the widgets only when necessary) but have hit a snag. In order to update the correct widgets, I would need to create a list of tables that rely on the table being added to/updated/deleted from. I played around with the column objects but was unable to find a way to do what I needed. hi dave - its a little unclear to me what you're looking for exactly. Is it that you'd like to determine, given a Table, what other Tables are dependent on it via foreign keys ? An iterative approach through the MetaData object can be used to work up a dictionary of this information, if that's what you're looking for. - 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] *all* *new* *tutorials* !!!!
Hi gang - The documentation for 0.4 is undergoing tremendous changes, and is now released, in its almost-there format, at http://www.sqlalchemy.org/docs/04/ . The goal with these docs is not just to update to new 0.4 paradigms, but to also raise the bar for accuracy and clarity. Of major note is that the datamapping and sql construction sections, as well as the old tutorial, have been entirely replaced by two new and very comprehensive tutorials, one targeted at ORM and the other at SQL Expression Language. Both have no prerequisites to start, they each can be the first thing you ever read about SQLAlchemy. Both are also fully executable doctest format, so they are guaranteed not to have my usual array of mistakes. Also here is a rewritten mapper configuration document to replace advanced datamapping. It includes clearer, up-to-date, and more correct examples of virtually every major mapper pattern we have, including all the new stuff like dynamic relations. With recently updated engine and metadata sections, the only major section left is sessions, which already includes information about the new autoflush and transactional sessions, as well as two-phase and SAVEPOINT recipes...I hope to simplify some of the older content here as well as standardize on the new sessionmaker function and its cousin, scoped_session, which replaces SessionContext as well as assignmapper (both are deprecated in 0.4). I hope everyone can check out the docs, come back with feedback/ corrections/questions, and start getting ready for 0.4 ! - 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] Query and efficient on-demand loading of all rows
Hi, I am using sqlalchemy like this: entries = session.query(User) for entry in entries: entry.check_it_is_all_right() # includes changing values of columns if necessary session.flush() As you might have noticed, I am iterating over all rows in the database. Since there are like thousands rows, each containing like 10kB of data (and I _do_ need all of the data for each entry), it is unconvenient for me how sqlalchemy treats this: it loads _all_ objects into memory - this takes approx. 30 seconds on a dedicated machine with 100% CPU usage, taking away tens of MB of RAM. (it is my virtual testing machine, it's fine there (although who wants to wait 30 seconds to only realize that the processing fails with exception on first entry :-)), but I most probably can't use such a resource-eater on a production server). It would however suffice that sqlalchemy made the access to the result sequentially, without loading more than is really needed at the moment. I thought the Python iterator protocol (__iter__ and next methods) are exactly tailored for this efficient access (e.g. iterating over lines of file objects (for line in open(file.txt, r)) works exactly efficiently how I would want sqlalchemy to in my case :-) - Is there a way for efficient on-demand iteration over all rows when using orm object mapping, or do I have to use a more low-level protocol (like sqlalchemy without orm (fetchone()), or even Python DB API itself?) Thanks for your suggestions, Boris Duek --~--~-~--~~~---~--~~ 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: Query and efficient on-demand loading of all rows
On Aug 6, 2007, at 10:42 PM, Boris Duek wrote: Hi, I am using sqlalchemy like this: entries = session.query(User) for entry in entries: entry.check_it_is_all_right() # includes changing values of columns if necessary session.flush() It would however suffice that sqlalchemy made the access to the result sequentially, without loading more than is really needed at the moment. I thought the Python iterator protocol (__iter__ and next methods) are exactly tailored for this efficient access (e.g. iterating over lines of file objects (for line in open(file.txt, r)) works exactly efficiently how I would want sqlalchemy to in my case :-) - I will show you some ways to do it; however, yes, I think you should consider using SQL-level instead of ORM level results to do what you want. The 30 seconds of overhead youre experiencing is probably not due to memory consumption as it is due to the overhead ORM loads require in order to instantiate objects, initialize and populate their attributes, and also perform various decision-making with regards to extension hooks and the like. Additionally, SQLAlchemy does place some overhead on SQL-level results as well since we do things like decode utf-8 into unicode and similar result processing but this overhead is much smaller (but still not as fast as raw DBAPI). So first, my thoughts on a streaming Query object. When looking to stream results from a Query (which ultimately comes from its instances() method), you need to consider the ORM's behavior and design regarding sessions and units of work; when you load objects, the full result is stored within a session, with the assumption that youre going to manipulate and work with these objects. To iterate through pieces of data and not hold onto it means youd want to expunge as you load. There is no functionality built directly into query.instances() to achieve this right now - a major reason its difficult is because its not very easy to tell when an individual instance is fully loaded; many subsequent rows may apply to a single result instance as it loads related collections in due to a join. Also its problematic to ensure the uniqueness of instances for some kinds of queries, particularly those which eagerly load related items (object # 1 references object A, then is returned and expunged. object # 2 also references object A..but now you get a *different* instance of A since the previous copy of it was expunged. surprises ensue). So embedding this feature directly into Query I fear would lead to many confused users, who are trying to get a quick way to be more efficient without really understanding the consequences...and as I mentioned, i think the overhead is primarily just populating the objects themselves, not the memory allocation part of it, so this feature would probably not solve too many problems (not to mention the increased complexity would slow it down even more). Externally, the most straightforward way to achieve this with Query would be by using LIMIT and OFFSET (typically by applying array slices to a Query) such that you query only some results at a time: query = session.query(MyObject).filter(whatever) start = 0 while True: result = query[start:start + 100] process result session.clear() # or session.expunge() each member in 'result' if len(result) 100: break else: start += 100 The above approach would also be compatible with queries which uses eager loading, since the LIMIT/OFFSET is applied inside of a subquery (in the case of eager loads being present) so that eager LEFT OUTER JOINS are tacked onto the correct core rowset. But, the above approach issues many queries, namely number of rows / clump size. To work around issuing clumped queries with LIMIT/ OFFSET, here is a variant on that idea (not tested, may need adjusting), which uses just one SQL statement issued but is not compatible with eager loading (unless you really tweaked it): class FakeResult(object): def __init__(self, result): self.result = result def fetchall(self): Query.instances() calls fetchall() to retrieve results. return only a 'slice' of results. return result.fetchmany(100) q = session.query(MyObject).filter(whatever) result = FakeResult(engine.execute(q.compile())) while True: results = q.instances(result) process result session.clear() # or session.expunge() each member in 'result' if len(result) 100: break Is there a way for efficient on-demand iteration over all rows when using orm object mapping, or do I have to use a more low-level protocol (like sqlalchemy without orm (fetchone()), or even Python DB API itself?) So onto the raw SQL idea. So yes, if you can adjust your processing functions in
[sqlalchemy] Re: *all* *new* *tutorials* !!!!
Cool. thx Michael! On 8/7/07, Michael Bayer [EMAIL PROTECTED] wrote: Hi gang - The documentation for 0.4 is undergoing tremendous changes, and is now released, in its almost-there format, at http://www.sqlalchemy.org/docs/04/ . The goal with these docs is not just to update to new 0.4 paradigms, but to also raise the bar for accuracy and clarity. Of major note is that the datamapping and sql construction sections, as well as the old tutorial, have been entirely replaced by two new and very comprehensive tutorials, one targeted at ORM and the other at SQL Expression Language. Both have no prerequisites to start, they each can be the first thing you ever read about SQLAlchemy. Both are also fully executable doctest format, so they are guaranteed not to have my usual array of mistakes. Also here is a rewritten mapper configuration document to replace advanced datamapping. It includes clearer, up-to-date, and more correct examples of virtually every major mapper pattern we have, including all the new stuff like dynamic relations. With recently updated engine and metadata sections, the only major section left is sessions, which already includes information about the new autoflush and transactional sessions, as well as two-phase and SAVEPOINT recipes...I hope to simplify some of the older content here as well as standardize on the new sessionmaker function and its cousin, scoped_session, which replaces SessionContext as well as assignmapper (both are deprecated in 0.4). I hope everyone can check out the docs, come back with feedback/ corrections/questions, and start getting ready for 0.4 ! - mike -- Cheers, - A --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
