~akspecs/numbeo-scraping-dev

json2sqlite.py: include timestamps in quality_of_life and climate tables v1 APPLIED

Rebecca Medrano: 1
 json2sqlite.py: include timestamps in quality_of_life and climate tables

 1 files changed, 17 insertions(+), 8 deletions(-)
Export patchset (mbox)
How do I use this?

Copy & paste the following snippet into your terminal to import this patchset into git:

curl -s https://lists.sr.ht/~akspecs/numbeo-scraping-dev/patches/26649/mbox | git am -3
Learn more about email & git

[PATCH] json2sqlite.py: include timestamps in quality_of_life and climate tables Export this patch

---
 numbeo/json2sqlite.py | 25 +++++++++++++++++--------
 1 file changed, 17 insertions(+), 8 deletions(-)

diff --git a/numbeo/json2sqlite.py b/numbeo/json2sqlite.py
index 9292297..b2ddb30 100755
--- a/numbeo/json2sqlite.py
+++ b/numbeo/json2sqlite.py
@@ -14,7 +14,7 @@ cur = con.cursor()
# Create countries table from countries.json
cur.execute('''
            CREATE TABLE IF NOT EXISTS countries (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                country_id INTEGER PRIMARY KEY AUTOINCREMENT,
                country_name VARCHAR(50) NOT NULL,
                country_url VARCHAR(100) NOT NULL UNIQUE
            )
@@ -34,7 +34,7 @@ with open('countries.json') as f:
# note: NOT cities.json - qoli has correct urls
cur.execute('''
            CREATE TABLE IF NOT EXISTS cities (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                city_id INTEGER PRIMARY KEY AUTOINCREMENT,
                city_name VARCHAR(50) NOT NULL,
                region VARCHAR(50),
                country_id INTEGER NOT NULL,
@@ -47,7 +47,7 @@ with open('qoli.json') as f:
    cities = json.load(f)
    for city in cities:
        country_id = cur.execute(f'''
                                 SELECT id
                                 SELECT country_id
                                 FROM countries
                                 WHERE country_name = '{city['country']}'
                                 ''').fetchone()[0]
@@ -69,6 +69,7 @@ cur.execute('''
            CREATE TABLE IF NOT EXISTS quality_of_life (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                city_id INTEGER NOT NULL UNIQUE,
                qol_timestamp TIMESTAMP,
                quality_of_life_index DECIMAL(5, 2),
                purchasing_power_index DECIMAL(5, 2),
                safety_index DECIMAL(5, 2),
@@ -86,13 +87,14 @@ with open('qoli.json') as f:
    cities = json.load(f)
    for city in cities:
        city_id = cur.execute(f'''
                              SELECT id
                              SELECT city_id
                              FROM cities
                              WHERE city_name = "{city['city']}"
                              ''').fetchone()[0]
        cur.execute('''
                    INSERT OR IGNORE INTO quality_of_life (
                        city_id,
                        qol_timestamp,
                        quality_of_life_index,
                        purchasing_power_index,
                        safety_index,
@@ -103,8 +105,9 @@ with open('qoli.json') as f:
                        traffic_commute_time_index,
                        pollution_index
                    )
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    ''', (city_id,
                          city['qol_timestamp'],
                          city['quality_of_life_index'],
                          city['purchasing_power_index'],
                          city['safety_index'],
@@ -122,6 +125,7 @@ cur.execute('''
            CREATE TABLE IF NOT EXISTS climate (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                city_id INTEGER NOT NULL UNIQUE,
                climate_timestamp TIMESTAMP,
                jan_score DECIMAL(5, 2), feb_score DECIMAL(5, 2),
                mar_score DECIMAL(5, 2), apr_score DECIMAL(5, 2),
                may_score DECIMAL(5, 2), jun_score DECIMAL(5, 2),
@@ -147,8 +151,13 @@ cur.execute('''
with open('climate.json') as f:
    cities = json.load(f)
    for city in cities:
        city_id = cur.execute(f'''
                              SELECT city_id
                              FROM cities
                              WHERE city_url = "{city['city_url']}"
                              ''').fetchone()[0]
        cur.execute('''
                    INSERT OR IGNORE INTO climate (city_id,
                    INSERT OR IGNORE INTO climate (city_id, climate_timestamp,
                                                   jan_score, feb_score,
                                                   mar_score, apr_score,
                                                   may_score, jun_score,
@@ -169,9 +178,9 @@ with open('climate.json') as f:
                                                   dec_low, dec_high)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?,
                            ?, ?, ?, ?, ?, ?, ?, ?, ?,
                            ?, ?, ?, ?, ?, ?, ?, ?, ?,
                            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
                            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    ''', (city['city_id'],
                    ''', (city_id, city['climate_timestamp'],
                          city['jan_score'], city['feb_score'],
                          city['mar_score'], city['apr_score'],
                          city['may_score'], city['jun_score'],
-- 
2.33.0
i've applied this patch as well ;)

oddly, the patch did not apply cleanly at first, which leads me to
believe that you haven't pulled all of the changes on the hosted sr.ht
repo with

git pull origin master --rebase

in any event, i'll show you in person how it looked on my end when
applying this patch.

--
ak