python - 尝试使用 to_sql 函数插入时，数据库引擎无法连接到 sql-server 实例

Question

我正在尝试使用 dataframe.to_SQL 将 pandas 数据框 CAPE 插入 SQL Server DB。我引用了以下解决方案来插入行。 PyOdbc fails to connect to a sql server instance

但我收到如下所示的错误。

源代码:

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

这是我收到的错误:

    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

示例数据帧值:

        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

Answer 1

DataFrame.to_sql()的第一个位置参数是表名，您将 engine (SQLAlchemy 对象)作为第一个参数传递。

所以试试这个:

CAPE.to_sql('[Tableau].[dbo].[Company_Table]',con=engine, if_exists='replace')