Psycopg2 - Connect to postgreSQL database using a connection string

Question

I currently have a connection string in the format of:

"localhost://username:password@data_quality:5432"

What is the best method of using this to connect to my database using psycopg2? e.g.:

connection = psycopg2.connect(connection_string)

Karelian

Answer 1

You could make use of urlparse, creating a dictionary that matches psycopg's connection arguments:

import psycopg2
from urllib.parse import urlparse

conStr = "localhost://username:password@data_quality:5432"
p = urlparse(conStr)

pg_connection_dict = {
    'dbname': p.hostname,
    'user': p.username,
    'password': p.password,
    'port': p.port,
    'host': p.scheme
}

print(pg_connection_dict)
con = psycopg2.connect(**pg_connection_dict)
print(con)

Out:

{'dbname': 'data_quality', 'user': 'username', 'password': 'password', 'port': 5432, 'host': 'localhost'}
<connection object at 0x105f58190; dsn: 'user=xxx password=xxx dbname=xxx host=xxx port=xxx', closed: 0>

Spoilsman

Answer 2

Documentation for pyscopg2 connect is here. Following their syntax you connect like this:

connection = psycopg2.connect(host='localhost', user='<username>',
                              password='<password>', 
                              dbname='data_quality', port=5432)

If you are using Windows, it can be stupid about resolving localhost if you don't have a network connection. You can get around this by instead using '127.0.0.1' for host

Gerah

Answer 3

Your connection string looks wrong. It shouldn't start with localhost, as that is the place for the protocol, like "http" or in this case, "postgres". And then you can use:

connection = connect("postgres://username:password@localhost:5432/data_quality")

... assuming data_quality is the name of your schema.

Filomenafiloplume