Skip to content

Locations db setup

Database setup.

Setup file to create a locations SQLITE database and load location information into the database to allow for queries.

create_connection(db_path)

Create connection.

Create a SQLite3 connection object and return it.

Parameters:

Name Type Description Default
db_path str

Path string to db directory location.

 required

Returns:

Name Type Description
conn sqlite3.Connection

SQLite3 connection object
Source code in report_generator/project_setup/locations_db_setup.py 
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
def create_connection(db_path: str) -> sqlite3.Connection:
    """Create connection.

    Create a SQLite3 connection object and return it.

    Args:
        db_path (str): Path string to db directory location.

    Returns:
        conn: SQLite3 connection object

    """
    conn = None
    try:
        conn = sqlite3.connect(os.path.join(db_path, "location.db"))
    except Error as e:
        logger.error(e)

    return conn

create_tables(conn)

Create SQL table strings.

Create SQL table strings for the locations database.

Use SQLite3 connection object to create tables in locations database with table strings.

Tables

country_codes: table representing country data geocode: table representing geocode data

Parameters:

Name Type Description Default
conn sqlite3.Connection

SQLite3 connection object

 required
Source code in report_generator/project_setup/locations_db_setup.py 
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
def create_tables(conn: sqlite3.Connection) -> None:
    """Create SQL table strings.

    Create SQL table strings for the locations database.

    Use SQLite3 connection object to create tables in locations
    database with table strings.

    Tables:
        country_codes: table representing country data
        geocode: table representing geocode data

    Args:
        conn: SQLite3 connection object

    """
    country_table = """CREATE TABLE IF NOT EXISTS country_codes (
        country_code_id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
        continent_name VARCHAR(50) NOT NULL,
        continent_code VARCHAR(4) NOT NULL,
        country_name VARCHAR(50) NOT NULL,
        two_letter_country_code VARCHAR(2),
        three_letter_country_code VARCHAR(3),
        country_number integer NOT NULL
    )
    """

    geocode_table = """CREATE TABLE IF NOT EXISTS geocode (
            geoname_id INT NOT NULL PRIMARY KEY,
            place_name VARCHAR(50),
            ascii_name TEXT,
            alternate_names TEXT,
            latitude REAL,
            longitude REAL,
            feature_class TEXT,
            feature_code TEXT,
            country_code TEXT,
            cc2 TEXT,
            admin1_code TEXT,
            admin2_code TEXT,
            admin3_code TEXT,
            admin4_code TEXT,
            population_info integer,
            elevation integer,
            dem integer,
            timezone TEXT,
            modification TEXT
        )
    """

    try:
        cursor = conn.cursor()
        cursor.execute(country_table)
        logger.debug("Country Table Created")
        cursor.execute(geocode_table)
        logger.debug("Geocode Table Created")
    except Error as e:
        logger.error(e)

insert_country_data(conn, csv_path)

Insert country data.

Inserts country data into the country_codes table

Parameters:

Name Type Description Default
conn sqlite3.Connection

SQLite3 connection object

 required
Source code in report_generator/project_setup/locations_db_setup.py 
128
129
130
131
132
133
134
135
136
137
138
139
140
141
def insert_country_data(conn: sqlite3.Connection, csv_path: str) -> None:
    """Insert country data.

    Inserts country data into the country_codes table

    Args:
        conn: SQLite3 connection object

    """
    # Open Country data in pandas
    file_path = os.path.join(csv_path, "country-and-continent-codes.csv")
    data_frame = pandas.read_csv(file_path)

    data_frame.to_sql("country_codes", conn, if_exists="replace", index=False)

insert_country_data_row(conn, row_values)

Insert country data row.

Inserts row of country data into country table in locations database.

Parameters:

Name Type Description Default
conn object

SQLite3 connection object

 required
row_values list

List of values from the Pandas dataframe row.

 required
