Connect with Database

Introduction

IC³ have built and maintained multiple databases to store the cleaned data. With the database, researchers are able to understand the datasets and filter the research sub-cohort easily. Our data has two different data models:

• UF IDR(Integrated Data Registry) format
• OMOP CDM v5.4
  tip

  In the future, all of data will be in OMOP CDM. We are working hard to fully map the Epic data now.

Instructions

Currently, we have three data servers.

Prod-MySQL

The database schemas are hosted by a MySQL server.
The authentication and authorization of the data server is managed by UF Health IT, to obtain the access to the database, please refer to the instructions here.
To work with the database, we recommend to use MySQL Workbench software.

• HostPath: mysql07.ahc.ufl.edu
• Port: 3306

Prod-MSSQL

The database schemas are hosted by a SQL server.
The authentication and authorization of the data server is managed by UF Health IT, to obtain the access to the database, please refer to the instructions here.
To work with the database, we recommend to use DBeaver software.

• HostPath: ahc-prismapdb.ahc.ufl.edu
• Port: 1433

databaseConnection.py

from sqlalchemy import create_engine 
from sqlalchemy.engine import URL 
# build connection with Windows Authentication
connection_string = ( 

    r"Driver={ODBC Driver 17 for SQL Server};"     

    r"Server={AHC-PRISMAPDB.AHC.UFL.EDU};"     

    r"Database={PRISMAP_IDEALIST};"     

    r"Trusted_Connection=yes;" 

) 

connection_url = URL.create( 

    "mssql+pyodbc",  

    query={"odbc_connect": connection_string} 

) 

engine = create_engine(connection_url) 

Test-MSSQL

The database schemas are hosted by a MySQL data server.
The authentication and authorization of the data server is managed by IC³ Data Core, to obtain the access to the database, please refer to the instructions here.
To work with the database, we recommend to use DBeaver software.
HostPath: 10.14.134.118 Port: 1434

databaseConnection.py

from sqlalchemy import create_engine 
from sqlalchemy.engine import URL 
# build connection with SQL Server Authentication
connection_string = ( 

  r"Driver={SQL Server Native Client 11.0};"     

  r"Server= {10.14.134.118,1434};"     

  r"Database= {IDEALIST_OMOP};"     

  "UID={xxusernamexxhere};"             

  "PWD={xxxpasswordxxhere};"    
) 

connection_url = URL.create( 

  "mssql+pyodbc",  

  query={"odbc_connect": connection_string} 

) 

engine = create_engine(connection_url) 

UF Health VPN

All databases are protected under UF Health restricted network. UF Health VPN is necessary to build the connection.