How to Access DSN parameters for PostgreSQL in Python

Introduction

If you’re connecting to a PostgreSQL database from a Python script, you might find yourself making use of a DSN (Data Source Name). A DSN is a string with a specific data structure that describes a connection to a particular data source. Many Python scripts that use the psycopg2 adapter rely on DSN parameters to help identify the data source for a PostgreSQL connection. You can use a method called get_dsn_parameters() that returns a Python dictionary object about the PostgreSQL connection. In this article, we’ll take a closer look at this method and show you how it can be used to access DSN parameters using PostgreSQL and Python.

Prerequisites

Before we dive into the details of our Python script, we need to review a few prerequisites that are essential for this tutorial:

• You’ll need to have the PIP package manager installed. If you’re not sure whether PIP is installed, use the pip3 -V command to check.

• You can use PIP to install the psycopg2 Python adapter distribution for PostgreSQL with the following command:

• You’ll also need to have PostgreSQL installed and running. You can verify that PostgreSQL is installed on the machine or server with this command: psql -V, which will return the version number of the psql interface.

• 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 Python dict object; it is also JSON-serializable. The following is a list of its dictionary attributes:

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

• "dbname" — This attribute represents the PostgreSQL database name.

• "host" — This attribute represents the domain name for the PostgreSQL server.

• "port" — This attribute represents the port that PostgreSQL is running on. The default port is 5432.

• "tty" — This attribute represents the TTY port for telecommunication devices.

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

• "sslmode" — This setting indicates the type of SSL Support enabled for PostgreSQL. The options for SSL mode include: allow, disable, prefer, require, verify-ca and verify-full.

• "sslcompression" — This numeric value represents the SSL (Secure Sockets Layer) compression level in cases where OpenSSL is installed and enabled.

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

• "target_session_attrs" — This attribute is used to specify what kind of PostgreSQL transactions will take place. The default value is any.

Here’s an example of what the dictionary key-value pairs for the dsn_parameters object might look like:

Import the JSON and psycopg2 Python Libraries for PostgreSQL

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

Connect to PostgreSQL Using the psycopg2 Python Adapter

Next, we’ll declare a connection object instance of the psycopg2 adapter library. We’ll do this inside a try-except indentation block:

NOTE: To avoid a Python exception, make sure that both the database and user specified in the method call exist, and confirm that the user has the appropriate permissions for the SQL statements.

Get the DSN Parameters for the PostgreSQL Connection

The next thing we’ll do is retrieve the DSN parameters for our PostgreSQL connection. We can do this by calling the connection object’s get_dsn_parameters() method, which returns a Python dict object containing all of the DSN paramaters discussed earlier:

Print the psycopg2 DSN parameters

The following code prints the parameters and accesses the "dbname" attribute to illustrate how it’s possible to isolate just one parameter:

Print the DSN parameters for the PostgreSQL connection

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

Access the psycopg2 Connection Object’s DSN String

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

Here’s how you can print the attribute string:

The output will look like the following:

Conclusion

If you’re planning to interact with PostgreSQL using a Python script, it’s important to understand all aspects of the connection process. In this tutorial, we focused on the connection object’s DSN parameters attribute, showing you how to access the DSN parameters using the get_dsn_parameters() method. With the examples we provided as a guide, you’ll be able to get the information you need about the PostgreSQL connections made in your own Python scripts.

Just the Code

Throughout this article, we examined our Python code one segment at a time. Shown below is the Python script in its entirety: