Creating a database with Python

Another way to work with data stored in a Google Cloud SQL instance is coding with Python programming language. The first step is to allow external connection enabling IP access.

To do so, just need to click Connections tab on the left-taskbar and click ADD NETWORK under Public IP. Give the network a name (i.e.: python-mysql) and enter the IP address 0.0.0.0/0 that allows entry to all IP addresses (we use 0.0.0.0/0 for lab purposes but, since allowing access to all IP addresses is a security issue, on production instances you should only enter the IP address range you would like to allow).

 

Connecting with the instance

First, we need to install the MySQL connector library:

pip install mysql-connector-python

To set up the connection we need:

• Username (should be root)
• Password (set up erlier on instance creation)
• Host (the public IP address of the SQL instance, available on the Cloud SQL Instances page):

We will use all the information to establish the connection like so:

import mysql.connector

config = {
    'user': 'root',
    'password': 'YOUR_PASSWORD',
    'host': '34.72.252.43'
}

# now we establish our connection
cnxn = mysql.connector.connect(**config)

Once connected to our Cloud MySQL instance (with Python), we are ready to create a database.

Creating the database

We use the previously setup connection cnxn to create the database on the instance:

cursor = cnxn.cursor()  # initialize connection cursor
cursor.execute('CREATE DATABASE sql_test_database')  # create a new 'sql_test_database' database
cnxn.close()  # close connection because we will be reconnecting to sql_test_database

Now we can connect to sql_test_database by adding database: sql_test_database to the config dictionary:

config['database'] = 'sql_test_database'  # add new database to config dict
cnxn = mysql.connector.connect(**config)
cursor = cnxn.cursor()

From the Instance Details window, we can see the new sql_test_database database under the Databases tab: