pandas 将数据插入MySQL [英] Pandas Insert data into MySQL

问题描述

我正在尝试使用熊猫(Python)将从.csv文件提取的数据列插入MySQL.

I am trying to insert columns of data that I extracted from .csv file into MySQL using Pandas (Python).

这是我到目前为止的代码.

Here is my code that I have so far.

import pandas as pd
from pandas.io import sql
from sqlalchemy import create_engine
engine = create_engine('mysql://username:password@localhost/dbname')
with engine.connect() as conn, conn.begin():

df = pd.read_csv('File.csv', usercols=['ID', 'START_DATE'], skiprows=skip)
print(df)

df.to_sql(con=con, name='Table1', if_exists='replace', flavor='mysql')

但是，它没有提及表1中的特定列名称.

But, it does not mention about specific column names in Table1..

我们如何表达呢?

推荐答案

我认为您的代码应像这样

I think your code should read like this

import pandas as pd
from pandas.io import sql
from sqlalchemy import create_engine

df = pd.read_csv('File.csv', usercols=['ID', 'START_DATE'], skiprows=skip)
print(df)

engine = create_engine('mysql://username:password@localhost/dbname')
with engine.connect() as conn, conn.begin():
    df.to_sql('Table1', conn, if_exists='replace')

但是，关于您的问题，除非我对Pandas的理解有误，否则无论df当前具有什么列，这些列都将被写入与mysql表相同名称的列中.

But, regarding your question, unless I am mistaken in my understanding of Pandas, whatever columns df presently has, those are going to be written to the columns of the same name of the mysql table.

如果您需要其他列名称，则需要重命名DataFrame中的列名称

If you need different column names, you'll want to rename those in the DataFrame

或使用参数，

索引:布尔值，默认为True
将DataFrame索引写为列.

index : boolean, default True
Write DataFrame index as a column.

index_label :字符串或序列，默认为无
索引列的列标签.如果未指定(默认)并且索引为True，则使用索引名称

index_label : string or sequence, default None
Column label for index column(s). If None is given (default) and index is True, then the index names are used

这篇关于 pandas 将数据插入MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！