Python to MS SQL Error: Error when connecting to SQL using sqlalchemy.create_engine() using pypyodbc

Question

Scenario:

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)

So is there any way i can using pypyodbc in sqlchem_conn_str

Thank u Jon and Gord for the response... Please find the error its throwing NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:mssql.pypyodbc — Rahul
– Rahul, Commented Jun 8, 2022 at 13:02

Gord Thompson · Accepted Answer · 2022-06-08 13:10:26Z

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

answered Jun 8, 2022 at 13:10

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

6 Comments

Rahul Over a year ago

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?

Gord Thompson Over a year ago

If you are unable to install pyodbc then how do you set up pypyodbc?

Rahul Over a year ago

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 <module>\\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 Over a year ago

adding to this error: src/pyodbc.h:56:10: fatal error: sql.h: No such file or directory #include <sql.h> ^~~~~~~ compilation terminated.

Rahul Over a year ago

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:

|

JonTout · Accepted Answer · 2022-06-08 13:09:29Z

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.

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?

Collectives™ on Stack Overflow

Python to MS SQL Error: Error when connecting to SQL using sqlalchemy.create_engine() using pypyodbc

2 Answers 2

6 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Linked

Related