There is a tiny SQLite database (1.4 MB) included with inspectomop to give first-time users a limited experimental playground and the ability to run code from the examples below.


Inspectomop does NOT contain EHR data from real patients. The data are entirely synthetic and come from the SynPUF dataset released by Centers for Medicaid and Medicare Services (CMS).

Connecting to a database

Inspector objects are in charge of interfacing with the backend database, extracting the available OMOP CDM tables, and performing queries.

Inspectors require a single parameter, connection_url, for instantiation:

In [1]: import inspectomop as iomop

In [2]: connection_url = iomop.test.test_connection_url()

In [3]: inspector = iomop.Inspector(connection_url)

connection_url is a database URL defined by SQLAlchemy that describes how to connect to your database. A database URL has three main components: a dialect, driver, and URL. The dialect indicates what type of backend DB you wish to connect to. You can use any supported by SQLAlchemy (MySql, SQLite, Postgres, etc.) out-of-the-box or a dialect written by a third party. See the full list here. The driver indicates which python DBAPI library you wish to use to run your queries. The SQLAlchemy dialects often contain a default DBAPI, so this may or may not be necessary depending on your configuration. Finally, the URL indicates where to look for the database and includes options for supplying a username and password.



See the SQLAlchemy docs on engine configuration for more details.

Here is an example URL for MySQL:

In [4]: mysql_url = 'mysql://johnny:appleseed@localhost/omop'

and one for SQLite:

In [5]: sql_url = 'sqlite:////abs/path/to/tiny_omop_test.sqlite3'

As you can see SQLite URLs are slightly different. They include an extra ‘/’ and thus will have ‘///’ for relative paths and ‘////’ for absolute paths.

Inspecting a database

Accessing tables

The tables property of an Inspector contains a dictionary of associated OMOP tables that are accessible by table name.

In [6]: inspector.tables.keys()
Out[6]: dict_keys(['attribute_definition', 'care_site', 'cdm_source', 'cohort', 'cohort_attribute', 'cohort_definition', 'concept', 'concept_ancestor', 'concept_class', 'concept_relationship', 'concept_synonym', 'condition_era', 'condition_occurrence', 'cost', 'death', 'device_exposure', 'domain', 'dose_era', 'drug_era', 'drug_exposure', 'drug_strength', 'fact_relationship', 'location', 'measurement', 'note', 'note_nlp', 'observation', 'observation_period', 'payer_plan_period', 'person', 'procedure_occurrence', 'provider', 'relationship', 'source_to_concept_map', 'specimen', 'visit_occurrence', 'vocabulary'])

In [7]: person = inspector.tables['person']

Accessing table columns

The columns in each table object are dot accessible and can be assigned to variables to construct query statements.

In [8]: from sqlalchemy import select

In [9]: person_id = person.person_id

In [10]: statement = select([person_id])

In [11]: print(statement)
SELECT main.person.person_id 
FROM main.person

Complete table descriptions

You can also get a description of all columns within a table, the data types, etc.

In [12]: inspector.table_info('person')
                         column      type  nullable  primary_key
0                     person_id   INTEGER     False         True
1             gender_concept_id   INTEGER     False        False
2                 year_of_birth   INTEGER     False        False
3                month_of_birth   INTEGER      True        False
4                  day_of_birth   INTEGER      True        False
5                birth_datetime  DATETIME      True        False
6               race_concept_id   INTEGER     False        False
7          ethnicity_concept_id   INTEGER     False        False
8                   location_id   INTEGER      True        False
9                   provider_id   INTEGER      True        False
10                 care_site_id   INTEGER      True        False
11          person_source_value      TEXT      True        False
12          gender_source_value      TEXT      True        False
13     gender_source_concept_id   INTEGER      True        False
14            race_source_value      TEXT      True        False
15       race_source_concept_id   INTEGER      True        False
16       ethnicity_source_value      TEXT      True        False
17  ethnicity_source_concept_id   INTEGER      True        False

Running built-in queries

A basic example

There are a variety of built in queries available in the Queries submodule. A typical query takes arguments for inputs (concept_ids, keywords, etc.), an Inspector to run the query against, and optionally a list of columns to subset from the default columns returned by the query.

# retrieve concepts for a list of concept_ids
In [13]: from inspectomop.queries.general import concepts_for_concept_ids

In [14]: concept_ids = [2, 3, 4, 7, 8, 10, 46287342, 46271022]

In [15]: return_columns = ['concept_name', 'concept_id']

In [16]: concepts_for_concept_ids(concept_ids, inspector, return_columns=return_columns).fetchall()
[(2, 'Gender'),
 (3, 'Race'),
 (4, 'Ethnicity'),
 (7, 'Metadata'),
 (8, 'Visit'),
 (10, 'Procedure'),
 (46271022, 'Chronic kidney disease'),
 (46287342, '2 ML Verapamil hydrochloride 2.5 MG/ML Injection')]


