Skip to content

AcoustiBase

AcoustiBase

Create an AcoustiBase object

Description:

AcoustiBase provides a convenient way to save material parameters used within AcousticTMM or identified using AcousticID. An SQLite database named 'acoustibase.db' is automatically created if the save_layer parameter from any of the 'Add_XXX_Layer' methods in AcousticTMM is set to True. Likewise, if the 'to_database' method is called from AcousticID after an optimization routine, the results will be saved to the database.

Source code in src/acoustipy/Database.py 
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
class AcoustiBase():
    '''
    Create an AcoustiBase object

    Description:
    ------------
    AcoustiBase provides a convenient way to save material parameters used within AcousticTMM or identified using AcousticID.  An SQLite database
    named 'acoustibase.db' is automatically created if the save_layer parameter from any of the 'Add_XXX_Layer' methods in AcousticTMM  is set to True.
    Likewise, if the 'to_database' method is called from AcousticID after an optimization routine, the results will be saved to the database.
    '''

    def __init__(self):

        self.connection = sqlite3.connect("acoustibase.db")

        self.cur = self.connection.cursor()

        self.cur.execute('''CREATE TABLE IF NOT EXISTS LAYER(id integer PRIMARY KEY, 
                                                            name TEXT NOT NULL UNIQUE,
                                                            base_model text,
                                                            biot_ef_model text,
                                                            thickness float,
                                                            flow resistivity float,
                                                            porosity float,
                                                            tortuosity float,
                                                            viscous_characteristic_length float,
                                                            thermal_characteristic_length float,
                                                            thermal_permeability float,
                                                            thermal_tortuosity float,
                                                            viscous_tortuosity float,
                                                            mass_density float,
                                                            pore_diameter float,
                                                            c_to_c_dist float,
                                                            median_pore_size float,
                                                            pore_size_distribution float)''')

        self.cur.execute('''CREATE TABLE IF NOT EXISTS STRUCTURE(id integer PRIMARY KEY, 
                                                            structure_name TEXT NOT NULL UNIQUE,
                                                            layer1 TEXT NOT NULL,
                                                            layer2 text,
                                                            layer3 text,
                                                            layer4 text,
                                                            layer5 text,
                                                            layer6 text,
                                                            layer7 text,
                                                            layer8 text,
                                                            layer9 text,
                                                            layer10 text,
                                                            layer11 text,
                                                            layer12 text,
                                                            layer13 text,
                                                            layer14 text,
                                                            layer15 text,
                                                            layer16 text)''')



    def close(self):
        """
        Close the sqlite connection
        """
        self.connection.close()

    def execute(self,
                new_data: list,
                table: str):
        """
        Insert a row of data into a table in the database

        Parameters:
        -----------

        new_data (list):
            data to insert into the table

        table (str):
            LAYER or STRUCTURE --> table to insert data into
        """
        try:
            self.cur.execute(f'INSERT INTO {table} VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', new_data)
        except sqlite3.IntegrityError:
            raise ValueError("Layer name already exists in the database.  Please choose a unique name.")

    def commit(self):
        """
        commit changes to the database
        """
        self.connection.commit()

    def pull(self,
             table: str):
        '''
        retrieve all data from a specified table in the database

        Parameters:
        -----------

        table (str):
            LAYER or STRUCTURE --> table to pull data from
        '''
        self.cur.execute(f'SELECT * from {table}')
        rows = self.cur.fetchall()
        return(rows)

    def query(self,
              SQL: str,
              params: str):
        '''
        Query the database

        Parameters:
        -----------

        SQL (str):
            SQL query to perform to execute on the database

        params (str):
            General parameter to feed to the SQL statement
        '''
        self.cur.execute(SQL,params)
        rows = self.cur.fetchall()
        return(rows)

    def summarize_layers(self):
        '''
        Write all information in the LAYER table of the database to a csv file
        '''
        self.cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
        tables = self.cur.fetchall()
        data = self.pull(tables[0][0])

        curs = self.cur
        sql = "select * from %s where 1=0;" % tables[0][0]
        curs.execute(sql)
        header =  [d[0] for d in curs.description]

        with open('layer_summary.csv','w',newline='') as out:
            csv_out=csv.writer(out)
            csv_out.writerow(header)
            csv_out.writerows(data)

    def summarize_structures(self):
        '''
        Write all information in the STRUCTURE table of the database to a csv file
        '''
        self.cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
        tables = self.cur.fetchall()
        data = self.pull(tables[1][0])

        curs = self.cur
        sql = "select * from %s where 1=0;" % tables[1][0]
        curs.execute(sql)
        header =  [d[0] for d in curs.description]

        with open('structure_summary.csv','w',newline='') as out:
            csv_out=csv.writer(out)
            csv_out.writerow(header)
            csv_out.writerows(data)

