Monday, April 10, 2006

Introducing SqlSoup

[Update Oct 2006: here is another serving of SqlSoup. Update 2: SqlSoup documentation is now part of the SQLAlchemy wiki.]
Ian Bicking wrote in Towards PHP that a successful Python PHP-killer (as Spyce aspires to be) will need to include a simple data-access tool that Just Works.
I had two thoughts:
  1. He's absolutely right
  2. I could do this with SqlAlchemy in an afternoon
My afternoons are in short supply these days, and it took two of them, counting the documentation. But it's live now, as sqlalchemy.ext.sqlsoup. (The 0.1.5 release includes a docless version of sqlsoup; I recommend the subversion code until Mike rolls a new official release.)
SqlSoup is NOT an ORM per se, although naturally it plays nicely with SqlAlchemy. SqlSoup inspects your database and reflects its contents with no other work required; in particular, no model definitions are necessary.
Here's what SqlSoup looks like, given pyweboff-ish tables of users, books, and loans (SQL to set up this example is included in the test code, but I won't repeat it here):
>>> from sqlalchemy.ext.sqlsoup import SqlSoup
>>> db = SqlSoup('sqlite:///:memory:')

>>> users = db.users.select()
>>> users.sort()
>>> users
[Class_Users(name='Bhargan Basepair',email='basepair@example.edu',password='basepair',classname=None,admin=1),
Class_Users(name='Joe Student',email='student@example.edu',password='student',classname=None,admin=0)]
Of course, letting the database do the sort is better (".c" is short for ".columns"):
>>> db.users.select(order_by=[db.users.c.name])
Field access is intuitive:
>>> users[0].email
u'basepair@example.edu'
Of course, you don't want to load all users very often. Let's add a WHERE clause. Let's also switch the order_by to DESC while we're at it.
>>> from sqlalchemy import or_, and_, desc
>>> where = or_(db.users.c.name=='Bhargan Basepair', db.users.c.email=='student@example.edu')
>>> db.users.select(where, order_by=[desc(db.users.c.name)])
[MappedUsers(name='Joe Student',email='student@example.edu',password='student',classname=None,admin=0), MappedUsers(name='Bhargan Basepair',email='basepair@example.edu',password='basepair',classname=None,admin=1)]
You can also use the select...by methods if you're querying on a single column. This allows using keyword arguments as column names:
>>> db.users.selectone_by(name='Bhargan Basepair')
MappedUsers(name='Bhargan Basepair',email='basepair@example.edu',password='basepair',classname=None,admin=1)
All the SqlAlchemy mapper select variants (select, select_by, selectone, selectone_by, selectfirst, selectfirst_by) are available. See the SqlAlchemy documentation (sql construction and data mapping) for details.
Modifying objects is also intuitive:
>>> user = _
>>> user.email = 'basepair+nospam@example.edu'
>>> db.flush()
(SqlSoup leverages the sophisticated SqlAlchemy unit-of-work code, so multiple updates to a single object will be turned into a single UPDATE statement when you flush.)
To finish covering the basics, let's insert a new loan, then delete it:
>>> book_id = db.books.selectfirst(db.books.c.title=='Regional Variation in Moss').id
>>> db.loans.insert(book_id=book_id, user_name=user.name)
MappedLoans(book_id=2,user_name='Bhargan Basepair',loan_date=None)
>>> db.flush()

>>> loan = db.loans.selectone_by(book_id=2, user_name='Bhargan Basepair')
>>> db.delete(loan)
>>> db.flush()






12 comments:

Anonymous said...

I hate to say this, but there is one thing that Just Works. It's a plain SQL (well, not exactly plain, but dbapi2 is ok). When I use it I don't need to translate in my mind between ORM and SQL, forth and back all the time.

After all, what you do with ORM is relational queries, just written other way. So, the question is: why write it other way when we already have SQL?

Martin said...

Well said! I completely agree. Nothing can replace the data modeling capabilities of SQL. There is a good reason why SQL is a mini-language rather than a simple API, it's powerful!

One problem I've found when using dbapi2 is that it is a tiny bit inconvenient to create CRUD queries because of the different ordering for different queries (e.g. update vs. insert) and to decode and access the results nicely. So I wrote a simple module that eases that task and provides an ORM-like interface, but without any of the magic. It's simple, and nothing happens behind your back, no caching, no nothing.

Check it out, released it last week:
http://furius.ca/antiorm/

Jonathan Ellis said...

Regular readers of this blog know that I'm the first to praise SQL for the things it gets right.

That doesn't, however, mean that raw SQL is always or even often the most convenient way to access your database from Python.

It's true that many if not most ORM tools are designed by people who don't understand databases well, and hence get a lot of things wrong. SqlAlchemy isn't one of these. Remember that in a few years when hand-coding your sql gets old. :)

foot said...

This is an awesome lil tool!

When you're browsing round a database checking out scheme's etc SQL is the go, but ORMish things like these make writing little python scripts which update some fields here and there heaps easier.

Also just noting: came across a little bug (i think), when using a mySQL db and the table has a mediumint field (which is mysql specific i think), sqlsoup says that the table doesn't exist... though this might be a problem further up the line (MySQLDB python interface), i'm not sure. Make all your mediumints normal ints and everything works!

Jonathan Ellis said...

Glad you like it.

Yeah, a lot of mysql weird-isms aren't supported yet by SA and hence SqlSoup. If you create a ticket with a test case at sqlalchemy.org/trac/, we'll fix it.

Anonymous said...

Well, SQL sucks for constructing complicated queries. sqlalchemy shines at stuff that does a random number of joins depending upon it's parameters; dealing with a number of SQL aspects like quoting, and so on.

Put bluntly, when I think about that API function that does that, I'm getting headaches. Trying to imagine doing this API method in plain SQL (aka string manipulation in Python) is beyond my scale for pain.

yacc

Anonymous said...

I agree Sql just plain works. But for a lot of newbies its hard to take it all at once. I been working with SQL for along time and still learning new ways to manipulate my queries. But, I remember those days when i was struggling with it... a tool like this would have been great. It makes sql look more like python. so new users learning python will probably find this an easy way to store and retrieve their data. Which I think was the point. But, for simple stuff, I find my self using it a lot. It saves time for simple stuff. I think it is a great tool.

n1ywb said...

The point to SqlAlchemy/SqlSoup isn't to replace SQL. The point is to develop SQL based programs FASTER and make them more MAINTAINABLE. A retard should be able to see how much less stupid repetitive error-prone boilerplate code is in an SqlSoup based program. If you NEED the phenmonial cosmic power of raw SQL, hey use it, it's available through the SqlAlchemy API. If you insist on doing everything by hand, great, I hope your clients find out that I can do the same work in a fraction of a time.

vak said...

great extention according to a description. can't get it fullly working though.

when I add "db._metadata.reflect()" it seem to do the essential part...

Maybe the doc is out-of-date?

vak said...

Also, after doing "db._metadata.reflect()" all column names are readed as unicode. and it is good so! the problem is, that "db.mycolumnname" is then rejected:

File "build/bdist.linux-i686/egg/sqlalchemy/ext/sqlsoup.py", line 422, in class_for_table
TypeError: type() argument 1 must be string, not unicode

Simon said...

I like this extension very much! nice work but just a little question, how do we do to close completly connection?
I'm using it on the net but always have a lot of sleep connection that appear... Thanks a lot for answering... and sorry for my bad english!

Jonathan Ellis said...

A two year old blog entry is not where you should look for tech support. :)

Briefly, sqlsoup uses the SA connection pool, so it's normal to see idle connections in a long-running process.

If you need more details, you should ask on the SA mailing list.