扫码加入数据分析学习群

一、 写入MySQL数据库

import numpy as np
import pandas as pd

import pymysql
from sqlalchemy import create_engine

#连接数据库的参数

db_info = {
           'user': 'root',
           'password': 'emtf',
           'host': 'localhost',
           'database': 'test'
           }

#创建连接数据库引擎

engine = create_engine('mysql://%(user)s:%(password)s@%(host)s/%(database)s?charset=utf8'
                            % db_info, encoding='utf-8')

#创建数据

import seaborn as sns
titanic = sns.load_dataset('titanic')

#将数据写入test数据库的titanics表中

titanic.to_sql('titanics', con=engine, if_exists='append', index=False)#if the tabel don't exist, create it

二、 读MySQL数据库

sql = "SELECT * FROM {0} where sex = '{1}' and class ='{2}';".format('titanics', 'female', 'First')
df = pd.read_sql(sql, con=engine)
df.head(12)