# The psycopg2 Package

The `psycopg2` ("psycho pee gee") package provides a way for Python to interface with [PostgreSQL](https://www.postgresql.org/) databases.

> Psycopg is the most popular PostgreSQL adapter for the Python programming language. At its core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL. - [Psycopg website](https://www.psycopg.org/)

Reference:

* <https://github.com/psycopg/psycopg2>
* <https://www.psycopg.org/docs/>
* <https://www.postgresql.org/docs/9.5/datatype.html>
* <https://www.psycopg.org/docs/usage.html>
* <https://www.psycopg.org/docs/cursor.html#cursor>
* <https://www.psycopg.org/docs/cursor.html#fetch>
* <https://www.psycopg.org/docs/extras.html>
* <https://www.psycopg.org/docs/extras.html#dictionary-like-cursor>

Run a `psycopg2` application "in development" using a database server on your local machine, and/or "in production" using a remote database server hosted by a provider like Heroku. If you run it in development, you should be able to connect via localhost, whereas if you run it in production, you should be able to connect using the production server's credentials. The professor recommends using [Table Plus](https://tableplus.com/) or some other GUI interface to your PostgreSQL databases, local or remote.

## Installation

As a prerequisite: install PostgreSQL on your local machine. If you are on a Mac, use Homebrew: `brew install postgresql` and follow the post-installation instructions. Make sure you can connect to your local PostgreSQL installation via a GUI or command-line interface. If attempting to connect from the command-line, try running `psql` or perhaps `psql -U your_username`, depending on the name of your computer's user and method of PostgreSQL installation. Note the username and password you are using to connect.

After demonstrating your ability to connect to a local PostgreSQL installation, install the Python package:

```bash
pip install psycopg2
```

## Usage

To setup this example, gain access to an existing PostgreSQL database, and observe its connection credentials.

```python
import os
from dotenv import load_dotenv
import psycopg2
from psycopg2.extras import DictCursor, execute_values

load_dotenv()

DB_NAME = os.getenv("DB_NAME")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")
DB_HOST = os.getenv("DB_HOST")

# CONNECT TO THE DATABASE

connection = psycopg2.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASSWORD, host=DB_HOST)
print("CONNECTION:", connection)

cursor = connection.cursor(cursor_factory=DictCursor)
print("CURSOR:", cursor)

# CREATE A TABLE

query = """
CREATE TABLE IF NOT EXISTS passengers (
    id SERIAL PRIMARY KEY,
    survived bool,
    full_name varchar,
    gender varchar,
    age int,
    fare float8
);
"""
cursor.execute(query)

# INSERTING RECORDS (SINGLE)

insertion_query = "INSERT INTO passengers (survived, full_name, gender, age, fare) VALUES (%s, %s, %s, %s, %s)"
record_to_insert = (False, "Passenger X", "male", 45, 100.00)
cursor.execute(insertion_query, record_to_insert)

# INSERTING RECORDS (MULTIPLE)

records_to_insert = [
  (False, "Passenger 1", "male", 45, 100.00),
  (True, "Passenger 2", "female", 42, 100.00),
  (True, "Passenger 3", "male", 10, 50.00),
  (True, "Passenger 4", "female", 8, 50.00),
]
multi_insertion_query = "INSERT INTO passengers (survived, full_name, gender, age, fare) VALUES %s"
execute_values(cursor, multi_insertion_query, records_to_insert) # third param: data as a list of tuples!

# EXECUTING QUERIES

cursor.execute("SELECT * from passengers;")
result = cursor.fetchall()
print("RESULT:", type(result))
for row in result:
    print("-----")
    print(type(row))
    print(row)

# CLEANING UP

connection.commit() # actually save the transaction (necessary only when creating tables or inserting data)
cursor.close()
connection.close()
```


---

# 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://prof-rossetti.gitbook.io/intro-to-python/notes/python/packages/psycopg.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.