■ SQLITE 데이터베이스 파일을 다운로드하고 해당 데이터를 업데이트하는 방법을 보여준다.
▶ main.py
|
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 |
import os import requests import shutil import sqlite3 import pandas as pd sourceURL = "https://storage.googleapis.com/benchmarks-artifacts/travel-db/travel2.sqlite" targetFilePath = "travel2.sqlite" backupFilePath = "travel2.backup.sqlite" overwrite = False def downloadDatabase(targetFilePath, backupFilePath): if overwrite or not os.path.exists(targetFilePath): response = requests.get(sourceURL) response.raise_for_status() with open(targetFilePath, "wb") as bufferedWriter: bufferedWriter.write(response.content) shutil.copy(targetFilePath, backupFilePath) def updataDatabase(targetFilePath, backupFilePath): shutil.copy(backupFilePath, targetFilePath) connection = sqlite3.connect(targetFilePath) tableNameList = pd.read_sql("SELECT name FROM sqlite_master WHERE type='table';", connection).name.tolist() dataFrameDictionary = {} for tableName in tableNameList: dataFrameDictionary[tableName] = pd.read_sql(f"SELECT * from {tableName}", connection) maximumActualDepartureTimestamp = pd.to_datetime(dataFrameDictionary["flights"]["actual_departure"].replace("\\N", pd.NaT)).max() localCurrentTimestamp = pd.to_datetime("now").tz_localize(maximumActualDepartureTimestamp.tz) timeDelta = localCurrentTimestamp - maximumActualDepartureTimestamp dataFrameDictionary["bookings"]["book_date"] = pd.to_datetime(dataFrameDictionary["bookings"]["book_date"].replace("\\N", pd.NaT), utc = True) + timeDelta dateTimeColumnNameList = [ "scheduled_departure", "scheduled_arrival", "actual_departure", "actual_arrival" ] for dateTimeColumName in dateTimeColumnNameList: dataFrameDictionary["flights"][dateTimeColumName] = pd.to_datetime(dataFrameDictionary["flights"][dateTimeColumName].replace("\\N", pd.NaT)) + timeDelta for tableName, dataFrame in dataFrameDictionary.items(): dataFrame.to_sql(tableName, connection, if_exists = "replace", index = False) del dataFrame del dataFrameDictionary connection.commit() connection.close() downloadDatabase(targetFilePath, backupFilePath) updataDatabase(targetFilePath, backupFilePath) |
▶ requirements.txt
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
certifi==2024.12.14 charset-normalizer==3.4.1 idna==3.10 numpy==2.2.1 pandas==2.2.3 python-dateutil==2.9.0.post0 pytz==2024.2 requests==2.32.3 six==1.17.0 tzdata==2024.2 urllib3==2.3.0 |
※ pip install requests pandas 명령을 실행했다.