You can get a list of columns a query returns by looking at the return_columns parameter in the docstring for each query.

Specifying how results are returned

By default all queries return a Results object. Results objects behave like database cursors and have the expected methods such as .fetchone() and .fetchall() for fetching rows.


Results objects ultimately point back to the underlying DBAPI used for interacting with the DB (pymssql for SQL Server, sqlite3 for SQLite, etc). More or less these should follow the pythong DB API spec for cursor objects. Most of this is handled by SQLAlchemy. Results is a subclass of sqlalchemy.engine.ResultProxy with additional methods for working with pandas.

Fetching examples

In [17]: results = concepts_for_concept_ids(concept_ids, inspector)

#get the return column names
In [18]: results.keys()

#get one row
In [19]: results.fetchone()
Out[19]: (2, 'Gender', 'OMOP generated', 'Domain', '', 'Domain', 'OMOP Domain')

#get many rows
In [20]: two_results = results.fetchmany(2)

In [21]: len(two_results)
Out[21]: 2

#iterating over rows
In [22]: for row in results:
   ....:     print(row[:2])
(7, 'Metadata')
(8, 'Visit')
(10, 'Procedure')
(46271022, 'Chronic kidney disease')
(46287342, '2 ML Verapamil hydrochloride 2.5 MG/ML Injection')

Results as pandas DataFrames

Results objects also have two handy methods, .as_pandas() and .as_pandas_chunks(), for returning results as pandas DataFrames.

#return the results as as a dataframe
In [23]: results = concepts_for_concept_ids(concept_ids, inspector).as_pandas()

In [24]: results[['concept_name','vocabulary_id']]
                                       concept_name vocabulary_id
0                                            Gender        Domain
1                                              Race        Domain
2                                         Ethnicity        Domain
3                                          Metadata        Domain
4                                             Visit        Domain
5                                         Procedure        Domain
6                            Chronic kidney disease        SNOMED
7  2 ML Verapamil hydrochloride 2.5 MG/ML Injection        RxNorm

# return the results in chunks
In [25]: chunksize = 3

In [26]: results = concepts_for_concept_ids(concept_ids, inspector).as_pandas_chunks(chunksize)

In [27]: for num, chunk in enumerate(results):
   ....:     print('chunk {}'.format(num + 1))
   ....:     print(chunk['concept_name'])
chunk 1
0       Gender
1         Race
2    Ethnicity
Name: concept_name, dtype: object
chunk 2
0     Metadata
1        Visit
2    Procedure
Name: concept_name, dtype: object
chunk 3
0                              Chronic kidney disease
1    2 ML Verapamil hydrochloride 2.5 MG/ML Injection
Name: concept_name, dtype: object

Creating custom queries

From SQLAlchemy SQL Expressions

Statements built out of constructs from SQLAlchemy’s SQL Expression API make queries backend-neutral paving the way for sharable code that can be used in a plug-and-play fashion. While there is no guarantee that every query will work with every backend, most of the basic selects, joins, etc should run without issue.

SQLAlchemy is extremely powerful, but like any software package, has a bit of a learning curve. It is highly recommended that users read the SQL Expression Language Tutorial and note the warning below.

Below are a few simple examples of using SQLAlchemy expression language constructs for running queries on the OMOP CDM.


Tables from Inspector.tables are actually mapped to ORM objects. These are NOT the same as Table objects from the SQLAlchemy Core API, although they can be used in nearly identical fashion in SQL Expressions with the following caveat about accessing table columns:

In [28]: from sqlalchemy import alias

In [29]: p = inspector.tables['person']

In [30]: p_alias = alias(inspector.tables['person'], 'p_alias')

# p is an automapped ORM object with dot accessible columns
In [31]: p
Out[31]: sqlalchemy.ext.automap.person

In [32]: p.person_id
Out[32]: <sqlalchemy.orm.attributes.InstrumentedAttribute at 0x7fd92f702eb8>

# p_alias is an Alias object.
# Columns must be accessed using .c.column
In [33]: p_alias
Out[33]: <sqlalchemy.sql.selectable.Alias at 0x7fd92f7485c0; p_alias>

In [34]: p_alias.c.person_id
Out[34]: Column('person_id', INTEGER(), table=<p_alias>, primary_key=True, nullable=False)

# and so this fails
In [35]: p_alias.person_id
AttributeError                            Traceback (most recent call last)
<ipython-input-35-a8a43d4647a5> in <module>
----> 1 p_alias.person_id

AttributeError: 'Alias' object has no attribute 'person_id'

Explanation: Using a portion of the SQLAlchemy ORM to infer table structure was a conscious design decision. Although it makes for a bit of confusion when constructing queries with SQL expressions users that work in an interactive development environment (iPython, Jupyter Notebooks, etc.) get the benefit of dot accessible column properties. In addition, automapping alleviates compatibility issues that would inevitably arise with hard-coded table structures on future versions of the OMOP CDM.

