Re: zipcode table

# utility to create the sql needed for populating the zipcode table.
# you should first download and unpack the text file from 
# http://www.cfdynamics.com/zipbase/, courtesy to [email protected].
# then just run this script, and the resulting data will populate the
# zipcode table from various gnue examples.
#
# done by alex bodnaru <[email protected]> for the gnue project, but 
# may be used for any purpose, without holding the author responsible 
# for any bad results may happen (but i won't wish that will).

import csv, string

def str2sql(string, capitalize=0):
    if capitalize:
        string = " ".join([word.capitalize() for word in string.split()])
    return "'" + string.replace("'", "''") + "'"

def str2float(string):
    if string:
        return str(float(string))
    else: return 'NULL'

fobj = open("ZIP_CODES.txt")
reader = csv.reader(fobj)

counties = {}

#"00501","+40.922326","-072.637078","HOLTSVILLE","NY","SUFFOLK","UNIQUE"
for zipcode, latitude, longitude, city, state, county, zip_class in reader:
    print "insert into zipcode (zipcode, city, state, longitude, latitude) values(%s, %s, %s, %s, %s);" % \
        (str2sql(zipcode), str2sql(city, 1), str2sql(state), str2float(longitude), str2float(latitude))
    counties[(county, state)] = 1

fobj.close()

for (county, state) in counties.keys():
    print "insert into county (county, state) values(%s, %s);" % \
        (str2sql(county, 1), str2sql(state))

<?xml version="1.0"?>

<form title="Master/Detail Demo">
  <options>
    <option name="version" value="0.0.3"/>
  </options>
  <datasource name="dsState" cache="15" connection="gnue" order_by="State"
              prequery="Y" table="State" primarykey="state"/>
  <datasource name="dsCities" cache="15" connection="gnue" detaillink="State"
              master="dsState" masterlink="State" order_by="City" table="Zipcode" primarykey="zipcode"/>
  <datasource name="dsCounties" cache="15" connection="gnue" detaillink="State"
              master="dsState" masterlink="State" order_by="County" table="County" primarykey="county,state"/>
  <logic>
    <block name="blkState" datasource="dsState" rows="5" transparent="Y" autoCreate="Y" autoNextRecord="Y" autoCommit="Y">
      <field name="entState" field="State"/>
      <field name="entDesc" field="Description"/>
    </block>
    <block name="blkCities" datasource="dsCities" rows="5">
      <field name="City" field="City"/>
      <field name="State" field="State"/>
      <field name="Zip" field="Zipcode"/>
      <field name="Zop"/>
    </block>
    <block name="blkCounties" datasource="dsCounties" rows="5">
      <field name="County" field="County"/>
      <field name="State" field="State"/>
    </block>
  </logic>
  <layout xmlns:c="GNUe:Layout:Char" c:height="18" c:width="43">
    <page name="Page_1">
      <label name="Label_2" text="Description" c:x="7" c:y="1"/>
      <entry block="blkState" field="entDesc" c:width="30" c:x="7" c:y="2"/>
      <label name="Label_1" text="Code" c:x="2" c:y="1"/>
      <entry block="blkState" field="entState" c:width="4" c:x="2" c:y="2"/>
      <scrollbar block="blkState" c:x="38" c:y="2" c:widht="1" c:height="5" />

      <box name="Box_1" c:height="8" label="State Cities" c:width="40" c:x="1"
           c:y="8"/>
      <label name="Label_3" rows="1" text="City" c:x="2" c:y="9"/>
      <entry block="blkCities" field="City" c:width="20" c:x="2" c:y="10"/>
      <label name="Label_4" rows="1" text="ST" c:x="23" c:y="9"/>
      <entry block="blkCities" field="State" c:width="3" c:x="23" c:y="10"/>
      <label name="Label_5" rows="1" text="Zip" c:x="27" c:y="9"/>
      <entry block="blkCities" field="Zip" c:width="10" c:x="27" c:y="10"/>
      <entry block="blkCities" field="Zop" hidden="Y" c:width="1" c:x="27" c:y="1"/>
      <scrollbar block="blkCities" c:x="38" c:y="10" c:widht="1" c:height="5" />

      <box name="Box_2" c:height="8" label="State Counties" c:width="29" c:x="41"
           c:y="8"/>
      <label name="Label_6" rows="1" text="County" c:x="42" c:y="9"/>
      <entry block="blkCounties" field="County" c:width="20" c:x="42" c:y="10"/>
      <label name="Label_4" rows="1" text="ST" c:x="63" c:y="9"/>
      <entry block="blkCounties" field="State" c:width="3" c:x="63" c:y="10"/>
      <scrollbar block="blkCounties" c:x="67" c:y="10" c:widht="1" c:height="5" />
    </page>
  </layout>
</form>