How to set a primary key in table for postgres using dataframes in pandas?

Question

I want to store a table into postgres 8.4 (edited previously stated:8.1) using pandas.DataFrame.to_sql via sqlalchemy using the following command.

df.to_sql("table_name",engine)

I am using postgres. How can I get set a PRIMARY KEY in this table that I am adding to the database? I tried to use the option index in the df.to_sql however, that does not seem to work.

Postgres 8.1 is more than 10 years old and is not supported. Please consider updating to a supported postgres version. — Ihor Romanchenko
– Ihor Romanchenko, Commented Mar 23, 2016 at 15:44
I don't have a DF-specific answer, but you can always add the primary key after the fact. — Jim Nasby
– Jim Nasby, Commented Mar 23, 2016 at 16:08
Hi, I am using 8.4.20. sorry about that. If it is not DF specific, how can it be changed after the fact...that may be just as good. Thanks in advance — user2015487
– user2015487, Commented Mar 23, 2016 at 20:00

cyn102 · Accepted Answer · 2017-06-13 03:50:12Z

2

If your df contains a column "id" which you want to use as PRIMARY KEY, then do this:

df.to_sql('table_name', engine, if_exists="append", index=False)
with engine.connect() as con:
    con.execute('ALTER TABLE table_name ADD PRIMARY KEY (id);')

answered Jun 13, 2017 at 3:50

cyn102

313 bronze badges

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

1 Comment

John Curry Over a year ago

Note that this will also work if you have created say a serial primary key in the table beforehand. index=False will allow you to insert the other cols. The primary key will auto-increment.

Collectives™ on Stack Overflow

How to set a primary key in table for postgres using dataframes in pandas?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related