Python sqlalchemy trying to write pandas dataframe to SQL Server using .to_sql

Question

I have a python code through which I am getting a pandas dataframe "df". I am trying to write this dataframe to Microsoft SQL server. I am trying to connect through the following code by I am getting an error

import pyodbc
from sqlalchemy import create_engine

engine = create_engine('mssql+pyodbc:///?odbc_connect=DRIVER={SQL Server};SERVER=bidept;DATABASE=BIDB;UID=sdcc\neils;PWD=neil!pass')
engine.connect()
df.to_sql(name='[BIDB].[dbo].[Test]',con=engine, if_exists='append')

However at the engine.connect() line I am getting the following error

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('08001', '[08001] [Microsoft][ODBC SQL Server Driver]Neither DSN nor SERVER keyword supplied (0) (SQLDriverConnect)')

Can anyone tell me what I am missing. I am using Microsoft SQL Server Management Studio - 14.0.17177.0

I connect to the SQL server through the following

Server type: Database Engine
Server name: bidept
Authentication: Windows Authentication

for which I log into my windows using username : sdcc\neils
and password : neil!pass

I am new to databases and python. Kindly let me know if you need any additional details. Any help will be greatly appreciated. Thank you in advance.

Possible duplicate of Connecting to SQL server from SQLAlchemy: "Neither DSN nor SERVER keyword supplied" — Jim G.
– Jim G., Commented Dec 25, 2017 at 15:44

Neil S · Accepted Answer · 2017-11-21 23:01:30Z

3

I was finally able to make it run.

import pyodbc
from sqlalchemy import create_engine
import urllib

params = urllib.quote_plus(r'DRIVER={SQL Server};SERVER=bidept;DATABASE=BIDB;Trusted_Connection=yes')
### For python 3.5: urllib.parse.quote_plus 
conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
engine = create_engine(conn_str)
reload(sys)
sys.setdefaultencoding('utf8')

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

Thanks to @gord-thompson who answered Here

Although my in my sql server, all the tables are under the 'dbo' schema (i.e. dbo.Test1, dbo.Other_Tables) and this query puts my table in 'sdcc\neils' schema (i.e. sdcc\neils.Test1, sdcc\neils.Other_Tables) any solution to this?

answered Nov 21, 2017 at 23:01

Neil S

2292 gold badges8 silver badges26 bronze badges

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

1 Comment

Neil S Over a year ago

solution df.to_sql(name='Test',con=engine, if_exists='append',index=False, schema="dbo")

Collectives™ on Stack Overflow

Python sqlalchemy trying to write pandas dataframe to SQL Server using .to_sql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related