uploading dataframe to an existing table in sql server

Question

I have a dataframe that I want to upload to a SQL Server database.

I have been looking at the pandas to_sql method but I can't seem to get it to work.

My dataframe is say 500 rows with 3 columns

column name      data type
dateLg           datetime
temperature      float64
city             object

And so my SQL table is called tblCityTemperature

  column name          data type
  DateLeg              datetime
  Temp                 float
  CC                   nvarchar(20)

Is there a way of mapping the columns in a dataframe to my sql table?

I tried the following where tblColNames is the three names in my sql table.

df.to_sql("tblPrices", cnxn, index_label=tblColNames)

I get the error message below which doesn't really make sense to me

ValueError: Length of 'index_label' should match number of levels, which is 1

BENY · Accepted Answer · 2019-02-24 19:32:04Z

2

You can change your columns before to_sql

df.columns=['DateLeg','Temp','CC']

Then you just need

df.to_sql("tblPrices", cnxn, if_exists ='append')

answered Feb 24, 2019 at 19:32

BENY

324k22 gold badges176 silver badges250 bronze badges

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

5 Comments

mHelpMe Over a year ago

Thanks, does it matter if the columns are in a different order to the sql they are being appended to? I'm getting this error... DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")

mHelpMe Over a year ago

I am to get data out of the sql table just fine

BENY Over a year ago

@mHelpMe if the name same it is ok , and you may need adding index=False as well since you append to an exist table

BENY Over a year ago

@mHelpMe also it is better check the way mentioned here stackoverflow.com/questions/43136121/…, change the way you are using

mHelpMe Over a year ago

thanks just trying that now... still having database issues. Will keep plugging away at it

Collectives™ on Stack Overflow

uploading dataframe to an existing table in sql server

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related