crdb
– CockroachDB support#
New in version 3.1.
CockroachDB is a distributed database using the same fronted-backend protocol of PostgreSQL. As such, Psycopg can be used to write Python programs interacting with CockroachDB.
Opening a connection to a CRDB database using psycopg.connect()
provides a
largely working object. However, using the psycopg.crdb.connect()
function
instead, Psycopg will create more specialised objects and provide a types
mapping tweaked on the CockroachDB data model.
Main differences from PostgreSQL#
CockroachDB behaviour is different from PostgreSQL: please refer to the database documentation for details. These are some of the main differences affecting Psycopg behaviour:
cancel()
doesn’t work before CockroachDB 22.1. On older versions, you can use CANCEL QUERY instead (but from a different connection).Server-side cursors are well supported only from CockroachDB 22.1.3.
backend_pid
is only populated from CockroachDB 22.1. Note however that you cannot use the PID to terminate the session; use SHOW session_id to find the id of a session, which you may terminate with CANCEL SESSION in lieu of PostgreSQL’spg_terminate_backend()
.Several data types are missing or slightly different from PostgreSQL (see
adapters
for an overview of the differences).The two-phase commit protocol is not supported.
LISTEN
andNOTIFY
are not supported. However the CHANGEFEED command, in conjunction withstream()
, can provide push notifications.
CockroachDB-specific objects#
- psycopg.crdb.connect(conninfo: str = '', *, autocommit: bool = False, prepare_threshold: int | None = 5, context: Optional[AdaptContext] = None, row_factory: Optional[RowFactory[Row]] = None, cursor_factory: Cursor[+Row]]] = None, **kwargs: Optional[Union[str, int]]) Self #
Connect to a database server and return a new
Connection
instance.This is an alias of the class method
CrdbConnection.connect
.If you need an asynchronous connection use the
AsyncCrdbConnection.connect()
method instead.
- class psycopg.crdb.CrdbConnection(pgconn: PGconn, row_factory: RowFactory[Row] = <function tuple_row>)#
Wrapper for a connection to a CockroachDB database.
psycopg.Connection
subclass.- classmethod is_crdb(conn: Connection[Any] | AsyncConnection[Any] | PGconn) bool #
Return
True
if the server connected toconn
is CockroachDB.- Parameters:
conn (
Connection
,AsyncConnection
,PGconn
) – the connection to check
- class psycopg.crdb.AsyncCrdbConnection(pgconn: PGconn, row_factory: AsyncRowFactory[Row] = <function tuple_row>)#
Wrapper for an async connection to a CockroachDB database.
psycopg.AsyncConnection
subclass.
- class psycopg.crdb.CrdbConnectionInfo(pgconn: PGconn)#
ConnectionInfo
subclass to get info about a CockroachDB database.The object is returned by the
info
attribute ofCrdbConnection
andAsyncCrdbConnection
.The object behaves like
ConnectionInfo
, with the following differences:- vendor#
The
CockroachDB
string.
- server_version#
Return the CockroachDB server version connected.
Return a number in the PostgreSQL format (e.g. 21.2.10 -> 210210).
- psycopg.crdb.adapters#
The default adapters map establishing how Python and CockroachDB types are converted into each other.
The map is used as a template when new connections are created, using
psycopg.crdb.connect()
(similarly to the waypsycopg.adapters
is used as template for new PostgreSQL connections).This registry contains only the types and adapters supported by CockroachDB. Several PostgreSQL types and adapters are missing or different from PostgreSQL, among which:
Composite types
range
,multirange
typesThe
hstore
typeGeometric types
Nested arrays
Arrays of
jsonb
The
cidr
data typeThe
json
type is an alias forjsonb
The
int
type is an alias forint8
, notint4
.