[PYTHON] Let's use MemSQL Vol.13: Practice 6

This time, we will challenge the cooperation between Jupyter Notebook and MemSQL! !!

In the previous verification, I tried to link MemSQL and R via Docker on Mac environment. This time, I would like to link Jupyter Notebook, which is famous in Python, with MemSQL. It seems that it is often used as a development environment for Python, so I hope it will be useful not only for analysis but also for machine learning and approaches to AI when it is done in the Python system.

Installation procedure

This time, we will work in the Mac environment as before.

(1) Introduce Xcode

First of all, we will introduce the promised Xcode.

In my environment, I have entered everything including the command line system at this stage, so I will move on to the maintenance of the related environment (in the posts of seniors who have already posted on the net, the command line environment was introduced here. There are some cases, so please check it just in case.

(3) Install Homebrew

Follow the instructions from ** Homebrew Homepage ** to install. Check if Homebrew is installed

% brew update
% brew upgrade
% brew doctor

By the way, the working environment this time was like this (for reference)

Finally, install Jupyter Notebook via pip3.

% pip3 install jupyter 

The installation will start, so please wait for a while. After the installation is complete, create a working directory and move it. (Please set xxx as appropriate)

% mkdir xxx
% cd xxx

It seems that the directory where jupyter notebook is executed automatically becomes the home screen, so in order to avoid cluttering around, create a working directory with mkdir in any location and move to that directory Let's start jupyter notebook after that.

% jupyter notebook

The startup process will start, so please wait for a while.

After the installation is executed for a while, the Jupyter Notebook home page will be displayed in the browser. I will check the promised operation.

print("Hello World")

Leave this description blank and select Run. It seems that it has started to move safely.

Finally cooperation with MemSQL ...

Now, we are finally going to work with MemSQL, but before that, there is a very important preparatory work that must be cleared. As many of you may have already noticed, when both use GUI-based consoles ... The common 8080 collision problem becomes apparent, so it is necessary to take countermeasures. This time, I will move the distribution port on the Jupyter Notebook side.

To work, follow the steps below on the Mac console.

% jupyter notebook --generate-config

The following files will be created in the top directory of your home, so check the contents and change the port number.

~/.jupyter/jupyter_notebook_config.py

With vi editor etc.

#c.NotebookApp.port = 8888

Look for and remove the # at the beginning of the line (in this case 8888 will be the port after the reboot)

The MemSQL console and Jupyter Notebook can now coexist safely.

Then install ** pymysql **.

% pip3 install pymysql

I entered quickly.

This time as well as last time, we will run MemSQL in the Docker environment on Mac and work in the direction of linking that environment with this season's Jupyter Notebook.

Start MemSQL on Docker.

% docker start memsql-ciab

Finally, the cooperation verification is executed! !!

I would like to go with this collaboration by pretending to be MySQL, which I am good at. For the database, try to read the table at the time of R cooperation created in the previous verification as it is.

Enter the following script on the Jupyter Notebook.

import pymysql

db = pymysql.connect(host='127.0.0.1',
                     user='root',
                     password='',
                     db='r_db',
                     charset='utf8',
                     cursorclass=pymysql.cursors.DictCursor)

db.commit()

If there is no problem with the settings up to the previous stage, the last ** db.commit () ** will connect to MemSQL.

Next, try reading the data from the target table.

with db:
    cur=db.cursor()
    cur.execute("SELECT * FROM test99")
    rows=cur.fetchall()
    for row in rows:
        print (row)

I was able to read the data safely.

Now let's write data from Jupyter Notebook to MemSQL ...

Basically, if you follow the above method, you can reach the goal without any problem.

import pymysql

db = pymysql.connect(host=  '127.0.0.1',
                             user='root',
                             password='',
                             db='r_db',
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)

db.commit()

with db:
   cur=db.cursor()
   cur.execute("CREATE TABLE IF NOT EXISTS p_test(id INT auto_increment primary key, data VARCHAR(25))")
   cur.execute("INSERT INTO p_test(data) VALUES('aaaaaaa')")
   cur.execute("INSERT INTO p_test(data) VALUES('bbbbbbb')")
   cur.execute("INSERT INTO p_test(data) VALUES('ccccccc')")

Let's check the result in the MemSQL console.

I was able to successfully complete the write verification.

This summary

This time, I briefly verified the cooperation between Jupyter Notebook, which is famous in the Python world, and MemSQL. If you can build the basic environment well, there will be no particular difficulty and you will be able to develop the next application. It seems that it is often used in the Python world as an engine for machine learning, AI, analysis, etc., so please take this opportunity to consider linking with MemSQL.

This environment is a combination of Docker version of MemSQL and normal installation jupyter Notebook on Mac. It is an environment that can be built on a powerful notebook like the previous R, so please feel free to use high-speed in-memory SQL.

** Let's use MemSQL Vol.14: Practice 7 **

Acknowledgments

The screenshots reprinted in this commentary use the images of the official homepage currently published by MemSQL, except for some, and are published on this content and the official homepage of MemSQL. Please understand that if the contents are different, the information of MemSQL will take precedence.