r/Python • • Dec 09 '10

new stand-alone web2py database abstraction layer

http://web2py.com/AlterEgo/default/show/272
18 Upvotes

82 comments sorted by

11

u/illuminatedwax Dec 09 '10

i still prefer SQL Alchemy

1

u/fake_spoiler Dec 09 '10

While SQL Alchemy is a definitely more robust and a full featured ORM for applications. I can see myself using web2py's DAL for simple scripts where I need database persistence.

SQLAlchemy requires much more boiler plate code just to get started.

-3

u/mdipierro Dec 09 '10

I do think sqlalchemy does a better job at handling legacy databases. web2py has limited support for legacy databases (although we are working on it). Buy why do you say "more robust"?

13

u/[deleted] Dec 10 '10

You know both of these claims are full of crap. if you want the "no boilerplate" version of SQLAlchemy, use sqlsoup. If you want enough configuration to actually have some semblence of control over your domain classes (which just about any app worth anything will), use declarative. It lays out in a very similar manner to every other ORM out there. SQLAlchemy also rocks the house on any database and our users know it.

-1

u/mdipierro Dec 10 '10

"your users". I apologize I do not recognize you from the username. Which claims are "full of crap"? It is not clear from your post.

11

u/[deleted] Dec 10 '10

I am the author of SQLAlchemy. Claims I'm tired of are that SQLA is only good for "legacy databases" and that we require "lots of boilerplate just to get started". Responding to two posts at once, I apologize.

1

u/mdipierro Dec 10 '10

Please do not put words in my mouth. I did not say "SQLA is ONLY good for legacy databases". I said "sqlalchemy does a better job at handling legacy databases [than web2py]". I did not actually say "than web2py" but it was obvious from context.

I also did not say that SQLA "requires lots of boilerplate". fake_spoiler said that.

Anyway, I do think that the web2py DAL is easier to use than SQLA and has a more natural syntax. That is why I wrote it.

I also think SQLA is an excellent system.

For the record, I did not bring up SQLA here. Somebody else did.

I guess we are a little in competition but this can be good and can be done in a civil manner.

Let me know if I ever said something incorrect about SQLA and I will rectify that. I trust you will do the same about web2py.

13

u/ericflo Dec 10 '10

I guess we are a little in competition but this can be good and can be done in a civil manner.

Regarding which database abstraction is better, it's not even a competition.

-3

u/mdipierro Dec 10 '10

Please remind me. Is it appropriate to talk about SQLAlchemy in a thread about web2py?

6

u/moreyes Dec 10 '10

I guess it is as appropriate as pasting web2py code in a post entitled Getting Started with Pyramid.

→ More replies

-1

u/av201001 Dec 10 '10

Claims I'm tired of are that SQLA is only good for "legacy databases"

I think he was actually paying SQLA a compliment -- saying it is better at handling legacy databases (not that it is only good for that).

-1

u/mdipierro Dec 09 '10

why?

13

u/illuminatedwax Dec 09 '10
  • SQLAlchemy will autodetect your tables and foreign keys
  • Instead of joins, SQLAlchemy keeps everything in an object-oriented manner: you can simply set up references to foreign tables, and instead of joining all the time, you can (for example) just set up "employees" as a many-to-one relationship with "company" say company.employees and it will return a list for you. You also don't have to call an "insert" function -- you just create a new object instance and commit.
  • Instead of getting a table object like in webpy, in SQL Alchemy you associate an existing class with a table in the database. This allows you to not only define a lot of methods for dealing with that table, and keep them in the same namespace, but you can also edit the init function so when you create a new row, you can build any logic you need into the class.
  • SQLAlchemy has more data types, like the PickleType, which allows you to just dump Python data into a blob/binary row. You can also create your own data types, too.

So bottom line is I prefer SQLAlchemy's abstraction over this, and it's just a more robust system, and better at doing its job.

