...
- Data location: gltg-source-data/Fox River Watershed Database
- Lastest MS access db: FoxDB_20171116.mdb
- Converted sqlite3 file: foxdb_20171116.sqlite3 (please use this one)
- CSV for latest sqlite3 file: selected_final_foxdb_20171116.csv
- Database design document:
- All documents: http://ilrdss.sws.uiuc.edu/fox/fox_report_phase1.asp?ws=3
- General description http://ilrdss.sws.uiuc.edu/fox/downloads/Fox_Chapter_4.pdf
- Section 4.4.1 shows a good example of SQL query
- Presentation at Nutrient Monitoring Council that explains what kinds of query Jong Lee did to extract the data from the database.
...
Code Block | ||||
---|---|---|---|---|
| ||||
""" Access a sqlite3 file, executes a SQL statement, and generates a CSV file for parsing Variables ---------- data_loc : str The location of the database (for example, ./foxdb/foxdb_20171116.sqlite3) query : str The SQL statement """ import sqlite3 import pandas as pd data_loc = "" query = "" conn = sqlite3.connect(data_loc) df = pd.read_sql_query(query, conn) df['Start_Date'] = pd.to_datetime(df['Start_Date'], errors='coerce') #use this to format date and time to only date df.to_csv("output.csv", encoding='utf-8', index=False, date_format='%Y-%m-%d') conn.close() |