wPvgUFsF

1# -*- coding: utf-8 -*-
2""" Functions to maintain the Geonames-database """
3
4import psycopg2
5import urllib, zipfile
6import os, sys, stat, geo_input
7
8def db_connection():
9    """ establish a connection to the Geonames-database """
10    try:
11        conn = psycopg2.connect("""dbname='geonames'
12            user='postgres'
13            host='localhost'
14            password='postgres'""")
15        return conn
16    except psycopg2.DatabaseError as db_e:
17        print "Error '{0}' occured. Arguments {1}.".format(db_e.message, db_e.args)
18        sys.exit(1)
19
20
21# All following functions assume an existing Geonames-database
22def download_missing():
23    """ check for missing country tables, download data and create missing country table(s) """
24    # create connection to database
25    db_con = db_connection()
26    db_cur = db_con.cursor()
27    print os.geteuid()
28    print os.getgroups()
29    # retrieve existing country tables
30    db_cur.execute(""" SELECT tablename FROM pg_tables
31        WHERE tablename LIKE 'cntr_%'""")
32    lst_cntr_tables = db_cur.fetchall()
33    # find missing countries
34    lst_cntr = geo_input.DICT_EU_CNTR.values()
35    for cntr in lst_cntr:
36        if 'cntr_' + cntr.lower() not in lst_cntr_tables:
37            # download data
38            cntr_url = "http://download.geonames.org/export/dump/{m_cntr}.zip".format(m_cntr=cntr)
39            cntr_down = urllib.urlretrieve(cntr_url)
40            # unzip downloaded data
41            with zipfile.ZipFile(cntr_down[0], 'r') as zip_down:
42                zip_down.extract(cntr + '.txt', '/Users/thob/Documents/Geo_download/')
43            #in_fobj = '/Users/thob/Documents/Geo_download/{m_cntr}.txt'.format(m_cntr=cntr)
44            #os.chmod(in_fobj, stat.S_IRWXG)
45            # create missing country tables
46            db_cur.execute(""" CREATE TABLE IF NOT EXISTS {n_table} (
47                geonameid integer NOT NULL,
48                name varchar(200),
49                asciiname varchar(200),
50                alternatenames varchar(10000),
51                latitude numeric(9, 6),
52                longitude numeric(9, 6),
53                feature_class char(1),
54                feature_code varchar(10),
55                country_code char(2),
56                cc2 varchar(200),
57                admin1_code varchar(20),
58                admin2_code varchar(80),
59                admin3_code varchar(20),
60                admin4_code varchar(20),
61                population bigint, 
62                elevation integer,
63                dem integer,
64                timezone varchar(40),
65                modification_date  date
66                )""".format(n_table='cntr_'+cntr.lower())
67                          )
68            # import data
69            db_cur.execute("""COPY cntr_lt FROM 'Users/thob/Documents/LT.txt' WITH DELIMITER '\t'""")
70            db_con.commit()
71            # clean up downloads
72    # close database connection
73    db_cur.close()
74    db_con.close()