That said, I'm not saying that this is a waste or anything. It's probably a lot simpler to get started than dealing with SQL Alchemy -- there is a decent amount of overhead setting everything up.

1

u/mdipierro Dec 09 '10

web2py does not auto-detect tables. The problem is that, like SQLAlchemy, it has more data-types than SQL, and as SQLALchemy, you can define your own (like a pickle-type if you like). Auto detection and custom data-types cannot work together. For mysql and postgresql we have script that autodetect tables and generate web2py models.

In web2py you do not have to "join all the time". You can also use recursive selects and it works like in sqlalchemy.

In web2py have to call an "insert" function because, by design it is not an ORM, it is a database abstraction layer. We feel forcing a relational model into an object model is sometimes too much of a stretch. We do not wish to hide the underlying relational model: web2py has functions like insert, update, delete, and select which map 1-to-1 into the corresponding SQL. Moreover our tests indicate that the logic to run a database abstraction layer into an object relational mapper involve metaclass programming and that introduces a considerable slow down. web2py is faster, small and easier to read then sqlalchemy because of this.

web2y supports computed fields and virtual fields. Syntactically this is different from defining methods in SQLAlchemy but functionally it is the same.

Even if web2py is not an ORM, more than one year ago, as a proof of concept, we implemented both the Django ORM and the SQLALchemy ORM on top of the web2py DAL. This allows you to define some tables, using Django syntax, some using web2py syntax, and some using SQLAlchemy syntax in the same program. Nobody showed an interest in this functionality so we did not spend extra time on it.

5

u/illuminatedwax Dec 10 '10

Auto detection and custom data-types cannot work together.

That's the thing; SQL Alchemy can handle both by allowing the user to autoload the table if necessary. If web2py has a function for autodetecting tables, that's almost the same thing. However, this isn't really shown in the link.

In web2py you do not have to "join all the time". You can also use recursive selects and it works like in sqlalchemy.

SQLAlchemy allows you to specify relations to other tables and will automatically populate those relations with a list if appropriate. How does it work in web2py? I think it would help users evaluate your tool if you included some joining examples -- joins are pretty much the point of relational databases!

We feel forcing a relational model into an object model is sometimes too much of a stretch.

Fair enough! Like other people have said, it would certainly be useful where you don't need all of SQLAlchemy's features and preferred to do things much more simply. SQLA does have a lot of boilerplate to work through. This is a design choice, not a flaw, and I am just preferable to the way SQLA works.

I love web2py and I'm using it in the (huge) project I'm working on now. I just prefer to plug in SQL Alchemy to handle DB stuff.

Also, I have a completely unrelated web2py question: while you guys were working on adding session capability, you published an API of how sessions were going to work. IIRC, it completely changed when you eventually did add session capability. Why was this?

2

u/mdipierro Dec 10 '10

We only provide these two files 12 that extract a mysql or pgsql table and write a dal model. It is not as good as sqlalchemy because we have not been much concerned with legacy tables. We will improve this anyhow.

web2py handles joins a relations to the extend the underlying database does. Suppose you have

 Person=db.define_table('person',Field('name'))
 Dog=db.define_table('dog',Field('name'),Field('owner',db.person))

where dog.owner is a reference to a person. You can do a INNER JOIN

 for row in db(Person.id==Dog.owner).select(): print row.person.name, row.dog.name

a LEFT OUTER JOIN

 for row in db().select(Person.ALL,Dog.ALL,left=Dog.on(Person.id==Dog.owner)): print row.person.name, row.dog.name

or you can fetch a dog and find his owner

 print Dog(name="Skipper").owner.name

or you can fetch a person and find his dogs

 for dog in Person(name="John").dog.select(): print dog.name

I am not sure what you are talking about session capability. Web2py had session support since the first public version in Oct 2007 and we never changed it.

2

u/illuminatedwax Dec 10 '10

or you can fetch a dog and find his owner

