通过pd.to_sql()将DataFrame写入Mysql

 

循环创建表，并且创建主键、外键

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.types import NVARCHAR, Float, Integer


def pd2sql():
    """
    to_sql目前只支持两类mysql引擎一个是sqlalchemy和sqlliet3
    :return:
    """
    # 初始化数据库连接，使用pymysql模块
    # MySQL的用户：root, 密码:147369, 端口：3306,数据库：mydb
    # ?charset=utf8 指定数据库编码
    engine = create_engine('mysql+pymysql://root:@localhost:3306/pandas2mysql?charset=utf8')
    conn = engine.connect()
    for i in range(1, 10):
        # 指定字段的数据类型
        dtypedict = {
            'index_code': NVARCHAR(length=255),
            'date': NVARCHAR(length=255),
            'open': NVARCHAR(length=255),
            'close': NVARCHAR(length=255),
            'low': NVARCHAR(length=255),
            'high': NVARCHAR(length=255),
            'volume': NVARCHAR(length=255),
            'money': NVARCHAR(length=255),
            'change': NVARCHAR(length=255)
        }

        csv_path = r'E:\data\yucezhe\trading-data-push.20190201\2019-02-01 index data.csv'

        # 读取本地CSV文件
        df = pd.read_csv(csv_path).head()

        # 将DataFrame储存为MySQL中的数据表，不储存index列
        df.to_sql(f'csv_table{i}', engine, if_exists='replace', index=False, dtype=dtypedict)

        # 执行原生sql语句
        # 设置主键
        conn.execute(f"alter table csv_table{i} add constraint p_key primary key (index_code)")

        # 从表设置外键
        if i%2 == 0:
            conn.execute(
                f"alter table csv_table{i-1} add  foreign key (index_code) references csv_table{i}(index_code)")

        print(f"Write to MySQL successfully! ---- csv_table{i}")
    engine.dispose()


pd2sql()

# 对已存在的表做主键：alter table csv_short1 add constraint p_key primary key (index_code);

# 对已存在的表做外键：alter table csv_short1 add  foreign key (index_code) references csv_short2(index_code);

# 内连接查询：select * from a,b where a.x = b.x

 

转载于:https://www.cnblogs.com/bigtreei/p/10671260.html