Psycopg2

​

Overview

This docs details how to connect a Python application to Oxla running inside a Docker container using the Psycopg2 library.

Psycopg2 is a PostgreSQl adapter for Python programming language. It allows you to execute SQL queries and interact with your Oxla instance using Python and is designed for multi-threaded applications that create and destroy lots of cursors (more on them here) and make a large number of concurrent INSERTs or UPDATEs.

​

Prerequisites

Before you begin, make sure you have the following installed:

• Docker
• Python (preferably with a virtual environment)
• The Psycopg2 library

​

Running Oxla Docker Container

1. Open your terminal
2. Pull and run Oxla Docker container using the following command:
   Copy

   Ask AI

   docker run --rm -it -p 5432:5432 public.ecr.aws/oxla/release:latest
   

​

Establishing Connection

To connect to Oxla using Psycopg2, use the psycopg2.connect() function. It creates a new database session and returns a connection object.

• Using keyword arguments:

  Copy

  Ask AI

  conn = psycopg2.connect(dbname="test", user="postgres", password="secret")
  

The following parameters can be used with the psycopg2.connect() function:

• dbname: your database connection name (i.e. “oxla_node_1”)
• user: username to authenticate with (i.e. “oxla”, which is the default username)
• password: password to authenticate with (i.e. “oxla”, which is the default password)
• host: localhost or 127.0.0.1
• Port: default for PostgreSQL is 5432

​

Example Usage

Here’s an example on how to connect to Oxla using keyword arguments:

import logging
import psycopg2

logging.basicConfig(level=logging.DEBUG, format='%(asctime)s - %(levelname)s - %(message)s')

try:
    conn = psycopg2.connect(
        dbname= "oxla_node_1",
        user="oxla",
        password="oxla",
        host="localhost",
        port="5432"
    )
    logging.info("Connection to Oxla database established successfully!")
...

Now, let’s define some table, insert data into it and query inserted data.

...
    # SQL statements to execute
    statements = [
        # Drop the film table if it exists
        "DROP TABLE IF EXISTS film;", 
        # Create the film table
        "CREATE TABLE film (title text NOT NULL, rating text,length int);",
        # Insert records into the film table
        "INSERT INTO film(title, length, rating) VALUES \
                ('ATTRACTION NEWTON', 83, 'PG-13'), \
                ('CHRISTMAS MOONSHINE', 150, 'NC-17'), \
                ('DANGEROUS UPTOWN', 121, 'PG'), \
                ('KILL BROTHERHOOD', 54, 'G'), \
                ('HALLOWEEN NUTS', 47, 'PG-13'), \
                ('HOURS RAGE', 122, 'NC-17'), \
                ('PIANIST OUTFIELD', 136, 'NC-17'), \
                ('PICKUP DRIVING', 77, 'G'), \
                ('INDEPENDENCE HOTEL', 157, 'NC-17'), \
                ('PRIVATE DROP', 106, 'PG'), \
                ('SAINTS BRIDE', 125, 'G'), \
                ('FOREVER CANDIDATE', 131, 'NC-17'), \
                ('MILLION ACE', 142, 'PG-13'), \
                ('SLEEPY JAPANESE', 137, 'PG'), \
                ('WRATH MILE', 176, 'NC-17'), \
                ('YOUTH KICK', 179, 'NC-17'), \
                ('CLOCKWORK PARADISE', 143, 'PG-13');",
        # Select all records from the film table
        "SELECT * FROM film;"
        ]

    # Create a cursor object to execute SQL queries
    cur = conn.cursor()

    # Execute each SQL statement in the list
    for statement in statements:
        try:
            cur.execute(statement)
            if statement.startswith("SELECT"):
                    # Fetch and print results from SELECT statement
                    outputs = cur.fetchall()
                    for output in outputs:
                        logging.info(f"Query Result: {output}")
            else:
                conn.commit() # Commit changes after each non-SELECT statement
        except psycopg2.Error as exec_error:
            conn.rollback()
            logging.warning(f"Transaction rolled back due to an error")

except psycopg2.Error as e:
    logging.info(f"Error during database operation: {e}")
    if conn:
        conn.rollback() # Rollback the transaction in case of an error
        logging.warning(f"Transaction rolled back due to error.")

finally:
    # Close the cursor and connection
    cur.close()
    conn.close()
    print(f"Oxla connection closed.")

​

Supported Features And Limitations

While Oxla supports many core SQL features, some limitations exist, in particular related to transaction handling and certain advanced Psycopg2 functionalities. This section clarifies which functions are fully supported, which have limited support and which are currently unavailable.

​

Supported Features

• Basic Connection and Cursor Management

  You can use psycopg2.connect() to establish connections and create cursors with conn.cursor() to execute SQL commands.

• SQL Execution

  Standard SQL commands such as CREATE TABLE, INSERT, SELECT, UPDATE and DELETE are supported and can be executed via cursor.execute().

• Transaction Control

  Basic transaction commands like conn.commit() and conn.rollback() are available but Oxla currently has limited transaction support and does not support multi-threaded transactions.

• Error Handling

  Psycopg2 exceptions such as psycopg2.errors can be caught and handled normally.

​

Limitations and Unsupported Features

• Multi-threaded Transactions

  Oxla does not support multi-threaded transactions as Psycopg2 normally provides. Avoid sharing connections or cursors across threads when performing transactional operations.

• Advanced Psycopg2 Features

  Features such as server-side cursors, asynchronous communication, notifications, prepared statements and pipeline mode may not be fully supported due to Oxla’s current architecture.

​

Oxla - Psycopg2 Feature Compatibility Table