July 2nd, 2023
Welcome back to our series on PostGreSQL for Linux users! In our previous post, we covered some basic PostGreSQL commands and showed you how to connect to your new database. In this post, we'll dive into some more advanced PostGreSQL commands and show you how to interact with your database using Python.
Step 1: Update Data in the Table
To update data in the "people" table, you can use the UPDATE command. Here's an example command to update the email address for the row with id 1:
UPDATE people SET email = 'johndoe@example.com' WHERE id = 1;
This command will update the email address for the row with id 1 to "johndoe@example.com".
Step 2: Delete Data from the Table
To delete data from the "people" table, you can use the DELETE command. Here's an example command to delete the row with id 1:
INSERT INTO people (name, age, email) VALUES ('John Doe', 30, 'john.doe@example.com');
DELETE FROM people WHERE id = 1;
Step 4: Retrieve Data from the Table
To retrieve data from the "people" table, you can use the SELECT command. Here's an example command to retrieve all rows from the "people" table:
SELECT * FROM people;
This command will delete the row with id 1 from the "people" table.
Step 3: Interact with Your Database using Python
Now that you know how to work with PostGreSQL commands in the command line interface, let's show you how to interact with your database using Python. Here's an example Python script that connects to your PostGreSQL database and retrieves all rows from the "people" table:
import psycopg2
conn = psycopg2.connect(
host="localhost",
database="mydatabase",
user="myuser",
password="mypassword"
)
cur = conn.cursor()
cur.execute("SELECT * FROM people")
rows = cur.fetchall()
for row in rows:
print(row)
cur.close()
conn.close()
Replace "mydatabase", "myuser", and "mypassword" with the name of your database, the username you created in the previous post, and the password for your PostGreSQL user.
This script will connect to your PostGreSQL database, retrieve all rows from the "people" table, and print them to the console.
That's it for this post! In the next post in this series, we'll cover some advanced PostGreSQL topics, including indexing and optimizing your database.