mapZ/database_inflater.py at master · itzmeanjan/mapZ · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
#!/usr/bin/python3


try:
    import psycopg2 as psql
except ImportError as e:
    print('[!]Module Unavailable : {}'.format(str(e)))
    exit(1)


def push_into_table(db_name, username, password, table_name, data):
    # pushing dataset for a certain layer, is done here
    try:
        conn = psql.connect(database=db_name, user=username, password=password)
        cursor = conn.cursor()  # cursors give us API for executing SQL queries
        count = 0
        for feature_id, feature_name, outline in data:
            cursor.execute("insert into {} values ( %s, %s, st_geogfromtext(%s))".format(table_name),
                           (feature_id, feature_name, outline))
            count += 1
            if(not count % 100):
                # after every 100 push, we just commit the changes made
                conn.commit()
        conn.commit()  # final commit
        cursor.close()
        conn.close()  # connection to DB closed
    except psql.DatabaseError as e:
        print('[!]Error: {}'.format(str(e)))
        cursor.close()
        conn.close()
        return False
    except Exception as e:
        print('[!]Error: {}'.format(str(e)))
        cursor.close()
        conn.close()
        return False
    return True  # in case of success


def create_table(db_name, username, password, table_name):
    # first we need to create a database with postgis plugin enabled
    try:
        conn = psql.connect(database=db_name, user=username, password=password)
        cursor = conn.cursor()
        cursor.execute("drop table if exists {}".format(table_name))
        cursor.execute(
            "create table {} (feature_id varchar primary key, feature_name varchar not null, outline geography)".format(table_name))
        cursor.execute("create index {} on {} using gist( outline )".format(
            '{}_index'.format(table_name), table_name))
        conn.commit()  # committing changes made to DB
        cursor.close()
        conn.close()  # closed connection to DB
    except psql.DatabaseError as e:
        print('[!]Error: {}'.format(str(e)))
        cursor.close()
        conn.close()
        return False
    except Exception as e:
        print('[!]Error: {}'.format(str(e)))
        cursor.close()
        conn.close()
        return False
    return True  # in case of success


def inflate_into_db(db_name, username, password, data_set):
    # database inflation handler
    try:
        for key, value in data_set.items():
            if(create_table(db_name, username, password, 'world_features_level_{}'.format(key))):
                print(
                    '[+]Pushing into table -- `{}`'.format('world_features_level_{}'.format(key)))
                push_into_table(db_name, username, password,
                                'world_features_level_{}'.format(key), value)
            else:
                return False
    except Exception as e:
        print('[!]Error: {}'.format(str(e)))
        return False
    return True


if __name__ == '__main__':
    print('[!]This module is designed to be used as a backend handler.')
    exit(0)