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