Connecting to SQL server from SQLAlchemy using odbc_connect

Question

I am new to Python and SQL server. I have been trying to insert a pandas df into our database for the past 2 days without any luck. Can anyone please help me debugging the errors.

I have tried the following

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 have also tried this

import pyodbc

conn_str = (
    r'Driver={SQL Server Native Client 11.0};'
    r'Server=bidept;'
    r'Database=BIDB;'
    r'Trusted_Connection=yes;'
    )

cnxn = pyodbc.connect(conn_str)

df.to_sql(name='Test',con=cnxn, if_exists='append')

for which I got this error

pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. (8180)")

Any help would be greatly appreciated as I am clueless as what to do.

Gord Thompson · Accepted Answer · 2017-11-21 18:56:13Z

8

As stated in the SQLAlchemy documentation, "The delimeters must be URL escaped" when using a pass-through exact pyodbc string.

So, this will fail ...

import pyodbc
from sqlalchemy import create_engine

params = r'DRIVER={SQL Server};SERVER=.\SQLEXPRESS;DATABASE=myDb;Trusted_Connection=yes'
conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
engine = create_engine(conn_str)

... but this will work:

import pyodbc
from sqlalchemy import create_engine
import urllib

params = urllib.parse.quote_plus(r'DRIVER={SQL Server};SERVER=.\SQLEXPRESS;DATABASE=myDb;Trusted_Connection=yes')
conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
engine = create_engine(conn_str)

answered Nov 21, 2017 at 18:56

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

7 Comments

Neil S Over a year ago

does this not work on Python 2.7? On 2.7 it is not able to find parse AttributeError: 'module' object has no attribute 'parse' was able to run it on Python 3.5. Testing the .to_sql() (Its been running since the last 5 mins. and stuck there... worried if it will complete or not)

Gord Thompson Over a year ago

Under Python2 it would just be urllib.quote_plus.

Neil S Over a year ago

This worked on Python 3.5. Thank you. Will see how I can update the tables periodically. I got an error at the end but I can see the table Error:

C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py:1168: UserWarning: The provided table name 'Test' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names. warnings.warn(msg, UserWarning)

Also the table that was created was in [sdcc\neils].[Test] and not [dbo].[Test].... Can anyone explain me why and what can I do to get in under [dbo]

Neil S Over a year ago

Also, i got this error UnicodeDecodeError: 'ascii' codec can't decode byte 0xe2 in position 11: ordinal not in range(128) for which I am using this import sys reload(sys) sys.setdefaultencoding('utf8')

Neil S Over a year ago

I was opening a new question for the Schema but found the solution in suggested questions. I just needed to add , schema="dbo" in the .to_sql method

|

Collectives™ on Stack Overflow

Connecting to SQL server from SQLAlchemy using odbc_connect

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related