Question-and-Answer Resource for the Building Energy Modeling Community
Get started with the Help page
Ask Your Question

Alternate ways of opening an E+ .sql file

asked 2019-03-07 11:06:53 -0500

ParticleSwarm's avatar

updated 2019-03-07 11:39:05 -0500

Hello Unmet!

I am just curious if there is an alternate way of opening a .sql file other than writing an Open Studio reporting measure or using Dview. I would like to manipulate the .sql data via Pandas in python but upon some research, I've found that that would require a remote database connection?

In other words, Is there a way to open an existing .sql file in python, make queries to the E+ data tables, and then use that data?

Any guidance would be appreciated!

edit retag flag offensive close merge delete

6 Answers

Sort by ยป oldest newest most voted

answered 2019-03-08 06:59:48 -0500

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]
        msg = ("Cannot find the EnergyPlusVersion in the SQL file. "
               "Please inspect query used:\n{}".format(query))
        raise ValueError(msg)


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)

   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
edit flag offensive delete link more


@Julien Marrec I am having problems with step [2]. I copypasted your code but get the error mesage "unable to open database file".

The eplusout.sql file is in the correct path. Not sure what the problem is.

Any ideas?


gebhardbeck's avatar gebhardbeck  ( 2020-10-16 02:13:59 -0500 )edit

Is the database already opened by something else? not read-only. Try logging out & in again (or reboot) then try again to open the sql file.

Julien Marrec's avatar Julien Marrec  ( 2020-10-20 03:09:02 -0500 )edit

Thankyou for your answer, sadly, i tried all that and still doesnt work, also rebooting pc and only trying to open the eplusout.sql as read only it always says "unable to open database file". Also a shorter file path didn't make a difference. I'm stumped, seems like i am probably missing a tiny mistake... I can open the file with a sql viewer so the file is fine.

gebhardbeck's avatar gebhardbeck  ( 2020-10-29 10:13:58 -0500 )edit

answered 2019-03-07 11:42:34 -0500

updated 2019-03-07 13:27:55 -0500

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)
edit flag offensive delete link more


Thank you so much!

ParticleSwarm's avatar ParticleSwarm  ( 2019-03-07 12:09:58 -0500 )edit

Please do use an URI to set it to read-only instead to avoid problems when reruning simulation while sql file is open in python. See my answer below.

Julien Marrec's avatar Julien Marrec  ( 2019-03-08 07:00:29 -0500 )edit

@ParticleSwarm consider accepting Julien's answer, which is the safer option.

MatthewSteen's avatar MatthewSteen  ( 2019-03-12 10:16:17 -0500 )edit

answered 2019-10-15 11:30:49 -0500

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

edit flag offensive delete link more

answered 2019-03-07 11:18:41 -0500

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?

edit flag offensive delete link more



The only one I have is from Julien Marrec's EPlusSQL2Pandas repo, which is no longer public. He spent a lot of time dealing with the date time format that EnergyPlus uses, which isn't standard (up vote issue #5360).

MatthewSteen's avatar MatthewSteen  ( 2019-03-07 11:30:06 -0500 )edit

answered 2021-12-28 17:14:10 -0500

altamar's avatar

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:

it is in testing mode! suggestions are welcome!

edit flag offensive delete link more

answered 2019-10-17 11:41:59 -0500

mikesweeney's avatar

updated 2019-10-17 11:43:56 -0500

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):

fyi, there is also an eQUEST version with similar functionality:

edit flag offensive delete link more

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account.

Add Answer

Training Workshops

Question Tools



Asked: 2019-03-07 11:06:53 -0500

Seen: 1,390 times

Last updated: Dec 28 '21