Introduction

Many SACS users require custom reports for use in other programs, custom scripts, or analysis documentation. The SACS Reports Database provides a way to programmatically access SACS results data to generate these types of reports. In this post, I will show you how to utilize Python to access the SQLite database and generate a custom report using SQL a query.

Generating a SQLite Database

SACS Reports Databases may be generated whenever a code check analysis is performed. The SACS Reports Database is enabled by checking the SACS Reports Database under the Analysis Options in the Analysis Generator. An additional output file, SACS Reports Data Base, will be generated by the analysis. This is a SQLite database which can be accessed by any program that supports the SQLite format.

SACS Reports Database Option

DB Browser for SQLite

In order to help visualize the SQLite database, I will be using a program called DB Browser for SQLite. This is a free and open source program which can be downloaded here:

https://sqlitebrowser.org/

If you open a SACS Reports Database in DB Browser, you are presented with the schema for the database. The schema contains the information about the data in the database. It provides information about the tables and the columns that make up those tables. The SACS Reports Database schema is constant so every SACS Reports Database will have the same schema. It is important to note that some tables are not populated with data if there is no relevant information, so some tables may be empty.

DB Browser Schema

Clicking the Browse Data tab will display the data for the selected table. In this example I am showing the R_POSTMEMBERRESULTS table.

DB Browser Data

Each table can be thought of as a Microsoft Excel worksheet with each row representing an entry and each column representing some sort of data for that entry. In fact, DB Browser allows you to copy and paste the data from a table into Microsoft Excel for queries, charts, or calculations. Each table will have some unique identifier which can be used to quickly identify an entry and may be used in queries.

However, queries can be written and executed directly in DB Browser using the SQL language in the Execute SQL tab. I find this very useful for prototyping queries that I will use in my Python scripts later on. In this example, I am selecting all columns for each entry from R_POSTMEMBERRESULTS where the maximum unity check is greater than 1.0.

DB Browser SQL

Python Programming Environment

Running a SQL query in DB Browser is great for prototyping, but if you want generate reports as part of a repetitive workflow, Python can help. Python is a free open source programming language that can be downloaded here:

https://www.python.org/

There is a built-in SQLite library for accessing SQLite databases. The documentation for the SQLite3 library can be found here:

https://docs.python.org/3/library/sqlite3.html

I’d recommend downloading Python with Anaconda. Anaconda is a Python distribution platform which includes a lot of common scientific libraries which extend the functionality of Python. There are also creation and visualization tools which can be used to create Pythons scripts and visualize the data that you are working with. You can get the Anaconda distribution of Python here:

https://www.anaconda.com/products/individual

I use Visual Studio Code as an editor for writing Python scripts. Visual studio code supports many plugins which feature syntax highlighting, debugging, and execution of Python scripts. Any text editor can be used to create Python scripts, like the python editor included with Python or the Spyder editor included with Anaconda, but I prefer Visual Studio Code for its feature set and ease of use.

https://code.visualstudio.com/

Writing a Simple SACS Report Script

Let’s take a look now at writing a similar python script to the script we created in the DB Browser example. I am going to make a small change to reduce the number of columns which are reported. Here is the SQL query we will be using:

SELECT MemberName, MemberGroup, LoadConditionName, MAX(MaxUC) 
FROM R_POSTMEMBERRESULTS
WHERE MAXUC > 1.0 
GROUP BY MemberName
ORDER BY MaxUC DESC;

Instead of SELECT * which returns all columns for each entry, we are only selecting the columns MemberName, Member Group, LoadConditionName, and MaxUC. I am also only returning the Maximum MaxUC for each group. GROUP BY creates separate groups for each MemberName which means the maximum UC will be return for each MemberName and ORDER BY MaxUC DESC means that the table will be sorted with the descending Maximum Unity Check. Here is the updated script in DB Browser:

DB Browser Simple Script

This is starting to look like an actual report!

Now let’s take write this as a python script.
import sqlite3

# Create a SQL connection to the SACS Reports Database
conn = sqlite3.connect('sacsdb.sample02')

# Create a cursor object to execute a query
c = conn.cursor()

# Execute SQL query
c.execute('''SELECT MemberName, MemberGroup, LoadConditionName, MAX(MaxUC) 
            FROM R_POSTMEMBERRESULTS
            WHERE MAXUC > 1.0
            GROUP BY MemberName
            ORDER BY MaxUC DESC;
            ''')

# Print each row
for row in c.fetchall():
    print(row)

# Close the connection
conn.close()

First I need to import the SQLite library, then create a connection to the database. I then create a cursor object which is used to execute the SQL query and print the selected table data. Finally, I close the database connection. Here is the output that is generated by this script:

