Python and Compose for MySQL

You can connect to MySQL with Python using MySQL's official Python connector.

First, install the connector package. After installing the package, you can connect to our database using the following code:

import mysql.connector

config = {  
    'user': 'admin',
    'password': 'mypass',
    'host': 'aws-us-east-1-portal.23.dblayer.com',
    'port': 15942,
    'database': 'compose',

    # Create SSL connection with the self-signed certificate
    'ssl_verify_cert': True,
    'ssl_ca': 'cert.pem'
}

connect = mysql.connector.connect(**config)  
cur = connect.cursor()  
cur.execute("SHOW DATABASES")

for row in cur:  
    print(row[0])

connect.close()

Notes:

• The example imports the MySQL connector and adds any connection parameters within a config dictionary. A secure connection is initialized using the deployment's self-signed certificate in the connection dictionary.
• ssl_verify_cert requires the ssl_ca option. Using the configuration option 'ssl_verify_cert': True, ensures that the server's certificate and the certificate stored in cert.pem are the same. If there's a certificate error, a ValueError is returned, indicating that the certificates don't match.
• A useful list of the available connection arguments can be found here.
• The MySQL Python connector requires that a cursor object be instantiated in order to execute SQL queries. In this code sample, the cursor method of the connection object here is assigned the variable cur, and the variable query is assigned to the SQL command, which tells MySQL to show all of the deployment's databases.
• The execute method executes database operations that convert Python objects to MySQL commands over a secure connection. In this example, the execution method tells MySQL to show all the databases, iterating over them, before logging the output to the console and closing the connection.

Still Need Help?

If this article didn't solve things, summon a human and get some help!