[sqlalchemy] Re: whats going to break in 0.4
Got it thanks jose Michael Bayer wrote: On Jul 4, 7:30 pm, Jose Galvez [EMAIL PROTECTED] wrote: Thanks Michael, I went back and reread the Proposal thread and I finally get what scalar() does and how it is different form one(). but how would first() differ from scalar() and how would all() differ from list()? At first blush they look like they would return the same type of query object Jose list() and scalar() get deprecated and go away in 0.5. --~--~-~--~~~---~--~~ 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: Connection management in Sqlalchemy.
Yes, Is there a way to get to that chapter? Thanks, Sam. On Jul 4, 9:30 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jul 4, 10:22 pm, SamDonaldson [EMAIL PROTECTED] wrote: I want to use MetaData() to return an object passed in it is the db uri. I want the connection object returned back to me from the meta. meta = MetaData(db_uri) #getting the connection object conn = meta.connect -- is this correct Now, I want to start a transaction and then commit or rollback upon exception using the connection object versus using session.create_transaction. no, its not correct. first, read this paragraph: http://www.sqlalchemy.org/docs/tutorial.html#tutorial_twoinone and then, read this page: http://www.sqlalchemy.org/docs/dbengine.html that should answer everything youve asked thus far. then youll probably want to read these: http://www.sqlalchemy.org/docs/metadata.htmlhttp://www.sqlalchemy.org/docs/sqlconstruction.html and then that is it. do *not* read anything about data mapping, sessions, or units of work. ignore all the various tutorials on pylons and TG that are all ORM-centric. you do not need to use anything from sqlalchemy.orm. as yet another alternative, if you want *strictly* just a connection pool, and *absolutely nothing else* except regular old DBAPI, you can also read this: http://www.sqlalchemy.org/docs/pooling.html If that appeals to you, i.e. using totally straight DBAPI, learn about that style of programming via the PEP: http://www.python.org/dev/peps/pep-0249/ I wanted an in-depth understanding of what goes on in connection management in sqlalchemy. I want to use sqlalchemy for it's connection management but I'm planning on building my own custom orm. if you like, I can hook you up with chapter 6 of the SA book, the only chapter ive written so far, which is quite in depth about connection management. --~--~-~--~~~---~--~~ 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] sqlite func,datetime
take the following code with sqlite 3.4 on cygwin, and i get strftime error on insert, any ideas? Python 2.5.1 (r251:54863, May 18 2007, 16:56:43) [GCC 3.4.4 (cygming special, gdc 0.12, using dmd 0.125)] on cygwin Type help, copyright, credits or license for more information. from elixir import * from sqlalchemy.types import * from sqlalchemy import func, DynamicMetaData metadata.connect('sqlite:///mytest.db') class TestC(Entity): with_fields( name= Field(Unicode(50), nullable=False), created = Field(TIMESTAMP(timezone=True), default=func.datetime('now', 'localtime')) ) create_all() z = TestC(name=mytestname) objectstore.flush() == error == Traceback (most recent call last): File mytest.py, line 17, in module objectstore.flush() File /usr/lib/python2.5/site-packages/SQLAlchemy-0.3.9dev_r2844- py2.5.egg/sqlalchemy/orm/session.py, line 302, in flush self.uow.flush(self, objects) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.3.9dev_r2844- py2.5.egg/sqlalchemy/orm/unitofwork.py, line 210, in flush flush_context.execute() File /usr/lib/python2.5/site-packages/SQLAlchemy-0.3.9dev_r2844- py2.5.egg/sqlalchemy/orm/unitofwork.py, line 400, in execute UOWExecutor().execute(self, head) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.3.9dev_r2844- py2.5.egg/sqlalchemy/orm/unitofwork.py, line 1018, in execute self.execute_save_steps(trans, task) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.3.9dev_r2844- py2.5.egg/sqlalchemy/orm/unitofwork.py, line 1032, in execute_save_steps self.save_objects(trans, task) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.3.9dev_r2844- py2.5.egg/sqlalchemy/orm/unitofwork.py, line 1023, in save_objects task.mapper.save_obj(task.polymorphic_tosave_objects, trans) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.3.9dev_r2844- py2.5.egg/sqlalchemy/orm/mapper.py, line 1189, in save_obj c = connection.execute(statement, params) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.3.9dev_r2844- py2.5.egg/sqlalchemy/engine/base.py, line 520, in execute return Connection.executors[c](self, object, *multiparams, **params) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.3.9dev_r2844- py2.5.egg/sqlalchemy/engine/base.py, line 560, in execute_clauseelement return self.execute_compiled(elem.compile(dialect=self.dialect, parameters=param), *multiparams, **params) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.3.9dev_r2844- py2.5.egg/sqlalchemy/engine/base.py, line 570, in execute_compiled context.pre_exec() File /usr/lib/python2.5/site-packages/SQLAlchemy-0.3.9dev_r2844- py2.5.egg/sqlalchemy/engine/default.py, line 201, in pre_exec self.parameters = self._encode_param_keys(self.dialect.convert_compiled_para ms(self.compiled_parameters)) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.3.9dev_r2844- py2.5.egg/sqlalchemy/engine/default.py, line 120, in convert_compiled_params parameters = parameters.get_raw_list() File /usr/lib/python2.5/site-packages/SQLAlchemy-0.3.9dev_r2844- py2.5.egg/sqlalchemy/sql.py, line 858, in get_raw_list return [self.get_processed(key) for key in self.positional] File /usr/lib/python2.5/site-packages/SQLAlchemy-0.3.9dev_r2844- py2.5.egg/sqlalchemy/sql.py, line 839, in get_processed return bind.typeprocess(value, self.dialect) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.3.9dev_r2844- py2.5.egg/sqlalchemy/sql.py, line 1841, in typeprocess return self.type.dialect_impl(dialect).convert_bind_param(value, dialect) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.3.9dev_r2844- py2.5.egg/sqlalchemy/databases/sqlite.py, line 38, in convert_bind_param return value.strftime(self.__format__) AttributeError: 'unicode' object has no attribute 'strftime' --~--~-~--~~~---~--~~ 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: sqlite func,datetime
On Jul 5, 2007, at 6:00 AM, Nick wrote: class TestC(Entity): with_fields( name= Field(Unicode(50), nullable=False), created = Field(TIMESTAMP(timezone=True), default=func.datetime('now', 'localtime')) ) set up the func as func.datetime('now', 'localtime', type=TIMESTAMP) --~--~-~--~~~---~--~~ 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: whats going to break in 0.4
On Jul 5, 2007, at 1:29 AM, [EMAIL PROTECTED] wrote: the new interface is super clean, consistent and flexible. and with that, we are able to add more features onto it. a cluttered interface doesn't accept new functionalities as easily. There was one more difference betwen filter* and select* - first is just building a query, 2nd is building a query and executing it. Are u going to have .execute() just like the sql interface? it does make a lot of sense to have 2 _similar_ interfaces, for similar things, e.g. querying via orm and querying via sql. youd think so, but in this case its wrong. Query does something distinctly different from a SQL statement. hence removing the name overlap will help eliminate confusion over this. the closest analogy on the SQL statement would be to provide all(), one(), and first() methods. but the SQL statement returns ResultProxy which has fetchXXX semantics, so doesnt really fit so much. the reason theyre different is because the ResultProxy is a lot more flexible in its particular situation,which is that theres no class representing the structure of the row...so an index based, column- name-based, and column-object based indexing system makes more sense. --~--~-~--~~~---~--~~ 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: whats going to break in 0.4
On Jul 5, 10:52 am, Rick Morrison [EMAIL PROTECTED] wrote: But scalar() is useful on the SQL-API side for getting real scalar values like count(*) and etc. In this role, it functions as one would expect scalar() to do, getting a scalar value instead of a result set. ...or is it just the badly-named Query.scalar() that will be going away? Query.scalar() goes away, result.scalar() stays since yes, scalar is the best word there (first column of first row). --~--~-~--~~~---~--~~ 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: Connection management in Sqlalchemy.
Michael and others, It seems like, if I want to use Sqlalchemy's connection support, I could do the following: Everytime I need to connect to db: engine = create_engine(db_uri) connection = engine.contextual_connect() # I now have the connection object, so I can start my txn on writes to the db trans = connection.begin() # now execute the sql connection.execute(INSERT .) trans.commit() connection.close() Even though this is trivial, I wanted to make sure that calling create_engine repeatedly is not a problem for every time I want to do a dml on mysql? And, based on the docs, contextual_connect() should function the same for the default plain strategy and threadlocal, and it will always try to get a connection from the pool. Thanks for your help on this and if you can, could you send me chapter 6 of your book? Thanks, Sam. On Jul 4, 9:30 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jul 4, 10:22 pm, SamDonaldson [EMAIL PROTECTED] wrote: I want to use MetaData() to return an object passed in it is the db uri. I want the connection object returned back to me from the meta. meta = MetaData(db_uri) #getting the connection object conn = meta.connect -- is this correct Now, I want to start a transaction and then commit or rollback upon exception using the connection object versus using session.create_transaction. no, its not correct. first, read this paragraph: http://www.sqlalchemy.org/docs/tutorial.html#tutorial_twoinone and then, read this page: http://www.sqlalchemy.org/docs/dbengine.html that should answer everything youve asked thus far. then youll probably want to read these: http://www.sqlalchemy.org/docs/metadata.htmlhttp://www.sqlalchemy.org/docs/sqlconstruction.html and then that is it. do *not* read anything about data mapping, sessions, or units of work. ignore all the various tutorials on pylons and TG that are all ORM-centric. you do not need to use anything from sqlalchemy.orm. as yet another alternative, if you want *strictly* just a connection pool, and *absolutely nothing else* except regular old DBAPI, you can also read this: http://www.sqlalchemy.org/docs/pooling.html If that appeals to you, i.e. using totally straight DBAPI, learn about that style of programming via the PEP: http://www.python.org/dev/peps/pep-0249/ I wanted an in-depth understanding of what goes on in connection management in sqlalchemy. I want to use sqlalchemy for it's connection management but I'm planning on building my own custom orm. if you like, I can hook you up with chapter 6 of the SA book, the only chapter ive written so far, which is quite in depth about connection management. --~--~-~--~~~---~--~~ 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: Connection management in Sqlalchemy.
On Jul 5, 1:17 pm, SamDonaldson [EMAIL PROTECTED] wrote: engine = create_engine(db_uri) connection = engine.contextual_connect() # I now have the connection object, so I can start my txn on writes to the db trans = connection.begin() # now execute the sql connection.execute(INSERT .) trans.commit() connection.close() Even though this is trivial, I wanted to make sure that calling create_engine repeatedly is not a problem for every time I want to do a dml on mysql? And, based on the docs, contextual_connect() should function the same for the default plain strategy and threadlocal, and it will always try to get a connection from the pool. heres from that dbengines.html page you read (emphasis added): The Engine will ask the connection pool for a connection when a SQL statement is executed. The default connection pool, QueuePool as well as the default SQLite connection pool SingletonThreadPool, will open connections to the database on an as-needed basis. As concurrent statements are executed, QueuePool will grow its pool of connections to a default size of five, and will allow a default overflow of ten. ***Since the Engine is essentially home base for the connection pool, it follows that you should keep a single Engine per database established within an application, rather than creating a new one for each connection.*** Thanks for your help on this and if you can, could you send me chapter 6 of your book? i sent you an invite to a google group where you can get a view of it. chapters will also be publically downloadable as we write them in a few months. --~--~-~--~~~---~--~~ 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: whats going to break in 0.4
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Jul 3, 2007, at 8:14 PM, jason kirtland wrote: Barry wrote: I have exactly the same architecture and exactly the same question. In fact, my goal in Mailman 3 is to be able to let sites configure the system to use any supported database backend, just by tweaking the configuration variable that specifies the engine url. We'll ship with SQLite, but it would be awesome if I didn't have to do anything else to 'automatically' support PostgreSQL or MySQL, etc. Although I haven't tried it with these other backends, it currently works great with alternative SQLite database file locations (such as the tempfile one I use during a test suite run). The regular MetaData gives you this configuration flexibility for a given installation. Cool. If you want to support simultaneous and distinct 'configurations' within a threaded process, each with its own set of database tables (possibly mixing backends as well), then a DMD is perfect. Every thread connects the DMD to the engine of its choice before work starts. In a Mailman context I could imagine a single fat worker process at an ISP that serviced lots of domains, each owned by a different user with separate data storage. Possibly, although I'm not thinking about that level of division. Once thing I /would/ like to be able to do is to connect to different databases for each 'storage domain' within a single process (Mailman itself will continue to be single threaded). What I mean by that is that Mailman has at least three separate related collections of data: mailing lists, users, and messages. It should be possible to put each of those three in separate databases using three different engine urls. The classic use case is this: say my user database lived in my web application, but that web app was separate from the mailing list system, and the data for the lists lived in a separate database. It should be possible for Mailman to get list configuration data from database B and user data from the web app's database A. Similarly, you might want to put the message storage in database C, say the one that your archiver used. Of course, this means that you can't design your data model to have foreign keys across these three storages, but that's not hard, though you have to manage consistency at the application layer. I'm not sure such a design is actually feasible with SQLAlchemy though; I think it wasn't back when I actually tried to do this ages ago. - -Barry -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (Darwin) iQCVAwUBRo1mu3EjvBPtnXfVAQLI1AP9EaNt/rlYtcwix/PEPU2OyETBlIKGwIhv g/QeKoMonD0kYwGuF7Y1RS+3xGch7UQAqDE5z1bU7bKJEZoBUQaUkhtqVSRgB5rX Bii8icIX3iephIgSIcPixLJ+V3yv2FcUE1JOjvD8puudMXQvF8WmN046OqnRAuXb m1ACR5gxysQ= =i0ep -END PGP SIGNATURE- --~--~-~--~~~---~--~~ 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: whats going to break in 0.4
On Jul 5, 5:46 pm, Barry Warsaw [EMAIL PROTECTED] wrote: What I mean by that is that Mailman has at least three separate related collections of data: mailing lists, users, and messages. It should be possible to put each of those three in separate databases using three different engine urls. The classic use case is this: say my user database lived in my web application, but that web app was separate from the mailing list system, and the data for the lists lived in a separate database. It should be possible for Mailman to get list configuration data from database B and user data from the web app's database A. Similarly, you might want to put the message storage in database C, say the one that your archiver used. Of course, this means that you can't design your data model to have foreign keys across these three storages, but that's not hard, though you have to manage consistency at the application layer. I'm not sure such a design is actually feasible with SQLAlchemy though; I think it wasn't back when I actually tried to do this ages ago. its quite feasable, particularly since you are separating concerns at the table/class level (as opposed to the row level, which is the sharding thing ive been talking about). there are three general approaches to this: 1. use three separate MetaData objects, each bound to their appropriate engine. 2. dont bind your MetaData. use an explicit Connection for every operation and ensure you use the right engine/connection for the particular tables you're dealing with. 3. similar to #2, if your app is ORM centric, build your own create_session() function which, after creating a new session, uses session.bind_mapper() and/or session.bind_table() to associate the each mapper or underlying table with its appropriate engine (or connection), then return the session. in all cases, when using ORM, you just cant have any eager loads across databases, obviously (since it uses JOIN). you can also combine all three methods together. setting up binds in the session explcitly will override any metadata-level binds, but things you dont bind will fall back to whats on the table's metadata. (see the docstring for session.get_bind() here: http://www.sqlalchemy.org/docs/sqlalchemy_orm_session.html#docstrings_sqlalchemy.orm.session_Session ) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---