Database Access
Jump to navigation
Jump to search
Navigation: User Guide ➔ COM Automation ➔ Example Python Files ➔ Example - Database Access
| Python Setup | Python Examples | Python Script | Optimisation | Visulisation | Python GUI | Tags and Data | |||
|---|---|---|---|---|---|---|---|---|---|
| Installation & Troubleshooting | List of Examples | Simple Script (pywin32) | SysCAD COM Python Class | Automated Model Testing | Constrained FEM (numpy|matplotlib) | Optimisation (numpy|scipy) | Adding Plots (numpy|matplotlib) | Dynamic with GUI | Accessing Data (sqlite3|pandas) |
Generate Species Tags for Stream Report
When extracting comprehensive data from SysCAD, it’s often useful to include a wide range of flow variables across multiple species. Manually creating these tags can be time-consuming—especially with long species lists. To streamline this process, you can use a CSV input file and a Python script to automatically generate species-specific tags for use in TagTable or TagSelect reports.
CSV Input File Structure
Prepare your input file with the following columns:
| Variable Group | SysCAD variable syntax | Engineering Units | Species List |
|---|---|---|---|
| MassFlow | Qo.QM. | t/h | H2O(g) |
| MassFrac | Qo.MF. | % | H2O(l) |
| Conc (All) | Qo.Cnc. | g/L | H2SO4(aq) |
| NiSO4(aq) |
The following script reads the input CSV and generates SysCAD-compatible tags for each species and variable group. The generated tags can be exported to a CSV file and easily inserted into the relevant report template when needed.
import pandas as pd
# Load the input file
df = pd.read_csv("SpeciesListInputFile1.csv")
df.columns = ['Group', 'Prefix', 'Suffix', 'Species']
# Detect group rows (where columns 1–3 are not null)
group_rows = df[df['Group'].notna() & df['Prefix'].notna() & df['Suffix'].notna()]
# Detect species entries (where column 4 is not null)
species_rows = df[df['Species'].notna()]
species_list = species_rows['Species'].tolist()
# Initialize output list
output = []
# Iterate through each group
for _, row in group_rows.iterrows():
group = row['Group']
prefix = row['Prefix']
suffix = row['Suffix']
# Determine phase requirement based on group name
if "Liq Only" in group:
valid_species = [s for s in species_list if "(aq)" in s]
elif "Vap Only" in group:
valid_species = [s for s in species_list if "(g)" in s]
else:
valid_species = species_list # No restriction
# Insert blank row before each new group (if output already has content)
if output:
output.append(["", "", "", ""])
# Add species entries for this group
for i, species in enumerate(valid_species):
groupheading = group if i == 0 else "" # Only write group name once
label = f"{group} {species}"
Eng_units = suffix
tag = f"{prefix}{species} ({suffix})"
output.append([groupheading, label, Eng_units, tag])
# Save to CSV
output_file = "species_group_tags.csv"
pd.DataFrame(output, columns=['Group', 'Label', 'Units', 'Tag']).to_csv(output_file, index=False)
print(f"✅ Tags saved to '{output_file}' with clean group headings and simplified units.")
|
Here’s a sample of the output generated by the script:
| Group | Label | Units | Tag |
| MassFlow | MassFlow H2O(g) | t/h | Qo.QM.H2O(g) (t/h) |
| MassFlow H2O(l) | t/h | Qo.QM.H2O(l) (t/h) | |
| MassFlow H2SO4(aq) | t/h | Qo.QM.H2SO4(aq) (t/h) | |
| MassFlow NiSO4(aq) | t/h | Qo.QM.NiSO4(aq) (t/h) | |
| MassFrac | MassFrac H2O(g) | % | Qo.MF.H2O(g) (%) |
| MassFrac H2O(l) | % | Qo.MF.H2O(l) (%) | |
| MassFrac H2SO4(aq) | % | Qo.MF.H2SO4(aq) (%) | |
| MassFrac NiSO4(aq) | % | Qo.MF.NiSO4(aq) (%) | |
| Conc (All) | Concentration (All basis) H2O(g) | g/L | Qo.Cnc.H2O(g) (g/L) |
| Conc (All) H2O(l) | g/L | Qo.Cnc.H2O(l) (g/L) | |
| Conc (All) H2SO4(aq) | g/L | Qo.Cnc.H2SO4(aq) (g/L) | |
| Conc (All) NiSO4(aq) | g/L | Qo.Cnc.NiSO4(aq) (g/L) |
NOTES:
- The output of this script is suitable to added to the Default Reports - Stream Table H worksheets.
- You can expand the input file with additional reporting groups to suit your project’s needs.
- For valid tag groups and syntax, refer to: List of Sample Species Variable Tags
- See "StreamReport_Species.xlsx" in Counter Current Washer Project for an example report with all the species tags added.
- The species list can be obtained from
- Species Properties ($SDB) - Other tab page, OR
- Plant Model - SpUse (Species Use).
- Solve the project
- Open the PlantModel access window, Species Tab, click the Flow.SpeciesInUse "Run" button, then select Clipboard:Used option.
- Paste the data into the input file.
Generate Tags for PSD Stream Report
For users working with particle size distribution (PSD) in their projects, it's often helpful to report stream PSD data. If the number of size intervals is small and only one ore contains size data, generating the report is relatively straightforward. However, when dealing with numerous size intervals and multiple ores with PSD data, creating a comprehensive SysCAD report can become quite time-consuming.
To simplify this process, you can use a Python script to assist with tag generation. Once generated, the tags can be copied directly into your report wherever needed.
import csv
var_names = ['FP', 'FPCum']
ore_types = ['Ore1(s)', 'Ore2(s)', 'Ore3(s)']
num_indices = 25
# Prepare rows grouped by var_name
rows = []
for var in var_names:
for i in reversed(range(num_indices)): # Reverse order: 24 → 0
row = [
f'Qo.Sz.I{i}.{var}.{ore} (%)' for ore in ore_types
]
rows.append(row)
rows.append([''] * len(ore_types)) # Blank row after each var_name group
# File name and path
file_name = 'PSD_by_column.csv'
# Write to CSV
with open(file_name, 'w', newline='') as csvfile:
writer = csv.writer(csvfile)
writer.writerow(ore_types) # Write headers
writer.writerows(rows)
# Confirmation message
print(f"✅ File saved successfully as '{file_name}'")
|
NOTES:
- The "StreamPSDReport.xlsx", located in the Size Distribution Project, was created using tags generated by this script.
- The variable tags used in the script can be expanded to include additional PSD variables. For more details on available syntax, refer to the Sz Parameter Definitions documentation.
- The tag list created by this script file is suitable for a multi-column report.
Find and Replace Tags
This example uses Python to connect to a SQLite database, allowing the user to specify a table, column, and target text for replacement. It updates matching entries and reports the number of rows modified. Useful for updating tags stored in the SysCAD ModelData.db3 file.
import sqlite3
# Default values
default_table_name = "PID_Cfg"
default_column_name = "OutputTag"
default_find_text = ".Extent (%)"
default_replace_text = ".Extent.Required (%)"
# === User-defined inputs ===
db_path = r"C:\Projects139\Alumina Demo\Full_High_Temp_Demo_Plant.spf\ModelData.db3"
# Prompt user for input, use default if input is empty
table_name = input(f"Enter table name [{default_table_name}]: ") or default_table_name
column_name = input(f"Enter column name [{default_column_name}]: ") or default_column_name
find_text = input(f"Enter text to find [{default_find_text}]: ") or default_find_text
replace_text = input(f"Enter replacement text [{default_replace_text}]: ") or default_replace_text
# Connect to the SQLite database
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
# Build and execute the update query
update_query = f"""
UPDATE {table_name}
SET {column_name} = REPLACE({column_name}, ?, ?)
WHERE {column_name} LIKE ?;
"""
try:
cursor.execute(update_query, (find_text, replace_text, f"%{find_text}%"))
rows_updated = cursor.rowcount
conn.commit()
print(f"Number of rows updated: {rows_updated}")
except sqlite3.OperationalError as e:
print(f"Error: {e}")
# Close the connection
conn.close()
|
Enter table name [PID_Cfg]:
Enter column name [OutputTag]:
Enter text to find [.Extent (%)]:
Enter replacement text [.Extent.Required (%)]:
Number of rows updated: 18
From Spreadsheet to Database
Manually entering large amounts of data into a database can be time-consuming and prone to mistakes.
- If your data is already in a spreadsheet, you can save time and reduce errors by writing a script to automate the process.
- If you're working with an existing database, it's a good idea to make a backup copy first and use that for testing.
- This example focuses on adding basic species data, but you can easily extend it to include additional fields thermophysical properties as used by the SysCAD.93.db3 file.
Using Python Script
The following example script reads species and property data from an Excel file (SampleSpeciesData.xlsx) and inserts it into a preformatted SQLite database (Test.db3). It uses the pandas library to read Excel sheets and sqlite3 to interact with the database.
## This program converts species data stored in an MS Excel spreadsheet into a preformatted SQLite database.
## It requires the 'openpyxl' library for reading Excel files with pandas.
import sqlite3 as sql
import pandas as pd
SpNameSheet = 'Species'
SpPropsSheet = 'Properties'
NameCol = 8
PropertyCol = 9
##read data"""
NameData = pd.read_excel(r'C:\SysCAD Projects\DatabaseTest\SampleSpeciesData.xlsx',sheet_name=SpNameSheet)
SpData = pd.read_excel(r'C:\SysCAD Projects\DatabaseTest\SampleSpeciesData.xlsx',sheet_name=SpPropsSheet)
## The database can be either empty or pre-populated with data.
## Each species entry is uniquely identified by a combination of ShortName and Phase.
## If a duplicate species is encountered during insertion, an error will occur and the entry will be skipped.
DB = sql.connect(r'C:\SysCAD Projects\DatabaseTest\Test.db3')
for index, row in NameData.iterrows():
try:
DB.execute("INSERT INTO 'SpeciesData' ('LongName','Component','ShortName','Phase','Occurrence','Definition') VALUES (?,?,?,?,?,?)",
[row['Long Name'], row['Component'], row['Short Name'], row['Phase'], row['Occ'], row['Definition']])
except Exception as e:
print(f"Error inserting row {index}: {e}")
continue # Skip to the next row
for index, row in SpData.iterrows():
try:
SpName = row['Species Name']
Pos1 = SpName.find('(')
Pos2 = SpName.find(')')
Phase = SpName[Pos1+1:Pos2]
ShortName = SpName[:Pos1]
SQLLine = "UPDATE 'SpeciesData' SET '"
SQLLine += row['Property'] + "' = '" + str(row['Equation (or Value)'])
SQLLine += "' WHERE (ShortName = '" + ShortName + "' AND Phase = '" + Phase + "');"
print (SQLLine)
DB.execute(SQLLine)
except sqlite3.IntegrityError as e:
print(f"Skipping duplicate or invalid entry: {row['LongName']} – {e}")
DB.commit()
DB.close()
|
Workflow Summary:
- Read Excel Data - Loads two worksheets:
- Species: Contains basic species information.
- Properties: Contains thermophysical properties for each species.
- Connect to SQLite Database - Opens a connection to Test.db3.
- Insert Species Data
- Iterates through each row in the Species worksheet.
- Inserts species details into the SpeciesData table.
- If a duplicate species (based on ShortName and Phase) is found, it logs an error and skips the entry.
- Update Property Data
- Iterates through each row in the Properties worksheet.
- Extracts ShortName and Phase from the Species Name field.
- Constructs and executes an SQL UPDATE statement to add the property value to the corresponding species entry.
- Logs any integrity errors (e.g., if the species entry doesn’t exist).
- Finalise - Commits all changes and closes the database connection.
SampleSpeciesData.xlsx
This workbook contains two worksheets, each with a specific purpose:
|
Species Worksheet - This sheet lists the species to be added to the database.
|
Properties worksheet - This sheet contains thermophysical properties associated with each species.
|