loading dataframe into table postgres and pandas with auto-incrementing id

user_14740191 0
Viewed 257

Is it possible to use the to_sql method in load a pandas dataframe into postgres if I have an incrementing column? Whenever I try to load the dataframe in my postgres table, the incrementing column id disappears. My dataframe I'm using to load into postgres does not contain the id, its just name, role and location.

my table:

CREATE TABLE Mytable (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    role VARCHAR(50),
    location VARCHAR(50),
);

CREATE UNIQUE INDEX index_by_name ON mytable (name);

inserting the table:

engine = create_engine("postgresql+psycopg2://postgres:postgres@localhost/cool_database")
df = pd.read_excel(mycooldataframe.xlsx)

df.to_sql("mytable", engine, if_exists="replace", index=False)
pandas python-3.x postgresql python
1 Answers

Try using if_exists="append" in your to_sql function. If you use "replace" instead it might recreate the table using only the columns in the excel file.

user_107342000
Related