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

Alternate ways of opening an E+ .sql file

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

ParticleSwarm gravatar image

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

3 Answers

Sort by ยป oldest newest most voted
3

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

Comments

Thank you so much!

ParticleSwarm gravatar imageParticleSwarm ( 2019-03-07 12:09:58 -0500 )edit
1

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 gravatar imageJulien Marrec ( 2019-03-08 07:00:29 -0500 )edit

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

MatthewSteen gravatar imageMatthewSteen ( 2019-03-12 10:16:17 -0500 )edit
3

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

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

Comments

1

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 gravatar imageMatthewSteen ( 2019-03-07 11:30:06 -0500 )edit

Your Answer

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

Add Answer

 

Question Tools

2 followers

Stats

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

Seen: 200 times

Last updated: Mar 08