Python on host

Sometimes it’s good to run database-related code from your local machine. So I show a way to build interaption between the database and the host program.

Database container

Script that is used ot create table in database described in the following cell:

%%writefile python_on_host/create_table.sql
CREATE TABLE main_table(
    id TEXT NOT NULL,
    text TEXT NOT NULL
);

Writing python_on_host/create_table.sql

Note you need to specify the port to be referred to later in the Python program.

%%bash
docker run --rm -d\
    --name pg_example_posgres_cont\
    -e POSTGRES_USER=docker_app\
    -e POSTGRES_PASSWORD=docker_app\
    -e POSTGRES_DB=docker_app_db\
    -p 5431:5432\
    -v ./python_on_host/create_table.sql:/docker-entrypoint-initdb.d/create_table.sql\
    postgres:15.4 &> /dev/null

Python program

I’m just going to connect to the database from this notebook.

Establish conneciton

In the psycopg2.connect function, mention port used in postgres container creation and localhost argument for host parameter.

import psycopg2

conn = psycopg2.connect(
    port = "5431", # same as when creating a postgres container
    dbname = "docker_app_db",
    user = "docker_app",
    password = "docker_app",
    host= "localhost"
)

Insert information

To understand that everything works insert a few lines into database.

import random
import string

cur = conn.cursor()
for i in range(20):
    text = ''.join(random.choices(string.ascii_lowercase, k=20))
    query = f"INSERT INTO main_table (id, text) VALUES ('{i}', '{text}');"
    cur.execute(query)
cur.close()

Check the result

Query from python.

cur = conn.cursor()
cur.execute("SELECT * FROM main_table;")
for i in cur:
    print(i)
cur.close()

('0', 'oylvndzcdwjbvaqhcipe')
('1', 'uhgzsoakjwsaekpjvqqm')
('2', 'ynelbigrcuhgipfwdwsi')
('3', 'jokojjrucprmxopvturd')
('4', 'rkdlrxbhhlwymqfkxpft')
('5', 'sukhsdqtfthqedbjyztn')
('6', 'taihpxcxtwjmupdjxidl')
('7', 'uvuyltiriwusqnzsbema')
('8', 'tdxphsmxkjmhhxfjgbcb')
('9', 'tecdszaaicciqlppjckh')
('10', 'tfqssrggcfqkaebjaitx')
('11', 'ljrreafprdqxrlmrcqaz')
('12', 'mfpaqefutrthoimtcxwu')
('13', 'ewynczxdhguwahyrcrjn')
('14', 'pzhymsodljpaoykckyqe')
('15', 'odewjbtonkbzqmvyzauu')
('16', 'zrkmmbidnlzppyrhtqjq')
('17', 'cnczkqtpznuagodepkwi')
('18', 'waspsclkkazunxvecfyf')
('19', 'zsxzovelsmduduneufkz')

Query from container.

Note Before quering from the container, you need to commit the changes from the connection.

conn.commit()

%%bash
docker exec pg_example_posgres_cont \
    psql --username docker_app --dbname docker_app_db -c 'SELECT * FROM main_table;'

 id |         text         
----+----------------------
 0  | oylvndzcdwjbvaqhcipe
 1  | uhgzsoakjwsaekpjvqqm
 2  | ynelbigrcuhgipfwdwsi
 3  | jokojjrucprmxopvturd
 4  | rkdlrxbhhlwymqfkxpft
 5  | sukhsdqtfthqedbjyztn
 6  | taihpxcxtwjmupdjxidl
 7  | uvuyltiriwusqnzsbema
 8  | tdxphsmxkjmhhxfjgbcb
 9  | tecdszaaicciqlppjckh
 10 | tfqssrggcfqkaebjaitx
 11 | ljrreafprdqxrlmrcqaz
 12 | mfpaqefutrthoimtcxwu
 13 | ewynczxdhguwahyrcrjn
 14 | pzhymsodljpaoykckyqe
 15 | odewjbtonkbzqmvyzauu
 16 | zrkmmbidnlzppyrhtqjq
 17 | cnczkqtpznuagodepkwi
 18 | waspsclkkazunxvecfyf
 19 | zsxzovelsmduduneufkz
(20 rows)

Close connection.

conn.close()

Stop container

%%bash
docker stop pg_example_posgres_cont

pg_example_posgres_cont