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)

3. So is there any way i can using pypyodbc in sqlchem_conn_str

CodePudding user response：

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.

CodePudding user response：

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