Alternate ways of opening an E+ .sql file

edit

Please do yourself a favor and never use conn = sqlite3.connect(sql_path), as if you try to rerun your simulation while the connection to the sql database is still open in python, it won't be able to write to the SQL file, which is very annoying (you could not realize that you aren't loading the results from your latest simulation). Instead use an URI setting the mode to read-only.

Here's a safer example:

In [1]:
import os
import re
import pathlib
import sqlite3
import pandas as pd

# Turn it into an absolute path
abs_sql_path = os.path.abspath(sql_path)
# Create a read-only URI
sql_uri = '{}?mode=ro'.format(pathlib.Path(abs_sql_path).as_uri())

A good idea is probably to start by checking simulation info, to ensure you have what you need

In [2]:
query = 'SELECT EnergyPlusVersion FROM Simulations'
with sqlite3.connect(sql_uri, uri=True) as con:
    cursor = con.cursor()
    r = cursor.execute(query).fetchone()
    if r:
        simulation_info = r[0]
    else:
        msg = ("Cannot find the EnergyPlusVersion in the SQL file. "
               "Please inspect query used:\n{}".format(query))
        raise ValueError(msg)

print(simulation_info)

Out[2]: EnergyPlus, Version 9.1.0-a310a424e4, YMD=2019.03.08 12:31

Then do stuff, favoring with statements too to ensure you close connections anyways

In [3]: 
with sqlite3.connect(sql_uri, uri=True) as con:
    df_env = pd.read_sql('SELECT * FROM EnvironmentPeriods', con=con)
df_env

Out[3]: 
   EnvironmentPeriodIndex  SimulationIndex                     EnvironmentName  EnvironmentType
0                       1                1     CHICAGO ANN HTG 99.6% CONDNS DB                1
1                       2                1  CHICAGO ANN CLG .4% CONDNS WB=>MDB                1

Here's some old Python code that I had in a Jupyter notebook that connects to the SQL database, queries it for the End Uses table, and writes it to a CSV file.

# In[1]:

import os
import numpy as np
import pandas as pd
import sqlite3

# In[2]:

sql_dir = r'C:\Users\Matt\Documents\WORKING\14038 NIST\14038-Baseline\run\6-EnergyPlus-0'
sql_file = 'eplusout.sql'
sql_path = os.path.join(sql_dir, sql_file)
print sql_path
write_dir = sql_dir

# In[3]:

# Connect an create a cursor
conn = sqlite3.connect(sql_path)

#Create a cursor and return the list of all tables in the database
c = conn.cursor()

# In[4]:

query = "SELECT * FROM TabularDataWithStrings WHERE ReportName='AnnualBuildingUtilityPerformanceSummary' AND TableName='End Uses'"
end_uses = pd.read_sql(query ,conn)
end_uses.to_csv(os.path.join(write_dir, 'SQL-EndUses.csv'), index=False)
end_uses

Modelkit has an SQL query function that makes this process easy. Check out my video demonstrating how to do it.

Here are some code examples to extract data from the EnergyPlus .sql file:

basic Python

Python with eppy

Ruby / OpenStudio API

I'm sure others have favorite tools. Maybe @MatthewSteen has a python jupyter notebook he is willing to share?

Recently i was curious to get the data directly from the SQL so i do not have to manage files in OpenStudio, so i wrote a class that query all variables, merge with the variable names and index times and then you have everything on a dataframe!

Also, i corrected the ugly time format EnergyPlus uses, you can find the class with an example here:

https://github.com/AltamarMx/read_eps...

it is in testing mode! suggestions are welcome!

Here is a Python module I've developed, which abstracts away SQL queries for both tabular and Hourly/Timestep reports, has support for IP unit conversion, and also includes a Plotly-based api for generating timeseries charts (scatter, heatmap, line, histogram, etc):

https://github.com/michaelsweeney/epr...

fyi, there is also an eQUEST version with similar functionality: https://github.com/michaelsweeney/eqr...