在Jupyter Notebook中操作MySQL Database（以mysql-connector-python操作）

Demostration of jupyter notebook connecting to MySQL database by mysql-connector-python¶

How to install in ubuntu16.04:

• pip install mysql-connector-python #type in terminal

MySQL Link

In [ ]:

import mysql.connector 

The loggin command in MySQL through the terminal is:

$ mysql -u root -p  

$ passwords:

With mysql-connector, the loggin will through mysql.connector.connect()

In [ ]:

import mysql.connector
HugoDB = mysql.connector.connect(
  host = "127.0.0.1",  # Input the database server IP
  user = "root", #Input the username
  password = "********", #input the username
  database = "PyPractice", #input the name of the database
  )
cursor=HugoDB.cursor()

Use cursor.excute() to input the standard MySQL commands. Let's create a new table first.

    CREATE TABLE MyFirstTable (
    ID INT NOT NULL AUTO_INCREMENT, 
    Name VARCHAR(50) NOT NULL DEFAULT '', 
    Height FLOAT(6,2) NOT NULL DEFAULT '0', 
    Weight FLOAT(6,2) NOT NULL DEFAULT '0', 
    Waist FLOAT(6,2) NOT NULL DEFAULT '0', 
    IQ INT(3) DEFAULT NULL, 
    PRIMARY KEY (ID)
    )ENGINE=MyISAM DEFAULT CHARSET=big5;

The details of the MySQL refer to the MySQL Tutorial

In [ ]:

cursor.execute(
    "CREATE TABLE MyFirstTable (ID INT NOT NULL AUTO_INCREMENT, Name VARCHAR(50) NOT NULL DEFAULT '', Height FLOAT(6,2) NOT NULL DEFAULT '0', Weight FLOAT(6,2) NOT NULL DEFAULT '0', Waist FLOAT(6,2) NOT NULL DEFAULT '0', IQ INT(3) DEFAULT NULL, PRIMARY KEY (ID))ENGINE=MyISAM DEFAULT CHARSET=big5;")

Also, INSERT new data into the table through cursor.execute()

In [ ]:

cursor.execute("INSERT INTO MyFirstTable VALUES (NULL,'王小明','175', '72', '78', NULL);")
cursor.execute("INSERT INTO MyFirstTable VALUES (NULL,'陳小華','162', '50', '65', '115');")
cursor.execute("INSERT INTO MyFirstTable VALUES (NULL,'黃中天','180', '90', '95', '119');")

In [59]:

cursor.execute("SELECT * FROM MyFirstTable;")
show_table = cursor.fetchall()
show_table

Out[59]:

[(1, '王小明', 175.0, 72.0, 78.0, None),
 (2, '陳小華', 162.0, 50.0, 65.0, 115),
 (3, '黃中天', 180.0, 90.0, 95.0, 119)]

Compare to the execution in the terminal