Access DSN parameters for PostgreSQL in Python

Introduction to the DSN parameters for PostgreSQL in Python

The DSN (Data Source Name) parameters help to identify the data source for the PostgreSQL connection. This article will show you how to access the DSN parameters in a PostgreSQL Python connection using the psycopg2 adapter.

The connection object instance created by the psycopg2 connect() method has its own attribute method called get_dsn_parameters() that returns a Python dictionary object about the Python connection to the PostgreSQL server.

Prerequisites for connecting to PostgreSQL in Python

Use the pip3 -V command to verify that the PIP package manager for Python 3 is installed and then you can install the psycopg2 Python adapter distribution for PostgreSQL with the following command:

• Install the psycopg2 distribution for Python 3 using its PIP package manager with the following command: pip3 install psycopg2.

• You can verify that PostgreSQL is installed on the machine or server by having it return the version number for the psql interface with this command: psql -V.

It’s recommended that you use Python 3, rather than Python version 2.7, because Python 2 is now deprecated.

Dictionary attributes for the DSN parameter

The dsn_parameters object returned by the connection instance of psycopg2 is a dict typed Python object that is JSON serializable. The following is a list of all of its dictionary attributes:

• "user" — This is the PostgreSQL user that will execute SQL statements and has privileges granted for the database.

• "dbname" — The PostgreSQL database name.

• "host" — The domain name for the PostgreSQL server.

• "port" — The port that PostgreSQL is running on (default is 5432).

• "tty" — TTY port for telecommunication devices.

• "options" — This value represents any command-line arguments that have been passed to the connection.

• "sslmode" — A setting that indicates the type of SSL Support for PostgreSQL. The options for the SSL mode are as follows: allow, disable, prefer, require, verify-ca, verify-full.

• "sslcompression" — A number value representing the SSL (Secure Sockets Layer) compression level if OpenSSL is installed and enabled.

• "krbsrvname" — The field name is shorthand for “Kerberos Server Name”. PostgreSQL allows for GSSAPI authentication using Kerberos. The default krbsrvname is postgres.

• "target_session_attrs" — The target session attributes is for specifying what kind of PostgreSQL transactions will take place. The default value is any.

The following is an example of what the dictionary key-value pairs might look like:

Import the JSON and psycopg2 Python libraries for PostgreSQL

You’ll have to import the connect() method library for psycopg2 in order to establish a connection. The example code in this article also uses Python’s built-in json library to format the DSN parameters JSON response:

Connect to PostgreSQL using the psycopg2 Python adapter

Declare a connection object instance of the psycopg2 adapter library inside of a try-except indentation block with the following code:

NOTE: Make sure that the database and user specified in the method call exist, and that the use has the appropriate permissions for the SQL statements, or else Python will return an exception.

Get the DSN Parameters for the PostgreSQL connection

Call the connection object’s get_dsn_parameters() method to have it return a Python dict containing all of the DSN paramaters mentioned earlier:

Print the psycopg2 DSN parameters

The following example code prints the parameters and accesses the "dbname" attribute as an example of how one can isolate just one parameter:

Print the DSN parameters for the PostgreSQL connection

The above code should return a response that looks something like the following:

Access the psycopg2 connection object’s DSN string

The connection object instance also has a read-only attribute string, called dsn, that contains information about the Python connection parameters used to connect to the PostgreSQL server.

Here’s how you can print the string attribute:

The above print() function will return something like the following results:

Conclusion to the DSN parameters for PostgreSQL in Python

WRITERS: Please write a conclusion about psycopg2 and its connection object’s DSN parameters attribute.

Just the Code