python - 如何在 Python 中从 Azure Functions 连接到 Azure 托管 MySQL 数据库？

Question

问题

Azure Functions 上的 Python 应用程序无法连接到我们的 Azure 托管 MySQL 数据库。该函数在使用本地测试时运行良好func start。我们没有问题连接到数据库或执行游标操作。但是，在 Azure 上部署时，我们会收到以下错误。

我们尝试使用不同的 SSL 证书并从数据库中完全关闭 SSL。到目前为止没有任何效果。

错误

2055: Lost connection to MySQL server at '<server_host>', system error: 1 [SSL: WRONG_VERSION_NUMBER] wrong version number (_ssl.c:1076)
Traceback (most recent call last):
  File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/network.py", line 436, in switch_to_ssl
    self.sock.do_handshake()
  File "/usr/local/lib/python3.7/ssl.py", line 1139, in do_handshake
    self._sslobj.do_handshake()
ssl.SSLError: [SSL: WRONG_VERSION_NUMBER] wrong version number (_ssl.c:1076)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/site/wwwroot/<script_folder>/__init__.py", line 88, in main
    host=os.environ['MYSQL_HOST'],
  File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/__init__.py", line 177, in connect
    return MySQLConnection(*args, **kwargs)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/connection.py", line 104, in __init__
    self.connect(**kwargs)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/abstracts.py", line 781, in connect
    self._open_connection()
  File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/connection.py", line 288, in _open_connection
    self._ssl, self._conn_attrs)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/connection.py", line 197, in _do_auth
    ssl_options.get('version', None))
  File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/network.py", line 444, in switch_to_ssl
    errno=2055, values=(self.get_address(), _strioerror(err)))
mysql.connector.errors.InterfaceError: 2055: Lost connection to MySQL server at '<server_host>', system error: 1 [SSL: WRONG_VERSION_NUMBER] wrong version number (_ssl.c:1076)

连接代码

import mysql.connector

conn = mysql.connector.connect(
    user=os.environ['MYSQL_USER'],
    password=os.environ['MYSQL_PASSWORD'],
    database=os.environ['MYSQL_DATABASE'],
    host=os.environ['MYSQL_HOST'],
)
cursor = conn.cursor()

要求

astroid==2.3.3
azure-common==1.1.23
azure-core==1.1.1
azure-functions==1.0.7
azure-identity==1.1.0
azure-keyvault==4.0.0
azure-keyvault-certificates==4.0.0b7
azure-keyvault-keys==4.0.0
azure-keyvault-secrets==4.0.0
azure-storage-blob==12.1.0
certifi==2019.11.28
cffi==1.13.2
chardet==3.0.4
cryptography==2.8
idna==2.8
importlib-metadata==1.3.0
isodate==0.6.0
isort==4.3.21
lazy-object-proxy==1.4.3
mccabe==0.6.1
more-itertools==8.0.2
msal==1.0.0
msal-extensions==0.1.3
msrest==0.6.10
mysql-connector-python==8.0.18
numpy==1.17.4
oauthlib==3.1.0
pandas==0.25.3
portalocker==1.5.2
protobuf==3.11.1
pycparser==2.19
PyJWT==1.7.1
pylint==2.4.4
pyOpenSSL==19.1.0
python-dateutil==2.8.1
pytz==2019.3
requests==2.22.0
requests-oauthlib==1.3.0
six==1.13.0
typed-ast==1.4.0
urllib3==1.25.7
wrapt==1.11.2
zipp==0.6.0

标签： pythonmysqlazureazure-functions

Answer 1

固定 - Pymysql

不幸的是，Microsoft 帮助台无法解决mysql.connector的问题。我们发现pymysql包能够使用 SSL CA 文件安全地连接。下面是我们的数据库连接的最终代码。

import pymysql
import pymysql.cursors
import pathlib

def get_ssl_cert():     
    current_path = pathlib.Path(__file__).parent.parent    
    return str(current_path / 'BaltimoreCyberTrustRoot.crt.pem')

### SQL Process
# Connection
conn = pymysql.connect(
    user=os.environ['MYSQL_USER'],
    password=os.environ['MYSQL_PASSWORD'],
    db=os.environ['MYSQL_DATABASE'],
    host=os.environ['MYSQL_HOST'],
    cursorclass=pymysql.cursors.DictCursor,
    ssl={'ssl': {'ssl-ca': get_ssl_cert()}}
)
cursor = conn.cursor()