Psycopg

Psycopg is a postgres adapter for python programming language.

import time
import docker
import psycopg
from src.rerun_docker import reload_docker_container

client = docker.from_env()
container_name = "psycopg_examples"
container_port = 5432

container = reload_docker_container(
    container_name,
    image="postgres:17.4",
    environment={
        "POSTGRES_PASSWORD": "password",
    },
    ports={f"{container_port}/tcp": 5432},
    detach=True,
    remove=True
)

def get_connection():
    return psycopg.connect(
        dbname="postgres",
        user="postgres",
        password="password",
        host="localhost",
        port=container_port
    )

Load data

To run anything in postgres you need to:

• Create a connection: psycopg.connect().

• Create cursor: connection.cursor().

• Execute your query: cursor.execute().

• Get data from cursor: cursor.fetchall, cursor.fetachone, cursor.description etc.

---

The following cell connects to the database.

connection = psycopg.connect(
    dbname="postgres",
    user="postgres",
    password="password",
    host="localhost",
    port=container_port
)

The next code executes the query in the database and loads the results and the name of the column.

with connection.cursor() as cursor:
    cursor.execute("SELECT 50 AS value1, 'test' AS value2;")
    columns = cursor.description
    data = cursor.fetchall()

print("Columns", columns)
print("Data", data)

Columns [<Column 'value1', type: int4 (oid: 23)>, <Column 'value2', type: text (oid: 25)>]
Data [(50, 'test')]

Columns

Information about columns is stored in the cursor.description attribute, which is a list of objects that describe columns.

---

The next cell shows the type of the object that describes column.

type(columns[0])

psycopg.Column

You can get the name of the column from the name attribute.

columns[0].name

'value1'

No data

Some queries should not to return any data. You can check if the given result set returns any data by checking the cursor.description attribute. If it takes the value None value, it means that the current result set corresponds to the sql command that doesn’t return any data.

---

The following cell executes some commands through psycopg and displays cursor.description for each SQL command.

connection = get_connection()

with connection.cursor() as cursor:
    cursor.execute("""
        CREATE TABLE table1 (val1 INT, val2 INT);
        INSERT INTO table1 VALUES (3,2), (4,5);
        SELECT * FROM table1;
        DROP TABLE table1;
    """
    )
    print(cursor.description)
    while cursor.nextset():
        print(cursor.description)

None
None
[<Column 'val1', type: int4 (oid: 23)>, <Column 'val2', type: int4 (oid: 23)>]
None

All descriptions take None except the one corresponding to the SELECT.

Row count

Check corresponding section of the official documentation.

Number of records affected by the operation that produced the current result set. Term “affected” isn’t really clear, but according to my experience it refer to the:

• Number of rows inserted by the INSERT operation

• Number of rows returned by the SELECT.

All other operations usually result in rowcount taking value -1.

---

The following cell shows cursor.rowcount for commands that only work with tables.

connection = get_connection()
cursor = connection.cursor()

cursor.execute("""
    DROP TABLE IF EXISTS my_table;
    CREATE TABLE my_table (val INT);
""")
cursor.rowcount

-1

So cursor.rowcount takes the value -1, because it doesn’t interact with any row in the database.

The following cell considers the opration of inserting some rows into the table.

cursor.execute("INSERT INTO my_table (val) VALUES (1), (2), (3), (4);")
cursor.rowcount

4

As a result, cursor.rowcount takes the value as the number of inserted rows.

The same result can be obtained by loading from the table - as it has 4 rows loaded.

cursor.execute("SELECT * FROM my_table;")
cursor.rowcount

4

cursor.close()
connection.close()

Server messages

In some cases postgreSQL server sends some textual information corresponding to the command that caused these responses.

• You can access the messages describing which commands were executed by using cursor.statusmessage attribute.

• You can access postgres log messages from special handler that can be added using connection.add_notice_handler method.

Check details:

• Server messages section of the official documentation.

• Description of the statusmessage attribute.

---

The following cell shows how you can extract various messagee that appear during the execution of the DROP TABLE IF EXISTS command.

connection = get_connection()

def log_notice(diag):
    print(f"The server says: {diag.severity} - {diag.message_primary}")

connection.add_notice_handler(log_notice)

with connection.cursor() as cursor:
    cursor.execute("DROP TABLE IF EXISTS default_values_example;")
    print(cursor.statusmessage)

connection.close()

The server says: NOTICE - table "default_values_example" does not exist, skipping
DROP TABLE

Output sets

There are cases where one call to the cursor can return multiple results:

• To execute, several SQL commands are passed in one str, such as SELECT 1; SELECT 2;.

• In case the executemany method is used to execute multiple SQL statements.

By default, cursor keeps information about the first command, but you can “move” the cursor to the next one by using the cursor.nextset() method.

---

The following cell creates cursor, which just executed code with two queries in it. Using this example we’ll consider how to load data from structures like this.

connection = get_connection()
cursor = connection.cursor()
cursor.execute("SELECT 1;SELECT 2;")

<psycopg.Cursor [TUPLES_OK] [INTRANS] (host=localhost database=postgres) at 0x7944a2b62150>

In the initial state, the cursor “points” to the result of the first SQL query.

cursor.fetchall()

[(1,)]

So cursor.fetchall() returns the result of the first SELECT. To access the results of the second select, you need to call nextstep.

cursor.nextset()

True

The fact that it returned True means that there is data for another query.

cursor.fetchall()

[(2,)]

The cursor.fetchall() returns data for the second query.

print(cursor.nextset())

None

The fact that cursor.nextstep() returned None means that there is no more data.

cursor.close()
connection.close()

Cursor attributes

Other cursor attributes that is determined by the command being executed, change with with the cursor.nextstep() call.

---

The following cell executes several SQL commands with different rowcount and statusmessage values, and prints the corresponding values of the attributes.

connection = get_connection()

def print_cursor_attirbutes(cursor):
    print("Status message:", cursor.statusmessage)
    print("Rows count:", cursor.rowcount)


with connection.cursor() as cursor:
    cursor.execute("""
        DROP TABLE IF EXISTS table1;
        CREATE TABLE table1 (val1 INT, val2 INT);
        SELECT 10;
    """)
    print_cursor_attirbutes(cursor=cursor)
    while cursor.nextset():
        print_cursor_attirbutes(cursor=cursor)

Status message: DROP TABLE
Rows count: -1
Status message: CREATE TABLE
Rows count: -1
Status message: SELECT 1
Rows count: 1