嗨,我正在尝试使用一个函数应用程序连接到azure sql服务器,获取一些数据并将其推送到Google Big Query。
当我使用VsCode扩展并使用";附加到Python函数";
但当我把它推到azure时,函数失败了,并出现错误";登录超时已过期";
Exception while executing function: Functions.getIntrestPushToGBQ Result: Failure
Exception: OperationalError: (pyodbc.OperationalError) ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
我已将防火墙设置为允许访问azure资源。
这是我用来连接的代码
driver = "{ODBC Driver 17 for SQL Server}"
server = "my-org.database.windows.net,1433"
database = "Mydatabase"
user = os.getenv("sqlUser")
password = os.getenv("sqlPassword")
params = urllib.parse.quote_plus(
f"DRIVER={driver};"
f"SERVER={server};"
f"DATABASE={database};"
f"UID={user};"
f"PWD={password};"
f"Authentication=ActiveDirectoryPassword"
)
engine = sa.create_engine(f"mssql+pyodbc:///?odbc_connect={params}")
df = pd.read_sql(
"""SELECT
count(DISTINCT parentcontactid) as antall,
[marketareaname],
[ismember],
CAST([createdon] as DATE) as date
FROM [Integration_CRM].[Lead]
GROUP BY [marketareaname], [ismember], CAST([createdon] as DATE)
ORDER BY CAST([createdon] as DATE)""",
engine,
)
functionapp正在Linux 上运行
修复PYODBC
连接错误:
尝试使用服务器的IP地址,例如server = '129.0.0.1’
当使用端口时,使用类似或129.0.0.1,1233
的东西
尝试删除额外的属性,如";认证";以及所有这些,然后再次验证。
使用Python连接Azure SQL数据库的示例:
import pyodbc
import pandas as pd
server = 'azuredemosqldemo.database.windows.net'
database = 'azuredemodatabase'
username = 'sqladmin'
password = '********'
driver= '{ODBC Driver 17 for SQL Server}'
with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
df=pd.read_sql_query('Select Top 5 ProductID, Name, ProductNumber from SalesLT.Product order by productid', conn)
print(df)
另请参阅使用Python 连接到Azure SQL Server
参考:使用pyodbc 连接到SQL