MySQL for Python Developers
Sequence
Inserting data
Sequence

Inserting data into a MySQL Database with Python

In the previous lesson, we discussed how to create a new table in the PlanetScale database using Python. We connected to the database and executed a CREATE TABLE statement through the cursor.execute function. In this lesson, we will cover how to insert data into the table we created.

Inserting Data

To insert data into the table, we will use an INSERT statement via the cursor.execute function. First, let's comment out the previous cursor.execute code for creating the table since we only need to create the table once. Then, start a new cursor.execute statement.

Inserting data into a table requires specifying the table name, column names, and the values you want to insert. For our example, we will insert data into the guest table we created earlier.

Here is the code for an INSERT statement:

Python
cursor.execute("""
INSERT INTO guest (guest_name, email)
VALUES ('Anthony', 'anthony@gmail.com')
""")
connection.commit()

After writing the code above, you can run the script from the command prompt:

Terminal
python your_script.py

Once the script finished running without any errors, you should have inserted a new row into the guest table with the guest name "Anthony" and email "anthony@gmail.com". To verify this, go back to your PlanetScale dashboard and run the following query:

SQL
SELECT * FROM guest;

You may notice that running this query does not return any results. The reason for this is because we haven't committed the transaction yet.

Committing transactions

When executing SQL statements, databases are set up to receive multiple statements in a transaction. The idea behind transactions is that all changes to the database should happen together. If statements within a transaction depend on one another and one fails, none of the changes should be saved to prevent bad data from being inserted.

To commit a transaction, we need to call the connection.commit() function after executing our INSERT statement:

Python
cursor.execute("""
INSERT INTO guest (guest_name, email)
VALUES ('Anthony', 'anthony@gmail.com')
""")
connection.commit()

Now, if you run the script again and then run the SELECT * FROM guest; query in your PlanetScale dashboard, you should see the inserted row containing the guest name "Anthony" and email "anthony@gmail.com".

Conclusion

In this lesson, we covered how to insert data into a PlanetScale database using Python. We used an INSERT statement and committed the transaction to save the changes.

In the next lesson, we will discuss a security issue you should be aware of when writing SQL statements using the cursor.execute method.

About this lesson

Learn how to insert data into a MySQL database with Python.

02:07
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.