6

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
  • 116,920
  • 32
  • 215
  • 418
Neil S
  • 229
  • 7
  • 20

1 Answers1

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)
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 1
    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) – Neil S Nov 21 '17 at 21:02
  • 2
    Under Python2 it would just be `urllib.quote_plus`. – Gord Thompson Nov 21 '17 at 21:05
  • 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 Nov 21 '17 at 21:30
  • 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 Nov 21 '17 at 22:17
  • @NeilS - Those are separate questions from the one you originally asked. Please create new questions for them. – Gord Thompson Nov 22 '17 at 13:09
  • 1
    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 – Neil S Nov 22 '17 at 17:43
  • Worked for me, on Azure Ubuntu 18.04, Python 3.7.6, to connect to an Azure SQL server. params = 'Driver={ODBC Driver 17 for SQL Server};Server=tcp:metocean.database.windows.net,1433;Database=%s;Uid=%s;Pwd=%s;Encrypt=yes;TrustServerCertificate=yes;Connection Timeout=30;'%(database,username,password) params = urllib.parse.quote_plus(params) – Plinio Bueno Andrade Silva Jul 07 '21 at 19:44