Guangning Yu's Blog

import pandas as pd
import pyodbc
import sqlalchemy
import urllib
def get_sqlalchemy_engine(driver, server, uid, pwd, database):
    conn_str = 'DRIVER={};SERVER={};UID={};PWD={};DATABASE={}'.format(driver, server, uid, pwd, database)
    quoted = urllib.parse.quote_plus(conn_str)
    engine = sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))
    return engine
if __name__ == '__main__':
    # create engine
    driver = 'ODBC Driver 17 for SQL Server'
    server = 'xxx'
    uid = 'xxx'
    pwd = 'xxx'
    database = 'xxx'
    engine = get_sqlalchemy_engine(driver, server, uid, pwd, database)
    # read excel
    file_path = 'xxx'
    df = pd.read_excel(file_path)
    # load into SQL Server
    schema_name = 'xxx'
    table_name = 'xxx'
    df.to_sql(table_name, schema=schema_name, con=engine, index=False, if_exists='replace')