Overview

This article describes how to connect to MySQL from Domino.

MySQL is an open source relational database management system.

Python and mysql-connector-python

Domino recommends the mysql-connector-python library for interacting with MySQL databases from Python.

Environment setup

Use the Dockerfile instruction below to install psycopg2 in your environment.

This instruction assumes you already have pip installed.

RUN pip install mysql-connector-python

For a basic introduction to modifying Domino environments, watch this tutorial video.

Credential setup

There are several environment variables you should set up to store secure information about your MySQL connection. Set the following as Domino environment variables on your user account:

• MYSQL_HOST

  Hostname where your MySQL service is running. Make sure your MySQL service and network firewall are configured to accept connections from Domino.

• MYSQL_USER

  The MySQL user you want to authenticate as.

• MYSQL_PASSWORD

  The password for the user chosen above.

Read Environment variables for secure credential storage to learn more about Domino environment variables.

Usage

Read the mysql-connector-python documentation for detailed information on how to use the package. Below is a simple example for connecting to MySQL with mysql-connector-python where:

• you have set up environment variables noted above with the host, user, and password
• your user has access to a database named db1 in the target MySQL instance
• the db1 database contains a table called employees

from mysql.connector import (connection)
import os

# fetch values from environment variables and set the target database
hostname = os.environ['MYSQL_HOST']
username = os.environ['MYSQL_USER']
password = os.environ['MYSQL_PASSWORD']
dbname = 'db1'

# establish connection to db1 database in your mysql service
cnx = connection.MySQLConnection(user=username,
                                 password=password,
                                 host=hostname,
                                 database=dbname)

# create cursor for passing queries to database
cursor = cnx.cursor()

# define query
query = ("SELECT * FROM employees")

# execute query
cursor.execute(query)

# print results
for row in cursor:
  print(row)

# close connection
cnx.close()

R and RMySQL

Domino recommends the RMySQL library for interacting with MySQL services from R.

Environment setup

Use the Dockerfile instructions below to add RMySQL to your environment.

RUN sudo apt-get install -y libmariadb-client-lgpl-dev
RUN R -e 'install.packages("RMySQL")'

For a basic introduction to modifying Domino environments, watch this tutorial video.

Credential setup

There are several environment variables you should set up to store secure information about your MySQL connection. Set the following as Domino environment variables on your user account:

• MYSQL_HOST

  Hostname where your MySQL service is running. Make sure your MySQL service and network firewall are configured to accept connections from Domino.

• MYSQL_USER

  The MySQL user you want to authenticate as.

• MYSQL_PASSWORD

  The password for the user chosen above.

Read Environment variables for secure credential storage to learn more about Domino environment variables.

Usage

Read the RMySQL documentation for detailed information on how to use the package. Below is a simple example for connecting to MySQL with RMySQL where:

• you have set up environment variables noted above with the host, user, and password
• your user has access to a database named db1 in the target MySQL instance
• the database contains a table named employees

# load the library
library(RMySQL)

# fetch values from environment variables and set the target database
hostname <- Sys.getenv['MYSQL_HOST']
username <-  Sys.getenv['MYSQL_USER']
password <- Sys.getenv['MYSQL_PASSWORD']
database <- 'db1'

# set up a driver and use it to create a connection to your database
con <- dbConnect(RMySQL::MySQL(), host = hostname,
 user = username, password = password, dbname = database)

# run a query and load the response into a dataframe
df_mysql <- dbGetQuery(con, "SELECT * FROM employees")

# close your connection when finished
dbDisconnect(con)