Standard/Default Approach:

From the docs: SQLObject (declaring the class): http://sqlobject.org/docs/SQLObject.html#declaring-the-class

To begin with, let's make a database connection. Choose from one of MySQLConnection, PostgresConnection, SQLiteConnection, and FirebirdConnection, depending on what database you use.

    conn = MySQLConnection(user='test', passwd='pwd', db='testdb') 
           [this method is now deprecated. Use the URI method below]
    conn = 'mysql://test:pwd@localhost/testdb'
    conn = 'mysql://test:pwd@localhost/testdb?debug=1'
           [To allow debugging, shows sql queries]
    conn = PostgresConnection('user=test passwd=pwd dbname=testdb') 
           [this method is now deprecated. Use the URI method below]
    conn = 'postgres://test:pwd@localhost/testdb'
    conn = SQLiteConnect('database.db')
    conn = 'sqlite://path/to/database.db'
    conn = DBMConnection('database/')
    conn = 'dbm://path/to/database/'

... (then assign using:)

    class Person(SQLObject):
         _connection = conn

The above tells how to create a connection and then assign that connection in the class definition. This will probably work for most people and consequently the documentation on connections pretty much ends there.

In this scenario, SQLObject automatically makes (and manages) a pool of connections as needed. It also caches each fetched instance to minimize future lookups. I've seen it mentioned that this should work for a multi-threaded application running under a single process. (true?)

Related Mailing List threads:

SQLite threadsafety? http://thread.gmane.org/gmane.comp.python.sqlobject/903

Overcoming SQL Object's non-OO behavior http://thread.gmane.org/gmane.comp.python.sqlobject/1341

connections, threads, processes http://thread.gmane.org/gmane.comp.python.sqlobject/1309

connections redux http://thread.gmane.org/gmane.comp.python.sqlobject/1310

Pool count http://thread.gmane.org/gmane.comp.python.sqlobject/998

Alternative Pooling:

In addition to using the built in pooling mechanism in SQLObject, you can configure a third party pooling mechanism and configure the class's _connection configuration to call that. (?)

Related Mailing List threads:

Pooling database connections http://thread.gmane.org/gmane.comp.python.sqlobject/958

Multiple Processes:

The recommended way to deal with an application that runs under multiple processes (or on multiple servers for that matter) is to turn off caching and use transactions.

Transactions http://sqlobject.org/docs/SQLObject.html#transactions

Specifying Your Class http://sqlobject.org/docs/SQLObject.html#sqlobject-class-specifying-your-class

Advanced/Alternative Connections:

Cases when you would not want to use the above methods:

• per thread connections (This approach is often advocated for database connections in Quixote)
• objects in more than one database

When assigning a connection for every thread, you will probably use the connection parameter to pass in the appropriate connection when creating new objects and fetching stored ones.

class Person(SQLObject):
     name = StringCol()

conn = MySQLConnection(user='test', db='testdb')

new_person = Person.new(name="Tom", connection=conn)

(note that the specific methods for creating a new object and fetching an old one have changed from SQLObject .5.x to .6)

These connections can be stored in a local variable in the thread, and reused as long as the thread is running.

With this method there could potentially be caching issues (definitely if it uses multiple processes). Turning caching off seems to be the easiest solution. However, in the case of using SQLObject with a web application that processes each request with a single thread, it would seem possible to leave caching on if you could expire the cache at the end of every request. If possible this would be a nice compromise between caching and no caching.

I have seen a few references on how to go about expiring the cache in the mailing list here (but haven't tried them yet):

Expiring Cache http://thread.gmane.org/gmane.comp.python.sqlobject/932

Flushing the cache http://thread.gmane.org/gmane.comp.python.sqlobject/1630

As far as I can tell, passing in the connection to the new/fetch operation is the only way to deal with object definitions with instances that are stored in multiple databases. (For an example of when you might want to do this, look at livejournal's architecture (they don't use SQLObject, just the architecture is appropriate). The incentive to do this is scalability beyond one server hosting the database. They store the entries for a user's journal on a specific database, but not necessarily the same one that another user's journal entries are stored on.)

(You could also configure a hybrid approach using the connection per thread model and the third party pooling solution mentioned earlier. In this scenario you would create a connection per thread and then lookup which connection to use in a global dictionary that maps a connection to a thread. This was mentioned here: Leaking connections http://thread.gmane.org/gmane.comp.python.sqlobject/1611 )

Connection no-no's:

When passing connection parameters in it can be tempting to create a new connection whenever one is needed, rather than keep track of ones created elsewhere. As far as I can tell there is no way to clean up/close these connections after you are finished with them. Obviously that can quickly eat up the available connections to your database.

Connection timeouts due to inactive connections:

mysql connection losses http://thread.gmane.org/gmane.comp.python.sqlobject/1311

Handling lost MySQL connections... http://thread.gmane.org/gmane.comp.python.sqlobject/1391

Other misc related topics on the mailing list:

Concurrency issues... http://thread.gmane.org/gmane.comp.python.sqlobject/1320

By: Charles Brandt

2004.08.29

Comments/Corrections are encouraged!

Comments:

When connecting to Unix Sockets simple leave the hostname empty:

conn = 'postgres://test@localhost/testdb'

becomes:

conn = 'postgres://test@/testdb'

No need for localhost and will give an error:

psycopg.OperationalError: could not connect to server: Connection refused
Is the server running on host "localhost" and accepting
TCP/IP connections on port 5432?
used connection string 'dbname=QMan user=test host=localhost'

Note by: Stewart Midwinter 2005.04.14