10.2. Basic SQLObject Features
SQLObject was designed from the bottom up to make you faster, stronger, and better looking. Okay, probably not stronger, and definitely not better looking, but it will make you more productive. It's up to you what you do with the time you get back in your life when you learn to let SQLObject save you from tedious and error-prone tasks. Perhaps you could write more unit tests, or perhaps you could ask the new marketing manager out for a date. The choice is yours.
The following sections describe some of the cool productivity-enhancing features, shortcuts, and ease-of-use tricks SQLObject provides.
The main goal of SQLObject is to translate SQL into objects. That means that what you used to do with SQL before, you can do now with a Python object. It's an implementation of the ActiveRecord design pattern. ActiveRecords have a one-to-one relationship between model classes and tables in the database.
As previously discussed, you can define classes that derive from the base SQLObject and represent tables in the database. Rows in the table are represented by instances of your SQLObject derived class.
When you set attributes on an instance, it updates column values in this row and updates the DB. Actually, you can defer updates to the DB for performance reasons, but we get back to that idea later.
When you set an attribute on the class itself, you operate on the entire table. That includes managing DB connections and creating your DB schema, indexes, constraints, default values for columns, relationships between tables, CRUD operations, and sophisticated queries. In addition, you can control the behavior and interaction of your model classes with the underlying database.
Here's a quick glance over the offerings of SQLObject. We'll go over the nuts and bolts in detail later.
10.2.1. Basic Connection Management
Every access to the database requires a DB connection. From the user's point of view, a connection is how you tell SQLObject to locate your database. It resembles a URI in the following format:
The connection is the only place where you specify the actual database you use. The rest of your data access code is totally DB-agnostic. Unfortunately, SQLObject doesn't provide a perfect abstraction; sometimes you need to consider the features of the underlying database you are using. Some databases don't support some features, or you might want to write a highly tuned query with database-specific features. With that said, SQLObject does a pretty darned good job, and you can write a large application using SQLite, and then switch to Postgres for production with no transition problems.
Here are a few sample database URLs:
mysql://user:pwd@/db postgres://user:pwd@localhost:5432/db sqlite:///full/path/to/db sqlite:/C|full/path/to/db sqlite:/:memory:
In TurboGears, you generally set this up in the dev.cfg file for your project, and it is then used by all the classes in your project. If you want more flexibility, however, there are multiple ways to specify the connection for a particular class. You can set the _connection class attribute, set the module's _connection_ variable (so that all classes in this module can share the same connection), or pass a connection object to the _init() method of an instance (to control the connection in a granular way per record). Probably, the easiest way is to use the sqlhub.processConnection that controls the connection for all SQLObject, in the current process. You can also have a connection per thread or custom connections.
Here's how you manually set up a connection to a SQLite database on Windows:
from sqlobject import * db_filename = os.path.abspath('test.db').replace(':\\', '|\\') connection_uri = 'sqlite:/' + db_filename connection = connectionForURI(connection_uri)
SQLite requires the full path to the database file, and on Windows you must replace the colon that follows the drive letter with a pipe (|). So, if your SQLite DB file is located in c:\db_dir\db_file.db the corresponding connection URI is sqlite:/c|\db_dir\db_file.db and in escaped form sqlite:/c|\\db_dir\\db_file.db. Note that this URI doesn't comply with URI format because there is only a single slash after the scheme. SQLite is a little different because it uses an actual filename for the connection. But don't let it distract you; other than the URI pointing to a file, the SQLite connection strings are exactly the same.
The connectionForURI() function creates a connection object. This connection object can now be used for declaring classes and accessing the DB. There are many ways to associate a connection with a model class. The simplest one is to assign one connection for the entire process. Every model class will automatically use this connection. Here is how it's done:
sqlhub.processConnection = connection
10.2.2. Automatic DB Schema Creation from SQLObject-Derived Classes
TurboGears provides a tg-admin sql create command, which we used to create the tables from the model class automatically.
But this is just a wrapper around functionality provided by SQLObject itself. This means that if you want to create tables programmatically from within your application, you can. Just define a class with some columns, call createTable(), and it will be created in the database. Here is how it's done:
class FighterRobot(SQLObject): name = StringCol() weapon_1 = StringCol() weapon_2 = StringCol() engine = StringCol(default='Basic engine') health = IntCol(default=100) FighterRobot.dropTable(ifExists=True) # drop previous definition if exists FighterRobot.createTable()
And here is the generated DB schema:
CREATE TABLE fighter_robot ( id INTEGER PRIMARY KEY, name TEXT, weapon_1 TEXT, weapon_2 TEXT, engine TEXT, health INT );
Note that the default values for engine and health didn't make it into the DB schema. SQLObject takes care of setting the values by itself, and whenever you create a new instance (insert a new row into the DB), it populates it properly.
Another important detail that you might notice is that the name of the class doesn't exactly match the name of the table that is created. SQLObject uses a special naming convention to translate Pythonic names to database names. That's why the FighterRobot class created a table called fighter_robot.
10.2.3. Fine Control on Behavior Using Metadata Class
Each model class has a nested class called sqlmeta that allows you to specify a plethora of interesting attributes such as caching and lazy updates, and a style object to control name translations. This is just a hint of what is to come. We discuss lazyUpdate thoroughly later on. Here is how to turn on lazy updates for the fighter robot:
class FighterRobot(SQLObject): class sqlmeta: lazyUpdate = True name = StringCol() weapon_1 = StringCol() weapon_2 = StringCol() engine = StringCol(default='Basic engine') health = IntCol(default=100)
10.2.4. Automatic Table Description from Existing DB Schema
This is the flip-side of automatic DB schema creation. If you already have an existing database, you don't have to labor through defining every column as an attribute of your model classes. You can just specify _fromDatabase = True and be done with it. Here is how it's done:
class FighterRobot(SQLObject): _fromDatabase = True
This system only works if the database itself and the Python drivers for that database provide the introspection capabilities that SQLObject needs. For example, this doesn't work for SQLite right now, but MySQL and Postgres work just fine. If you use one of the less commonly used SQLObject backends, your experience may vary.
Remember that your DB should have a table called fighter_robot to match the FighterRobot class name. This feature is useful if your DB is created and maintained by an external person or group. This way you are protected (to some degree) from DB schema changes. Of course, you must convert the data in the DB itself and change the parts of the code that relied on the old schema, but things such as adding a new column to a table or changing the name/type of a column you didn't access in your code should be transparent.