append the data to already existing table in pandas using to_sql

Question

I have the following data frame

ipdb> csv_data
  country    sale        date  trans_factor
0   India  403171  12/01/2012             1
1  Bhutan  394096  12/01/2012             2
2   Nepal   super  12/01/2012             3
3  madhya  355883  12/01/2012             4
4   sudan     man  12/01/2012             5

As of now i am using below code to insert data in table, like if table already exists, drop it and create new table

csv_file_path = data_mapping_record.csv_file_path
original_csv_header = pandas.read_csv(csv_file_path).columns.tolist()
csv_data = pandas.read_csv(csv_file_path, skiprows=[0], names=original_csv_header, infer_datetime_format=True)
table_name = data_mapping_record.csv_file_path.split('/')[-1].split('.')[0]
engine = create_engine(
    'postgresql://username:password@localhost:5432/pandas_data')

# Delete table if already exits
engine.execute("""DROP TABLE IF EXISTS "%s" """ % (table_name))

# Write the pandas dataframe to database using sqlalchemy and pands.to_sql
csv_data_frame.to_sql(table_name, engine, chunksize=1000)

But what i need is, without deleting the table, if table already exists just append the data to the already existing one, is there any way in pandas to_sql method ?

Community · Accepted Answer · 2020-06-20 09:12:55Z

51

IIUC you can simply use if_exists='append' parameter:

csv_data_frame.to_sql(table_name, engine, if_exists='append', chunksize=1000)

from docs:

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 27, 2017 at 13:37

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

append the data to already existing table in pandas using to_sql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related