MySQL_Query_database.md

Template request | Bug report | Generate Data Product

Tags: #mysql #database #snippet #operations #naas

Author: Jeremy Ravenel

Description: This notebook provides an introduction to querying a MySQL database.

Input

Import libraries

import os
import pymysql
import pandas as pd

Variables

host = os.getenv("MYSQL_HOST")
port = os.getenv("MYSQL_PORT")
user = os.getenv("MYSQL_USER")
password = os.getenv("MYSQL_PASSWORD")
database = os.getenv("MYSQL_DATABASE")

Model

Connect to database

conn = pymysql.connect(
    host=host,
    port=int(port),
    user=user,
    passwd=password,
    db=database,
    charset="utf8mb4",
)

Send the query

df = pd.read_sql_query(
    "SELECT DATE(created_at) AS date, COUNT(*) AS count FROM user GROUP BY date HAVING date >= '2017-04-01' ",
    conn,
)
df.tail(10)

Output

Display result

%matplotlib inline

df.index = df['date']
p = df.tail(10).plot.bar()

conn.close()