MySQL

This section shows how to connect MySQL as a data source on the Platform.

Python

# Platform Kernels: Python 2,3
# Libraries: sqlalchemy==1.1.11, MySQLdb==1.2.5, pandas==0.20.3

# Make sure these are installed before running the code snippet below :
#!sudo apt-get -y install libmysqlclient-dev
#!pip install MySQL-python
#!pip install sqlalchemy

from sqlalchemy import create_engine
import MySQLdb as mdb
import pandas as pd


def pull_data_from_mysqldb(query, creds):
    """Given a SQL query and a connection object, this function
    returns a pandas dataframe with the results of the query.

    Parameters
    ----------

    query: string
        SQL query of interest

    creds: dict
        Contains your mysql database credentials. The dictionary should have
        four keys : "MYSQL_HOST", "MYSQL_USER", "MYSQL_PASSWD", "MYSQL_DB".
        Those should be stored as environment variables (os.envion[])
        on the platform.


    Returns
    -------

    a pandas dataframe with the results of the query.

    """
    # Establishing a connection object to the MySQL Database
    conn = mdb.connect(creds['MYSQL_HOST'], creds['MYSQL_USER'],
                       creds['MYSQL_PASSWD'], creds['MYSQL_DB'])
    return pd.read_sql(query, conn)


def insert_data_into_mysqldb(dataframe, creds, table, mode='append'):
    """Either appends, replace or fail the content of a dataframe
    to a MySQL table.

    Parameters
    ----------

    dataframe: pandas dataframe
        Dataframe of interest

    creds: dict
        Credentials to access the MySQL database.

    table: string
        Table in database that you would like to modify

    mode: string
        Database writing. Possible values are :
            - fail: if table exists, do nothing
            - replace: of table exists, drop it, recreate it, and insert dat
            - append: if table exists, insert data. Create table if does not exist.
            see pandas.DataFrame.to_sql() for more info.

    """

    # Define string for creating engine
    eng_str = 'mysql+mysqldb://{0}:{1}@{2}/{3}'.format(creds['MYSQL_USER'],
                                                       creds['MYSQL_PASSWD'],
                                                       creds['MYSQL_HOST'],
                                                       creds['MYSQL_DB'])
    engine = create_engine(eng_str, echo=False)

    dataframe.to_sql(name='{}'.format(table),
                     con=engine, if_exists=mode, index=False)

Usage Example

# Platform Kernels: Python 2,3
# Libraries: sqlalchemy==1.1.11, MySQLdb==1.2.5, pandas==0.20.3

# Make sure these are installed before running the code snippet below :
#!sudo apt-get -y install libmysqlclient-dev
#!pip install MySQL-python
#!pip install sqlalchemy

from sqlalchemy import create_engine
import MySQLdb as mdb
import pandas as pd

from mysql import pull_data_from_mysqldb, insert_data_into_mysqldb

# Usage Example :

# Put in the credentials in a dictionary:
creds = dict(MYSQL_HOST=os.environ['MYSQL_HOST'],
             MYSQL_USER=os.environ['MYSQL_USER'],
             MYSQL_PASSWD=os.environ['MYSQL_PASSWD'],
             MYSQL_DB=os.environ['MYSQL_DB'])

# Pull data from a MySQL database:
data = pull_data_from_mysqldb("SELECT * FROM my_table", creds)

# Define dataframe to append
df = pd.DataFrame({'customer_id': [1, 2, 3, 4, 5],
                   'height': [2, 3, 4, 5, 6]})

# Push a dataframe to a MySQL database:

insert_data_into_mysqldb(df, creds, 'my_table', mode='replace')

R

# Platform Kernels: R3
# Libraries: RMySQL==0.10.13, DBI==0.6-1

## Install MySQL
#!sudo apt-get -y update
#!sudo apt-get -y install libmysqlclient-dev

# Install and import RMySQL
install.packages("RMySQL")

library(RMySQL)
# RMySQL_0.10.13 DBI_0.6-1



readFromSQL <- function(conn, query) {
  # Read from a MySQL database and returns the results
  # of the query as a dataframe.
  #
  # Args:
  #   query: String containing the SQL query.
  #   conn: connection object from dbConnect()
  #
  # Returns:
  #   dataframe with the results of the query.
  #
    resultsDataframe <- dbGetQuery(conn = conn, statement = query)
    return(resultsDataframe)
}

writeToSQL <- function(conn, tableName, dataframe, mode='append') {
  # Write to a SQL database table the dataframe.
  #
  # Args:
  #   dataframe: dataframe you want to write to a table.
  #   tableName: name of the table you want to write the data to.
  #   conn: connection object from dbConnect()
  #   mode: write mode. Values are :
  #          - overwrite: Overwrite an existing table.
  #          - append: Append to an exisiting table.
  #
    if(mode=='overwrite'){
        dbWriteTable(conn = conn, name = tableName, value = dataframe, overwrite = TRUE)
    } else if(mode=='append'){
        dbWriteTable(conn = conn, name = tableName, value = dataframe, append = TRUE)
    } else
        print('Wrong choice of mode. Values are overwrite or append.')
}

Usage Example

main <- function(){

# Install and import RMySQL
# install.packages("RMySQL")

library(RMySQL)

# Set up the connection object:
conn = dbConnect(MySQL(),
                 user=Sys.getenv("MYSQL_USER"),
                 password=Sys.getenv("MYSQL_PASSWD"),
                 host=Sys.getenv("MYSQL_HOST"),
                 dbname=Sys.getenv("MYSQL_DB"))


# Read from a MySQL database table:
dataframeResults <- readFromSQL(conn, "SELECT * FROM my_table")

# Write a dataframe to a MySQL database table:
writeToSQL(conn, "my_table", dataframeResults , mode='overwrite')

# Close connection
dbDisconnect(conn)
}