SQLObject
2nd September 2003
My new favourite toy is SQLObject, an object-relational mapper which makes heavy use of Python’s special method names to create objects which can be used to transparently access and modify data in a relational database. I tried to write something like this in PHP once before and failed miserably, but SQLObject has such an elegant design that I’m just annoyed I didn’t find out about it sooner. Here’s some example code, adapted from the SQLOBject site:
from SQLObject import *
# Set up a database connection
__connection__ = PyPgSQLConnection()
# This class defines a table
class Person(SQLObject):
firstName = StringCol(length=100)
middleInitial = StringCol(length=1, default=None)
lastName = StringCol(length=100)
# Now create the table (if running for the first time)
Person.createTable()
# Create a record for me
p = Person.new(firstName='Simon', lastName='Willison')
print p
# Outputs <Person 1 firstName='Simon' middleInitial=None lastName='Willison'>
# Set my middle initial (updates the database)
p.middleInitial = 'P'
# Print my full name
print p.firstName, p.middleInitial, p.lastName
SQLObject has plenty more tricks up its sleeve: it can create class definitions by introspecting a database table, handle one to many and many to many joins, and generate complicated SELECT statements on the fly using simple, database independant syntax. It comes with support for MySQL, Postgres and SQLite. Postgres support uses the psycopg module, but we use pyPgSQL so I wrote a simple connection wrapper to support that module which I’ve submitted to the SQLObject mailing list.
More recent articles
- Weeknotes: Llama 3, AI for Data Journalism, llm-evals and datasette-secrets - 23rd April 2024
- Options for accessing Llama 3 from the terminal using LLM - 22nd April 2024
- AI for Data Journalism: demonstrating what we can do with this stuff right now - 17th April 2024
- Three major LLM releases in 24 hours (plus weeknotes) - 10th April 2024
- Building files-to-prompt entirely using Claude 3 Opus - 8th April 2024
- Running OCR against PDFs and images directly in your browser - 30th March 2024
- llm cmd undo last git commit - a new plugin for LLM - 26th March 2024
- Building and testing C extensions for SQLite with ChatGPT Code Interpreter - 23rd March 2024
- Claude and ChatGPT for ad-hoc sidequests - 22nd March 2024
- Weeknotes: the aftermath of NICAR - 16th March 2024