pandas操作数据库

import pymysql
import pandas as pd
from sqlalchemy import create_engine
import sqlalchemy
conn = pymysql.connect(
    host="127.0.0.1",
    user="root",
    password="123",
    db="test12",
    port=3306,
    charset="utf8"
)
# 创建游标
cur = conn.cursor()
# 游标执行sql语句获取结果
cur.execute("select * from company")
# 获取所有数据
data = cur.fetchall()
print(data)
# 切片获取所有数据
for d in data:
    print(d[0], d[1], d[2])
# 提交
conn.commit()
# 关闭游标
cur.close()
# 关闭连接
conn.close()
print("1,---------")
# 用于pandas的sql语句
sql = "select * from company"
# 允许连接mysql数据库使用的方法是pymysql
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/test12?charset=utf8")
df = pd.read_sql(sql, engine)
print(df)
print("2,---------")
# 封装成函数:
def reader(query, db):
    sql = query
    engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/{}?charset=utf8".format(db))
    df = pd.read_sql(sql, engine)
    return df
df_company = reader("select * from company", "test12")
df_dataanalyst = reader("select * from dataanalyst", "test12")
print("3,---------")
# 根据companyId匹配
merged = pd.merge(df_dataanalyst, df_company, on="companyId")
print(merged.head())
print("4,---------")
# 获取城市和对应公司全称个数
print(merged.groupby(["city", "companyFullName"]).count())
print("5,--------")
# 切片获取id
print(merged.groupby(["city", "companyFullName"]).count()["positionId"])
print("6,-----------")
# 展示成数据框
print(merged.groupby(["city", "companyFullName"]).count()["positionId"].reset_index())
print("7,----------")
# 获取前5行
result = merged.groupby(["city", "companyFullName"]).count()["positionId"].reset_index()
print(result.head())
print("8,----------")
# 更改数据类型
print(result.info())
print("9,---------")
建立newtable2表

# 插入到新表newtable里面
result.to_sql(name="newtable2", con="mysql+pymysql://root:123@127.0.0.1:3306/test12?charset=utf8",
              if_exists="append", index=False)
print(result)
print("10,--------")
# 删除列字段
del result["city"]
print(result)
print("11,--------")
# 当有空字段时插入
result.to_sql(name="newtable2", con="mysql+pymysql://root:123@127.0.0.1:3306/test12?charset=utf8",
              if_exists="append", index=False)

 

 

# 写入到csv文件
result.to_csv(path_or_buf=r"D:\mycode\用pandas\data\newtable2.csv")
print("12,---------")