• Requires the Psycopg2 driver and Genshi.
• Assumes polygon geometry stored in PostGIS table called mytable.
• Assumes mytable has columns named name, gid and the_geom.
• Depends on a genshi template named template.kml in the same directory as the script.
• Will generate a moderately styled multigeometry KML of polygons and points called postgis.kml that takes advantage of multigeometry hover over styling.

postgis2kml.py

#!/usr/bin/env python
#
# postgis2kml.py
#
# A python script for generating styled KML from postgis.
#
# Requires the the Genshi Templating Language and the Psycopg2 driver
# author = Dane Springmeyer (dane.springmeyer [ at ] gmail.com)
# thanks = "http://jasonbirch.com, http://zcologia.com

 import psycopg2
 import psycopg2.extras
 from genshi.template import !TemplateLoader

 connection = psycopg2.connect("dbname='postgres' user='postgres' host='localhost' password='postgres'");

 sql = connection.cursor(cursor_factory=psycopg2.extras.!DictCursor)
 sql.execute("SELECT gid, name, ST_AsKML(ST_PointOnSurface(the_geom)) as placemark, ST_AsKML(the_geom) as geometry from mytable")
 rows = sql.fetchall()

 def generate_kml():
 	for row in rows:
 		gid = row['gid']
 		placemark_id = {'id': gid}
 		name = row['name']
 		placemark = row['placemark']
 		style = "#multipoly"
 		geometry = row['geometry']
 		yield {
 			'gid': gid,
 			'name': name,
 			'placemark_id': placemark_id,
 			'placemark': placemark,
 			'style': style,
 			'geometry': geometry,
             }

 loader = !TemplateLoader(['.'])
 template = loader.load('template.kml')
 stream = template.generate(collection=generate_kml())

 kml = open('postgis.kml', 'w')
 kml.write(stream.render('xml', strip_whitespace=True))
 kml.close()

template.kml

<?xml version="1.0" encoding="utf-8"?>
<kml xmlns="http://earth.google.com/kml/2.2" xmlns:py="http://genshi.edgewall.org/" xmlns:atom="http://www.w3.org/2005/Atom/">
    <Document>
    <name>KML generated from PostGIS</name>
   	<description>Using Python templating</description>
       <StyleMap id="multipoly">
        <Pair>
          <key>normal</key>
          <styleUrl>#polynormal</styleUrl>
        </Pair>
        <Pair>
          <key>highlight</key>
          <styleUrl>#polyhover</styleUrl>
        </Pair>
      </StyleMap>
      <StyleMap id="invert">
        <Pair>
          <key>normal</key>
		  <styleUrl>#polyhover</styleUrl>
        </Pair>
        <Pair>
          <key>highlight</key>
          <styleUrl>#polynormal</styleUrl>
        </Pair>
      </StyleMap>
      <Style id="polynormal">
        <IconStyle>
          <scale>0.5</scale>
          <Icon>
            <href>http://maps.google.com/mapfiles/kml/pal4/icon56.png</href>
          </Icon>
        </IconStyle>
        <LabelStyle>
          <scale>0</scale>
        </LabelStyle>
        <LineStyle>
          <antialias>1</antialias>
          <color>b3bfbfc1</color>
          <width>1</width>
        </LineStyle>
        <PolyStyle>
          <color>7d00ff00</color>
        </PolyStyle>
        <BalloonStyle id="ballo0nnormal">
  		  <displayMode>hide</displayMode>
  		 </BalloonStyle>
      </Style>
      <Style id="polyhover">
        <IconStyle>
          <scale>0.85</scale>
          <Icon>
            <href>http://maps.google.com/mapfiles/kml/pal4/icon48.png</href>
          </Icon>
        </IconStyle>
        <LabelStyle>
          <scale>0.9</scale>
        </LabelStyle>
        <LineStyle>
          <antialias>1</antialias>
          <color>ff07cefe</color>
          <width>0</width>
        </LineStyle>
        <PolyStyle>
          <color>7dff0000</color>
          <outline>0</outline>
        </PolyStyle>
        <BalloonStyle id="balloonhover">
      <!-- a background color for the balloon -->
      <bgColor>7fe8e8e8</bgColor>
      <!-- styling of the balloon text -->
      <text><![CDATA[
      <b><font face="Lucida Grande" color="#000080" size="+1">$[name]</font></b>
      <br/><br/>
      <font face="Lucida Grande" color="#333333">$[description]</font>
      <br/><br/>
      ]]></text>
    </BalloonStyle>
      </Style>
      <Placemark py:for="item in collection" py:attrs="item['placemark_id']">
        <name py:content="item['name']">NAME</name>
        <description>
          <![CDATA[ ${item['name']} ]]>
		</description>
        <styleUrl py:content="item['style']">STYLE</styleUrl>
        <address>address</address>
        <MultiGeometry>
        <?python
        from genshi import XML
        from genshi.filters import Transformer
        altitude = XML('<altitudeMode>absolute</altitudeMode>')
        extrude = XML('<extrude>1</extrude>')
        polys = XML(item['geometry'])
        extruded_polys = XML( polys | Transformer('//Polygon/outerBoundaryIs').before(extrude).before(altitude)).select('Polygon')
        placemarks = XML(item['placemark'])
        ?>
        $placemarks
        $extruded_polys
        </MultiGeometry>
      </Placemark>
    </Document>