I have written a python method to form a pandas df and upsert it into sqlserver. This method works perfectly fine when I try to execute the python file alone. But it throws collation exception when I try to run via Azure functions.

Here is my code:

import pandas as pd
from sqlalchemy import create_engine, types, inspect, MetaData, Table

eng = sqlAlchemy_getConnection(sql_alchemy_connection_url)
inspector = inspect(eng)
target_table = "my_users"
get_colss = inspector.get_columns(target_table,"dbo")
dtype_mapping = {
                column['name']: column['type']
                for column in get_colss
            }
src_df.to_sql(temp_table,sqlAlchemy_conn,schema="dbo",if_exists='replace',index=False,dtype=dtype_mapping)

Error when trying to execute from Azure function:

Exception user_name (VARCHAR(100) COLLATE "SQL_Latin1_General_CP1_CI_AS") not a string

Drivers:

• ODBC Driver 17 for SQL Server

What could be the cause of this issue?

I have written a python method to form a pandas df and upsert it into sqlserver. This method works perfectly fine when I try to execute the python file alone. But it throws collation exception when I try to run via Azure functions.

Here is my code:

import pandas as pd
from sqlalchemy import create_engine, types, inspect, MetaData, Table

eng = sqlAlchemy_getConnection(sql_alchemy_connection_url)
inspector = inspect(eng)
target_table = "my_users"
get_colss = inspector.get_columns(target_table,"dbo")
dtype_mapping = {
                column['name']: column['type']
                for column in get_colss
            }
src_df.to_sql(temp_table,sqlAlchemy_conn,schema="dbo",if_exists='replace',index=False,dtype=dtype_mapping)

Error when trying to execute from Azure function:

Exception user_name (VARCHAR(100) COLLATE "SQL_Latin1_General_CP1_CI_AS") not a string

Drivers:

• ODBC Driver 17 for SQL Server

What could be the cause of this issue?

• python
• sql-server
• pandas
• sqlalchemy
• azure-functions

Share Improve this question Follow edited 2 days ago TylerH 21.1k7878 gold badges7979 silver badges114114 bronze badges asked Apr 1 at 10:18 Ramaraju.dRamaraju.d 1,36566 gold badges2828 silver badges4848 bronze badges 3

• Can you provide the code of Azure functions? – RithwikBojja Commented Apr 1 at 10:21
• Hi @RithwikBojja, sorry for wasting your time. I just noticed that connection string is overwritten while calling the method from function_app.py. My bad. – Ramaraju.d Commented Apr 1 at 10:36
• 1 Yes, the issue will be with connection string or with the driver that needs to be changed with 18. – RithwikBojja Commented Apr 1 at 10:39

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Glad to know you're able to figure out the resolution. Posting this as a solution so that people came across similar issue might find it handy.

It worked for me when I used correct connection string:

import logging as rilg
import pandas as pd
import azure.functions as func
from sqlalchemy import create_engine, inspect
import pyodbc

sql_alchemy_connection_url = (
    "mssql+pyodbc://username:[email protected]:1433/test1?"
    "driver=ODBC+Driver+17+for+SQL+Server&Encrypt=yes&TrustServerCertificate=no&Connection+Timeout=30"
)

def sqlAlchemy_getConnection(connection_url):
    return create_engine(connection_url)

app = func.FunctionApp(http_auth_level=func.AuthLevel.ANONYMOUS)

@app.route(route="http_trigger")
def http_trigger(req: func.HttpRequest) -> func.HttpResponse:
    rilg.info('Hello Rithwik.')
    ritheng = sqlAlchemy_getConnection(sql_alchemy_connection_url)
    tester = inspect(ritheng)
    sink = "Teams"  
    get_colss = tester.get_columns(sink, "dbo")
    datatyp = {
        column['name']: column['type'] 
        for column in get_colss
    }
    ridf = pd.DataFrame({
        'ID': [8,7],
        'Season_ID': [1, 1],
        'Team_Name': ['Test Rith','Team Cho']
    })
    temp_table = "test" 
    ridf.to_sql(temp_table, ritheng, schema="dbo", if_exists='replace', index=False, dtype=datatyp)
    return func.HttpResponse("Data Sent", status_code=200)

Output: