使用odbc_connect从SQLAlchemy连接到SQL Server [英] Connecting to SQL server from SQLAlchemy using odbc_connect

问题描述

我是Python和SQL Server的新手.在过去的两天里，我一直在尝试将pandas df插入我们的数据库，但没有任何运气.谁能帮我调试错误.

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.

我尝试了以下

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

但是在engine.connect()行中，出现以下错误

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

谁能告诉我我所缺少的.我正在使用Microsoft SQL Server Management Studio-14.0.17177.0

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

我通过以下内容连接到SQL Server

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

我也尝试过

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

我收到此错误

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.

推荐答案

如

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)

这篇关于使用odbc_connect从SQLAlchemy连接到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！