Slashdot Mirror

The last time I tinkered with Django, I tried writing a simple CRUD app for an existing database at my job. After wasting a few hours learning how the ORM package works and setting up my models, I came to realize that Django does not support database tables with composite primary keys . In other words, it doesn't work with almost any legacy database that you will find in any enterprise shop on the planet. It wasn't worth re-working the schema, so I had to retreat back to Spring MVC and wrestle its XML.

I'll give Django credit, though. It's still several steps ahead of Ruby on Rails, which my last experiment taught me doesn't support more than one database per app !

I make a point to take one these "dynamic language" frameworks for a spin every 6-12 months... because I keep hearing that they are leapfrogging Java, and Oracle is destroying the universe anyway, and my career will be over soon if I don't migrate my skillset. So far I'm just not seeing it, though. These frameworks may be useful for simple non-commerce apps, stood up in a hurry by junior-level devs... but none of them seem remotely ready for any of the realities I deal with day-to-day when working for large companies or handling commerce.

I haven't used any of the frameworks reviewed since where I work we have our own Python web framework. However, the thing that immediately struck me when reading the reviews is that several of the web frameworks have there own database abstraction libraries and/or ORMs, which seems wrong. Why should the DB access be tied to a web framework? I'd rather choose a framework that's good at the web stuff and a framework that's good at the DB stuff and not have one come with the other. I've been dabbling with SQLAlchemy which does support composite primary and foreign keys, unlike the ORM I have to use every day (much to my annoyance) and is not tied to any web framework.

While I'd like to it not be the case, I'd have to agree with you about the general not-quite-there-yet state of dynamic frameworks. That said, Django's custom ORM leaves much to be desired. Next time you decide to give a python framework a try, pick one which uses SQLAlchemy as it's ORM layer. You'll find it to be a much more sophisticated library (similar to Java's Hibernate). In particular, it has all the features you just mentioned. Not integrating SQLAlchemy is one of the main things that keeps me from using Django... any other ORM layer in Python seems doomed to play catch-up.

I don't know whether that's a DBAPI feature or a psycopg2 extension, but I tested that code (adapted for my own PostgreSQL table) and verified that it works exactly like you'd expect it to.

The second example was SQLAlchemy, my preferred way of never having to write another line of SQL. I know I can write complex, efficient queries if I want to. I also know that I'd rather let the computer figure it out for me. :-)

I'm not sure about C++, but for python and sqlite I'd recommend SQLAlchemy. You can set up mappings directly from the database tables to your python classes, maintaining the type information.

There's bound to be similar sql libraries available for C++.

> Does Python have something equivalent?

Take a look at SQLAlchemy. You'll cry at how primitive ActiveRecord is by comparison. If you really need to get your ActiveRecord pattern on (it's the name of a pattern, not a product), there's always the much less powerful, but much simpler and still popular SQLObject.

Then there's Django's ORM, GeniuSQL, Storm, Axiom...

Staying OT with you, I do not agree with your opinion on SQL in general, although using it for basic CRUD in an object-oriented application is tedious and bug-prone. For that, I warmly recommend the up-and-coming Python-based ORM called SQLAlchemy. See: http://www.sqlalchemy.org/.

Relational databases will be around as long as humans generate relational data. Take the classic example of an invoice that may have many entries, each entry referencing an inventory item. This sort of thing is likely to exist forever, and RDBMSes model that pretty well.

As far as whether the backend is row- or column-oriented - who cares? As long as I can use the one most appropriate to my access pattern, the implementation details just don't interest me enough to get worked into a furor. Don't get me wrong - I think that there are some neat developments in the works - it's just that I don't have a strong general preference on how my information is physically laid down on the platters.

A more interesting question for me is whether SQL is obsolete. For the most part, I'd say that it is in the sense that most people need never use it directly. We use SQLAlchemy instead of writing raw SQL, and the Java folks seem to be fond of Hibernate. I still look at the generated queries sometimes to convince myself that it's sane or for debugging or optimization purposes, but if they inserted a new middle layer between Alchemy and PostgreSQL that used something completely different, our code wouldn't notice the difference.

How does Storm compare to SQLAlchemy in terms of features?

For beginners register_globals can create nasty suprises, but for those who know that it is on and write the code accordingly, I don't see what is the problem with register_globals being enabled.

Most PHP programmers ARE beginners, so it creates many many unnecessary nasty surprizes. And for experts, it does not give you anything that you can't already do another way. It adds no real value to the language for experts (of which there are few), and totally screws beginners (of which there are many). Now please explain why you're apologizing for PHP and making excuses for why register_globals is still part of the language after all these years everyone knew it was wrong? I ask again: why did they make such a horrible mistake in the first place?

