Error in connecting to cloudsql using python

Question

I am a newbie to this google App engine. I have written a small python code that was intended to connect the database in google cloud. Unfortunately I am getting this error.

Traceback (most recent call last):
File "/home/revanth/Projects/workspace/project2/connect.py", line 28, in  <module>
main()
File "/home/revanth/Projects/workspace/project2/connect.py", line 19, in main
user='revanth'
File "/usr/lib/python2.7/dist-packages/MySQLdb/__init__.py", line 81, in Connect
return Connection(*args, **kwargs)
File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 187, in __init__
super(Connection, self).__init__(*args, **kwargs2)
 _mysql_exceptions.OperationalError: (1045, "Access denied for user 'revanth'@'76.183.80.171' (using password: NO)")

Here is my code in python

import googleapiclient
import MySQLdb
import os
#from google.storage.speckle.python.api import rdbms_googleapi

def main():           
 env = os.getenv('SERVER_SOFTWARE')
if (env and env.startswith('Google App Engine/')):
  # Connecting from App Engine
  db = MySQLdb.connect(
    unix_socket='/cloudsql/citric-cistern-97118:geoeq',
    user='revanth')
  else:
  # Connecting from an external network.
  # Make sure your network is whitelisted
  db = MySQLdb.connect(
    host='173.194.232.179',
    port=3306,
    user='revanth'
    )

cursor = db.cursor()
cursor.execute('select * from Whether')
rows = cursor.fetchall()
print rows 

if __name__ == '__main__':
 main()

Please help me out.

Answer 1

I figured out the solution. I just need to add 0.0.0.0/0 to allow all the external IP address to connect it.

Here is the Link: https://cloud.google.com/sql/docs/access-control

I need to specify the password in the options.

Answer 2

To grant access to an App Engine application:

• Go to the Google Developers Console and select a project by clicking on the project name.
• In the sidebar on the left, click Storage > Cloud SQL to show a list of Cloud SQL instances for the project.
• Find the instance to which you want to grant access and click the instance name.
• Click Edit.
• In the Authorized App Engine Applications box, enter one or more Google App Engine application IDs.

Note: In order to guarantee low latency, the Google Cloud SQL instance can be kept as close as possible to a specified App Engine application by choosing a Preferred Location when you create an instance or by configuring an existing instance. This applies only to instances created in the US and EU locations. Click Save to apply your changes.

Answer 3

I think that your import MySQLdb has compatibilty issue. I have a same issue in google cloud run. My code run in local machine is well, but run in cloud run has error "mysql.connector.errors.DatabaseError: 2003 (HY000): Can't connect to MySQL server on '34.105.87.213:3306' (110)"

I change to sqlalchemy lib, the issue is gone.

old lib:

import os
import mysql.connector

HOMAPP_NEWS_URL = "xxxxxxxx"

def write_to_db(contents):
    # MySQL数据库连接配置
    config = {
        'host': os.environ.get("HOST"),
        'port': os.environ.get("PORT"),
        'database': os.environ.get("DB_NAME"),
        'user': os.environ.get("DB_USER"),
        'password': os.environ.get("DB_PASS"),
        'raise_on_warnings': True
    }

# 连接到MySQL数据库
conn = mysql.connector.connect(**config)
cursor = conn.cursor()

for content in contents:
    # 遍历contents数组，并将数据写入MySQL数据库
    for news in content:
        # 准备SQL插入语句
        insert_query = "INSERT INTO t_news (source, title, content, url, pics, type, status, update_date) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
        insert_values = (news.source, news.title, news.content, news.url, HOMAPP_NEWS_URL, news.type, news.status, news.update)

        # 执行SQL插入语句
        cursor.execute(insert_query, insert_values)
        conn.commit()

# 关闭数据库连接
cursor.close()
conn.close()
print("Write db completed")

new lib:

import os
import sqlalchemy

HOMAPP_NEWS_URL = "XXXXXXXXXX"

def write_to_db(contents):
    # MySQL数据库连接配置
    db_user = os.environ["DB_USER"]  # e.g. 'my-database-user'
    db_pass = os.environ["DB_PASS"]  # e.g. 'my-database-password'
    db_name = os.environ["DB_NAME"]  # e.g. 'my-database'
    unix_socket_path = os.environ["INSTANCE_CONNECTION_NAME"]  # e.g. '/cloudsql/project:region:instance'

pool = sqlalchemy.create_engine(
    # Equivalent URL: mysql+pymysql://<db_user>:<db_pass>@/<db_name>?unix_socket=<socket_path>/<cloud_sql_instance_name>
    sqlalchemy.engine.url.URL.create(
        drivername="mysql+pymysql",
        username=db_user,
        password=db_pass,
        database=db_name,
        query={"unix_socket": unix_socket_path},
    ),
)

# 连接到MySQL数据库
with pool.connect() as conn:
    stmt = sqlalchemy.text(
        f"INSERT INTO t_news (source, title, content, url, pics, type, status, update_date)"
        " VALUES (:source, :title, :content, :url, :pics, 'Repost', 'Publish', :update_date)"
    )

    # 遍历contents数组，并将数据写入MySQL数据库
    for content in contents:
        for news in content:
            # 执行SQL语句
            conn.execute(stmt, {
                'source': news.source,
                'title': news.title,
                'content': news.content,
                'url': news.url,
                'pics': HOMAPP_NEWS_URL,
                'update_date': news.update
            })                
            conn.commit()

# 关闭数据库连接
conn.close()
print("Write db completed")