MySQL

Create an MySQL Datasource
1.Navigate to: Resources > Data Sources
2.Select the MySQL Icon:
Data Source Inputs
Name (required)
Host (required): 
Enter the hostname of your mysql database. 
Example (using MySQL in AWS RDS): zepl-db-test.sddks8d7d89s.us-west-1.rds.amazonaws.com
Port (required): 3306 is the default port for MySQL
Database (optional): 
If this is filled in, Zepl will append the database name to your connection string. 
Example:zepl-db-test.sddks8d7d89s.us-west-1.rds.amazonaws.com/<database name>
Credential Type: See Data Source Security​
Username (required - can be entered later): Username used for connecting to MySQL
Password (required - can be entered later): Password used for connecting to MySQL
Use MySQL
Read
Python
Scala
PySpark
R
SQL
1
%python
2
import pandas as pd
3
# Connect to MySQL using attached Data Source object called zepl_docs_mysql
4
conn = z.getDatasource("zepl_docs_mysql")
5
​
6
# Create cursor object
7
cursor = conn.cursor()
8
cursor.execute("USE employees")
9
​
10
# Query Table customers, fetch results, and return results to a list object (res)
11
cursor.execute('SELECT * FROM customers')
12
res = cursor.fetchall()
13
​
14
# Create Pandas DataFrame from query results
15
df = pd.DataFrame(data=res, columns = cursor.column_names)
16
​
17
# Display results
18
z.show(df)
Copied!
Use z.getDatasource()  to return MySQL connection object: mysql.connector.connect​
1
%spark
2
var url = "jdbc:mysql://<host>:<port>/<database>"
3
var table_name = "<table name>"
4
var driver = "com.mysql.jdbc.Driver" // Do not change  
5
var user = "<username>"
6
var pass = "<password>"
7
​
8
// Create JDBC connection to MySQL using spark.read operations
9
val df = spark.read
10
  .format("jdbc")
11
  .option("driver", driver)
12
  .option("url", url)
13
  .option("dbtable", table_name)
14
  .option("user", user)
15
  .option("password", pass)
16
  .load()
17
​
18
// Display DataFrame
19
z.show(df)
Copied!
Option 1: Use Zepl's Data Source
Please follow guidance from the Python tab 
Option 2: Using SparkSQL
1
%pyspark
2
​
3
url = "jdbc:mysql://<hostname>:<port>/<database_name>"
4
table_name = "<table_name>"
5
driver = "com.mysql.jdbc.Driver" # Do not change  
6
user = "<username>"
7
password = "<password>"
8
​
9
# Create JDBC connection to MySQL using spark.read operations
10
df = spark.read\
11
  .format("jdbc")\
12
  .option("driver", driver)\
13
  .option("url", url)\
14
  .option("dbtable", table_name)\
15
  .option("user", user)\
16
  .option("password", password)\
17
  .load()
18
​
19
# Display DataFrame
20
z.show(df)
Copied!
1
%r
2
# TODO:
Copied!
1
%datasource.<data source name here> 
2
# Specify db to query from
3
USE employees;
4
​
5
# Read data through 'select' statement
6
SELECT * FROM customers;
Copied!
Write
Python
Scala
PySpark
R
SQL
1
%python
2
import pandas as pd
3
​
4
# Connect to MySQL using attached Data Source. Create connection object
5
conn = z.getDatasource("zepl_docs_mysql")
6
# Create cursor object
7
cursor = conn.cursor()
8
​
9
# Create Database
10
cursor.execute("CREATE DATABASE if not exists employees DEFAULT CHARACTER SET 'utf8'")
11
​
12
# Select Database - Not required if added to the Data Source connection
13
cursor. execute("USE employees")
14
​
15
# Create Table
16
cursor.execute("CREATE TABLE if not exists customers (name VARCHAR(255), address VARCHAR(255))")
17
​
18
# Create data to Insert
19
data=(['Sean Connery', '6925 Hollywood Blvd, Hollywood, CA 90028'], ['Dolly Parton', '6712 Hollywood Boulevard, Hollywood, CA 90028'])
20
df = pd.DataFrame(data, columns=("name", "address"))
21
​
22
# Insert Data
23
sql = "INSERT INTO customers(name, address) values(%s, %s)"
24
cursor.execute(sql, data[0])
25
cursor.execute(sql, data[1])
26
​
27
# Commit insert statments
28
conn.commit()
29
​
30
# Close connection
31
cursor.close()
Copied!
1
%spark
2
import java.util.Properties
3
​
4
// Create Properties object for write function
5
val properties = new Properties();
6
​
7
// Create inputs
8
var url = "jdbc:mysql://<host>:<port>/<database>"
9
var table_name = "<table name>"
10
var driver = "com.mysql.jdbc.Driver" // Do not change  
11
var user = "<username>"
12
var pass = "<password>"
13
​
14
// Set Properties
15
properties.setProperty("driver", driver);
16
properties.setProperty("user", user);
17
properties.setProperty("password", pass);
18
​
19
// Write df to MySQL table
20
df.write.mode("append").jdbc(url, table_name, properties)
Copied!
Option 1: Using Zepl's DataSource
Please follow guidance from the Python tab 
Option 2: Using SparkSQL
1
%pyspark
2
url = "jdbc:mysql://<hostname>:<port>/<database_name>"
3
table_name = "<table_name>"
4
driver = "com.mysql.jdbc.Driver" # Do not change  
5
user = "<username>"
6
password = "<password>"
7
​
8
df.write.format('jdbc').options(
9
      url=url,
10
      driver=driver,
11
      dbtable=table_name,
12
      user=user,
13
      password=password)\
14
      .mode('append')\
15
      .save()
Copied!
1
%r
2
# TODO:
Copied!
1
%datasource.zepl_docs_mysql
2
# Specify db to query from
3
USE employees;
4
​
5
# Write data through 'insert' statement
6
INSERT INTO customers(name, address) values("Chris Rock", "7021 Hollywood Boulevard, Hollywood, CA 90028");
Copied!
​Open all examples in Zepl​
Configure Authentication in MySQL
In your MySQL database be sure that these items are configured properly:
MySQL should be accessible to Zepl's whitelisted IP addresses
The user associated with your user name and password have access to the desired databases and tables
User should have read and write permissions on the desired databases and tables
​