Using Deepnote for data analysis

Deepnote is an advanced data science notebook designed to facilitate collaboration and integration with data sources like databases and APIs. It supports various languages and tools, making it a versatile platform for data analysis. This article provides a step-by-step guide on using Deepnote for data analysis, focusing on Python tools, MySQL database integration, and Jupyter Notebooks.

Learning objectives

• Create Python tools for data analysis using Jupyter Notebooks
• Access data from MySQL databases for data analysis

Install anaconda

Anaconda is a popular distribution of Python and R for scientific computing and data science. It simplifies package management and deployment.

Download and install Anaconda

Download Anaconda: Access Miniconda and download the Windows Installer.
Install packages: Open the Anaconda command prompt and run the following commands to install essential packages:

conda install numpy pandas matplotlib

conda update conda

Managing environments

Data analysis often requires specific versions of Python or other tools. Setting up multiple environments helps manage these dependencies. You can create and manage environments in Anaconda to keep your projects organized and compatible with different package versions.

Setup Deepnote notebook to access data from MySQL databases

Install MySQL libraries

First, you need to install the necessary libraries to connect to MySQL databases.

pip install ipython-sql
conda install mysql-python

Load SQL Magic Extension in Jupyter Notebook

The SQL magic extension allows you to run SQL queries directly in Jupyter Notebooks.

%load_ext sql
%config SqlMagic.autopandas=True

Import libraries

Before connecting to the MySQL database, import the necessary libraries.

import pandas as pd
import numpy as np
import MySQLdb

Connect to MySQL database

To connect to your MySQL database, configure the connection string with your database credentials.

mysql://username:password@hostname/database

Execute SQL commands

You can execute SQL commands directly in your Jupyter Notebook using the %%sql magic command.

%%sql mysql://username:password@hostname/database
SELECT * FROM table_name LIMIT 10;

Create Pandas dataframe

Fetch the results of your SQL query into a Pandas DataFrame for further analysis.

df = %sql SELECT * FROM table_name WHERE condition;

Connect to pidata database and query data

Select records

Use the following command to select records from the pidata database.

%%sql mysql://pilogger:foobar@172.20.101.81/pidata
SELECT * FROM temps LIMIT 10;

Create dataframe

Fetch the selected records into a DataFrame for analysis.

df = %sql SELECT * FROM temps WHERE datetime > date(now());

Access MySQL Data Using MySQLdb Library

Connect and Query

Set up the connection parameters and establish a connection using the MySQLdb library.

database = "pidata"
hostname = "172.20.101.81"
port = 3306
uid = "pilogger"
pwd = "foobar"

Establish the connection and create a cursor object to execute queries.

conn = MySQLdb.connect(host=hostname, user=uid, passwd=pwd, db=database)
cur = conn.cursor()

Create dataframe

Fetch the data from the database into a Pandas DataFrame and close the connection.

new_dataframe = pd.read_sql("SELECT * FROM temps", con=conn)
conn.close()

Create tables and users in MySQL

Create table temps3

Use the following SQL commands to create a new table temps3 in your MySQL database.

DROP TABLE IF EXISTS temps3;
CREATE TABLE temps3 (
    device VARCHAR(20) DEFAULT NULL,
    datetime DATETIME DEFAULT NULL,
    temp FLOAT DEFAULT NULL,
    hum FLOAT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Create user and grant privileges

Create a new user and grant the necessary privileges to interact with the temps3 table.

CREATE USER 'user1'@'%' IDENTIFIED BY 'logger';
GRANT SELECT, INSERT, DELETE, UPDATE ON pidata.temps3 TO 'user1'@'%';
FLUSH PRIVILEGES;

Conclusion

Using Deepnote in conjunction with tools like Anaconda, Jupyter Notebooks, and MySQL databases streamlines the data analysis process. This guide provides a foundation for setting up your environment, accessing data, and performing analyses efficiently. If you encounter any issues, please get in touch with our support. Happy coding in Deepnote!