wine-python/sql

%matplotlib inline
import pandas as pd
import matplotlib
import matplotlib.pyplot as plt
matplotlib.style.use(['seaborn-talk', 'seaborn-ticks', 'seaborn-whitegrid'])

!curl 'http://stevenmasley.me/winemag-data_first150k.csv' -o wine.csv

!rm wine.csv.gz
!gzip wine.csv
!gzip -cd wine.csv.gz | head -3

wine = pd.read_csv("wine.csv.gz", encoding="utf-8", dtype="unicode")
wine_1 = {
    'Unnamed: 0':"ID"
}
wine.rename(columns=wine_1, inplace=True)

!sudo -H pip3 install -U sqlalchemy
from sqlalchemy import create_engine

conn_string = 'mysql://{user}:{password}@{host}:{port}/?charset=utf8'.format(
    user='root',
    password='dwdstudent2015',
    host = 'localhost',
    port=3306,
    encoding='utf-8'
)
engine = create_engine(conn_string)

engine.execute('CREATE DATABASE IF NOT EXISTS wine')
engine.execute('USE wine')
engine.execute('DROP TABLE IF EXISTS wine')

wine.to_sql(name='wine3', if_exists='replace', index=False, con=engine, chunksize=1000)