Source code in report_generator/project_setup/locations_db_setup.py 
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
def insert_country_data_row(conn: object, row_values: list) -> None:
    """Insert country data row.

    Inserts row of country data into country table in locations database.

    Args:
        conn: SQLite3 connection object
        row_values: List of values from the Pandas dataframe row.

    """
    sql = """ INSERT INTO country_codes
    (
        continent_name,
        continent_code,
        country_name,
        two_letter_country_code,
        three_letter_country_code,
        country_number
    )
    VALUES(?,?,?,?,?,?)"""

    try:
        cursor = conn.cursor()
        cursor.execute(sql, row_values)
        conn.commit()
    except Error as e:
        logger.error(row_values)
        logger.error(e)

insert_geocode_data(conn, csv_path)

Insert geocode data.

Inserts geocode data into locations database.

Opens the csv files that contain the geocode data directory and iterates through to insert the geocode data by section.

Parameters:

Name Type Description Default
conn object

SQLite3 connection object

 required
csv_path str

Path string to csv directory

 required
Source code in report_generator/project_setup/locations_db_setup.py 
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
def insert_geocode_data(conn: object, csv_path: str) -> None:
    """Insert geocode data.

    Inserts geocode data into locations database.

    Opens the csv files that contain the geocode data directory
    and iterates through to insert the geocode data by section.


    Args:
        conn:       SQLite3 connection object

        csv_path:   Path string to csv directory

    """
    dir_path = os.path.join(csv_path, "split_csv")

    files = os.listdir(dir_path)
    progress_bar = tqdm.tqdm(total=len(files), desc="Inserting Data")
    for i in range(len(files)):
        file = files[i]
        file_path = os.path.join(dir_path, file)
        insert_geocode_data_section(conn, file_path)
        progress_bar.update(1)

insert_geocode_data_section(conn, file_path)

Insert geocode data section.

Opens ands processes geocode data section csv file and then inserts it into the geocode_data table in the locations database.

Parameters:

Name Type Description Default
conn object

SQLite3 connection object

 required
file_path str

string of filepath to section csv

 required
Source code in report_generator/project_setup/locations_db_setup.py 
200
201
202
203
204
205
206
207
208
209
210
211
212
213
def insert_geocode_data_section(conn: object, file_path: str) -> None:
    """Insert geocode data section.

    Opens ands processes geocode data section csv file and
    then inserts it into the geocode_data table in the locations
    database.

    Args:
        conn: SQLite3 connection object
        file_path: string of filepath to section csv

    """
    data_frame = pandas.read_csv(file_path, sep="\t")
    data_frame.to_sql("geocode", conn, if_exists="append", index=False)

locations_database_setup(location_path)

Location database setup.

Sets up database for locations data. Creates database, loads data from csv. Creates tables in database. Inserts csv data into the database.

Parameters:

Name Type Description Default
location_path str

Path string to project location.

 required
Source code in report_generator/project_setup/locations_db_setup.py 
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
def locations_database_setup(location_path: str) -> None:
    """Location database setup.

    Sets up database for locations data. Creates database,
    loads data from csv. Creates tables in database. Inserts
    csv data into the database.

    Args:
        location_path (str): Path string to project location.

    """
    db_path = os.path.join(location_path, "location_database")
    csv_path = os.path.join(location_path, "csv_files")
    conn = create_connection(db_path)
    logger.info("Location Database Created")
    create_tables(conn)
    time.sleep(1)
    logger.info("Tables Created")
    insert_country_data(conn, csv_path)
    logger.info("Country Data Populated")
    time.sleep(1)
    logger.info("Populating Geocode data:")
    insert_geocode_data(conn, csv_path)
    logger.info("Geocode Data Populated")
    time.sleep(1)
    logger.info("Location database set up complete.")
    conn.close()

main()

Locations db main method.

Source code in report_generator/project_setup/locations_db_setup.py 
216
217
218
def main():
    """Locations db main method."""
    locations_database_setup()