数据分析—python操作mysql数据库

一、连接和查询

①pymysql

import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
user= 'root',
password = '123456',
db='test',
charset = 'utf8')
cur = conn.cursor()
cur.execute('select * from v3_admin')
cur.fetchall()
#cur.commit() 修改语句时用
cur.close()
conn.close()

②sqlalchemy +pandas

import pandas as pd
from sqlalchemy import create_engine
sql = 'select * from v3_admin'
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
df = pd.read_sql(sql,engine)
df

二、写入数据库

import pandas as pd
from sqlalchemy import create_engine
def reader(sql):
    engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
    df = pd.read_sql(sql,engine)
    return df
user = reader('select user_id,user_name,pid from v3_chatuser limit 10')
user
address = reader('SELECT id,area_name from v3_area')
address
# merged = pd.merge(user,address,how='inner',left_on='pid',right_on='id')
# merged 
merged = user.merge(right=address,how='inner',left_on='pid',right_on='id')
result = merged.groupby(['area_name']).count()['user_name'].reset_index()
#name 表示将要写入的表名称，con数据库连接，if_exists 表示是否存在，index=False表示不插入索引
result.to_sql(name='num',con='mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8',if_exists='append',index=False)