Start by running your Docker Desktop application. Then via the command line run the following command to pull the latest postgres image and start an instance.
docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword --name postgres_docker postgres
We will now have a Docker image of postgres running with a default database named postgres. To create a new database or to log directly into the instance, we can use the below command.
docker exec -it postgres_docker psql -U postgres
That's with Docker and PostgreSQL setup. Let's now dive into Python project.
Create a new project folder and create a python environment.
python3 -m venv env && source env/bin/activate
Now we have our python environment ready for installation of needed packages.
We will install psycopg2 via pip and then will create a file named main.py.
pip install psycopg2
Inside main.py we will start by importing psycopg2 and connecting to our database instance.
import psycopg2
try:
conn = psycopg2.connect(database="postgres",
host="localhost",
user="postgres",
password="mysecretpassword",
port="5432")
print("Connected to database")
except psycopg2.OperationalError as e:
print("Error:", e)
We can now try running the above code via terminal.
python3 main.py
On running of the command we should see either error or the message 'Connected to database' displayed.
Let's again log into the docker instance. And create a table named products and insert few entries.
docker exec -it postgres_docker psql -U postgres
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price NUMERIC(10, 2) NOT NULL
);
We now have the table products created with the above columns. We can now insert a few entries with the following SQL command.
INSERT INTO products (name, price) VALUES ('Product 1', 19.99);
INSERT INTO products (name, price) VALUES ('Product 2', 29.99);
INSERT INTO products (name, price) VALUES ('Product 3', 39.99);
Back to main.py. We will now write a function to fetch all the products and print to the console.
def fetch_all_products():
cursor = conn.cursor()
cursor.execute("SELECT * FROM products")
print(cursor.fetchall())
fetch_all_products()
Now try running the main.py again.
We can see the following result printed.
[(1, 'Product 1', Decimal('19.99')), (2, 'Product 2', Decimal('29.99')), (3, 'Product 3', Decimal('39.99'))]
We will now create few more functions to do the inserting of new entry from our python code, followed by updation and deletion.
First let's create a function to fetch product by id.
def fetch_product_by_id(id):
cursor.execute("SELECT * FROM products WHERE id = 3")
print(cursor.fetchone())
fetch_product_by_id(3)
The result on running the command will be:
(3, 'Product 3', Decimal('39.99'))
Next we will create a update by id function.
def update_product_by_id(id):
cursor.execute("UPDATE products SET price = 2.00 WHERE id = 3")
conn.commit()
update_product_by_id(3)
On running the code, the product with id of 3 will have it's price updated. To check if the value updated, run the code again and on the print of fetch product by id pass 3.
Let' delete the product with id of 3 now.
def delete_product_by_id(id):
cursor.execute("DELETE FROM products WHERE id = 3")
conn.commit()
delete_product_by_id(3)
The above code will delete the product with id of 3. Now we run the fetch_product_by_id again with id of 3, we will receive None in response.
Now for the final function we will insert a new product into the database.
def insert_product(name, price):
cursor.execute("INSERT INTO products (name, price) VALUES (%s, %s)", (name, price))
conn.commit()
insert_product("Apple", 1.00)
We now have a function that inserts new products in to the table products. We can see the newly added item by again running the code to display the results of fetch_all_products.
