Simon Willison’s Weblog

grant XXX on * ? (via) PostgreSQL doesn’t have a way to say “this user is allowed to select/update/etc on all tables in database X”. That kind of sucks. UPDATE: This is fixed in PostgreSQL 9, see the comments. # 16th March 2010, 6:26 pm

django-batch-select (via) A smart attempt at solving select_related for many-to-many relationships in Django. Add a custom manager to your model and call e.g. Entry.objects.all()[:10].batch_select(“tags”) to execute two queries—one pulling back the first ten entries and another using an “IN” query against the tags table to pull back all of the tags for those entries in one go. # 23rd November 2009, 4:19 pm

Mandelbrot set in PostgreSQL. Surprisingly short SQL statement that produces an ASCII art Mandelbrot set. # 13th August 2009, 2:23 pm

SQL pie chart. Generating ASCII art pie charts using the world’s scariest MySQL SELECT statement. # 13th August 2009, 1:04 pm

YQL: INSERT INTO internet. insert into twitter.status (status,username,password) values (“Playing with INSERT, UPDATE and DELETE in YQL”, “twitterusername”,“twitterpassword”) # 8th July 2009, 8:19 pm

With YQL Execute, the Internet becomes your database. This is nuts (in a good way). Yahoo!’s intriguing universal SQL-style XML/JSONP web service interface now supports JavaScript as a kind of stored procedure language, meaning you can use JavaScript and E4X to screen-scrape web pages, then query the results with YQL. # 29th April 2009, 10:50 pm

python-sqlparse (via) Python library for re-identing SQL statements. This could make debugging Django’s generated SQL a whole lot easier. You can try the library out using an App Engine hosted application (complete with an API). # 28th April 2009, 8:25 pm

peeping into memcached. “Peep uses ptrace to freeze a running memcached server, dump the internal key metadata, and return the server to a running state”—you can then load the resulting data in to MySQL using LOAD LOCAL INFILE and analyse it using standard SQL queries. # 20th April 2009, 6:35 pm

Sphinx 0.9.9-rc2 is out. Interesting new feature: the Sphinx search server now supports the MySQL binary protocol, so you can talk to it using a regular MySQL client library and fire off search queries using SELECT syntax and the new SphinxQL query language. # 8th April 2009, 1:59 pm

YQL opens up 3rd-party web service table definitions to developers. This really is astonishingly clever: you can create an XML file telling Yahoo!’s YQL service how to map an arbitrary API to YQL tables, then make SQL-style queries against it (including joins against other APIs). Another neat trick: doing a SQL “in” query causes API requests to be run in parallel and recombined before being returned to you. # 9th February 2009, 9:08 pm

Amazon SimpleDB—Now With Select. So now all three of Yahoo!, Amazon and Google have invented their own SQL-like languages (YQL, SimpleDB and GQL)—though it looks like Yahoo!’s is the only one that attempts to provide joins. # 18th December 2008, 8:59 am

YQL—converting the web to JSON with mock SQL. YQL just got a whole lot more interesting to me—I had no idea they were exposing an HTML and RSS scraping tool over a JSONP API in addition to all of the Yahoo! web service methods. # 13th December 2008, 9:39 am

Secrets of the Django ORM. An undocumented (and unsupported) method of poking a Django QuerySet’s internal query to add group_by and having clauses to a SQL query. # 8th November 2008, 11:49 pm

Mass Attack FAQ. Thousands of IIS Web servers have been infected with an automated mass XSS attack, not through a specific IIS vulnerability but using a universal XSS SQL query that targets SQL Server and modifies every text field to add the attack JavaScript. If an app has even a single SQL injection hole (and many do) it is likely to be compromised. # 26th April 2008, 9:12 am

IronPython, MS SQL, and PEP 249. How Dino Viehland got Django’s ORM to talk to the .NET database layer. # 19th March 2008, 9:46 am

Queryset Implementation. Malcolm explains the work that has gone in to the queryset-refactor branch. Executive summary: Python’s ORM is probably a lot better at SQL than you are. # 19th March 2008, 9:43 am

django-mptt (via) Jonathan Buchanan’s simple utility for performing Modified Preorder Tree Traversal (efficient tree operations in SQL) on Django models. # 29th December 2007, 11:33 am

A Visual Explanation of SQL Joins. It turns out Venn diagrams are an excellent way of illustrating joins. # 12th October 2007, 9:42 am

DbMigration—a schema migration tool for Django. Nice and simple tool for adding schema migrations to a Django application. # 27th September 2007, 3:04 pm

Finding Lookup Items that Are Not Used. How to do left outer joins (and other custom SQL) using the Django ORM. # 13th August 2007, 5:08 pm

Storm. New Python ORM from Canonical, emphasising multiple database support, intelligent local cache invalidation and a thin layer over the underlying SQL. # 9th July 2007, 8:44 am

SELECT * FROM everything, or why databases are awesome. I’m beginning to think that for scalable applications the thinner your ORM is the better—if you even use one at all. # 22nd June 2007, 12:40 am

Facebook Query Language. The Facebook API now lets you run SQL-like queries. You can’t do joins but you can perform very simple subselects. # 25th February 2007, 12:06 pm