CODEWITHSUNDEEP
python-3.xgoogle-app-enginegoogle-cloud-platformsqlalchemygoogle-cloud-sql
crazysra
crazysra

Reputation: 151

Inserting data into Cloud SQL for MySQL from python code from App Engine

I have a Python code in App Engine on Google Cloud Platform that gets data from the Firebase and formats it into a list of tuples , where each row specifies one row of the database. I want to insert this data into Cloud SQL for MySQL . I have generated a Cloud Service Account and have also connected it to my Cloud SQL for MySQL using sqlalchemy , I am not able to understand how to insert the rows to Cloud SQL.

I have used the below code from Cloud SQL website and replaced the needed elements but how to insert into the Cloud SQL for MySQL?

db = sqlalchemy.create_engine(
    # Equivalent URL:
    # mysql+pymysql://<db_user>:<db_pass>@/<db_name>?unix_socket=/cloudsql/<cloud_sql_instance_name>
    sqlalchemy.engine.url.URL(
        drivername="mysql+pymysql",
        username=db_user,
        password=db_pass,
        database=db_name,
        query={"unix_socket": "/cloudsql/{}".format(cloud_sql_connection_name)},
    ),
    # ... Specify additional properties here.
    # ...
)

Upvotes: 0

Views: 2342

Answers (3)

schoon
schoon

Reputation: 3373

The other answers do not explain how to insert, so ...

First add the vpc connector.

Then you might need to add in appengine connectivity, adding as a public IP whatever you IP is (for me, this fixed the Can't connect to MySQL server on 'localhost' ([Errno 2] No such file or directory error).

Then to insert:

metadata = MetaData(bind=engine)
your_table= Table('yourtable', metadata, autoload=True)
i = insert(your_table)
i = i.values({
    "your_field": some_value
    })

Session = sessionmaker(bind=engine)
session = Session()
session.execute(i)
session.commit()

Upvotes: 1

Jose V
Jose V

Reputation: 1496

Since you are using App Engine Standard, in order to connect to a Cloud SQL instance you need to set up a VPC Access connector and configure your app to use it.

You can find how to do it in the Docs

If you were using App Engine Flexible, you wouldn't need to create a VPC Access Connector. Instead, you could use UNIX Domain Sockets

Upvotes: 1

Gabe Weiss
Gabe Weiss

Reputation: 3342

https://cloud.google.com/sql/docs/mysql/connect-app-engine

This doc should help if you have any holes missing in what you're trying to do.

But the likely problem is that App Engine uses a service account to authorize connections to other GCP products. Be sure that the App Engine service account you're using has permissions to connect. The IAM roles would be one of:

Cloud SQL Client (this is the one you probably need) Cloud SQL Editor Cloud SQL Admin

Upvotes: 1

Related Questions