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 thessl_ca
option. Using the configuration option'ssl_verify_cert': True
, ensures that the server's certificate and the certificate stored incert.pem
are the same. If there's a certificate error, aValueError
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, thecursor
method of the connection object here is assigned the variablecur
, and the variablequery
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!
Updated about a year ago