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!
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:
env\Scripts\activate
source env/bin/activate
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
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:
DB_HOST=your_database_host
DB_USERNAME=your_database_username
DB_PASSWORD=your_database_password
DB_NAME=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_NAME = os.getenv("DB_NAME")
connection = mysql.connector.connect(
host=DB_HOST,
user=DB_USERNAME,
password=DB_PASSWORD,
database=DB_NAME,
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.
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.
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!