RuntimeError:OperationalError:(2003，无法在“实例的IP地址"上连接到MySQL服务器 [英] RuntimeError: OperationalError: (2003, Can't connect to MySQL server on 'IPaddress of the instance'

问题描述

我正在尝试运行 Python(版本2.7.1')脚本，其中我正在使用 pymysql软件包从CSV文件向数据库创建表

I'm trying to run a Python(version 2.7.1') script where I am using pymysql package to create a table into a database from a CSV file.

它在我的本地系统中正常运行，但是，当在 Google Cloud Dataflow 中作为管道的一部分运行相同的脚本时，会出现问题.

It runs correctly in my local system, however, the problem appears when running the same script as a part of a pipeline in Google Cloud Dataflow.

我的Python函数如下:

My Python function is the following one:

class charge_to_db(beam.DoFn):
    def process(self, element):
        import pymysql

        with open(element, 'r') as f:
            data = f.read().decode("UTF-8")

        datalist = []
        for line in data.split('\n'):
            datalist.append(line.split(','))

        db = pymysql.connect(host='IPaddress', user='root', password='mypassword', database='stack_model')

        cursor = db.cursor()
        cursor.execute("DROP TABLE IF EXISTS stack_convergence")

        # create column names from the first line in fList
        up = "upper_bnd"
        primal = "primal"
        d = "dualit"
        gap = "gap_rel"
        teta = "teta"
        alpha = "alpha"
        imba = "imba_avg"
        price = "price_avg"
       # create STUDENT table // place a comma after each new column except the last
       queryCreateConvergenceTable = """CREATE TABLE stack_convergence(
                                {} float not null,
                                {} float not null,
                                {} float not null,
                                {} float not null,
                                {} float not null,
                                {} float not null,
                                {} float not null,
                                {} float not null )""".format(up, primal, d, gap, teta, alpha, imba, price)

        cursor.execute(queryCreateConvergenceTable)

在云中运行此功能时，出现以下错误:

When running this function in the cloud I'm obtaining the following error:

  RuntimeError: OperationalError: (2003, 'Can\'t connect to MySQL server on \'35.195.1.40\' (110 "Connection timed out")')

我不知道为什么会发生此错误，因为它在本地系统中正确运行，因此我可以从本地系统访问我的云SQL实例，但不能访问云中的数据流.

I don't know why this error is occurring because it runs correctly in local system, so from the local system I have access to my cloud SQL instance, but not from the dataflow in the cloud.

为什么会发生此错误?

推荐答案

在Dataflow上，您不能将IP列入白名单以使Dataflow能够访问SQL实例.如果要使用Java，最简单的方法是使用JdbcIO/JDBC套接字工厂.

On Dataflow you cannot whitelist an IP to enable Dataflow to access a SQL instance. If you would be using Java, the easiest way would be to use JdbcIO / JDBC socket factory.

但是，由于您使用的是Python，因此使用特定于Python的数据库连接功能模仿JdbcIO.read()的实现将有所帮助. 此相关问题，其中带有

But since you're using Python, then mimicking the implementation of JdbcIO.read() using Python-specific database connectivity facilities would help. There's this related question with a workaround after changing some Cloud SQL settings and adding related python codes.

如果这看起来很复杂，您也可以导出数据从Cloud SQL传输到Cloud Storage ，然后从Cloud Storage加载.

If this seems complex, alternatively you can export data from Cloud SQL to Cloud Storage and then load from Cloud Storage.

这篇关于RuntimeError:OperationalError:(2003，无法在“实例的IP地址"上连接到MySQL服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！