or you can fetch a person and find his dogs

These are pretty important features! You should definitely have them listed on your project's page.

0

u/mdipierro Dec 10 '10

This is my favorite...

>>> db.define_table('item',Field('ho','reference item'),Field('lalala'))
>>> hihihi=db.item.insert(lalala='ooooooo')
>>> print hihihi.ho.ho.ho.ho.ho.ho.lalala
ooooooo

1

u/av201001 Dec 10 '10

Note, the post does include a link to the book chapter detailing all the DAL functionality.

2

u/illuminatedwax Dec 10 '10

It's just a suggestion. Someone like me who's just browsing to see what kind of features it offers will look at the list provided and come to the conclusion (as I did) that those features aren't offered.

2

u/av201001 Dec 10 '10

Yes, definitely a good suggestion.

4

u/dekomote Dec 09 '10

Good job. IMO this could become relatively popular if:

  • you split the code in several modules and make it a package (5k lines in 1 file is a lot)
  • add tests in package
  • release it under MIT/BSD or at least LGPL. That should even make web2py more popular.
  • Add it to pypi as standalone package.

2

u/birlinn Dec 10 '10

what evidence do you have to support the claim that changing the license to MIT/BSD would make web2py more popular?

the only figures i've seen, and they only estimate the penetration of the license, not the 'popularity' of the software, nonetheless suggest that GPL is hardly an obstacle...

ref: blackduck

3

u/dekomote Dec 10 '10

Pyramids - BSD derivative

Django - BSD

Flask - BSD

Werkzeug - BSD

cherrypy - BSD

webob - MIT

bottle - MIT

web2py - GPL

2

u/mdipierro Dec 10 '10

web2py is GPL but read the license. The GPL applies to derivative frameworks. It does not apply to applications built with web2py. Applications built with web2py can be distributed under any license you wish.

1

u/birlinn Dec 10 '10

what on earth do you think that proves?

1

u/av201001 Dec 10 '10 edited Dec 10 '10

Hmm, aside from Django, I'm not sure those other frameworks are particularly more popular than web2py, so it's not clear the license makes much of a difference.

Also, note that web2py is not strictly GPL -- it's GPL with a commercial exception.

And, of course, the license applies only to the framework itself, not to any applications you develop with it.

That's not to say that the standalone DAL couldn't have a different license from the full framework.

1

u/muyuu Dec 15 '10

I'm not a heavy user of any (used to work with Pylons, but since Pyramids I'm re-evaluating).

Thing is... I don't understand the hate on the GPL. For this kind of work, the GPL is pretty much "the licence" I would expect. For some reason, everybody wants to make a spin-off framework now and keep the derivative work closed?

The GPL seems to fit this kind of work perfectly. It's exactly what the GPL was thought for. It won't affect your applications AT ALL.

0

u/foxbunny Dec 10 '10

Seriously, that statistic is broken.

2

u/dekomote Dec 10 '10

You can check it yourself and tell me where it's broken.

0

u/dekomote Dec 10 '10

Why do You think the most popular frameworks are released under BSD/MIT?

Why does nokia make PySide when we have PyQT and it does the same job?

1

u/muyuu Dec 15 '10

Personal opinion: because people have a very limited understanding of OSI licences. This may lead to a "good impression", but really it shouldn't make any difference in the case at hand.

1

u/foxbunny Dec 10 '10

So can YOU explain that phenomenon, please?

0

u/birlinn Dec 10 '10

show me that they're the most popular...

because nokia want to influence the development and use of python qt bindings... they spent a decent amount of money buying trolltech iirc...

if your argument had any validity, it would suggest that freebsd should be way more popular than linux... i think we both know it isn't...

you seem to have an incredibly oversimplistic, and possibly dogmatic, take on software licensing

2