One of the biggest problems currently with PHP is the lack of good database module with support for multiple different database engines with things like prepared statements.

That's ironic, because people often repeat the myth that PHP makes it easy to interface with the database. Easy but deadly. The reality that PHP has horrible database handling is what's causing PHP programmers to defect to Ruby on Rails (which has ActiveRecord) in droves. Zend tried to imitate ActiveRecord but that resulted in the ZActiveRecord Boondoggle. Python has some excellent database interfaces and object-relation mappers, like SQLObject and SQLAlchemy, which beat the pants of anything PHP has to offer.

-Don

If PHP is so great at supporting databases, then why did Zend give up on their ZActiveRecord Boondoggle, which wouldn't work as they falsely advertised because of a stupid PHP bug they refused to fix? Why isn't there anything like ActiveRecord for PHP? Because it's impossible, because of a bug that came about because the PHP developers didn't understand object oriented programming when they tried to imitate Java's class system.

Python has no trouble implementing ActiveRecord-like ORMs, and even has much more advanced SQL database toolkits than ActiveRecord. What does PHP have that compares with Python's SQLAlchemy?

-Don

Not only is the PHP development team negligent when it comes to dealing with security bugs, but they're incompetent when it comes to understanding the limitations of their own language, the tenants of object oriented programming, and database object-relational mapper design.

Here is quick summary of my previous post about Zend's ZActiveRecord Boondoggle:

The creators of PHP are morons, and their support company Zend is dishonest and incompetent. The ZActiveRecord boondoggle demonstrates exactly what I mean: They can't program their way out of a paper bag, an don't even understand the limitations of the very language that they haphazardly "designed".

To summarize: [Criticism of PHP "references".] [Criticism of PHP "object system".] [Description of "static methods".] [Description of Ruby on Rail's ActiveRecord.] [Description of "ZActiveRecord", Zend's lame half-baked cargo cult attempt to ape ActiveRecord.] [Zend busted for misleading screencast.] [Blog posting that proves: ZActiveRecord Can't Work.] [Zend misunderstood their own language's object system, showed fake code in a screencast that could not possibly work (because of a PHP bug), and made promises of ZActiveRecord they couldn't keep, because of that stupid bug they refused to fix.] [Longstanding bug that PHP static methods that the developers refuse to fix.] [Summary of comments on the bug that prevents anyone from writing an ActiveRecord-like ORM in PHP.] [Final word from andi, calls it expected behavior, demonstrating how he misunderstands object oriented programming, and casually dismisses all the bug report's valid comments.] [Zend gets hoisted on their own pitard by this very bug.]

And here's the final word from andi: "Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.php.net/manual/ [php.net] and the instructions on how to report a bug at http://bugs.php.net/how-to-report.php [php.net] This is expected behavior. self:: binds statically to its class (to the best of my knowledge other languages like C++ don't support this either as they require to explicitly use the class name). There are actually advantages also to this approach as they allow you to protect and encapsulate functinality."

Since Zend's dishonest and embarassing public-relations disaster with the ZActiveRecord Boondoggle, Zend has quietly withdrawn their plans for ZActiveRecord, instead of fixing the stupid bug that made it impossible to implement and ORM like ActiveRecord in PHP.

It's not just security that the PHP developers and Zend don't care about. They also don't care about nor understand object oriented programming, nor database object-relational mapping.

There is absolutely no reason to use PHP, when there are great languages like Python with extremely powerful, easy to use, safe and secure, database independent, object-relational mappers like SQLObject , and intelligent scalable SQL toolkits with multiple modular ORMs like SQLAlchemy. That stuff so blows away PHP's horribly insecure string-concatination approach to SQL, and all of the kludgy PHP database libraries people have cobbled together over the years.

-Don

Django doesn't use SQLObject. You can, as a programmer, use it of course, since everything in Django is nicely decoupled, but Django uses it's own ORM. The main advantage of using this ORM is of course the production ready administration interface that you get for free, but if you don't need that, there is nothing stoping you from using SQLObject or even better, SQLAlchemy. In fact, there is even a branch in Django to have SQLAlchemy support in the framework.

Using a different ORM, template engine, etc in Django is just a import statement away.

Absolutely right. The effort required to "fix" PHP is gargantuan (and futile) compared to the effort of simply learning a better language that doesn't need to be "fixed". It's ridiculous to put any more effort into PHP, when so many excellent alternatives are immediately available.

