Database Access

Navigation: User Guide ➔ COM Automation ➔ Python Automation ➔ Example - Database Access

Python Setup		Python Examples
Installation & Troubleshooting	Python Utilities	Basic Usage & Scenarios	Constrained FEM (numpy|scipy|matplotlib)	Optimisation (COM | scipy tools)	Model Testing Framework	Dynamic with GUI	Dynamic External DLL	Programmatic Model Generation	Importing data to SysCAD

---

Manually entering a lot of data into the database can be tedious and error-prone. However if you have the data in a spreadsheet, then you can write a script to automate this. (If you are putting stuff into an existing database, be sure to make a backup copy first, and work with that!) This example just adds basic species data, but you can add further fields for specific heat functions and other thermophysical data.

# -*- coding: utf-8 -*-
"""
Created on Wed Jul 15 09:08:46 2020
@author: Owner
"""
##This program is used to convert an excel spreadsheet into a SQLite database for SysCAD
import sqlite3 as sql
import pandas as pd
SpNameSheet = 'Species'
SpPropsSheet = 'Properties' 
NameCol = 8
PropertyCol = 9

##read data"""
NameData = pd.read_excel('C:\Work\Projects\CreateDBFromHardwiredSpecs\SDB Properties Rev 3.5.xlsm',sheet_name=SpNameSheet)
SpData = pd.read_excel('C:\Work\Projects\CreateDBFromHardwiredSpecs\SDB Properties Rev 3.5.xlsm',sheet_name=SpPropsSheet)

##now add the species to the database
##User must create a copy of the Default.93.db3 database and store it at a location of their choice
##Recommend deleting all entries in the copied Default.93.db3 file
DB = sql.connect('C:\Work\Projects\CreateDBFromHardwiredSpecs\Default.93.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 (Links Species)'],row['Short Name  (Unique - with Phase)'],row['Phase'],row['Occ'],row['Definition']])
   except:
       print("Error")
        
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:
       print("Error in inputting species data")
      
DB.commit()
DB.close()