('303-401', 'T01', 'STM1', 101.31858826)
('838-807', 'W01', 'OPR1', 1.9060121775)
('835-838', 'W02', 'OPR1', 1.7982919216)
('835-803', 'W01', 'OPR1', 1.7539373636)
('307-403', 'T01', 'STM3', 1.4504685402)
('834-835', 'W01', 'OPR2', 1.3921561241)
('801-834', 'W01', 'OPR1', 1.3628070354)
('807-839', 'W01', 'OPR3', 1.2099322081)
('803-807', 'W02', 'OPR3', 1.1427518129)
('701-801', 'LG7', 'OPR3', 1.1260848045)
('703-803', 'LG7', 'OPR1', 1.1024968624)
('837-838', 'W01', 'OPR3', 1.0813544989)
('718-707', 'W01', 'STM1', 1.0537719727)
('715-703', 'W01', 'STM1', 1.0420355797)

It’s not quite as pretty as the DB Browser output, but the same data is present and can be used in subsequent code for further post processing and/or visualization.

Writing a Simple Reports Database Script with Pandas

Pandas is a Python data analysis library for tabular data. It is very well suited to working with spreadsheets and databases and may be easier to work with than the SQLite standard library in Python. It is included with the Anacondas Python distribution so if you’ve installed Python with Anaconda you should already have it.

https://pandas.pydata.org/docs/getting_started/index.html#getting-started

I am now going to make the same script from the previous example using the Pandas Sqlite interface.

import pandas as pd
import sqlite3

# Create a SQL connection to the SACS Reports Database
conn = sqlite3.connect('sacsdb.sample02')

# Execute SQL Query as a Pandas Dataframe
df = pd.read_sql_query('''SELECT MemberName, MemberGroup, LoadConditionName, MAX(MaxUC) 
                        FROM R_POSTMEMBERRESULTS
                        WHERE MAXUC > 1.0
                        GROUP BY MemberName
                        ORDER BY MaxUC DESC;
                        ''', conn)

# Print the Dataframe
print(df)

# Close the Connection
conn.close()

The code is a bit more concise; there is no cursor object defined and dataframes do not require a loop to be printed. Furthermore you will note that the dataframe contains the column and row identifiers which were not present in the previous SQLite3 implementation.

We can also use Pandas functionality to write out the table as a CSV file using the following command:

# Export to CSV
df.to_csv('max_ucs.csv')

This can be opened by Excel and formatted for reporting purposes.

Writing an Advanced Reports Database Script

Now I am going to show a more complex script using the SACS AnalysisEngine to perform the analysis, the SQL join command to combine data from multiple tables, matplotlib to generate a chart of the data and export that chart as an image, and export the tables to CSV files.

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import sqlite3
import subprocess

#SACS program information
defaultSACSDir = "C:\\Program Files (x86)\\Bentley\\Engineering\\SACS 14 CONNECT Edition (SES)"
enginePath = defaultSACSDir + "\\AnalysisEngine.exe"
runFilePath = r"D:\SACS\Communities\Post\Working with the SACS Reports Database\sacsample02.runx"

# Run the SACS Analysis
res = subprocess.run([enginePath,runFilePath,defaultSACSDir])

# Create a SQL connection to the SACS Reports Database
conn = sqlite3.connect('sacsdb.sample02')

# Execute SQL Query as a Pandas Dataframe
df = pd.read_sql_query('''SELECT R_JOINT.JointName, R_POSTJOINTREACTIONRESULTS.LoadConditionName, R_POSTJOINTREACTIONRESULTS.ForceX, R_POSTJOINTREACTIONRESULTS.ForceY, R_POSTJOINTREACTIONRESULTS.ForceZ, R_POSTJOINTREACTIONRESULTS.ForceUnits
                        FROM R_POSTJOINTREACTIONRESULTS
                        INNER JOIN R_JOINT ON R_POSTJOINTREACTIONRESULTS.JointID=R_JOINT.ID
                        WHERE LoadConditionReportOption = 1
                        ORDER BY LoadConditionName;
                        ''', conn)

# Close the Connection
conn.close()

# Create Load Summation Data Frame with BaseShear Calculation
pd.options.display.float_format = '{:.2f}'.format
load_summations = df.groupby('LoadConditionName')[['ForceX', 'ForceY', 'ForceZ']].sum()
load_summations['BaseShear'] = np.sqrt(load_summations['ForceX']**2 + load_summations['ForceY']**2)

# Export to CSV
df.to_csv('joint_reactions.csv')
load_summations.to_csv('load_summations.csv')

# Generate Plots of the Base Shear and Gravity Load Summations
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(12, 8))
load_summations['BaseShear'].plot(kind='bar', title='Base Shear', ax=ax1, ylabel=df['ForceUnits'][0])
ax2 = load_summations['ForceZ'].plot(kind='bar', title='Gravity Load', ax=ax2)
fig.suptitle('Load Summations')
plt.savefig('load_summations.png')

Here is the chart that was generated by the script:

and here are the Joint Reactions and Load Summation tables that were generated:

Conclusion

In this post I’ve shown you how to generate a SACS Reports Database, tools which can be used to interact with those SQLite databases, and some example scripts which can be used to generate reports, graphics, and perform calculations on results. Hopefully you can use this in your own work to improve your workflows and processes.