Monolinguistic newbie PHP apologists: There is nothing "special" about PHP. The feeling of power it gives you is from the COMPUTER, not the language. Every other programming language gives you the EXACT same feeling of power. PHP is not any easier to learn than most other modern languages, in fact it's much harder to learn to deal with all of its nuances, quirks, bugs, limitations, and work around all of its design flaws, which you don't have to worry about with better languages like Ruby and Python, which are both extremely easy to learn and use. PHP's simplicity is an ILLUSION, and its flaws are a FACT.

Case in point: Ruby's ActiveRecord and Python's SQLAlchemy make it MUCH EASIER and also MUCH SAFER to access SQL databases, than anything PHP has. And that's not because nobody's bothered to write a decent ORM in PHP: they have, and they failed miserably. If you think you can do a better job than Zend's ZActiveRecord Boondoggle, then go ahead and try, but even the charlitans who DESIGNED PHP were so overconfident in its abilities and broadsided by its limitations and design flaws that they didn't even understand themselves before they tried and failed.

-Don

PHP can't force to to write good code, but it certainly forces you to write bad code.

Case in point: Zend's ZActiveRecord Boondoggle. PHP's object model is so broken that it can't support an ORM as simple and easy to use as Ruby's ActiveRecord, let alone one as powerful and flexible as Python's SQLAlchemy.

How do PHP's "numerous other data abstraction layers" compare to ActiveRecord and SQLAlchemy? Why can't Zend themself figure out how to implement their own version of ActiveRecord? It's because a fatal flaw in the design of the PHP language, that's why.

It's no wonder so many people are abandoning PHP for better languages like Ruby and Python.

-Don

If I didn't use and program in php, then I wouldn't know first hand what was so horrible about it. You're not qualified to complain about a language (or anything else) if you don't know what's wrong with it from first hand experience.

Back to the discussion: So which database interface do you use in PHP, and how is it better and more secure than SQLAlchemy or ActiveRecord?

-Don

