Connect with Python API

Connect to your server using python scripts

You can also connect to your database and execute queries through PostgreSQL's Python API. We'll be using the python package psycopg2 to connect to our database in the following steps:

1. Create a database.ini file containing sensitive information needed to connect to our database

2. Create a config.py script containing a config function

3. Create and run a connect_to_db.py script to connect to the database

I relied heavily on this website for the information on this page:

PostgreSQL Python: Connecting to PostgreSQL ServerNeon

Create "database.ini"

To connect to your database, you first need to provide Python your credentials. If you're sharing project code with team, or if your code is in a public repository, you don't want to share your host name, username, and (most importantly) password with everyone who sees the Python scripts that run queries.

Instead, we can write all of our login credentials in a file titled database.iniand refer to it in our config.py script. Additionally, if you're using a GitHub repository to share project code, we can add database.ini to our .gitignore file so that our personal login information is not contained in the script.

In your folder where you will place all your Python scripts with queries (meaning the script will require you to connect to your database), write a database.ini file that contains the following information:

[postgresql]
host=ec2-[your-public-IPv4-address].[region].compute.amazonaws.com"
database=db
user=username
password=password

Create config.py script

To set your configurations for connecting to your database in Python, create a config.py file with a function called config, which you will be using whenever you want to connect to your database in your scripts. (2)

Place this code in a script titled config.py:

from configparser import ConfigParser

def config(filename='database.ini', section='postgresql'):
    # create a parser
    parser = ConfigParser()
    # read config file
    parser.read(filename)

    # get section, default to postgresql
    db = {}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            db[param[0]] = param[1]
    else:
        raise Exception(
            'Section {0} not found in the {1} file'.format(section, filename)
            )

    return db

Connect to your Database

Now, we will run a function called "connect()" to test whether we can connect to our database.

Place the following code in a script called connect_to_db.py:

import psycopg2
from config import config

def connect():
    """ Connect to the PostgreSQL database server """
    conn = None
    try:
        # read connection parameters
        params = config()

        # connect to the PostgreSQL server
        print('Connecting to the PostgreSQL database...')
        conn = psycopg2.connect(**params)
		
        # create a cursor
        cur = conn.cursor()
        
	# execute a statement
        print('PostgreSQL database version:')
        cur.execute('SELECT version()')

        # display the PostgreSQL database server version
        db_version = cur.fetchone()
        print(db_version)
       
	# close the communication with the PostgreSQL
        cur.close()
    
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    
    finally:
        if conn is not None:
            conn.close()
            print('Database connection closed.')


if __name__ == '__main__':
    connect()

Run connect_to_db.py in your terminal:

python connect_to_db.py

Your output should be a variation of the following

Connecting to the PostgreSQL database...
PostgreSQL database version:
(<PostgreSQL version you're using on AWS EC2 machine>)
Database connection closed.

If you see the above output, then you've successfully connected to your database!

Additional Resources

If you want to learn more about creating table and executing other queries using the Python API, the following documentation, which I also referenced throughout this section, has tutorials and examples on creating table and executing other queries using psycopg2.

PostgreSQL PythonNeon

Also, the following Medium post also discusses how to use pandas to create dataframes from tables in your database (towards the end of the post).

https://towardsdatascience.com/python-and-postgresql-how-to-access-a-postgresql-database-like-a-data-scientist-b5a9c5a0ea43towardsdatascience.com