在 python 中将 Pandas Dataframe 插入到 Sql-server DB 中需要帮助
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45667144/
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
Need help in inserting Pandas Dataframe into Sql-server DB in python
提问by Arvinth Kumar
I am trying to insert pandas dataframe CAPEinto SQL ServerDB using dataframe.to_SQL. I have referred the following solution to insert rows.
PyOdbc fails to connect to a sql server instance.But i am getting error in urllib.parse.quote_plusline.
我正在尝试使用 dataframe.to_SQL将 Pandas 数据帧CAPE插入到SQL Server数据库中。我已经参考了以下解决方案来插入行。
PyOdbc 无法连接到 sql server 实例。但我收到了错误urllib.parse.quote_plus。
Can anyone help provide a solution to insert dataframe in Sql-server DB.
任何人都可以帮助提供在 Sql-server DB 中插入数据框的解决方案。
Source code:
源代码：
CAPE # Input dataframe
connection = pdc.connect('Driver={SQL Server};''Server=GIRSQL.GIRCAPITAL.com;''Database=Tableau;''uid=SQL_User;pwd=Greentableau!')
connection_string = urllib.parse.quote_plus(connection)
connection_string = "mssql+pyodbc:///?odbc_connect=%s" % connection_string
engine = sq.create_engine(connection_string)
CAPE.to_sql(engine, name='[Tableau].[dbo].[Company_Table]',if_exists='replace')
This is the error I am getting:
这是我得到的错误：
Traceback (most recent call last):
File "<input>", line 1, in <module>
File "C:\Users\Abhay\Python36-32\lib\urllib\parse.py", line 803, in quote_plus
string = quote(string, safe + space, encoding, errors)
File "C:\Users\Abhay\Python36-32\lib\urllib\parse.py", line 787, in quote
return quote_from_bytes(string, safe)
File "C:\Users\Abhay\Python36-32\lib\urllib\parse.py", line 812, in quote_from_bytes
raise TypeError("quote_from_bytes() expected bytes")
TypeError: quote_from_bytes() expected bytes
connection = pdc.connect('Driver={SQL Server};''Server=GIRSQL.GIRCAPITAL.com;''Database=Tableau;''uid=SQL_User;pwd=Greentableau!')
connection_string = ur.quote(connection)
Traceback (most recent call last):
File "<input>", line 1, in <module>
File "C:\Users\Abhay\Python36-32\lib\urllib\parse.py", line 787, in quote
return quote_from_bytes(string, safe)
File "C:\Users\Abhay\Python36-32\lib\urllib\parse.py", line 812, in
quote_from_bytes
raise TypeError("quote_from_bytes() expected bytes")
TypeError: quote_from_bytes() expected bytes
Sample Dataframe value:
示例数据框值：
Date Company Value Category BICS_LEVEL_1_SECTOR_NAME BICS_LEVEL_2_INDUSTRY_GROUP_NAME BICS_LEVEL_3_INDUSTRY_NAME BICS_LEVEL_4_SUB_INDUSTRY_NAME BICS_LEVEL_5_SEGMENT_NAME BICS_REVENUE_LEVEL_ASSIGNED BS_TOT_VAL_OF_SHARES_REPURCHASED COUNTRY COUNTRY_OF_LARGEST_REVENUE EQY_SH_OUT GICS_INDUSTRY_GROUP_NAME GICS_INDUSTRY_NAME GICS_SECTOR_NAME GICS_SUB_INDUSTRY_NAME ICB_SECTOR_NAME INDUSTRY_GROUP INDUSTRY_SECTOR INDUSTRY_SECTOR_NUM INDUSTRY_SUBGROUP MARKET_SECTOR_DES Real_Earnings Real_Price CAPE_10 Percentile_10_CAPE
0 1975-04-30 3M Co 0 EPS Materials Chemicals Specialty Chemicals Adhesives & Sealants NaN 10399 3635.82 US United States 596.767 Capital Goods Industrial Conglomerates Industrials Industrial Conglomerates General Industrials Miscellaneous Manufactur Industrial 10011 Diversified Manufact Op Equity 0 0 NaN NaN
1 1975-04-30 3M Co 0 Stock Price Materials Chemicals Specialty Chemicals Adhesives & Sealants NaN 10399 3635.82 US United States 596.767 Capital Goods Industrial Conglomerates Industrials Industrial Conglomerates General Industrials Miscellaneous Manufactur Industrial 10011 Diversified Manufact Op Equity 0 0 NaN NaN
2 1975-04-30 3M Co 0 Cash Flow Materials Chemicals Specialty Chemicals Adhesives & Sealants NaN 10399 3635.82 US United States 596.767 Capital Goods Industrial Conglomerates Industrials Industrial Conglomerates General Industrials Miscellaneous Manufactur Industrial 10011 Diversified Manufact Op Equity 0 0 NaN NaN
I am using SQL server version 13.0.4
我使用的是 SQL 服务器版本 13.0.4
Version 2:
版本 2：
I have updated my code. Now its giving sqlalchemy.exc.DBAPIError:
我已经更新了我的代码。现在它的给予sqlalchemy.exc.DBAPIError:
Code:
代码：
import pyodbc
import sqlalchemy
CAFE # sample dataframe
engine = sqlalchemy.create_engine("mssql+pyodbc://SQL_User:[email protected]/Tableau?driver=SQL+Server+Native+Client+11.0")
engine.connect()
CAPE.to_sql(name='[Tableau].[dbo].[Test_table]',con=engine, if_exists='replace')
Error:
错误：
sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
回答by MaxU
AFAIK there is no need to quote a connection string.
AFAIK 不需要引用连接字符串。
Here is an example from SQL Alchemy online docs:
这是SQL Alchemy 在线文档中的一个示例：
engine = create_engine("mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=SQL+Server+Native+Client+10.0")
I.e. we don't have to call/use PyODBC directly - SQL Alchemy will do it for us...
即我们不必直接调用/使用 PyODBC - SQL Alchemy 会为我们完成...
PS the driver name will depend on your SQL Server version...
UPDATE:thanks to @ArvinthKumar - here is the connection string that finally worked:
更新：感谢@ArvinthKumar - 这是最终有效的连接字符串：
engine = create_engine('mssql+pyodbc:///?odbc_connect=DRIVER={SQL Server};SERVER=GIRSQL.GIRCAPITAL.com;DATABASE=Tableau;UID=SQ????L_User;PWD=sql_password')