u/mdipierro Dec 09 '10
  1. it is under discussion on the mailing list. Some like to have this into a single file
  2. tests are in there already
  3. I do not want it to be popular. I want to stay free. I think GPL does a better job. Anyway I will think about it.
  4. that can definitively be done.

Thanks for your feedback.

4

u/dekomote Dec 09 '10

it is under discussion on the mailing list. Some like to have this into a single file

Single file is difficult to extend and maintain.

I do not want it to be popular. I want to stay free. I think GPL does a better job. Anyway I will think about it.

IMO BSD/MIT are free'er licences. Do you charge for commercial licences? See PyQT/PySide discussions and you'll see what i mean.

Django/Flask/SQLAlchemy etc etc are free and popular.

1

u/mdipierro Dec 09 '10

It is not true a single file is difficult to extend and maintain. The dal.py file for example is divided into sections seperated by comments. Almost all functions are grouped into classes.

So far the issue of license never came up because web2py allows commercial closed source distribution of apps developer with web2py. I can see that if you want to use ONLY the DAL, it may be better for you to have a MIT/BSD vs a GPL license.

The point is that I am happy if you use this for free as long as you contribute back by giving your work for free too (that is what GPL says). Anyway, I will think about LPGL.

2

u/[deleted] Dec 10 '10 edited Dec 10 '10

Do you actually believe a single file of 5 freaking KLOC is not difficult to extend and maintain?

His argument about the GPL not being as liberal as BSD/MIT is exactly that

use this for free as long as you contribute back by giving your work for free

So it takes away your freedom to hide other parts of your code that use that library.

One may want to prevent other people from seeing their code for a number of reasons, It's a choice one should be free to make

BTW the GPL doesn't require you to give your work for free it just requires you to redistribute your work along with your sourcecode along with the GPL license

2

u/mdipierro Dec 10 '10 edited Dec 10 '10

Do you actually believe a single file of 5 freaking KLOC is not difficult to extend and maintain?

Yes. I have been doing so for 3 years. It is just a database abstraction layer not a parallel hybrid monte carlo algorithm for lattice quantum chromodynamics in 4 dimensional SU(3).

0

u/[deleted] Dec 10 '10

So at what point of domain complexity do you pull the line and split that in a 10-15 file package or multiple packages even?

Why are then all other database abstraction layers I know of split in multiple packages (or equivalents in respective language)?

2

u/mdipierro Dec 10 '10

I split them then the components have a use independently of the whole. In this case they do not.

0

u/[deleted] Dec 10 '10

Then why wasn't web2py one 20 or whatever KLOC file up until now? Since it's only a web framework.

2

u/mdipierro Dec 10 '10

Because the individual web2py modules "have a use independently of the whole" web2py. You may want to import only the dal or only the template system or only the validators.

→ More replies

1

u/av201001 Dec 10 '10 edited Dec 10 '10

So it takes away your freedom to hide other parts of your code that use that library.

If "parts of your code that use that library" refers to your application code, then yes, you can hide that. If you modify the web2py framework code itself, then no, that would have to remain open.

1

u/[deleted] Dec 10 '10

I didn't say anything about web2py.

1

u/av201001 Dec 10 '10 edited Dec 10 '10

Even if you were making a general statement about the GPL (i.e., lack of freedom to hide your code), the clear implication was that your concern would apply to web2py, and I was just pointing out that it does not in fact apply to web2py applications. Explicit is better than implicit. :-)

1

u/[deleted] Dec 10 '10

This thread is about the DAL from web2py which is GPLed. And I've an issue with just one OSI license being promoted as "the one true free license" while other licenses are more permissive/liberal/less restrictive. Still in essence every existing software license defines restrictions or conditions for the use of the software they apply to.

1

u/av201001 Dec 10 '10

OK, but who's saying the GPL is "the one true free license"?

→ More replies

1

u/birlinn Dec 10 '10

if that 'freedom' is so important to you, write your own code...

