In this post we see how to use a PostrgreSQL database with Python.
I decided to use the dockerized version of PostgreSQL. I already have Docker installed on my PC. In case you need to install it, head over to Docker for Windows Docker desktop version
Step 1. Pull the image form the docker hub with
docker pull postgres
Step 2. Run the image docker
docker run --name test-db -e POSTGRES_PASSWORD=my_secret_password -d -p 5432:5432 postgres
A bit of explanations:
–name is the name of the container (not to confuse with the container ID)
-p is the port that is going to be open in the guest machine, port 5432 of the guest machine is forwarded to port 5432 of the host. Therefore I can connect to the guest just with localhost:5432
To run a bash console in the guest machine use this command:
docker exec -it <CONTAINER ID> bash
To get all the \ use the command:
docker container ls
In the bash run the psql application: psql -U postgres
Once you get the psgl prompt, you can use different commands:
- to list the database available: `\l`
- to list existing user: `\du`
- to list tables (also called relations): `\dt`import os
os.system('docker run --name test-db -e POSTGRES_PASSWORD=my_secret_password -d -p 5432:5432 postgres')
125
If the container exists already, we just need to start it:
os.system('docker container start test-db')
0
We use the package psycopg2 to communicate with PostgreSQL. You can install it with pip install psycopg2
import psycopg2
Create a connection with the database, using the database name and user I got with psql commands seen above.
conn = psycopg2.connect("host=127.0.0.1 dbname=postgres user=postgres password=my_secret_password")
PostgreSQL needs a commit command to be issued in order to save the transaction in the database. Here we are going to set autocommit=True, so every transaction will be autocommitted, without the need of an exciplit commit.
conn.autocommit = True
In alternativ one needs to commit any transaction with conn.commit().
Get a cursor for the database.
cur = conn.cursor()
Create a table with the two fields, user_name and age
cur.execute("create table if not exists user_table (user_name varchar, age int);")
Execute our first query on it, counting the records in the table
cur.execute("select count(*) from user_table")
print(cur.fetchall())
[(0,)]
cur.execute("INSERT INTO user_table (user_name, age) VALUES ('Luci', 666);")
cur.execute("INSERT INTO user_table (user_name, age) VALUES ('Jesus', 33);")
cur.execute("SELECT * FROM user_table")
print(cur.fetchall())
[('Luci', 666), ('Jesus', 33)]
cur.execute("INSERT INTO user_table (user_name, age) VALUES ('Maddy', 13);")
cur.execute("SELECT * FROM user_table")
print(cur.fetchall())
[('Luci', 666), ('Jesus', 33), ('Maddy', 13)]
cur.execute("SELECT * FROM user_table")
for record in cur:
print(record)
('Luci', 666)
('Jesus', 33)
('Maddy', 13)
Other useful commands¶
To know the last query issued:
cur.query
b'SELECT * FROM user_table'
To fetch a given number of records:
cur.execute("SELECT * from user_table")
cur.fetchmany(2)
[('Luci', 666), ('Jesus', 33)]
If I fetch another element it start after the already fetched records
cur.fetchmany(1)
[('Maddy', 13)]
If you want to delete the table use drop
cur.execute('DROP TABLE user_table')
At the end, close the cursor and the connection
cur.close()
conn.close()