Zepl
Search…
MySQL

  1. 1.
    Navigate to: Resources > Data Sources
  2. 2.
    Select the MySQL Icon:

  • 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

Python
Scala
PySpark
R
SQL
%python
import pandas as pd
# Connect to MySQL using attached Data Source object called zepl_docs_mysql
conn = z.getDatasource("zepl_docs_mysql")
​
# Create cursor object
cursor = conn.cursor()
cursor.execute("USE employees")
​
# Query Table customers, fetch results, and return results to a list object (res)
cursor.execute('SELECT * FROM customers')
res = cursor.fetchall()
​
# Create Pandas DataFrame from query results
df = pd.DataFrame(data=res, columns = cursor.column_names)
​
# Display results
z.show(df)
Use z.getDatasource() to return MySQL connection object: mysql.connector.connect​
%spark
var url = "jdbc:mysql://<host>:<port>/<database>"
var table_name = "<table name>"
var driver = "com.mysql.jdbc.Driver" // Do not change
var user = "<username>"
var pass = "<password>"
​
// Create JDBC connection to MySQL using spark.read operations
val df = spark.read
.format("jdbc")
.option("driver", driver)
.option("url", url)
.option("dbtable", table_name)
.option("user", user)
.option("password", pass)
.load()
​
// Display DataFrame
z.show(df)

Please follow guidance from the Python tab

%pyspark
​
url = "jdbc:mysql://<hostname>:<port>/<database_name>"
table_name = "<table_name>"
driver = "com.mysql.jdbc.Driver" # Do not change
user = "<username>"
password = "<password>"
​
# Create JDBC connection to MySQL using spark.read operations
df = spark.read\
.format("jdbc")\
.option("driver", driver)\
.option("url", url)\
.option("dbtable", table_name)\
.option("user", user)\
.option("password", password)\
.load()
​
# Display DataFrame
z.show(df)
%r
# TODO:
%datasource.<data source name here>
# Specify db to query from
USE employees;
​
# Read data through 'select' statement
SELECT * FROM customers;

Python
Scala
PySpark
R
SQL
%python
import pandas as pd
​
# Connect to MySQL using attached Data Source. Create connection object
conn = z.getDatasource("zepl_docs_mysql")
# Create cursor object
cursor = conn.cursor()
​
# Create Database
cursor.execute("CREATE DATABASE if not exists employees DEFAULT CHARACTER SET 'utf8'")
​
# Select Database - Not required if added to the Data Source connection
cursor. execute("USE employees")
​
# Create Table
cursor.execute("CREATE TABLE if not exists customers (name VARCHAR(255), address VARCHAR(255))")
​
# Create data to Insert
data=(['Sean Connery', '6925 Hollywood Blvd, Hollywood, CA 90028'], ['Dolly Parton', '6712 Hollywood Boulevard, Hollywood, CA 90028'])
df = pd.DataFrame(data, columns=("name", "address"))
​
# Insert Data
sql = "INSERT INTO customers(name, address) values(%s, %s)"
cursor.execute(sql, data[0])
cursor.execute(sql, data[1])
​
# Commit insert statments
conn.commit()
​
# Close connection
cursor.close()
%spark
import java.util.Properties
​
// Create Properties object for write function
val properties = new Properties();
​
// Create inputs
var url = "jdbc:mysql://<host>:<port>/<database>"
var table_name = "<table name>"
var driver = "com.mysql.jdbc.Driver" // Do not change
var user = "<username>"
var pass = "<password>"
​
// Set Properties
properties.setProperty("driver", driver);
properties.setProperty("user", user);
properties.setProperty("password", pass);
​
// Write df to MySQL table
df.write.mode("append").jdbc(url, table_name, properties)

Please follow guidance from the Python tab

%pyspark
url = "jdbc:mysql://<hostname>:<port>/<database_name>"
table_name = "<table_name>"
driver = "com.mysql.jdbc.Driver" # Do not change
user = "<username>"
password = "<password>"
​
df.write.format('jdbc').options(
url=url,
driver=driver,
dbtable=table_name,
user=user,
password=password)\
.mode('append')\
.save()
%r
# TODO:
%datasource.zepl_docs_mysql
# Specify db to query from
USE employees;
​
# Write data through 'insert' statement
INSERT INTO customers(name, address) values("Chris Rock", "7021 Hollywood Boulevard, Hollywood, CA 90028");

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

Copy link
On this page
Create an MySQL Datasource
Data Source Inputs
Use MySQL
Read
Write
Open all examples in Zepl
Configure Authentication in MySQL