Connect MySQL to Python application

Easily connect to your MySQL hosting deployments at ScaleGrid from Python applications to optimize your MySQL management in the cloud.

How to Connect to MySQL from the Python Application

Step 1 - Create a user

Create a MySQL database user on ScaleGrid Deployment.

Step 2 - Get cluster details

Get the following MySQL deployment information from your cluster details page:
a. Hostname
b. Port (default 3306)
c. SSL certificate (if deployment has SSL)

Step 3 - Install Python

Install Python and python-mysql connector (MySQLConnector Python or PyMySQL) on your client machine.

Step 4 - Connect to MySQL

Run one of the following code samples to connect to your MySQL deployment and list the databases after you enter the information from step 2.
a. Sample code to connect to MySQL without SSL (MySQLConnector Python):

[root@centos]# cat first.py
import mysql.connector
servername="SG-myssl-32702.servers.mongodirector.com"
username="t"
password="Test!123456"
database="testdb"
// Create connection
cnx = mysql.connector.connect(user=username, 
                              password=password, 
                              host=servername, 
                              port=3306, 
                              database=database)
// List databases
mycursor=cnx.cursor()
mycursor.execute("SHOW DATABASES")
for db in mycursor:
    print(db)
// Close connection
cnx.close()

b. Sample code to connect to MySQL with SSL (MySQLConnector Python):
Download your SSL certificate as shown in step 2 and save it as a ca.pem file in the same directory as your Python file.

[root@centos]# cat second.py
import mysql.connector
servername="SG-myssl-32702.servers.mongodirector.com"
username="t"
password="Test!123456"
database="testdb"
sslcert_file="ca.pem"
// Create connection
cnx = mysql.connector.connect(user=username, 
                              password=password, 
                              host=servername, 
                              port=3306, 
                              database=database, 
                              ssl_ca=sslcert_file, 
                              ssl_verify_cert=True)
// List databases
mycursor=cnx.cursor()
mycursor.execute("SHOW DATABASES")
for db in mycursor:
    print(db)
// Close connection
cnx.close()

c. Sample code to connect to MySQL without SSL (PyMySQL):

[root@centos]# cat third.py
import pymysql
servername="SG-myssl-32702.servers.mongodirector.com"
username="t"
password="Test!123456"
database="testdb"
// Create connection
cnx = pymysql.connect(user=username, 
                              password=password, 
                              host=servername, 
                              port=3306, 
                              database=database)
// List databases
mycursor=cnx.cursor()
mycursor.execute("SHOW DATABASES")
for db in mycursor:
    print(db)
// Close connection
cnx.close()

d. Sample code to connect to MySQL with SSL (PyMySQL):
Download your SSL certificate as shown in step 2 and save it as a ca.pem file in the same directory as your Python file.

[root@centos]# cat fourth.py
import pymysql
servername="SG-myssl-32702.servers.mongodirector.com"
username="t"
password="Test!123456"
database="testdb"
sslcert_file="ca.pem"
// Create connection
cnx = mysql.connector.connect(user=username, 
                              password=password, 
                              host=servername, 
                              port=3306, 
                              database=database, 
                              ssl={'ca': sslcert_file})
// List databases
mycursor=cnx.cursor()
mycursor.execute("SHOW DATABASES")
for db in mycursor:
    print(db)
// Close connection
cnx.close()

Step 5 - Connect to MySQL with/without SSL

To run the Python script to connect to MySQL with/without SSL (MySQLConnector Python or PyMySQL):
Shell

[root@centos]# python first.py
('information_schema',)
('mysql',)
('performance_schema',)
('sys',)
('testdb',)

📘
Your Python application is now connected to your MySQL deployment!