从python notebook查询Azure Database for MySQL DB

gjmwrych 于 2023-04-28 发布在 Mysql

关注(0)|答案(1)|浏览(96)

我试图将CSV文件中的数据插入到Azure Database for MySQL DB中，但在建立连接的过程中遇到了问题。
我遵循了这个教程：https://learn.microsoft.com/en-us/azure/mysql/single-server/connect-python
我做了所有的事情，但仍然被这个错误卡住了：
2003年（HY000）：无法连接到'. www.example上的MySQL服务器<serv_name> www.example.com 10060）
下面是我的代码：

from azure.storage.blob import BlobServiceClient
import pandas as pd
import time
import mysql.connector
from mysql.connector import errorcode

config = {
  'host':'<serv_name>.mysql.database.azure.com',
  'user':'<username>',
  'password':'<password>',
  'database':'<db_name>',
  'port': port,
  'ssl_disabled': False,
  'client_flags': [mysql.connector.ClientFlag.SSL],
  'ssl_ca': 'DigiCertGlobalRootG2.crt.pem'
}

# Construct connection string

try:
   conn = mysql.connector.connect(**config)
   print("Connection established")
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with the user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
  cursor = conn.cursor()

不考虑data_local。csv它只是我的blob存储中的数据
如果你有什么推荐的话，我会非常感谢的

mysql

来源：https://stackoverflow.com/questions/76073727/query-a-azure-database-for-mysql-db-from-a-python-notebook

1条答案

按热度按时间

olhwl3o21#

如果有人感兴趣，这里是最终代码：

from azure.storage.blob import BlobServiceClient
import pandas as pd
import time
import csv
import mysql.connector
    
STORAGEACCOUNTURL= "https://-----.blob.core.windows.net/"
STORAGEACCOUNTKEY= "---"
CONTAINERNAME= "-----"
BLOBNAME= "---/data.csv"

# get csv from blob
blob_service_client_instance = BlobServiceClient(account_url=STORAGEACCOUNTURL, credential=STORAGEACCOUNTKEY)
blob_client_instance = blob_service_client_instance.get_blob_client(CONTAINERNAME, BLOBNAME, snapshot=None)
blob_data = blob_client_instance.download_blob()

# Get blob data as bytes
blob_data_bytes = blob_data.content_as_bytes()

# Decode bytes to string using UTF-8 encoding
blob_data_str = blob_data_bytes.decode('utf-8')

# Parse CSV data
csv_data = csv.reader(blob_data_str.splitlines())

# Iterate over rows
for row in csv_data:
    print(row)

cnx = mysql.connector.connect(user="user", password="passwd", host="your-serv.mysql.database.azure.com", port=3306, database="your_db", ssl_ca="DigiCertGlobalRootG2.crt.pem", ssl_disabled=False) 

cursor = cnx.cursor()

next(csv_data)
for row in csv_data:

    query = "INSERT INTO planets (name, distance, stellar_magnitude, planet_type) VALUES (%s, %s, %s, %s)"
    data = (row[0],row[1],row[2],row[3])

    cursor.execute(query, data)

cnx.commit()
cursor.close()
cnx.close()

赞(0）回复(0）举报 2023-04-28

我来回答

从python notebook查询Azure Database for MySQL DB

1条答案

相关问题

热门标签

最新问答