close()

Close the sqlite connection

Source code in src/acoustipy/Database.py 
62
63
64
65
66
def close(self):
    """
    Close the sqlite connection
    """
    self.connection.close()

execute(new_data, table)

Insert a row of data into a table in the database

Parameters:

new_data (list): data to insert into the table

table (str): LAYER or STRUCTURE --> table to insert data into

Source code in src/acoustipy/Database.py 
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
def execute(self,
            new_data: list,
            table: str):
    """
    Insert a row of data into a table in the database

    Parameters:
    -----------

    new_data (list):
        data to insert into the table

    table (str):
        LAYER or STRUCTURE --> table to insert data into
    """
    try:
        self.cur.execute(f'INSERT INTO {table} VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', new_data)
    except sqlite3.IntegrityError:
        raise ValueError("Layer name already exists in the database.  Please choose a unique name.")

commit()

commit changes to the database

Source code in src/acoustipy/Database.py 
88
89
90
91
92
def commit(self):
    """
    commit changes to the database
    """
    self.connection.commit()

pull(table)

retrieve all data from a specified table in the database

Parameters:

table (str): LAYER or STRUCTURE --> table to pull data from

Source code in src/acoustipy/Database.py 
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
def pull(self,
         table: str):
    '''
    retrieve all data from a specified table in the database

    Parameters:
    -----------

    table (str):
        LAYER or STRUCTURE --> table to pull data from
    '''
    self.cur.execute(f'SELECT * from {table}')
    rows = self.cur.fetchall()
    return(rows)

query(SQL, params)

Query the database

Parameters:

SQL (str): SQL query to perform to execute on the database

params (str): General parameter to feed to the SQL statement

Source code in src/acoustipy/Database.py 
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
def query(self,
          SQL: str,
          params: str):
    '''
    Query the database

    Parameters:
    -----------

    SQL (str):
        SQL query to perform to execute on the database

    params (str):
        General parameter to feed to the SQL statement
    '''
    self.cur.execute(SQL,params)
    rows = self.cur.fetchall()
    return(rows)

summarize_layers()

Write all information in the LAYER table of the database to a csv file

Source code in src/acoustipy/Database.py 
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
def summarize_layers(self):
    '''
    Write all information in the LAYER table of the database to a csv file
    '''
    self.cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = self.cur.fetchall()
    data = self.pull(tables[0][0])

    curs = self.cur
    sql = "select * from %s where 1=0;" % tables[0][0]
    curs.execute(sql)
    header =  [d[0] for d in curs.description]

    with open('layer_summary.csv','w',newline='') as out:
        csv_out=csv.writer(out)
        csv_out.writerow(header)
        csv_out.writerows(data)

summarize_structures()

Write all information in the STRUCTURE table of the database to a csv file

Source code in src/acoustipy/Database.py 
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
def summarize_structures(self):
    '''
    Write all information in the STRUCTURE table of the database to a csv file
    '''
    self.cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = self.cur.fetchall()
    data = self.pull(tables[1][0])

    curs = self.cur
    sql = "select * from %s where 1=0;" % tables[1][0]
    curs.execute(sql)
    header =  [d[0] for d in curs.description]

    with open('structure_summary.csv','w',newline='') as out:
        csv_out=csv.writer(out)
        csv_out.writerow(header)
        csv_out.writerows(data)