pandas往mysql表中追加数据_如何将pandas数据框插入到数据库中已存在的表中？

Zen of Python:

Explicit is better than implicit.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.

)

所以，我推荐这个例子，通常是：df.to_sql(con=engine, name='table_name',if_exists='append', dtype={

'Column1': String(255),

'Column2': FLOAT,

'Column3': INT,

'createTime': DATETIME},index=False)

手动设置sql表主键(如：Id)，并在Navicat或MySQL工作台中检查increment。

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 `.

.. versionadded:: 0.24.0

就这些。