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:
1 | pip3 install psycopg2 |
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 thepsql
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 is5432
."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
andverify-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 defaultkrbsrvname
ispostgres
."target_session_attrs"
— This attribute is used to specify what kind of PostgreSQL transactions will take place. The default value isany
.
Here’s an example of what the dictionary key-value pairs for the dsn_parameters
object might look like:
1 2 3 4 5 6 7 8 9 10 | "user": "objectrocket", "dbname": "some_database", "host": "localhost", "port": "5432", "tty": "", "options": "", "sslmode": "prefer", "sslcompression": "1", "krbsrvname": "postgres", "target_session_attrs": "any" |
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:
1 2 3 4 5 | # import the psycopg2 database adapter for PostgreSQL from psycopg2 import connect # import Python's JSON library to format JSON strings import json |
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:
1 2 3 4 5 6 7 8 9 10 | # instantiate a cursor object from the connection try: # declare a new PostgreSQL connection object conn = connect ( dbname = "some_database", user = "objectrocket", host = "localhost", password = "mypass" ) |
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:
1 2 3 4 5 | # return a dict object of the connection object's DSN parameters dsm_param = conn.get_dsn_parameters() # print the JSON response from the get_dsn_parameters() method call print ("\nget_dsn_parameters():", json.dumps(dsm_param, indent=4)) |
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:
1 2 3 4 5 6 | # print the get_dsn_parameters() attributes print ("connection dsn():", conn.dsn) print ("dbname:", dsm_param["dbname"]) except Exception as err: print ("\npsycopg2 connection error:", err) |
Print the DSN parameters for the PostgreSQL connection
The code shown above should return a response that looks like the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | get_dsn_parameters(): { "user": "objectrocket", "dbname": "some_database", "host": "localhost", "port": "5432", "tty": "", "options": "", "sslmode": "prefer", "sslcompression": "1", "krbsrvname": "postgres", "target_session_attrs": "any" } connection dsn(): user=objectrocket password=xxx dbname=some_database host=localhost dbname: some_database |
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:
1 | print ("DSN:", conn.dsn) |
The output will look like the following:
1 | 'user=objectrocket password=xxx dbname=some_database host=localhost' |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | #!/usr/bin/python3 # -*- coding: utf-8 -*- # import the psycopg2 database adapter for PostgreSQL from psycopg2 import connect # import Python's JSON library to format JSON strings import json # instantiate a cursor object from the connection try: # declare a new PostgreSQL connection object conn = connect ( dbname = "some_database", user = "objectrocket", host = "localhost", password = "mypass" ) # return a dict object of the connection object's DSN parameters dsm_param = conn.get_dsn_parameters() # print the JSON response from the get_dsn_parameters() method call print ("\nget_dsn_parameters():", json.dumps(dsm_param, indent=4)) # print the get_dsn_parameters() attributes print ("connection dsn():", conn.dsn) print ("dbname:", dsm_param["dbname"], "\n") print ("DSN:", conn.dsn) except Exception as err: print ("\npsycopg2 connection error:", err) |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started