nanodeg_p3_redshift/create_tables.py at master · lastnode/nanodeg_p3_redshift · 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
from etl_utils import run_query, create_connection
from sql_queries import create_table_queries, drop_table_queries

"""
Script that connects to Redshift and creates the tables required
by the `etl.py` script which should be run next.
"""


def drop_tables(cur, conn):

    """
    Connects to the Amazon Redshift database specifiied in 'dwh.cfg'
    in order to drop tables remaining from previous ETL processes.

    Runs the list of queries in `create_table_queries` in the `sql_queries`
    module through etl_utils.run_query() which executes them in turn.n.

    Paramters:
    cur (psycopg2.cursor()) - cursor of the (Postgres) db
    conn (psycopg2.connect()) - connection to the (Postgres) db

    Returns:
    None
    """

    for query in drop_table_queries:
        run_query(cur, conn, query)


def create_tables(cur, conn):

    """
    Connects to the Amazon Redshift database specifiied in 'dwh.cfg'
    in order to set up the required staging and final data tables.

    Runs the list of queries in `create_table_queries` in the `sql_queries`
    module through etl_utils.run_query() which executes them in turn.n.

    Paramters:
    cur (psycopg2.cursor()) - cursor of the (Postgres) db
    conn (psycopg2.connect()) - connection to the (Postgres) db

    Returns:
    None
    """

    for query in create_table_queries:
        run_query(cur, conn, query)


def main():

    """
    Main function of this script that creates the tables.

    Calls `etl_utils.py.create_connection()` in order to connect
    to the Postgres server. Then it passes the connection and the
    cursor to the following functions so they can set up the tables
    required by the `etl.py` script:

    drop_tables()
    creat_tables()

    Parameters:
    None

    Returns:
    None
    """

    cursor, connection = create_connection("dwh.cfg")

    drop_tables(cursor, connection)
    create_tables(cursor, connection)

    connection.close()


if __name__ == "__main__":
    main()