Connecting a Python project to a PlanetScale database

In this lesson, we'll go through the process of setting up a connection between a Python project and a PlanetScale database. We will create a virtual environment, install necessary packages, and write the code for connecting to the database.

Let's get started!

Setting up a virtual environment

First, we need to create a virtual environment for our Python project. In your terminal, run the following command:

python -m venv env

This will create a virtual environment named env. Once it's done, activate the environment with the following command:

• For Windows:

env\Scripts\activate

• For Linux/macOS:

source env/bin/activate

Installing required packages

Next, we need to install two packages: python-dotenv and mysql-connector-python. The former helps us in managing environment variables, and the latter allows us to interface with the PlanetScale database. To install these packages, run the following commands:

pip install python-dotenv
pip install mysql-connector-python

Creating configuration files

Create two files in your project folder: .env and main.py.

In the .env file, we're going to store the environment variables needed to connect to our PlanetScale database. Here's an example of what the contents might look like:

HOST=your_database_host
USERNAME=your_database_username
PASSWORD=your_database_password
DATABASE=your_database_name

Replace placeholder values with the details from your PlanetScale database. You can find this information by going to your database in the PlanetScale dashboard, clicking "Connect", and selecting "Python" from the dropdown.

In the main.py file, we'll write the code to connect to the database using the mysql-connector-python package.

import os
import mysql.connector
from dotenv import load_dotenv


load_dotenv()


DB_HOST = os.getenv("DB_HOST")
DB_USERNAME = os.getenv("DB_USERNAME")
DB_PASSWORD = os.getenv("DB_PASSWORD")
DB_DATABASE = os.getenv("DB_DATABASE")


connection = mysql.connector.connect(
    host=DB_HOST,
    user=DB_USERNAME,
    password=DB_PASSWORD,
    database=DB_DATABASE,
    ssl_verify_identity=True,
    ssl_ca="path/to/ssl_cert"
)


print("Connected to the database")

This code reads the environment variables from the .env file using the dotenv package, and then creates a connection to the database using the mysql.connector.connect() function. Don't forget to replace the SSL certificate path in ssl_ca.

Testing the connection

To verify that everything works, run the main.py script:

python main.py

If nothing is returned and no errors are thrown, that means the connection was established successfully. If you encounter any errors, such as incorrect SSL certificate paths or incorrect database credentials, make sure to address those issues.

Conclusion

Congratulations! You've successfully connected your Python project to a PlanetScale database. Now you can move on to the next step - interacting with the database, such as sending commands or making changes.

In the next lesson, we'll discuss how to send commands to the database and make changes using our Python script. Stay tuned!