Creating your first table with Python and MySQL

In this lesson, we will walk through the process of creating a new MySQL table using Python. Specifically, we'll create a table, add some data to it, and fetch that data back out. This lesson assumes that you have set up a PlanetScale account, created a database, and configured your Python environment (including the required MySQL connectors and cursor objects) to interact with your PlanetScale database. Please visit the previous lessons if not.

Creating the table

Let's start by creating a table in our database. To do this, we will use a CREATE TABLE SQL statement inside a cursor.execute() method in Python. In this example, we will create a table called guests to store information about hotel guests, such as their ID, name, and email address.

Here is the full CREATE TABLE statement:

CREATE TABLE guests (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    guest_name VARCHAR(100),
    email VARCHAR(100)
)

Now let's create the table using Python and the cursor.execute() method:

    CREATE TABLE guests (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        guest_name VARCHAR(100),
        email VARCHAR(100)
    )
""")

Once you have written this code, run your script. If no errors occur, your table has been successfully created.

To verify the table was successfully created in the PlanetScale database, you can use the PlanetScale CLI. First, list your available databases:

pscale database list

Find your desired database, in this case, hotel_management. Now, connect to the database with the shell:

pscale shell hotel_management

Once connected, query the guests table to make sure it exists:

SELECT * FROM guests;

If you receive an empty response, it means the table exists and is ready to be used. If you get an error saying that the table does not exist, double-check your code and try again.

Adding data to the table

Now that we have a table, let's add some data to it. To insert data into the table, we will use the INSERT INTO SQL statement. For this example, we'll add a guest named "John Doe" with the email address "john@example.com".

Here's the INSERT INTO statement:

INSERT INTO guests (guest_name, email)
VALUES ('John Doe', 'john@example.com')

To add this data to the table using Python and the cursor.execute() method:

cursor.execute("""
    INSERT INTO guests (guest_name, email)
    VALUES ('John Doe', 'john@example.com')
""")

Finally, to commit the insert and perform any subsequent database operations, add the following line after cursor.execute():

cursor.commit()

After running your script, you should now have successfully added a new row to the guests table in your PlanetScale database.

Fetching data from the table

With data in our guests table, we can now fetch it using Python and the cursor.execute() method. In this example, we will use a SELECT statement to retrieve all rows from the guests table.

First, write your SELECT statement:

SELECT * FROM guests;

Now, use the cursor.execute() method to fetch the data from the table:

cursor.execute("SELECT * FROM guests")

To fetch all the rows from the result set, use the cursor.fetchall() method:

result = cursor.fetchall()

The result variable now contains a list of tuples, each representing a row in the table. You can iterate over this list to access each row's data:

for row in result:
    print(row)

With these steps, you have learned how to create a table, add data to it, and fetch that data back out using Python and the PlanetScale database. As you continue working with PlanetScale, you can use similar techniques to execute other SQL statements and interact with your database.