0

Scenario:

  1. I am trying to Convert the SQL output directly to Table using dataframe.to_sql, so for that i am using sqlalchemy.create_engine() and its throwing error when trying to createngine()

     sqlchemyparams= urllib.parse.quote_plus(ConnectionString)
 sqlchemy_conn_str = 'mssql+pypyodbc:///?odbc_connect={}'.format(sqlchemyparams)
 engine_azure = sqlalchemy.create_engine(sqlchemy_conn_str,echo=True,fast_executemany = 
 True, poolclass=NullPool)
 df_top_features.to_sql('Topdata', engine_azure,schema='dbo', index = False, if_exists = 
 'replace')

2.It will work fine if i use:pyodbc

sqlchemy_conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(sqlchemyparams)
  1. So is there any way i can using pypyodbc in sqlchem_conn_str
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Rahul
  • 21
  • 7

2 Answers2

1

SQLAlchemy does not have a pypyodbc driver defined for the mssql dialect, so

mssql+pypyodbc:// …

simply will not work. There may be some way to "fool" your code into using pypyodbc when you specify mssql+pyodbc://, similar to doing

import pypyodbc as pyodbc

in plain Python, but it is not recommended.

In cases where pyodbc cannot be used, the recommended alternative would be mssql+pymssql://.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 1.i am trying to run py file using Azure Machine learning pipeline notebook. There i am unable to install pyodbc, So i went for pypyodbc, So is there a way to do to_sql without sqlalchemy, instead of using normal pyodbc connection iterating over each item and inserting? – Rahul Jun 08 '22 at 13:18
  • If you are unable to install pyodbc then how do you set up pypyodbc? – Gord Thompson Jun 08 '22 at 13:27
  • import os os.system(f"pip install pandas") os.system(f"pip install scikit-learn") os.system(f"pip install pyodbc") .............it gives below error:"message": "{'code': ExecutionFailed, 'message': [{\"exit_code\":1,\"error_message\":\"Execution failed with error: Traceback (most recent call last):\\n File \\\"xyz.py\\\", line 14, in \\n import pyodbc as pyodbc\\nModuleNotFoundError: No module named 'pyodbc'\\n\\n\",\"process_name\":\"/azureml-envs/azureml..................but when i replace it with os.system(f"pip install pypyodbc") and import pypyodbc as pyodbc it works fine. – Rahul Jun 08 '22 at 13:43
  • adding to this error: src/pyodbc.h:56:10: fatal error: sql.h: No such file or directory #include ^~~~~~~ compilation terminated. – Rahul Jun 10 '22 at 04:52
  • Building wheels for collected packages: pyodbc Building wheel for pyodbc (setup.py): started Building wheel for pyodbc (setup.py): finished with status 'error' ERROR: Command errored out with exit status 1: – Rahul Jun 10 '22 at 05:43
  • Thank you Gord thompson. It got resolved by using os.system(f"apt install unixodbc-dev --yes") before os.system(f"pip install pyodbc") ....from you answer in : https://github.com/mkleehammer/pyodbc/issues/441. – Rahul Jun 10 '22 at 06:08
0

Here's what I do

import sqlalchemy as sa
from sqlalchemy import create_engine, event
from sqlalchemy.engine.url import URL

Then create varaibles to holder the server, database, username and password and pass it to...

params = urllib.parse.quote_plus("DRIVER={SQL Server};"
                                 "SERVER="+server+";"
                                 "DATABASE="+database+";"
                                 "UID="+username+";"
                                 "PWD="+password+";")

engine = sa.create_engine("mssql+pyodbc:///?odbc_connect={}".format(params))

then upload data to sql using.

dfc.to_sql('jobber',con=engine,index=False, if_exists='append')

Using https://www.dataquest.io/blog/sql-insert-tutorial/ as a source.

JonTout
  • 618
  • 6
  • 14
  • 1.i am trying to run py file using Azure Machine learning pipeline notebook. There i am unable to install pyodbc, So i went for pypyodbc, So is there a way to do to_sql without sqlalchemy, instead of iterating over each item and inserting? – Rahul Jun 08 '22 at 13:17