CODEWITHSUNDEEP
sql-serverpython-3.xsqlalchemy
dady7749
dady7749

Reputation: 125

SQLalchemy append dataframe to existing SQL Server table

I'm trying to append two columns from a dataframe to an existing SQL server table. The code runs but when I query the SQL table, the additional rows are not present. What am I missing?

import sqlalchemy
engine = sqlalchemy.create_engine("mssql+pyodbc://user:pw@host:port/dbname?driver=ODBC+Driver+13+for+SQL+Server")

df.to_sql(name='database.tablename', con=engine, if_exists='append', index=False)

Upvotes: 1

Views: 4714

Answers (1)

Bill Huang
Bill Huang

Reputation: 4648

You cannot use dot notation in the name= parameter. Just use name=tablename. The other parts are fine.

If you need to assign a non-default (dbo) schema, there is a schema= parameter for df.to_sql(). The prefix database. is redundant because you have already assigned dbname in the engine.

Tested with SQL Server 2017 (latest docker image on debian 10) and anaconda python 3.7.

Test code

SQL Server part (create an empty table)

use testdb;
go

if OBJECT_ID('testdb..test') is not null
    drop table test;
create table test (
    [Brand] varchar(max),
    [Price] money
);

Python part

from pandas import DataFrame
import sqlalchemy

# check your driver string
# import pyodbc
# pyodbc.drivers()  # ['ODBC Driver 17 for SQL Server']

# connect
eng = sqlalchemy.create_engine("mssql+pyodbc://myid:mypw@localhost:1433/testdb?driver=ODBC+Driver+17+for+SQL+Server")

df = DataFrame(
    data={'Brand': ['A','B','C'],
          'Price': [10.00, 20.00, 30.00]}, 
    columns=['Brand', 'Price']
)
df.to_sql(name="test", schema="dbo", con=eng, if_exists="append", index=False)

Result

select * from [test]

| Brand | Price   |
|-------|---------|
| A     | 10.0000 |
| B     | 20.0000 |
| C     | 30.0000 |

Upvotes: 2

Related Questions