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 INSERT
s or UPDATE
s.
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
- Open your terminal
- Pull and run Oxla Docker container using the following command:
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:
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
or127.0.0.1
Port
: default for PostgreSQL is5432
Example Usage
Here’s an example on how to connect to Oxla using keyword arguments:
Now, let’s define some table, insert data into it and query inserted data.
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 withconn.cursor()
to execute SQL commands. -
SQL Execution
Standard SQL commands such as
CREATE TABLE
,INSERT
,SELECT
,UPDATE
andDELETE
are supported and can be executed viacursor.execute()
. -
Transaction Control
Basic transaction commands like
conn.commit()
andconn.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
Psycopg2 Feature | Oxla Support | Notes |
---|---|---|
psycopg2.connect() | Yes | Standard connection parameters supported |
connection.server_version() | Yes | Returns server version |
connection.protocol_version() | Yes | Returns protocol version |
connection.isolation_level() | Limited | - |
connection.set_client_encoding(encoding) | Yes | - |
connection.encoding() | Yes | - |
connection.status() | Yes | - |
connection.close() | Yes | - |
connection.closed() | Yes | - |
async_connection.poll() | No | Async connections not supported |
async_connection.cancel() | No | Async connections not supported |
async_connection.isexecuting() | No | Async connections not supported |
cursor.execute(operation, parameters) | Yes | Core SQL execution supported |
cursor.executemany(operation, seq_of_parameters) | Yes | - |
cursor.batch_execute(operation, parameters_list) | No | - |
cursor.copy_from(file, table, sep, null, size, columns) | Limited | - |
cursor.copy_to(file, table, sep, null, columns) | Limited | - |
cursor.fetchall() | Yes | - |
cursor.fetchmany(size) | Yes | - |
cursor.fetchone() | Yes | - |
cursor.statusmessage() | Yes | - |
cursor.description() | Yes | - |
cursor.rowcount() | Yes | - |
cursor.close() | Yes | - |
cursor.closed() | Yes | - |