Example of using geoalchemy.

gistfile1.py

# A simple script to load data from a shapefile into a spatialite db.
# Transfering the geometries is slow. It would probably be much faster to
# use WKB directly for the load, but I cannot get this to work.

# This is largely copied from
#   https://github.com/geoalchemy/geoalchemy/blob/master/examples/spatialite.py
import os, osgeo.ogr
from sqlite3 import dbapi2 as sqlite
from sqlalchemy.orm import sessionmaker
from sqlalchemy import event, create_engine, MetaData
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy import GeometryColumn, Geometry, GeometryDDL

# put the spatialite dll on the path. If one had pyspatialite installed thn
# this would not be required... but trying to get that on a windows machine
# was way too hard to bother
dirDLLspatialite = 'C:/bin'
os.environ['PATH'] = dirDLLspatialite + ';' + os.environ['PATH']

engine = create_engine('sqlite:///D:\\temp\\test_1.db', module=sqlite, echo=False)

# this enables the extension on each connection
@event.listens_for(engine, "connect")
def connect(dbapi_connection, connection_rec):
    dbapi_connection.enable_load_extension(True)
    dbapi_connection.execute("SELECT load_extension('libspatialite-4.dll')")

metadata = MetaData(engine)
session = sessionmaker(bind=engine)()
# only need this next line when initially creating the db.
#session.execute("SELECT InitSpatialMetaData();")

Base = declarative_base(metadata=metadata)

class Region(Base):
    __tablename__ = 'regions'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    code = Column(String(32))
    geom = GeometryColumn(Geometry(2))

# enable the DDL extension, which allows CREATE/DROP operations
# to work correctly.  This is not needed if working with externally
# defined tables.
#------- this give me exceptions -------
#GeometryDDL(Region.__table__)

metadata.create_all(engine)

# A shape file to load data from
sfn = r'D:\data\ABS\ASGC\STE09aAust.shp'
shapeData = osgeo.ogr.Open(sfn)
layer = shapeData.GetLayer()

for index in xrange(layer.GetFeatureCount()):
    feature = layer.GetFeature(index)
    geometry = feature.GetGeometryRef()
    name = feature.GetFieldAsString('STATE_NAME')
    print 'loading: %s' % name
    region = Region(
        name = name,
        code = feature.GetFieldAsString('STATE_CODE'),
        geom = geometry.ExportToWkt())
    session.add(region)
    session.commit()

# read the regions out and dump as WKB
regions = session.query(Region)
for r in regions:
    out = open('%s.txt' % r.name, 'wb')
    out.write(r.geom.geom_wkb)
    out.close()