销售派单创建数据库操作

import pandas as pd
import pymysql
from sqlalchemy import create_engine
import datetime

#创造pymysql链接
conn1 = pymysql.Connect(
    host = 'localhost',
    port = 3306,
    user = 'root',
    password = '123456',
    database = 'recommend2',
    charset = 'utf8',
)

# sqlalchemy的链接
conn2 = create_engine('mysql+pymysql://root:123456@localhost:3306/recommend2?charset=utf8')
conn3 = create_engine('mysql+pymysql://root:123456@localhost:3306/recommend?charset=utf8')
# SQL语句
'''
create table status( 
    id int primary key auto_increment, 
    content varchar(20)
);

create table category(
    id int primary key auto_increment,
    content varchar(100) not null
);

create table customer (
    id int primary key auto_increment,
    tel varchar(32),
    category_id int,
    constraint c1 foreign key (category_id) references category(id) on delete cascade
);

create table salesstaff(
    id int primary key auto_increment,
    name varchar(16),
    account varchar(16) not null unique ,
    password varchar(32) not null
);

create table mission(
    id int primary key auto_increment,
    customer_id int not null ,
    salesstaff_id int not null,
    createDate date not null,
    status_id int not null,
    constraint c2 foreign key (customer_id) references customer(id) on delete cascade ,
    constraint c3 foreign key (salesstaff_id) references salesstaff(id) on delete cascade ,
    constraint c4 foreign key (status_id) references status(id) on delete cascade

);
'''

sql_status = 'create table status( id int primary key auto_increment, content varchar(20));'
sql_category = 'create table category( id int primary key auto_increment,content varchar(100) not null);'
sql_customer = 'create table customer ( id int primary key auto_increment, tel varchar(32), category_id int, constraint c1 foreign key (category_id) references category(id) on delete cascade);'
sql_salesstaff = 'create table salesstaff( id int primary key auto_increment, name varchar(16), account varchar(16) not null unique , password varchar(32) not null );'
sql_mission = 'create table mission( id int primary key auto_increment, customer_id int not null , salesstaff_id int not null, createDate date not null, status_id int not null, constraint c2 foreign key (customer_id) references customer(id) on delete cascade , constraint c3 foreign key (salesstaff_id) references salesstaff(id) on delete cascade , constraint c4 foreign key (status_id) references status(id) on delete cascade );'


cs = conn1.cursor()
cs.execute(sql_status)
cs.execute(sql_category)
cs.execute(sql_customer)
cs.execute(sql_salesstaff)
cs.execute(sql_mission)
conn1.commit()
cs.close()# 创建表结构

# 读取销售表
df_salesstaff = pd.read_excel(
    './数据/销售人员.xlsx',
    sheet_name='销售信息',
)
df_salesstaff.head()
df_salesstaff['name'] = df_salesstaff['name'].apply(
    lambda name:name.strip()
)#将name列的空格去掉

# 写入数据
df_salesstaff.to_sql(
    'salesstaff',         # sql表名字
    conn2,                # mysql链接
    index=False,          # 写入数据的时候，不使用index
    if_exists='append'    # 如果表结构存在，则添加
)

# 读取客户信息表中的全部sheet_names，sheet_names其实为标签名
categorys = pd.ExcelFile('./数据/顾客信息.xlsx').sheet_names#得到职业种类的列表
categorys

# 构造SQL语句
values = []
for category in categorys:
    values.append('("%s")' % category)
sql = 'insert into category (content) values %s;' % ','.join(values)
sql

values

# 执行SQL语句
cs = conn1.cursor()
cs.execute(sql)
conn1.commit()
cs.close()#关掉之前的连接

# 顾客表有（id,tel,category_id ),pd.read_excel读取顾客信息中的全部数据，customer_pd_dict格式： {'sheet_name':dataframe}
customer_pd_dict = pd.read_excel('./数据/顾客信息.xlsx',sheet_name=None) 
# 每个dataframe添加种类列
for key in customer_pd_dict.keys():
    customer_pd_dict[key]['种类'] = key
# 合并多个dataframe
df_customer = pd.concat(
    [customer_pd_dict[key] for key in customer_pd_dict.keys()],# 合并dataframe的表集合
    axis=0, # 行合并
)

df_customer.head()

# 更改列名
df_customer.columns = ['tel','category']
df_customer.head()

# 建立category到category_id的映射
# 读取category1
df_category = pd.read_sql('select id,content from category;',conn1)
df_category.head()

df_category.index = df_category['content'].values
df_category.head()

category_content_to_id_dict = df_category.to_dict()['id']
category_content_to_id_dict

# 修正df_customer中的category为category_id
df_customer['category'] = df_customer['category'].apply(
    lambda category: category_content_to_id_dict[category]
)
df_customer.head()

# 修正列名字
df_customer.columns = ['tel','category_id']
df_customer.head()

# 写入数据库
df_customer.to_sql(
    'customer',
    conn2,
    index=False,
    if_exists='append',
)

沟通结果

# 读取任务表
mission_df_dict = pd.read_excel('./数据/2020-06月销售任务清单.xlsx',sheet_name=None)
mission_df_dict
# 添加日期列
for createDate in mission_df_dict.keys():
    mission_df_dict[createDate]['createDate'] = createDate
# 合并任务表
df_mission = pd.concat(
    [mission_df_dict[createDate] for createDate in mission_df_dict.keys()],
    axis=0,
)
df_mission.head()

# 修正列名
df_mission.columns = ['tel','salesstaff_name','status','createDate']
df_mission.head()

# 统计搜有的沟通结果status
statuss = df_mission['status'].unique().tolist()
statuss

# 创造status的dataframe，用来写入MySQL
df_status = pd.DataFrame(
    statuss,
    columns = ['content'],
)
df_status.head()

# 写入数据库
df_status.to_sql(
    'status',
    conn2,
    index=False,
    if_exists='append',
)

沟通任务

df_mission.head()

# tel--> customer.id
# 查询customer
df_customer = pd.read_sql(
    'select id,tel from customer;',
    conn2,
)
df_customer.head()

df_customer.index = df_customer['tel'].values
df_customer.head()

customer_tel_to_id_dict = df_customer.to_dict()['id']
customer_tel_to_id_dict

# salesstaff_name-->salesstaff.id
# 查询salesstaff
df_salesstaff = pd.read_sql(
    'select id,name from salesstaff;',
    conn2
)
df_salesstaff.index = df_salesstaff['name'].values
salesstaff_name_to_id_dict = df_salesstaff.to_dict()['id']
salesstaff_name_to_id_dict

# status-->status.id
# 查询salesstaff
df_status = pd.read_sql(
    'select id,content from status;',
    conn2
)
df_status.index = df_status['content'].values
status_content_to_id_dict = df_status.to_dict()['id']
status_content_to_id_dict

# 变更外键
df_mission['tel'] = df_mission['tel'].apply(
    lambda tel:customer_tel_to_id_dict[tel]
)
df_mission['salesstaff_name'] = df_mission['salesstaff_name'].apply(
    lambda name:salesstaff_name_to_id_dict[name]
)
df_mission['status'] = df_mission['status'].apply(
    lambda status:status_content_to_id_dict[status]
)
df_mission.head()

# 修改列名
df_mission.columns = ['customer_id','salesstaff_id','status_id','createDate']
df_mission.head()

df_mission.to_sql('mission',conn2,index=False,if_exists='append')