Skip to main content

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()






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?
Anonymous 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. :)
Anonymous 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
Anonymous 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.

Popular posts from this blog

Why schema definition belongs in the database

Earlier, I wrote about how ORM developers shouldn't try to re-invent SQL . It doesn't need to be done, and you're not likely to end up with an actual improvement. SQL may be designed by committee, but it's also been refined from thousands if not millions of man-years of database experience. The same applies to DDL. (Data Definition Langage -- the part of the SQL standard that deals with CREATE and ALTER.) Unfortunately, a number of Python ORMs are trying to replace DDL with a homegrown Python API. This is a Bad Thing. There are at least four reasons why: Standards compliance Completeness Maintainability Beauty Standards compliance SQL DDL is a standard. That means if you want something more sophisticated than Emacs, you can choose any of half a dozen modeling tools like ERwin or ER/Studio to generate and edit your DDL. The Python data definition APIs, by contrast, aren't even compatibile with other Python tools. You can't take a table definition

Python at Mozy.com

At my day job, I write code for a company called Berkeley Data Systems. (They found me through this blog, actually. It's been a good place to work.) Our first product is free online backup at mozy.com . Our second beta release was yesterday; the obvious problems have been fixed, so I feel reasonably good about blogging about it. Our back end, which is the most algorithmically complex part -- as opposed to fighting-Microsoft-APIs complex, as we have to in our desktop client -- is 90% in python with one C extension for speed. We (well, they, since I wasn't at the company at that point) initially chose Python for speed of development, and it's definitely fulfilled that expectation. (It's also lived up to its reputation for readability, in that the Python code has had 3 different developers -- in serial -- with very quick ramp-ups in each case. Python's succinctness and and one-obvious-way-to-do-it philosophy played a big part in this.) If you try it out, pleas

A review of 6 Python IDEs

(March 2006: you may also be interested the updated review I did for PyCon -- http://spyced.blogspot.com/2006/02/pycon-python-ide-review.html .) For September's meeting, the Utah Python User Group hosted an IDE shootout. 5 presenters reviewed 6 IDEs: PyDev 0.9.8.1 Eric3 3.7.1 Boa Constructor 0.4.4 BlackAdder 1.1 Komodo 3.1 Wing IDE 2.0.3 (The windows version was tested for all but Eric3, which was tested on Linux. Eric3 is based on Qt, which basically means you can't run it on Windows unless you've shelled out $$$ for a commerical Qt license, since there is no GPL version of Qt for Windows. Yes, there's Qt Free , but that's not exactly production-ready software.) Perhaps the most notable IDEs not included are SPE and DrPython. Alas, nobody had time to review these, but if you're looking for a free IDE perhaps you should include these in your search, because PyDev was the only one of the 3 free ones that we'd consider using. And if you aren