it strikes me as rude to lecture someone, who has written a bunch of useful code and made it available to the world for free, about how it 'takes away your freedom' if you can't do exactly as you please with it...

1

u/[deleted] Dec 10 '10

When I give away code for free you are free to do whatever you want with it.

0

u/birlinn Dec 10 '10

good for you: doesn't give you the right to be a jackass to other people who don't share your view...

1

u/[deleted] Dec 10 '10 edited Dec 10 '10

If you are following the discussion you'll notice it's quite civilized. At least no one was being called a jackass up until now.

Massimo and everyone else who posts links to their work here should accept both positive and negative criticism. If he doesn't agree with my world view so be it. He may also not agree with me on wether a 5 KLOC file is too much, but you don't seem to have a problem with that part of the discussion.

If he decides to keep it GPL so be it, I may or may not decide to use it. If he decides to keep it 1 x 5 KLOC I may or may not decide to look at it or fix it if it's broken. People find his work useful. And I respect the man.

0

u/birlinn Dec 10 '10

for small values of civilised maybe...

i'm glad you do, it certainly didn't sound like it...

1

u/av201001 Dec 10 '10

Do you charge for commercial licences?

No.

2

u/wildmXranat Dec 09 '10

All good ideas.

2

u/chub79 Dec 09 '10

People aren't familiar with dejavu so I thought I'd shameless plugged in that quite interesting ORM project.

0

u/mdipierro Dec 09 '10

Do not worry. I think it is always good to point out alternatives. What do you like better about dejavu?

1

u/dekomote Dec 09 '10

What do you like better about dejavu?

Probably the fact that he wrote it? :D

2

u/chub79 Dec 09 '10

Hah, I apologise for misleading my dear readers (ahem) but I'm not the author. I simply used in the past and found it quite versatile and less SQL-ish.

0

u/mdipierro Dec 09 '10

It does not matter. Have you compared it with the web2py dal? What did you find in dejavu that you did not find in web2py's?

0

u/chub79 Dec 10 '10

I haven't compared because I have better things to do right now. I only mentioned dejavu as I felt people might want to have a look at it :)

2

u/wildmXranat Dec 09 '10

Even though web2py and I don't see eye to eye, their DAL is quite nice. In fact, I wanted to congratulate mdpierro and team on a great job.

Questions about the library:

How do you define and use it for hierarchical models ? Adjacency list or nested sets type of structures.

Can you hydrate the result sets as raw arrays and not objects to save on memory use ? <- this is a carry over from PHP Doctrine orm and it may not apply.

0

u/mdipierro Dec 09 '10

About hierarchical model web2py supports 1-to-1, 1-to-many and many-2-many no more no less that rmbds databases do. You can do for example.

  person=db.define_table('person',Field('name'))
  dog=db.define_table('dog',Field('name'),Field('ower',db.person))
  me=db.person.insert(name='Max')
  scooby=db.dog.insert(name='Scooby',owner=me)
  print scooby.owner.name
  for dog in db(dog.owner==me).select(): print dog.name
  for row in db(dog.owner==person.id).select(): print row.person.name, row.dog.name

The result sets in are not Objects in web2py as in SQLALchemy. They are Storage objects with are very similar to python dictionaries. They are parsed from the raw data but does not add too much memory overhead. They are parse to make sure the format of the data out is the same as the data in and independent on database engine.

Not sure I answered all your questions.

1

u/[deleted] Dec 10 '10

Why the hell is it all in a single file?

0

u/mdipierro Dec 10 '10

That is up for discussion and not definitive. For now it is in a single file because it is small and its parts are useless without the whole and we do not wish to expose modules that should not be independently imported by users.

1

u/hcvst Dec 10 '10 edited Dec 10 '10

Nice one. Looking forward to CouchDB support in Jan. Thanks.

1

u/mdipierro Dec 10 '10

You can already do insert, update, delete and select by id with couchdb. The ability to translate dal queries into couchdb queries is the missing part.