Export All ARIA Database Table and Column Names Using Python

The Eclipse Scripting API (ESAPI) doesn't always provide you with the data you need. Usually, however, you can get that data from the ARIA database directly.

I explained how to do this in the post Access the ARIA Database with the Entity Framework. In that post, I also showed you how to get the primary physician's full name. In another post, I showed you how to Get the MLC Leaf Widths Using the Entity Framework.

For the data you need, you're going to have to search the ARIA database to find any relevant tables and columns. There are over 6,000 columns across all tables, so searching by eye won't be easy. I normally use SQL Server Management Studio to access the database, but I haven't found a way to search through the tables and columns.

The ARIA Varian System Database Reference Guide from myvarian contains the names of all the database tables and columns, as well as their description. You can search through this PDF to find any relevant names. However, not everyone has access to myvarian.

Therefore, I wrote a Python 3 script to export all table and column names from the ARIA database. You can save the results to a file and then search through it using a text editor. The script is available on GitHub as ExportAriaTablesAndColumns.

You don't need to run the script from a thick client or from a Citrix server. As long as the computer you use has access to the ARIA database server, it will work. Of course, you'll need a user name and password to connect to the database.

Before you run the script, you need to have Python 3 installed. Python doesn't come with a SQL Server library out of the box, so you'll need to install one separately. Fortunately, it's very easy to do so in the command-line:

pip install pyodbc

I had never used this library before, so I often referred to the documentation as I developed the script. Because the script is very small, I'll include it here:

import pyodbc

connection = pyodbc.connect(
    'DRIVER={SQL Server};'
    'SERVER=server;'
    'DATABASE=variansystem;'
    'UID=username;'
    'PWD=password')

cursor = connection.cursor()
tables = [row.table_name for row in cursor.tables(tableType='TABLE')]

for table in tables:
    print(table)

    cursor = connection.cursor()
    columns = [row.column_name for row in cursor.columns(table)]

    for column in columns:
        print("  " + column)

Of course, you need to replace server, username, and password with the correct values.

The main concept to understand is the cursor. This is a database concept that allows you to go through database objects, such as tables and queries. Here, I'm using the cursor to go through all the tables and all the columns per table.

It looks like SQL Server supports only one cursor at a time, so first I get all the table names and be done with that cursor. Then, I go through each table, output its name, and get a new cursor. Using that cursor, I get the column names for that table and be done with that cursor. Finally, I output each column name (preceded by two spaces for indentation).

You can run the script in the command-line and re-direct the output to a file:

python export-aria-tables-columns.py > output.txt

Now you can open the output file in a text editor and search for the data you're looking for. You may or may not find it right away (or at all). You'll probably have to try a few related words.