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:

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:

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:

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:

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.