Re: Is there something easier than ORM?
一首诗 wrote: > Hi all, > > Recently I am studying some python ORM libraries, such as sqlalchemy. > > These are very powerful technologies to handle database. But I think > my project are not complicated to enough to benefit from a complete > ORM system. > > What I really want, is some easy ways to load data from database, and > change rows of data to list of named tuple, then I could send these > data to my client application. > > I don't think I want these subtle behavior such as lazy load, auto > update, ect. in ORM. > > So is there some libraries like that? > > Or is there some tools that could generate code from database scheme > as I want? If ORM is not used, you might have to contend with different tools for different databases - and will have to write SQL commands. Luckily everything follows DB-API2 properly. http://www.python.org/dev/peps/pep-0249/. So you mostly dont have to change the commands to access DB. For the different tools, this http://wiki.python.org/moin/DatabaseInterfaces might be a good starting point. For example - psycopg2 for postgresql is almost the default etc. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is there something easier than ORM?
一首诗 schrieb: Hi all, Recently I am studying some python ORM libraries, such as sqlalchemy. These are very powerful technologies to handle database. But I think my project are not complicated to enough to benefit from a complete ORM system. What I really want, is some easy ways to load data from database, and change rows of data to list of named tuple, then I could send these data to my client application. I don't think I want these subtle behavior such as lazy load, auto update, ect. in ORM. So is there some libraries like that? Or is there some tools that could generate code from database scheme as I want? Sqlalchemy. You don't need to use the ORM-layer, and you can use reflection to create schema-objects like tables. Then you can use that to create SQL-queries simple & powerful, whilst being DB-agnostic and having a road to start using the ORM if you discover it is useful for you. To be honest: if you can control the schema, I'd still go for an orm. I for example use elixir. It makes the easy things *really* easy, and the complicated ones ar still possible. Diez -- http://mail.python.org/mailman/listinfo/python-list
Re: Is there something easier than ORM?
On Feb 17, 1:27 pm, 一首诗 wrote: > Hi all, > > Recently I am studying some python ORM libraries, such as sqlalchemy. > > These are very powerful technologies to handle database. But I think > my project are not complicated to enough to benefit from a complete > ORM system. > > What I really want, is some easy ways to load data from database, and > change rows of data to list of named tuple, then I could send these > data to my client application. > > I don't think I want these subtle behavior such as lazy load, auto > update, ect. in ORM. > > So is there some libraries like that? > > Or is there some tools that could generate code from database scheme > as I want? I think there is room for a poor man toolkit, something in between SQLAlchemy and raw DB API. However, I am not aware of any, and for the moment I am using a custom made solution. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is there something easier than ORM?
Thanks for your reply. With sqlalchemy, an mapped must living in a session, you have no way to disconnect it with its session. For example : #- user = session.query(User).first() session.expunge(user) print user.name #Error here #- I just want to get an read-only copy of user disconnected with session to avoid unexpected database operation. But after expunge, properties of user is not accessible anymore. BTW : why you choose elixir instead of sqlalchemy's own schema definition style? Doesn't including another library means more chances of bugs? On Feb 17, 9:24 pm, "Diez B. Roggisch" wrote: > 一首诗 schrieb: > > > > > Hi all, > > > Recently I am studying some python ORM libraries, such as sqlalchemy. > > > These are very powerful technologies to handle database. But I think > > my project are not complicated to enough to benefit from a complete > > ORM system. > > > What I really want, is some easy ways to load data from database, and > > change rows of data to list of named tuple, then I could send these > > data to my client application. > > > I don't think I want these subtle behavior such as lazy load, auto > > update, ect. in ORM. > > > So is there some libraries like that? > > > Or is there some tools that could generate code from database scheme > > as I want? > > Sqlalchemy. You don't need to use the ORM-layer, and you can use > reflection to create schema-objects like tables. > > Then you can use that to create SQL-queries simple & powerful, whilst > being DB-agnostic and having a road to start using the ORM if you > discover it is useful for you. > > To be honest: if you can control the schema, I'd still go for an orm. I > for example use elixir. It makes the easy things *really* easy, and the > complicated ones ar still possible. > > Diez -- http://mail.python.org/mailman/listinfo/python-list
Re: Is there something easier than ORM?
On Feb 17, 8:15 am, 一首诗 wrote: > Thanks for your reply. > > With sqlalchemy, an mapped must living in a session, you have no way > to disconnect it with its session. > > For example : > > #- > user = session.query(User).first() > session.expunge(user) > print user.name #Error here > #- > > I just want to get an read-only copy of user disconnected with session > to avoid unexpected database operation. > But after expunge, properties of user is not accessible anymore. If you don't want any unexpected database operations, don't call flush () or commit() or just call rollback() BEFORE you do any real operations. There is a good sqlalchemy mailing list where even the developers hang out and answer questions. I'm sure they could point you in the right direction too. Mike > > BTW : why you choose elixir instead of sqlalchemy's own schema > definition style? > Doesn't including another library means more chances of bugs? > > On Feb 17, 9:24 pm, "Diez B. Roggisch" wrote: > > > 一首诗 schrieb: > > > > Hi all, > > > > Recently I am studying some python ORM libraries, such as sqlalchemy. > > > > These are very powerful technologies to handle database. But I think > > > my project are not complicated to enough to benefit from a complete > > > ORM system. > > > > What I really want, is some easy ways to load data from database, and > > > change rows of data to list of named tuple, then I could send these > > > data to my client application. > > > > I don't think I want these subtle behavior such as lazy load, auto > > > update, ect. in ORM. > > > > So is there some libraries like that? > > > > Or is there some tools that could generate code from database scheme > > > as I want? > > > Sqlalchemy. You don't need to use the ORM-layer, and you can use > > reflection to create schema-objects like tables. > > > Then you can use that to create SQL-queries simple & powerful, whilst > > being DB-agnostic and having a road to start using the ORM if you > > discover it is useful for you. > > > To be honest: if you can control the schema, I'd still go for an orm. I > > for example use elixir. It makes the easy things *really* easy, and the > > complicated ones ar still possible. > > > Diez -- http://mail.python.org/mailman/listinfo/python-list
Re: Is there something easier than ORM?
On Tue, 2009-02-17 at 06:15 -0800, 一首诗 wrote: > Thanks for your reply. > > With sqlalchemy, an mapped must living in a session, you have no way > to disconnect it with its session. > > For example : > > #- > user = session.query(User).first() > session.expunge(user) > print user.name #Error here > #- > > I just want to get an read-only copy of user disconnected with session > to avoid unexpected database operation. > But after expunge, properties of user is not accessible anymore. For that, the Right Thing to Do(tm) is to log in to your session with read-only permissions. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is there something easier than ORM?
On Feb 17, 2009, at 7:27 AM, 一首诗 wrote: Hi all, Recently I am studying some python ORM libraries, such as sqlalchemy. These are very powerful technologies to handle database. But I think my project are not complicated to enough to benefit from a complete ORM system. What I really want, is some easy ways to load data from database, and change rows of data to list of named tuple, then I could send these data to my client application. I don't think I want these subtle behavior such as lazy load, auto update, ect. in ORM. So is there some libraries like that? Or is there some tools that could generate code from database scheme as I want? As others have suggested, there are lower-level libraries for database access, like the sqlite library included with Python >= 2.5. The API wrapper I'm most familiar with (psycopg2) has some nice features like returning Postgres text as Python strings and Postgres ints as Python ints and Postgres arrays as Python lists. It can also return rows as dictionaries keyed by column names. You get some nice things for free. I read someone's advice that one doesn't need an ORM for simple projects but they become more useful as projects grow in complexity. I'm not so sure of that. I felt like the opposite could well be true -- the limitations inherent in a general purpose tool like an ORM are less likely to be a problem in a small, simple project than a larger one. In short, I gather that others on this list are a lot more fond of SqlAlchemy and ORMs in general than I am. Granted, my experience is very limited. I tried to integrate SqlAlchemy in one project, struggled for a long time to express how I wanted my tables joined, and finally found that performance was bad compared to our homegrown SQL. My boss and I were both very comfortable with SQL and were happy to go back to writing our own SQL statements and coding a data access layer. I don't intend this as a criticism of SqlAlchemy. On the contrary I am impressed by what it does. But I often see people promoting ORM as the solution to all database access problems, and I certainly don't feel like it is. Good luck, Philip -- http://mail.python.org/mailman/listinfo/python-list
Re: Is there something easier than ORM?
Philip Semanchuk wrote: [... snip comments on SqlAlchemy which could likewise apply to other similar offerings ...] I don't intend this as a criticism of SqlAlchemy. On the contrary I am impressed by what it does. But I often see people promoting ORM as the solution to all database access problems, and I certainly don't feel like it is. I'm with you: I'm very glad that so many people find SA (and Storm and Mother & SQLObject etc. useful). Personally, I earn my living designing, maintaining and coding for relational databases and I'm simply more at home in native SQL. If you're on Windows, pyodbc offers you a general spread of databases and has quite a few conveniences such as named rows returned; it's fairly robust and is actively maintained. I imagine that most people who do this kind of thing long ago put together a simple wrapper module (mine's imaginatively called "sql") which does just enough to be useful but then gets out the way. All that said, if you don't *want* to have to think about SQL then something like SA + Elixir is about as useful a combination as I've found. TJG -- http://mail.python.org/mailman/listinfo/python-list
Re: Is there something easier than ORM?
Philip Semanchuk wrote: > In short, I gather that others on this list are a lot more fond of > SqlAlchemy and ORMs in general than I am. Granted, my experience is > very limited. I tried to integrate SqlAlchemy in one project, > struggled for a long time to express how I wanted my tables joined, > and finally found that performance was bad compared to our homegrown > SQL. My boss and I were both very comfortable with SQL and were happy > to go back to writing our own SQL statements and coding a data access > layer. > > I don't intend this as a criticism of SqlAlchemy. On the contrary I am > impressed by what it does. But I often see people promoting ORM as the > solution to all database access problems, and I certainly don't feel > like it is. the reason i, at least, like sqlalchemy so much is for exactly the reasons you outlined. unlike other orm solutions it doesn't force you to use orm. you can also use sql directly - either as simple strings or by constructing it via python methods (which can be a very powerful way of programatically constructing sql commands that would be a nightmare to write by hand). that gives you the flexibility to deal with each problem in the way that feels most natural, without having to switch between tools (in fact, i have mixed orm and "direct" sql in a single project with no problems using sqlalchemy - reading from one database using sql and writing to another using objects). andrew -- http://mail.python.org/mailman/listinfo/python-list
Re: Is there something easier than ORM?
On 2009-02-17 10:52, andrew cooke wrote: Philip Semanchuk wrote: In short, I gather that others on this list are a lot more fond of SqlAlchemy and ORMs in general than I am. Granted, my experience is very limited. I tried to integrate SqlAlchemy in one project, struggled for a long time to express how I wanted my tables joined, and finally found that performance was bad compared to our homegrown SQL. My boss and I were both very comfortable with SQL and were happy to go back to writing our own SQL statements and coding a data access layer. I don't intend this as a criticism of SqlAlchemy. On the contrary I am impressed by what it does. But I often see people promoting ORM as the solution to all database access problems, and I certainly don't feel like it is. the reason i, at least, like sqlalchemy so much is for exactly the reasons you outlined. unlike other orm solutions it doesn't force you to use orm. you can also use sql directly - either as simple strings or by constructing it via python methods (which can be a very powerful way of programatically constructing sql commands that would be a nightmare to write by hand). that gives you the flexibility to deal with each problem in the way that feels most natural, without having to switch between tools (in fact, i have mixed orm and "direct" sql in a single project with no problems using sqlalchemy - reading from one database using sql and writing to another using objects). Me, too! I have often used SQLAlchemy as "a better DB-API" even if I don't touch its ORM. I really don't know what I would have done in my past projects without SQLAlchemy's table reflection and metadata introspection capabilities. It does suffer some from TMTOWTDI, but there's usually a valid use case floating around somewhere for each WTDI. Its public image definitely suffers from the impression that it's "an ORM" that can be compared on equal terms with packages that actually are just ORMs. I describe it as a very powerful toolkit for solving a wide variety of problems involving SQL databases. One of those tools happens to be an ORM. -- Robert Kern "I have come to believe that the whole world is an enigma, a harmless enigma that is made terrible by our own mad attempt to interpret it as though it had an underlying truth." -- Umberto Eco -- http://mail.python.org/mailman/listinfo/python-list
Re: Is there something easier than ORM?
> So is there some libraries like that? I always use a DB-API implementation for the database I use, i.e. psycopg/psycopg2. Named tuples are really easy to provide: class NamedTuple: def __init__(self, names, values): for name, value in izip(names, values): setattr(self, name, value) person_rows = "first last age".split() def get_person(conn, id): conn.execute("select first, last, age from person where id=%d" % (id,)) return NamedTuple(person_rows, conn.fetchone()) Regards, Martin -- http://mail.python.org/mailman/listinfo/python-list
Re: Is there something easier than ORM?
On Feb 18, 3:10 am, Robert Kern wrote: > Its public image definitely suffers from the impression that it's "an ORM" > that > can be compared on equal terms with packages that actually are just ORMs. I > describe it as a very powerful toolkit for solving a wide variety of problems > involving SQL databases. One of those tools happens to be an ORM. I'm going to have to steal that description the next time I try to sell a co-worker on the value of SQLAlchemy. There's always a strong reaction against the mention of ORMs, generally along the lines of it moving the programmer too far away from the real action. But my experience is identical to both andrew's and your's; there is far far more of value in SQLA than the ORM alone. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is there something easier than ORM?
On Feb 17, 5:35 pm, Philip Semanchuk wrote: > > I don't intend this as a criticism of SqlAlchemy. On the contrary I am > impressed by what it does. But I often see people promoting ORM as the > solution to all database access problems, and I certainly don't feel > like it is. I am also not a big fan of ORM, especially in situations where you have performance issues and you are using database specific features. In such situations you don't care about portability, but you care about having your SQL explicit, so that you can run it directly under the profiler. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is there something easier than ORM?
On 2009-02-17 13:27, 一首诗 wrote: > Hi all, > > Recently I am studying some python ORM libraries, such as sqlalchemy. > > These are very powerful technologies to handle database. But I think > my project are not complicated to enough to benefit from a complete > ORM system. > > What I really want, is some easy ways to load data from database, and > change rows of data to list of named tuple, then I could send these > data to my client application. > > I don't think I want these subtle behavior such as lazy load, auto > update, ect. in ORM. > > So is there some libraries like that? Python has a DB-API standard for direct access to databases using a very simple cursor-based approach: http://www.python.org/dev/peps/pep-0249/ All the DB-API compatible modules provide such an interface: http://wiki.python.org/moin/DatabaseInterfaces > Or is there some tools that could generate code from database scheme > as I want? I am not aware of an automated tool for generating SQL queries in form of Python functions, but it is certainly possible to write one by tapping into the system tables of the database of your choice. Our database tools mxODBC and mxODBC Connect come with a set of catalog methods that make such introspection very easy across databases and platforms: http://www.egenix.com/products/python/mxODBC/ http://www.egenix.com/products/python/mxODBCConnect/ -- Marc-Andre Lemburg eGenix.com Professional Python Services directly from the Source (#1, Feb 18 2009) >>> Python/Zope Consulting and Support ...http://www.egenix.com/ >>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/ >>> mxODBC, mxDateTime, mxTextTools ...http://python.egenix.com/ ::: Try our new mxODBC.Connect Python Database Interface for free ! eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48 D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg Registered at Amtsgericht Duesseldorf: HRB 46611 http://www.egenix.com/company/contact/ -- http://mail.python.org/mailman/listinfo/python-list
Re: Is there something easier than ORM?
On Feb 17, 10:28 pm, alex23 wrote: > On Feb 18, 3:10 am, Robert Kern wrote: > > > Its public image definitely suffers from the impression that it's "an ORM" > > that > > can be compared on equal terms with packages that actually are just ORMs. I > > describe it as a very powerful toolkit for solving a wide variety of > > problems > > involving SQL databases. One of those tools happens to be an ORM. > > I'm going to have to steal that description the next time I try to > sell a co-worker on the value of SQLAlchemy. There's always a strong > reaction against the mention of ORMs, generally along the lines of it > moving the programmer too far away from the real action. But my > experience is identical to both andrew's and your's; there is far far > more of value in SQLA than the ORM alone. I just saw this thread via the weekly Python URL email and wanted to add one bit here. When I've been selling people on using SQLAlchemy, one argument that I make is that if you're using a relational database for storage but your program is using objects (and good Python programs do!), then you're doing ORM. If you're not using SQLAlchemy (or similar), you're likely doing ORM badly. SQLAlchemy's SQL layer definitely makes it a different beast from most ORMs. Kevin -- http://mail.python.org/mailman/listinfo/python-list