You can use below process to write data from excel file to SQL database using python:
Step1:
Connect Azure SQL database using below code :
server = '<serverName>.database.windows.net'
database = '<databaseName>'
user = '<userName>'
password = '<Password>'
driver = 'ODBC Driver 17 for SQL Server'
conn_str = (
f"DRIVER={{{driver}}};"
f"SERVER={server};"
f"DATABASE={database};"
f"UID={user};"
f"PWD={password}"
)
conn_str_encoded = urllib.parse.quote_plus(conn_str)
try:
engine = create_engine(f"mssql+pyodbc:///?odbc_connect={conn_str_encoded}", fast_executemany=True)
print("Passed")
except:
print("Failed!")
Step2:
Create a data frame by reading the excel from the path using below code:
df = pd.read_excel('<filepath>', engine='openpyxl')
Write data frame into SQL database using below code:
df.to_sql('samp', con=engine, if_exists='replace', index=False)
Complete code:
import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib
import pyodbc
import pandas as pd
server = '<serverNmae>.database.windows.net'
database = '<dbName>'
user = '<userName>'
password = '<password>'
driver = 'ODBC Driver 17 for SQL Server'
conn_str = (
f"DRIVER={{{driver}}};"
f"SERVER={server};"
f"DATABASE={database};"
f"UID={user};"
f"PWD={password}"
)
conn_str_encoded = urllib.parse.quote_plus(conn_str)
try:
engine = create_engine(f"mssql+pyodbc:///?odbc_connect={conn_str_encoded}", fast_executemany=True)
print("Passed")
except:
print("Failed!")
df = pd.read_excel('<excelFilePath>', engine='openpyxl')
df.to_sql('samp', con=engine, if_exists='replace', index=False)
print("Saved in the table")
print(df)
You can get the output as mentioned below after executing above code:
Output from SQL database:
