# Connect with Python API

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:

{% embed url="<https://www.postgresqltutorial.com/postgresql-python/connect/>" %}

### 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.ini`and 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](https://www.postgresqltutorial.com/postgresql-python/connect/))

Place this code in a script titled `config.py`:&#x20;

```
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.

{% embed url="<https://www.postgresqltutorial.com/postgresql-python/>" %}

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).

{% embed url="<https://towardsdatascience.com/python-and-postgresql-how-to-access-a-postgresql-database-like-a-data-scientist-b5a9c5a0ea43>" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://aws-postgresql-docs.beliciarodriguez.com/remote-server-accessing-server-on-local-computer/connect-with-python-api.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.