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 ]