Pandas to_sql() inserting index

Question

I am using Pandas 0.18.1, and while fiddling with this code,

import pd

def getIndividualDf(item):
    var1 = []
    # ... populate this list of numbers
    var2 = []
    # ... populate this other list of numbers

    newDf = pd.DataFrame({'var1': var1, 'var2': var2})
    newDf['extra_column'] = someIntScalar
    yield newDf

dfs = []
for item in someList:
    dfs.append(getIndividualDf(item))

resultDf = pd.concat(dfs)
resultDf['segment'] = segmentId # this is an integer scalar

from sqlalchemy import create_engine
engine = create_engine('postgresql://'+user+':'+password+'@'+host+'/'+dbname)
resultDf.reset_index().to_sql('table_name', engine, schema="schema_name", if_exists="append", index=False)

I was getting this exception:

(psycopg2.ProgrammingError) column "index" of relation "table_name" does not exist

Indeed, there is no such column in the table, only because there is no such explicit column in the data frame. Which is why it's weird.

Running

print(list(resultDf))

just before the to_sql() call, yields

['var1', 'var2', 'extra_column', 'segment']

Removing index=False from the to_sql() call changes the error to this:

(psycopg2.ProgrammingError) column "level_0" of relation "table_name" does not exist

I am puzzled. How do I get rid of index column?

Update
print(resultDf.head()) yielded this information:

     var1       var2  extra_column  segment
0       8   0.101653    2077869737   201606
1       9   0.303694    2077869737   201606
2      10   0.493210    2077869737   201606
3      11   0.661064    2077869737   201606
4      12   0.820924    2077869737   201606

try not reset_index() and add the index=False to the to_sql() call, also can you show us resultDF.head() — Steven G
– Steven G, Commented May 12, 2017 at 16:26
resultDF.head() starts off with a zero-index based column without a name, and continues with all of the named columns that are of interest to me. I'll post a sample in the question. Thanks for trying to help, I really appreciate it. — Alex
– Alex, Commented May 15, 2017 at 10:17

Steven G · Accepted Answer · 2019-02-28 01:42:40Z

59

You need not to reset the index before writing to sql such has:

resultDf.to_sql('table_name', engine, schema="schema_name", if_exists="append", index=False)

edited Feb 28, 2019 at 1:42

answered May 12, 2017 at 16:36

Steven G

17.3k11 gold badges57 silver badges79 bronze badges

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

3 Comments

Alex Over a year ago

You are absolutely right. I still don't understand why reset_index() would affect the explicit index=False, but at this point I'm too tired to ask such questons, and am just happy that I can finally move forward with my data analysis ^_^

Steven G Over a year ago

@AlexanderMP when you reset index, in creates a columns called level_0 and populate it with the index.

Mich28 Jun 20 at 1:52

It's June 2025, I'm using most current packages for all, and still getting 'level_0' column, index column is also there, index=False only affect `level_0'. I don't do any reset index

Collectives™ on Stack Overflow

Pandas to_sql() inserting index

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related