Getting started with MySQL

Date: April 1st 2016
Last updated: April 1st 2016

Run MySQL

mysql -u root -p

MySQL shell
Get grants for user 'duck' at localhost. Copy the password into the following mysql statement.

USE surfdiary; # database changed
SHOW GRANTS FOR duck@localhost;
GRANT ALL PRIVILEGES ON surfdiary TO 'duck'@'localhost' IDENTIFIED BY PASSWORD '*password' WITH GRANT OPTION;
GRANT SELECT ON surfdiary.* TO duck@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
exit

Connector in Python

# library
import MySQLdb

# connect to db
db = MySQLdb.connect(host='localhost',user='duck',passwd='password',db='surfdiary')

cur = db.cursor()
cur.execute("SELECT boardlength, boardwidth FROM surferprofile_board;")

for row in cur.fetchall():
    print(row)

#... 
#('5 10', '19 3/4')
#('5 10', '18 7/8')
#...

Send sql query to pandas dataframe

import pandas as pd

df_mysql = pd.read_sql("""SELECT boardperformance, 
                                  boardlength, 
                                  boardwidth, 
                                  boardthickness, 
                                  boardvolume FROM surferprofile_board;""", 
                                  con = db)

df_mysql
#   boardperformance boardlength boardwidth boardthickness  boardvolume
#0                HP        5 10     18 3/8         2 3/16        24.69
#1                HP         6 0     18 5/8         2 5/16        27.17
#2                HP         6 2     18 3/4          2 3/8        28.25