Python sqlalchemy 尝试使用 .to_sql 将 Pandas 数据帧写入 SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47402225/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Python sqlalchemy trying to write pandas dataframe to SQL Server using .to_sql
提问by Neil S
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
我有一个 python 代码，通过它我得到了一个Pandas数据框“df”。我正在尝试将此数据框写入 Microsoft SQL 服务器。我正在尝试通过以下代码进行连接，但出现错误
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
但是在 engine.connect() 行我收到以下错误
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
谁能告诉我我错过了什么。我正在使用 Microsoft SQL Server Management Studio - 14.0.17177.0
I connect to the SQL server through the following
我通过以下方式连接到SQL服务器
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.
我是数据库和 python 的新手。如果您需要任何其他详细信息，请告诉我。任何帮助将不胜感激。先感谢您。
回答by Neil S
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
感谢@gord-thompson在这里回答
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?
尽管我在我的 sql server 中，所有表都在“dbo”模式下（即 dbo.Test1、dbo.Other_Tables），并且此查询将我的表置于“sdcc\neils”模式（即 sdcc\neils.Test1、sdcc\ neils.Other_Tables) 有什么解决办法吗？
