I have connected to databases from Python in a variety of ways. I have used the DB-API interfaces directly from my code and I have used the ORM tools such as SQLAlchemy. Since TurboGears uses SQLAlchemy I have been using that more than other methods recently and I have grown to like it.
SQLAlchemy enables you to define a table as a Python class. You can then use this as you would any other class. Here is an example of the definition: -
# -*- coding: utf-8 -*-
"""radiopage model module."""
from sqlalchemy import *
from sqlalchemy.orm import mapper, relation
from sqlalchemy import Table, ForeignKey, Column, Sequence
from sqlalchemy.types import Integer, Unicode, Text
#from sqlalchemy.orm import relation, backref
from necryon.model import DeclarativeBase, metadata, DBSession
class RadioPage(DeclarativeBase):
__tablename__ = 'radiopage'
#{ Columns
id = Column(Integer, Sequence('radiopage_id_seq'), primary_key=True)
title = Column(Text)
data = Column(Text, nullable=False)
description = Column(Text)
#}