[I posted this earlier in the context of PostgreSQL Slammed by PHP Creator, but it bears repeating, since the charlitans at Zend still haven't addressed the problem, and NEVER WILL. Would anyone from Zend please finally comment, and explain just how PHP's plan for a database solution is better and more secure than Python's SQLAlchemy? -Don]

The creators of PHP are morons, and their support company Zend is dishonest and incompetent. The ZActiveRecord boondoggle demonstrates exactly what I mean: They can't program their way out of a paper bag, an don't even understand the limitations of the very language that they haphazardly "designed".

It makes me laugh that Lerdorf would slam Postgres, because the PHP designers have no understanding of object oriented programming or databases: instead they invent half baked cargo-cult designs, which are naive reactions to other systems they don't understand: they try to ape their surface features without understanding the reasons behind the way they're designed.

PHP references were thrown in as a band-aid to work around the horrible design flaw that arrays and objects were foolishly DEEP COPIED by default. If you pass or return an array from function to function, its contents are DEEP COPIED, which is EXTREMELY inefficient and leads to all kinds of horrible bugs because it's the last thing a sane programmer would expect. So instead of fixing the design flaw in PHP, they add "references" that LOOK and SOUND like C++ references, but actually are completely different, again misleading programmers into thinking they understand what's going on, but working totally differently than a sane person would expect. PHP references are actually half baked symbol table references. The sloppy implementation caused many bugs that CORE DUMP PHP! PHP references were so poorly thought out and badly designed, that there were many edge conditions that they hadn't considered, that simply didn't work together, caused memory leaks and core dumps, and had useless and confusing semantics: callers passing references, functions declaring that they take references, functions returning references, etc. Compare that to C++'s simple and consistent definition of references in term of pointers. The only way to make a PHP reference to an object is to put it in a variable -- you can't make a reference to a field of an object or the return value of a function without storing it in a temporary variable -- totally unlike C++, and totally stupid.

PHP's object oriented programming system is a half-baked imitation of C++'s object model, haphazardly designed by charlitans who had no clue about the fundamentals of object oriented programming, elegant language design or efficient implementation. First of all, if you're going to try to imitate an existing design without understanding it, then for god's sake, at least imitate a language whose object system doesn't suck, and a language that has similar semantics to the language you're trying to kludge. C++ is a static compiled language, and its object system deeply reflects that fact. (That is to say, there's very little reflection beyond RTTI, because the compiler throws all the interesting stuff away! And C++'s oop design had to make many horrible compromises because the C++ object system was designed to map directly into C se

Since you're wise enough to dump PHP and switch to Python, and you're looking for a good way to do SQL, then you should definitely check out SQLAlchema. It's light years ahead of Ruby's "ActiveRecord", plus it has several different front-ends that simplify it and make it as easy to use as Ruby's ActiveRecord and Python's SQLObject, while still allowing you to use its much more advanced features.

Not only is it much more powerful than Ruby's ActiveRecord (which is causing people to abandon PHP in droves), but SQLAlchemy is astronomically better than anything PHP has, and PHP will never have anything that even approaches ActiveRecord because of foolish bugs and design flaws that the charlitans on the PHP team stubbornly refuse to fix. (As described in detail in Zend's ZActiveRecord Boondoggle.)

-Don

SQLAlchemy: The Python SQL Toolkit and Object Relational Mapper

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

It provides a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.

SQLALCHEMY'S PHILOSOPHY

SQL databases behave less and less like object collections the more size and performance start to matter; object collections behave less and less like tables and rows the more abstraction starts to matter. SQLAlchemy aims to accommodate both of these principles.

SQLAlchemy doesn't view databases as just collections of tables; it sees them as relational algebra engines. Its object relational mapper enables classes to be mapped against the database in more than one way. SQL constructs don't just select from just tables--you can also select from joins, subqueries, and unions. Thus database relationships and domain object models can be cleanly decoupled from the beginning, allowing both sides to develop to their full potential.

Scales Up

Powerful enough for complicated tasks, such as:

* Eager-load a graph of objects and their dependencies via joins
* Map recursive adjacency structures automatically
* Map objects to not just tables but to any arbitrary join or select statement
* Combine multiple tables together to load whole sets of otherwise unrelated objects from a single result set
* Commit entire graphs of object changes in one step

Scales Down

Extremely easy to use for all the basic tasks, such as:

* Constructing SQL from Python expressions
* Pooling database connections
* Loading objects from the database and saving changes back

DBA Approved

Built to conform to what DBAs demand, including the ability to swap out generated SQL with hand-optimized statements, full usage of bind parameters for all literal values, fully transactionalized and batched database writes using the Unit of Work pattern.

Highly Modular

Different parts of SQLAlchemy can be used independently of the rest. You can use the connection pool by itself and deal with raw connections; or you can use the SQL construction language by itself, either in direct conjunction with one or more database connections or as standalone constructs which return their string-compiled contents. While SQLAlchemy has a great ORM provided, the other parts have no dependency on it; its usage is completely optional. Simpler facades for the ORM can be used as well, such as the ActiveMapper and SqlSoup extension modules. SQLAlchemy is architected in an open style that allows plenty of customization, supporting user-defined datatypes, custom SQL extensions, and a plugin system which can augment or extend any functionality including SQL expressions an

Since you're wise enough to dump PHP and switch to Python, and you're looking for a good way to do SQL, then you should definitely check out SQLAlchema. It's light years ahead of Ruby's "ActiveRecord", plus it has several different front-ends that simplify it and make it as easy to use as Ruby's ActiveRecord and Python's SQLObject, while still allowing you to use its much more advanced features.

Not only is it much more powerful than Ruby's ActiveRecord (which is causing people to abandon PHP in droves), but SQLAlchemy is astronomically better than anything PHP has, and PHP will never have anything that even approaches ActiveRecord because of foolish bugs and design flaws that the charlitans on the PHP team stubbornly refuse to fix. (As described in detail in Zend's ZActiveRecord Boondoggle.)

-Don

SQLAlchemy: The Python SQL Toolkit and Object Relational Mapper

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

It provides a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.

SQLALCHEMY'S PHILOSOPHY

SQL databases behave less and less like object collections the more size and performance start to matter; object collections behave less and less like tables and rows the more abstraction starts to matter. SQLAlchemy aims to accommodate both of these principles.

SQLAlchemy doesn't view databases as just collections of tables; it sees them as relational algebra engines. Its object relational mapper enables classes to be mapped against the database in more than one way. SQL constructs don't just select from just tables--you can also select from joins, subqueries, and unions. Thus database relationships and domain object models can be cleanly decoupled from the beginning, allowing both sides to develop to their full potential.

Scales Up

Powerful enough for complicated tasks, such as:

* Eager-load a graph of objects and their dependencies via joins
* Map recursive adjacency structures automatically
* Map objects to not just tables but to any arbitrary join or select statement
* Combine multiple tables together to load whole sets of otherwise unrelated objects from a single result set
* Commit entire graphs of object changes in one step

Scales Down

Extremely easy to use for all the basic tasks, such as:

* Constructing SQL from Python expressions
* Pooling database connections
* Loading objects from the database and saving changes back

DBA Approved

Built to conform to what DBAs demand, including the ability to swap out generated SQL with hand-optimized statements, full usage of bind parameters for all literal values, fully transactionalized and batched database writes using the Unit of Work pattern.

