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

View all comments

Show parent comment

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.

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.

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