MySQL for Python Developers
Sequence
Creating a table
Sequence

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:

SQL
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:

SQL
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:

Terminal
pscale database list

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

Terminal
pscale shell hotel_management

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

SQL
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:

SQL
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:

Python
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():

Python
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:

SQL
SELECT * FROM guests;

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

Python
cursor.execute("SELECT * FROM guests")

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

Python
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.

About this lesson

Learn how to create a MySQL table with Python, add data to it, and return the data.

02:33
Closed captioned

Meet your instructor, Anthony Herbert

Software Developer

I'm a software developer and educator focused on helping others build their dream software projects.

Feedback or questions? Reach out to our team at education@planetscale.com.

By submitting your email, you agree to the processing of your personal information by PlanetScale as described in the Privacy Policy.