Subversion Repositories Misc

#!/usr/bin/env python
 
# forgottenislanderbot - makes a DSL coverage map from Bell Aliant's website.
# Copyright (c) 2010 Art Ortenburger
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
#
 
# fib-cadb2sql.py
# by Art Ortenburger IV <utrrrongeeb a users , sf , net>
# written 2010-01-13 -
#
# forgottenislanderbot -- TSV db -> SQlite db
#
# Converts PEI Government civic address databases
# from tab-separated-values text to Python SQlite.
# Removes and adds columns in the process.
#
 
 
import sys
import csv
import sqlite3
import fibmod
 
print sys.argv[0],"""(forgottenislanderbot TSV db -> sqlite db)    Version 0.1
Copyright (c) 2010 Art Ortenburger. Licensed under the GNU GPL; no warranty."""
 
 
def processFile(inFilename):
    """ processes one tab-separated-values PEI gov't civic address database,
        adding it to the new sqlite database.
        I suspect duplicates are handled very poorly.
        inFilename: string; TSV to open."""
    try:
        ftr = open(inFilename,"rb") # file to read
        tdp = csv.reader(ftr,"excel-tab") # tsv data parser
        # loop through list of values, adding each one in turn.
        for ccar in tdp: # current civic address
            qdbC.execute("insert into dsl values (?,?,?,?,?,?)",
                         [ int(ccar[0]),ccar[1],ccar[2],
                           float(ccar[5]),float(ccar[6]),0 ])
    except Exception, errdet:
        print "error parsing input: ",errdet
    finally:
        try:
            ftr.close()
        except:
            pass
 
 
# list of files to add to new sqlite
filesToParse = []
 
# minimum two arguments, of course
if len(sys.argv)<2:
    print """
Usage:  fib-cadb2sql.py (outputFile.sqlite) (inputFile1.tsv)...
"""
    exit(0)
else:
    filesToParse = sys.argv[2:]
 
qdbCon = sqlite3.connect(sys.argv[1]) # output SQlite database connection
qdbC = qdbCon.cursor() # output SQlite database cursor
 
# create db tables
# probably handles existing database poorly 
qdbC.execute("create table dsl (\
civic integer, road text, town text, lat real, long real, dsl integer)")
 
# loop through input TSV list, parsing and adding to database
for curFile in filesToParse:
    processFile(curFile)
 
try:
    qdbC.close()
except:
    pass
try:
    qdbCon.commit()
    qdbCon.close()
except Exception, errdet:
    print "Error saving database:",errdet
 
print "done"
 
# TSV format is
# [#]   [RD]    [CMTY]  [CNTY]  [LAT]   [LON]   [COP]   [FIRE]  [MED]
# SQlite format is
# [#]   [RD]    [CMTY]  [LAT]   [LON]   [DSL]
# DSL options: [ 0: unknown, 1: no, 2: basic, 3: ultra ]