This topic describes how to connect to MySQL from Domino.
MySQL is an open source relational database management system.
The easiest way to connect to a MySQL instance from Domino is to use a Domino Data Source.
Domino recommends the mysql-connector-python library for interacting with MySQL databases from Python.
Environment setup
Use the following Dockerfile instruction to install psycopg2 in your environment.
This instruction assumes you already have pip installed.
USER root
RUN pip install mysql-connector-python
USER ubuntu
Credential setup
There are several environment variables you must 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 about 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 calledemployees
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()
Domino recommends the RMySQL library for interacting with MySQL services from R.
Credential setup
There are several environment variables you must 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 about 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)