Python and PostgreSQL: Create a PostgreSQL Database Connection Using a Config File

This technique can easily be applied to other databases.

1. pip install two packages if you do not already have them in your Python environment.

(Search the internet for ‘python pip install’ if you need help.)

A. psycopg2: To interact with PostgreSQL databases

B. configparser: To interact with config files

2. Create a config file named config.ini. Paste in the text below:

[default]
host = localhost
db_name = your_database_name
postgres_user = postgres_username
postgres_password = your_password

Edit the details to match your environment.

3. Create a Python file named main.py. Paste in the text below:

from configparser import SafeConfigParser
import psycopg2

parser = SafeConfigParser()
parser.read('config.ini')

host = parser.get('default', 'host')
db_name = parser.get('default', 'db_name')
postgres_user = parser.get('default', 'postgres_user')
postgres_password = parser.get('default', 'postgres_password')

print('Python program: Connect to a PostgeSQL database.\n')

conn_string = "host='%s' dbname='%s' user='%s' password='%s'" % (host, db_name, postgres_user, \
   postgres_password)
conn = psycopg2.connect(conn_string)

cursor = conn.cursor() # Required to interact with the database.

sql = """
SELECT current_database();
"""

print("Query sent to the database:")
print(sql)

cursor.execute(sql)

# Note: If inserting data, Commit after executing the cursor, or nothing will happen.
#conn.commit()

# Building an output string:
sql_output = ''

for record in cursor:
   sql_output += str(record) + '\n'

# Write the output from the database:
print(sql_output)

4. Test the program. Run this command at a command prompt:

python main.py

Program output:

Python program: Connect to a PostgeSQL database.

Query sent to the database:

SELECT current_database();

('your_database_name',)

Your output will look slightly different, unless your database is named ‘your_database_name’.