This repository provides two Python modules for performing robust, logged, and flexible database operations on SQLite and PostgreSQL databases. These classes are designed for streamlined data engineering workflows, especially where Pandas integration is essential.
Provides the SQLiteDB class for interacting with SQLite databases.
- Connect to a SQLite database file.
- Read table records into a Pandas DataFrame.
- Insert single or batch records from DataFrames.
- Join module metadata to enrich serial number data.
- Retrieve the latest measurement date.
- Centralized logging of errors and events.
from sqlite_operations import SQLiteDB
db = SQLiteDB("/path/to/database.db")
df = db.read_records("module-metadata")
db.create_sqlite_record("module-metadata", ["column1", "column2"], ["value1", "value2"])Provides the PostgresDB class for interacting with PostgreSQL databases.
- Connect to PostgreSQL with credentials.
- Query tables into Pandas DataFrames.
- Insert single records using parameterized SQL queries.
- Execute arbitrary SQL commands.
- Built-in error handling and cursor management.
from postgres_operations import PostgresDB
db = PostgresDB(
host="localhost",
dbname="your_db",
user="your_user",
password="your_password"
)
results = db.read_records_from_postgres("SELECT * FROM module_metadata;")
db.create_postgres_records_from_dataframe("module_metadata", ["module_id", "make"], ["123", "ABC Solar"])Provides the NSF_DB class for interacting with interacting with the bucket in NSF ACCESS.
- Credential Handling: Loads access credentials from JSON securely
- S3 Client Config: Custom S3-compatible client with private endpoint support
- Upload Support: Uploads raw datafiles using pd.Series.
The JSON key file must include the following fields:
{
"access_key_id": "YOUR_ACCESS_KEY",
"secret_access_key": "YOUR_SECRET_KEY",
"endpoint_url": "https://your-private-endpoint"
}Note the keyfile should be a .json file, like key.json, to avoid running into errors, when initializing/authenticating the bucket. Furthermore, make sure to get the keys for R/W from the bucket allocation details in ColdFront.
from nsf_operations import NSF_DB
nsf_db = NSF_DB(
key_file: "/path/to/your/key_file.json"
)
# Upload file
df = pd.Series(['file1.txt','file2.txt'])
nsf_db.put_files(df, bucket_name="bucket_name") # Change to match the bucket name to upload files
## To upload to a specific folder in the bucket
nsf_db.upload_files(df, bucket_name="bucket_name", prefix="test") #Change the name of the prefix argument to upload to a specifc folder, automatically creates folder if not present inside the bucket.
## To list all the files in the bucket
nsf_db.list_files(df, bucket_name="bucket_name")
## To list all the files in the bucket directory
nsf_db.list_files(df, bucket_name="bucket_name", prefix="test") # Change the prefix argument to list files in a different directory
## To get files from the bucket
nsf_db.get_files(bucket_name="{bucket_name}", prefix=None) # Change the prefix argument to download files from specific directory in the bucket- Python 3.7+
- pandas
- sqlite3 (standard library)
- psycopg2
- boto3
- botocore == 1.35.95
Install required packages:
pip install pandas psycopg2 boto3 botocoreThe SQLite class automatically generates a log file (named based on the DB path) that tracks operations and exceptions. PostgreSQL operations print informative errors and could easily be extended with Python’s logging module.
- Automated Raw Data Upload to NSF ACCESS (Future Considerations: Parallel Processing (Upload/Download), Progress Bar)
- RDF Enrichment Via Comments and Metadata Table
- Workflow Integrations and Notebook Support
- Integration with Airflow and other orchestration tools
Brent Thompson University of Central Florida – Data Enabled Photovoltaics Research Group
MIT License – Feel free to use and adapt this code for academic, commercial, or personal use.