Pandas 将数据插入 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45288598/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pandas Insert data into MySQL
提问by Java
I am trying to insert columns of data that I extracted from .csv file into MySQL using Pandas (Python).
我正在尝试使用 Pandas (Python) 将从 .csv 文件中提取的数据列插入到 MySQL 中。
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')
But, it does not mention about specific column names in Table1..
但是，它没有提到 Table1 中的特定列名。
How do we express that?
我们如何表达？
回答by OneCricketeer
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')
But, regarding your question, unless I am mistaken in my understanding of Pandas, whatever columns dfpresently has, those are going to be written to the columns of the same name of the mysql table.
但是，关于您的问题，除非我对 Pandas 的理解有误，否则无论df目前有哪些列，这些列都将写入 mysql 表的同名列。
If you need different column names, you'll want to rename those in the DataFrame
如果您需要不同的列名，则需要重命名 DataFrame 中的列名
Or use the parameters, as mentioned,
或者使用参数，如上所述，
index: boolean, default True
Write DataFrame index as a column.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
index: 布尔值，默认 True
将 DataFrame 索引写入列。index_label：字符串或序列，默认无
索引列的列标签。如果没有给出（默认）并且索引为真，则使用索引名称
回答by Adnan shah
This is what i did in my project
这就是我在我的项目中所做的
import pandas as pd
import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://root:@localhost/pd_test')
ratings = pd.read_csv('ratings2.csv', sep='\t', encoding='latin-1',
usecols=['user_id', 'movie_id', 'user_emb_id',
'movie_emb_id','rating'])
ratings.to_sql('test', con=engine, if_exists='append',index=False,chunksize=1)
Hope this help!!
希望这有帮助！！
