MySQL
- 1.Navigate to: Resources > Data Sources
- 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>
- 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)
%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
Last modified 2yr ago