Export SQL query to Excel

A PyODBC connection string can also be sent in pyodbc’s format directly, as specified in the PyODBC documentation, using the parameter odbc_connect.

When you read data into a dataframe using pandas.read_sql(), pandas expects the first argument to be a query to execute (in string format).

import pyodbc as pdb
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.engine import URL

server = 'SQLSERVER' 
database = 'DB' 
username = 'UID' 
password = 'PWD' 

# Pass through exact Pyodbc string into sqlalchemy
conn_string = 'DRIVER={SQL Server Native Client 11.0};SERVER=tcp:'+server+';DATABASE='+database+';UID='+username+';PWD='+ password

conn_url = URL.create("mssql+pyodbc", query={"odbc_connect": conn_string})

# SQLAlchemy connectable
conn = create_engine(conn_url).connect()

cursor.execute('exec USR_SP product_id, \'\', \'\', \'en\'')

sSql = 'exec USR_SP product_id, \'2022-02-01\', \'2022-05-01\''

# Query into dataframe
df = pd.read_sql(sSql, conn)

sExportFile = 'C:/Export/Export.xlsx'
df.to_excel(sExportFile)

sqlalchemy.org: Pass through exact Pyodbc string into sqlalchemy