Need help in inserting Pandas Dataframe into Sql-server DB in python

Question

I am trying to insert pandas dataframe CAPE into SQL Server DB 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_plus line.

Can anyone help provide a solution to insert dataframe in 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

Version 2:

I have updated my code. Now its giving 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)')

what is you SQL Server version?

MaxU - stand with Ukraine
– MaxU - stand with Ukraine

2017-08-14 08:39:04 +00:00
Commented Aug 14, 2017 at 8:39 — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Aug 14, 2017 at 8:39
SQL server version 13.0.4

Arvinth Kumar
– Arvinth Kumar

2017-08-14 16:23:18 +00:00
Commented Aug 14, 2017 at 16:23 — Arvinth Kumar
– Arvinth Kumar, Commented Aug 14, 2017 at 16:23

MaxU - stand with Ukraine · Accepted Answer · 2017-08-15 20:48:32Z

3

AFAIK there is no need to quote a connection string.

Here is an example from SQL Alchemy online docs:

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...

PS the driver name will depend on your SQL Server version...

UPDATE: thanks to @ArvinthKumar - here is the connection string that finally worked:

engine = create_engine('mssql+pyodbc:///?odbc_connect=DRIVER={SQL Server};SERVER=GIRSQL.GIRCAPITAL.com;DATABASE=Tableau;UID=SQ‌‌L_User;PWD=sql_password')

edited Aug 15, 2017 at 20:48

answered Aug 14, 2017 at 8:47

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Arvinth Kumar Over a year ago

I have changed my code to following

params = urllib.parse.quote_plus("DRIVER={SQL Server};SERVER=GIRSQL.GIRCAPITAL.com;DATABASE=Tableau;UID=SQ‌L_User;PWD=Greentabl‌eau!") engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params) engine.connect() df.to_sql(name='[Tableau].[dbo].[Test table]',con=engine, index=False, if_exists='append')

Now it works fine

MaxU - stand with Ukraine Over a year ago

@ArvinthKumar, thanks for the update! Could you also post an output of print("mssql+pyodbc:///?odbc_connect=%s" % params) - this might be helpful for those having the same issue in future?

Arvinth Kumar Over a year ago

Output of print("mssql+pyodbc:///?odbc_connect=%s" % params) Statement :

Engine(mssql+pyodbc:///?odbc_connect=DRIVER={SQL Server};SERVER=GIRSQL.GIRCAPITAL.com;DATABASE=Tableau;UID=SQL_User;PWD=Greentableau!)

MaxU - stand with Ukraine Over a year ago

@ArvinthKumar, so if you try

conn = create_engine('mssql+pyodbc:///?odbc_connect=DRIVER={SQL Server};SERVER=GIRSQL.GIRCAPITAL.com;DATABASE=Tableau;UID=SQ‌L_User;PWD=Greentabl‌eau!')

- does it work properly?

MaxU - stand with Ukraine Over a year ago

@ArvinthKumar, thanks for testing! I'll update my answer. I hope it might help someone in future...

Collectives™ on Stack Overflow

Need help in inserting Pandas Dataframe into Sql-server DB in python

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related