in Product


Using Python Jupyter Notebook With SingleStoreDB

Arnaud Comet

Director, Product Management

Using Python Jupyter Notebook With SingleStoreDB

I love notebooks as I can easily emulate my code, visualize its output and collaborate with my colleagues.

Today, I want to show you a quick Python Jupyter notebook tutorial on how to connect to SingleStoreDB from your local notebook. I also give you some code snippets on how to write into and read from SingleStoreDB. I will finish with a quick visualization code snippet using Plotly.

To dive deeper into SingleStore Notebooks, check out this blog on our launch. Looking for even more technical detail? Head over to our Notebooks documentation.

introIntro

Here are a few things to know before starting:

  • I am using a csv file from the gapminder data that you can download here
  • I would recommend using Visual Studio Code or Anaconda.
  • I uploaded the notebook that can be downloaded here.

You should be familiar with working with a local notebook environment, installing Python library and of course using SingleStoreDB (try it for free).

get-the-right-librariesGet the Right Libraries

The first cell in my notebook is to import the libraries in your notebook environment. If you don’t have these libraries installed, you can go on on pypi.org to install each library on your local machine (Pymysql, pandas, Plotly and SQLalchemy).

import pymysql
import pandas as pd
from pandas.io import sql
from sqlalchemy import Column, VARCHAR
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
import sqlalchemy
import pandas as pd
import plotly.express as px

connect-to-a-single-store-db-databaseConnect to a SingleStoreDB Database

My second cell is to set the variables for my connection:

# I set the username, password and database name as variables
UserName='Your user name'
Password='Your Password'
DatabaseName='Your database'
URL='Your URL'

where-can-you-find-the-urlWhere Can You Find the URL?

The URL you need to enter will look like the following:

svc-df0gt59a-af3a-4ty5-9ff2-13b8h81c155-dml.aws-oregon-2.svc.singlestore.com

If you have a workspace, you can access the connection string by clicking on your workspace, and then the Connect button:

The next step is to find the connection string URL:

The third cell in my notebook is to set the connection to the database:

# Creating the database connection
db_connection_str = "mysql+pymysql://"+UserName+ ":" +Password
+"@"+URL+"/"+ DatabaseName
db_connection = create_engine(db_connection_str)

insert-a-dataframe-into-a-newly-created-tableInsert a Dataframe Into a Newly Created Table

If you want to follow the tutorial, you can download the csv file on your local machine in the same folder as your notebook.

First, you need to load the csv file into a dataframe (fourth cell):

df_data = pd.read_csv("gapminder_tidy.csv")
df_data

Second, you can load that dataframe into an existing or new table (it will create the table in SingleStoreDB):

# Insert whole DataFrame into MySQL
df_data.to_sql('gapmindertidy', con = db_connection, if_exists = 'append', 
index = False, chunksize = 1000, dtype ={'country': sqlalchemy.types.NVARCHAR(length=255),
 'continent': sqlalchemy.types.NVARCHAR(length=255),
 'year': sqlalchemy.types.INTEGER(),
 'metric': sqlalchemy.types.NVARCHAR(length=255),
 'value': sqlalchemy.types.Float(precision=3, asdecimal=True)} )

If you switch to SingleStoreDB portal, you should see the following result for a “Select * from gapmindertidy” command:

read-a-table-from-single-store-dbRead a Table from SingleStoreDB

Now, you can read the table you just created with the following command:

DataFromDB = pd.read_sql('SELECT * FROM gapmindertidy', con=db_connection)
DataFromDB

 You should see the following result:

visualize-data-using-plotlyVisualize Data Using Plotly

Here is a code snippet to do some visualization on this table:

df_gdp_oceania = DataFromDB.query("continent=='Oceania' &
metric=='gdpPercap'").sort_values(['country','year'],ascending = [True,
True])
fig = px.line(df_gdp_oceania, x = "year", y = "value", color = "country")
fig.show()

That code snippet should show the following graph once executed:

Voila, you are all set to do more serious things with SingleStoreDB using Python and notebooks!

Stay tuned on our developer experience announcements in the future by following us on Twitter @SingleStoreDevs.

Try SingleStoreDB free.


Share