Select all of the conditions for person 1:

In [36]: from sqlalchemy import select, and_

In [37]: c = inspector.tables['concept']

In [38]: co = inspector.tables['condition_occurrence']

In [39]: person_id = 1

In [40]: statement = select([co.condition_start_date, co.condition_concept_id, c.concept_name]).\
   ....:             where(and_(\
   ....:                 co.person_id == person_id,\
   ....:                 co.condition_concept_id == c.concept_id))

In [41]: print(statement)
SELECT main.condition_occurrence.condition_start_date, main.condition_occurrence.condition_concept_id, main.concept.concept_name 
FROM main.condition_occurrence, main.concept 
WHERE main.condition_occurrence.person_id = :person_id_1 AND main.condition_occurrence.condition_concept_id = main.concept.concept_id

In [42]: inspector.execute(statement).as_pandas()
   condition_start_date  ...                                       concept_name
0            2010-03-12  ...                                       Osteoporosis
1            2009-07-25  ...                                           Backache
2            2009-07-25  ...                                      Low back pain
3            2010-08-17  ...                                        Neck sprain
4            2010-11-05  ...                 Subchronic catatonic schizophrenia
5            2009-10-14  ...                                       Hypocalcemia
6            2010-03-12  ...                           Congestive heart failure
7            2010-11-05  ...                                      Schizophrenia
8            2010-03-12  ...               Antiallergenic drug adverse reaction
9            2010-04-01  ...                                   Bipolar disorder
10           2010-03-12  ...                          Pure hypercholesterolemia
11           2009-10-14  ...                                 Postoperative pain
12           2010-04-01  ...  Bipolar I disorder, single manic episode, in f...
13           2009-07-25  ...                                Menopausal syndrome
14           2009-07-25  ...                               Thoracic radiculitis
15           2010-03-12  ...                                 Retention of urine

[16 rows x 3 columns]

Count the number of inpatient and outpatient visits for each person broken down by visit type and sorted by person_id:

In [43]: from sqlalchemy import join, func

In [44]: vo = inspector.tables['visit_occurrence']

In [45]: j = join(vo, c, vo.visit_concept_id == c.concept_id)

In [46]: j2 = join(j, p, vo.person_id == p.person_id)

In [47]: visit_types = ['Inpatient Visit','Outpatient Visit']

In [48]: statement = select([p.person_id, func.count(vo.visit_occurrence_id).label('num_visits'), c.concept_name.label('visit_type')]).\
   ....:             select_from(j2).\
   ....:             where(c.concept_name.in_(visit_types)).\
   ....:             group_by(p.person_id, c.concept_name).\
   ....:             order_by(p.person_id)

In [49]: inspector.execute(statement).as_pandas()
   person_id  num_visits        visit_type
0          1           1   Inpatient Visit
1          1           1  Outpatient Visit
2          2           4   Inpatient Visit
3          2           2  Outpatient Visit
4          3           1  Outpatient Visit
5          5           4  Outpatient Visit
6          7          18  Outpatient Visit
7          8          11  Outpatient Visit
8          9           2  Outpatient Visit

From Strings

You can execute unaltered SQL strings directly, but remember to always used parametrized code for shared/production projects.


Only use strings for rapid prototyping and in-house projects! Executing strings directly breaks backend compatibility and can potentially lead to SQL injection attacks!


In [50]: inspector.execute('select person_id from person').as_pandas()
0          1
1          2
2          3
3          4
4          5
5          6
6          7
7          8
8          9
9         16

Sharing custom queries as functions

Custom queries that may prove useful to the OMOP CDM community can easily be shared by wrapping them in a function and following a standard recipe. View the source code on GitHub to get a better feel of how to construct queries and contribute (via pull request or posting your function in issues).

In general, consider the following:

  • appropriately named query functions should begin with the data you intend to return and end with the data/parameters you expect as input. E.g. concepts_for_concept_ids
  • the return value for a query should always be a Results object. This provides consistency and gives the end-user control over how to process the results.
  • write a docstring following the numpydoc docstring guide to accompany your code.


def output_for_input(inputs, inspector, return_columns=None):
    Short description.

    Longer explanation.

    inputs : type
        description of inputs
    inspector : inspectomop.inspector.Inspector
    return_columns : list of str, optional
        - optional subset of columns to return from the query
        - columns : ['col_name_1', 'col_name_2']

    results : inspectomop.results.Results
        a cursor-like object with methods such as fetchone(), fetchmany() etc.


    columns = [] # specify return columns

    if return_columns: # filter based on end-user selection
        columns = [col for col in columns if in return_columns]

    statement = select([columns]).where(inputs == criteria)

    return inspector.execute(statement)