[Python] Authenticating into Azure from Python

I am trying to connect from Python to Azure server to upload a table into Azure.

My code:

def create_and_store_dataframe_to_azure(df, table_name, server, database, password, driver="ODBC Driver 18 for SQL Server", schema='dbo'):
try:
params = quote_plus(
f"DRIVER={driver};SERVER={server},1433;DATABASE={database};UID={username}@myazure.windows.net;PWD={password};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=60;"
)
engine = sqlalchemy.create_engine(f"mssql+pyodbc:///?odbc_connect={params}")
column_types = {
'int64': 'BIGINT',
'float64': 'FLOAT',
'object': 'NVARCHAR(MAX)',
'bool': 'BIT',
'datetime64[ns]': 'DATETIME'
}
create_table_query = f"CREATE TABLE {schema}.{table_name} ("
for col in df.columns:
col_type = column_types.get(str(df[col].dtype), 'NVARCHAR(MAX)') # Default to NVARCHAR if type unknown
create_table_query += f"[{col}] {col_type}, "
create_table_query += "[InsertedTimestamp] DATETIME DEFAULT GETDATE() );"
with engine.begin() as conn:
conn.execute(text(f"DROP TABLE IF EXISTS {schema}.{table_name}")) # Drop table if it exists
conn.execute(text(create_table_query))
print(f"Table {schema}.{table_name} created successfully.")
df = df.drop(columns=['InsertedTimestamp'], errors='ignore')
df.to_sql(name=table_name, con=engine, schema=schema, if_exists='append', index=False, method="multi", chunksize=50)
print(f"Data successfully inserted into {schema}.{table_name}")
except Exception as e:
print(f"Error creating or inserting data into Azure SQL: {e}")

server = "myazure.windows.net"
database = "DB"
driver = "ODBC Driver 18 for SQL Server"
username = "myemail@myemail.com"
authentication = "ActiveDirectoryInteractive"
password = "mypwd"



but then when running the function I get the following error:

Error creating or inserting data into Azure SQL: (pyodbc.InterfaceError) ('28000', "[28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user 'username@myazure.windows.net'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute (0); [28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user 'myuser@myazure.windows.net'. (18456); [28000] [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute (0)")


I checked and

• I have the driver ODBC Driver 18 for SQL Server installed
• pwd and user are correct
• server and DB are correct

Any error with the code??

Continue reading...