Python 如何将 Pandas 数据框插入到数据库中已经存在的表中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38610723/
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
How to insert a pandas dataframe to an already existing table in a database?
提问by DevEx
I'm using sqlalchemyin pandas to query postgres database and then insert results of a transformation to another table on the same database. But when I do
df.to_sql('db_table2', engine)I get this error message:
ValueError: Table 'db_table2' already exists.I noticed it want to create a new table. How to insert pandas dataframe to an already existing table ?
我sqlalchemy在 Pandas 中使用来查询 postgres 数据库，然后将转换结果插入到同一数据库上的另一个表中。但是当我这样做时，
df.to_sql('db_table2', engine)我收到此错误消息：
ValueError: Table 'db_table2' already exists.我注意到它想要创建一个新表。如何将 Pandas 数据框插入到已经存在的表中？
df = pd.read_sql_query('select * from "db_table1"',con=engine)
#do transformation then save df to db_table2
df.to_sql('db_table2', engine)
ValueError: Table 'db_table2' already exists
回答by MaxU
make use of if_existsparameter:
使用if_exists参数：
df.to_sql('db_table2', engine, if_exists='replace')
or
或者
df.to_sql('db_table2', engine, if_exists='append')
from docstring:
来自文档字符串：
"""
if_exists : {'fail', 'replace', 'append'}, default 'fail'
- fail: If table exists, do nothing.
- replace: If table exists, drop it, recreate it, and insert data.
- append: If table exists, insert data. Create if does not exist.
"""
回答by JiangKui
Zen of Python:
Explicit is better than implicit.
Python之禅：
显式优于隐式。
df.to_sql(
name,# Name of SQL table.
con, # sqlalchemy.engine.Engine or sqlite3.Connection
schema=None, # Something can't understand yet. just keep it.
if_exists='fail', # How to behave if the table already exists. You can use 'replace', 'append' to replace it.
index=True, # It means index of DataFrame will save. Set False to ignore the index of DataFrame.
index_label=None, # Depend on index.
chunksize=None, # Just means chunksize. If DataFrame is big will need this parameter.
dtype=None, # Set the columns type of sql table.
method=None, # Unstable. Ignore it.
)
So, I recommend this example, normally:
所以，我推荐这个例子，通常：
df.to_sql(con=engine, name='table_name',if_exists='append', dtype={
'Column1': String(255),
'Column2': FLOAT,
'Column3': INT,
'createTime': DATETIME},index=False)
Set the sql table Primary Key manually(like: Id) and check increment in Navicat or MySQL Workbench.
手动设置 sql 表主键（如：Id）并在 Navicat 或 MySQL Workbench 中检查增量。
The Id will increment automatically.
Id 将自动增加。
Parameters
----------
name : string
Name of SQL table.
con : sqlalchemy.engine.Engine or sqlite3.Connection
Using SQLAlchemy makes it possible to use any DB supported by that
library. Legacy support is provided for sqlite3.Connection objects.
schema : string, optional
Specify the schema (if database flavor supports this). If None, use
default schema.
if_exists : {'fail', 'replace', 'append'}, default 'fail'
How to behave if the table already exists.
* fail: Raise a ValueError.
* replace: Drop the table before inserting new values.
* append: Insert new values to the existing table.
index : bool, default True
Write DataFrame index as a column. Uses `index_label` as the column
name in the table.
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.
A sequence should be given if the DataFrame uses MultiIndex.
chunksize : int, optional
Rows will be written in batches of this size at a time. By default,
all rows will be written at once.
dtype : dict, optional
Specifying the datatype for columns. The keys should be the column
names and the values should be the SQLAlchemy types or strings for
the sqlite3 legacy mode.
method : {None, 'multi', callable}, default None
Controls the SQL insertion clause used:
* None : Uses standard SQL ``INSERT`` clause (one per row).
* 'multi': Pass multiple values in a single ``INSERT`` clause.
* callable with signature ``(pd_table, conn, keys, data_iter)``.
Details and a sample callable implementation can be found in the
section :ref:`insert method <io.sql.method>`.
.. versionadded:: 0.24.0
That's all.
就这样。
