domino logo
5.0
  • Tech Ecosystem
  • Get Started
  • Domino Cloud
  • Collaborate
  • Projects
  • Work with Data
  • Workspaces
  • Environments
  • Executions
  • Deploy Models and Apps
  • Model Monitoring
  • Organizations
  • Security and Credentials
  • Notifications
  • Search
  • Domino CLI
  • Troubleshooting
  • Get Help
domino logo
About Domino
Domino Data LabKnowledge BaseData Science BlogTraining
User Guide
>
Work with Data
>
Data Sources
>
Connect to Data Sources
>
Connect to MySQL

Connect to MySQL

This topic describes how to connect to MySQL from Domino. MySQL is an open source relational database management system.

Warning
Connect to MySQL
  1. Domino recommends the mysql-connector-python library to interact with MySQL databases from Python.

  2. Use the following Dockerfile instruction to install psycopg2 in your environment. You must have pip installed.

    USER root
    RUN pip install mysql-connector-python
    USER ubuntu
  3. You must set up the Domino environment variables to store secure information about your MySQL connection.

    • 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 previously.

      See Secure Credential Storage to learn more about Domino environment variables.

  4. See the mysql-connector-python documentation for information about how to use the package. The following is an example to connect to MySQL with mysql-connector-python where:

    • You have set up environment variables 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()
Connect to R and RMySQL
  1. To interact with MySQL services from R, Domino recommends the RMySQL library.

  2. Use the following Dockerfile instructions to add RMySQL to your environment.

USER root

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

USER ubuntu
  1. Set the following Domino environment variables to store secure information about your MySQL connection.

    • 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 previously.

      See Secure Credential Storage to learn more about Domino environment variables.

  2. See the RMySQL documentation for information about how to use the package. The following is an example for connecting to MySQL with RMySQL where:

    • You have set up environment variables 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)
Domino Data LabKnowledge BaseData Science BlogTraining
Copyright © 2022 Domino Data Lab. All rights reserved.