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)
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 |
