Python sqlalchemy尝试使用.to_sql将 pandas 数据框写入SQL Server [英] Python sqlalchemy trying to write pandas dataframe to SQL Server using .to_sql

问题描述

我有一个python代码，通过它可以获取熊猫数据框"df".我正在尝试将此数据帧写入Microsoft SQL Server.我正在尝试通过以下代码进行连接，但出现错误

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

但是在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

我是数据库和python的新手.请让我知道是否需要其他详细信息.任何帮助将不胜感激.预先谢谢你.

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.

推荐答案

我终于能够使其运行.

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)

感谢@ gord-thompson回答了在这里

Thanks to @gord-thompson who answered Here

尽管我在SQL Server中，但所有表都在"dbo"模式下(即dbo.Test1，dbo.Other_Tables)，并且此查询将我的表置于"sdcc \ neils"模式(即sdcc \ neils.Test1)下，sdcc \ neils.Other_Tables)对此有任何解决方案?

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?

这篇关于Python sqlalchemy尝试使用.to_sql将 pandas 数据框写入SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！