Highly Modular

Different parts of SQLAlchemy can be used independently of the rest. You can use the connection pool by itself and deal with raw connections; or you can use the SQL construction language by itself, either in direct conjunction with one or more database connections or as standalone constructs which return their string-compiled contents. While SQLAlchemy has a great ORM provided, the other parts have no dependency on it; its usage is completely optional. Simpler facades for the ORM can be used as well, such as the ActiveMapper and SqlSoup extension modules. SQLAlchemy is architected in an open style that allows plenty of customization, supporting user-defined datatypes, custom SQL extensions, and a plugin system which can augment or extend any functionality including SQL expressions an

One of the most appealing aspects of Turbogears imho is the work to supersede the db layer, currently SQLObject, with SQLAlchemy. The problem with most web frameworks is that their ORM db layers treat the db as just an object store, rather than as a relational database. This works for relatively simple 37signals-esque websites, but not for more complicated sites that require a relational algebra engine, for example any relativley complicated web-based BI application or booking engine. For example, one of the fundamental features of the relational model is that the result of any query against a table or number of tables is another table. In that respect, SQLAlchemy lets you map classes not just to tables, but also to joins, unions, and subqueries (which are themselves tables). Turbogears is the only framework I know of so far that is attempting to integrate SQLAlchemy. They're still in the relatively early stages of the integration, it seems, but are working to provide an easy migration from SQLObject to SQLAlchemy when the latter is ready, making TurboGears top on my list of webframeworks to consider/recommend for new projects.

One excellent Python project that doesn't get enough publicity is my favorite ORM tool by Michael Bayer - SqlAlchemy http://sqlalchemy.org/. Think Rail's ActiveRecord with Hibernate power.

There was some talk of integrating it with TurboGears to create some competition for SqlObject. Not sure where this stands currently.

after reading the docs etc. it looks like yet another "Ruby on rails is popular, let's make something!" which is nowhere near finished

It's not, both are extremely different from one another (and even more since the Django guys went the magic_removal route). And while Django is not finished (nor is rails) it's very much production ready.

why are there so many drivers? There shouldn't be a need for much more than one for each database - and e.g. in the case of PostgreSQL, that should be the bundled one. pyPgSQL, PyGreSQL, Psycopg, PoPy, Proboscis.... and I'm sure I forgot some. The perl DBI interface was more pleasant to use almost 10 years ago than the python DB interface is today. JDBC also got it right. A de-facto standard python ORM would be nice too.

I hear you, that's probably the biggest issue with the Python community, this "No tool does exactly what I want, so I'll just build a new one, no point in extending an existing one" attitude.

Brings some interresting stuff to the table though (have you tried SQL Alchemy in your Quest For An ORM?)

Another Python issue is that the community can hardly bring itself to standardize things, and even then people usually try to build even more tools (sometimes better though, see py.test versus STL's unittest, or effbot's ElementTree which will finally be part of the STL with Python 2.5 versus the whole xml package crapfest)

• Turbogears. Turbogears may become the choice for webapps with more complicated database requirements, eg apps that require a relational algebra engine rather than a simple object store, given their ongoing work to integrate the SQLAlchemy DB layer.
• Django also looks good
• web.py for a lightweight framework.

I think that TG is all around more Pythonic than Django. It incorporates many different successful Python projects, whereas Django rolled their own (Templates, ORM, Controller, etc.). Not that that's a bad thing, but TG does enjoy quite a few benefits by building on other successful projects (more eyes looking over the code, more people interested, more developers). Also, the TurboGears development process was opened a lot earlier than Django, so there is more community involvement, more developers, and more activity, even though it has been around for less time.

Some of TG's key features like the Toolbox, Internationalization (adm18n), Model Designer / Browser, Identity, template plugins, etc. are a direct result of close community involvement. On the Django side, their "Magic Removal" branch is a result of late community involvement, which is only now "removing warts that Django has accumulated over the years". Similarly, Django devleopers have said their architecture is loosely coupled, but some things (e.g. the templating system) are still not easily swappable. TG definitely is designed to work closely with the main components, but some developers prefer different things, so there is a layer of support for different templates (Cheetah, Stan, ZPT, etc.), and recent work has been done to get an SQLAlchemy compatibility layer for people who don't like SQLObject.

Either way, 2006 will be the year of Python web frameworks (I would even go as far as saying that Python will leapfrog the RoR hype), with TG and Django both experiencing a lot of maturing as they reach 1.0 and beyond. Also, recent developments in WSGI and standardization between Python web frameworks means it will be easy to switch between Django/TG/etc and build apps out of reusable pieces, no matter what framework they were originally written in.