Hi there,
Is there a way to export custom measurements (scale readings and dissolved oxygen readings) via the UI, the data is in the database but I don’t know how to extract it. Or is the appropriate way to extract the data via SQLite command line?
Hi there,
Is there a way to export custom measurements (scale readings and dissolved oxygen readings) via the UI, the data is in the database but I don’t know how to extract it. Or is the appropriate way to extract the data via SQLite command line?
Hi @Johannes,
This is on our roadmap: How can plugins add tables to /export-data? · Issue #112 · Pioreactor/pioreactorui · GitHub
Currently, you will have to use the utility:
pio run export_experiment_data --help
Example:
pio run export_experiment_data --tables my_custom_sql_table --output ~/output.zip
This puts a file output.zip
into your home folder, and you can scp
/ filezilla it to your local computer.
Also interested in this feature. Would make sensor plugin much easier to use.
Made a python script that does all of the required steps on your computer. Makes it easier to pull data than manually doing it via UI anyway for my use case. Others may find it useful as you can build on top of it to get plots etc. Change variable and directories as needed
import paramiko
from scp import SCPClient
import zipfile
import os
# Set up SSH and SCP connection details
hostname = 'pio.local'
username = 'pioreactor'
password = 'raspberry'
remote_zip_path = '/home/pioreactor/temp_output.zip'
local_dir = os.path.expanduser('path on your computer')
table_name ='your custom table name'
local_zip_path = os.path.join(local_dir, 'temp_output.zip')
# Step 1: SSH into the device and run the command
def run_ssh_command():
try:
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(hostname, username=username, password=password)
experiment_name = input("Enter the experiment name: ")
command = f'pio run export_experiment_data --experiment "{experiment_name}" --tables {table_name} --output {remote_zip_path}'
stdin, stdout, stderr = ssh.exec_command(command)
error = stderr.read().decode()
if error:
print("Error:", error)
ssh.close()
print("Experiment data export complete.")
except Exception as e:
print("Error in SSH command execution:", e)
# Step 2: SCP the file to the local directory
def scp_file():
try:
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(hostname, username=username, password=password)
with SCPClient(ssh.get_transport()) as scp:
scp.get(remote_zip_path, local_zip_path)
ssh.close()
print("File transfer complete.")
except Exception as e:
print("Error in SCP file transfer:", e)
# Step 3: Unzip the file and rename the CSV inside
def unzip_and_rename():
try:
# Ensure local directory exists
if not os.path.exists(local_dir):
os.makedirs(local_dir)
# Unzip the downloaded file
with zipfile.ZipFile(local_zip_path, 'r') as zip_ref:
zip_ref.extractall(local_dir)
# Find the extracted CSV file
extracted_files = zip_ref.namelist()
csv_file = [f for f in extracted_files if f.endswith('.csv')][0]
csv_path = os.path.join(local_dir, csv_file)
# Prompt for new file name
new_filename = input("Enter new file name for the CSV (without extension): ") + ".csv"
new_filepath = os.path.join(local_dir, new_filename)
# Rename the file
os.rename(csv_path, new_filepath)
print(f"CSV file has been renamed to {new_filename}")
except Exception as e:
print("Error in unzipping or renaming file:", e)
# Run the steps in sequence
run_ssh_command()
scp_file()
unzip_and_rename()
This is now much easier to accomplish with our export datasets API, in version 24.12.5. See docs here: