# Connecting to PostgreSQL using Python

> Connect to PostgreSQL from Python using psycopg2 or PyGreSQL inside a virtual environment, with example code.

Source: https://helpcenter.ruachost.com/developers/databases/connecting-to-postgresql-using-python/

> **PostgreSQL is available on Managed VPS only:**
Ruachost shared hosting supports MySQL (MariaDB) databases, not PostgreSQL. To use PostgreSQL, choose a [Managed VPS](https://ruachost.com/vps).

Python provides multiple libraries for connecting to PostgreSQL databases. On **ruachost.com**, you can use packages such as **psycopg2** or **PyGreSQL** to establish connections, run queries, and manage your data.

## Why Use Python for PostgreSQL?

- Easy integration with web applications and scripts.
- Supports complex queries and transactions.
- Portable SQL API allows switching between libraries with minimal code changes.

## Steps to Connect to PostgreSQL Using Python

### Step 1: Set Up a Virtual Environment

1. Log in to your hosting account via SSH.
2. Create a virtual environment:

   ```bash
   python3 -m venv sqlenv
   ```
3. Activate the environment:

   ```bash
   source sqlenv/bin/activate
   ```
4. Update pip:

   ```bash
   source sqlenv/bin/activate
   ```

### Step 2: Install a PostgreSQL Package

- For **psycopg2**:

  ```bash
  pip install psycopg2
  ```
- For **PyGreSQL**:

  ```bash
  pip install pygresql
  ```

### Step 3: Connect Using Python’s Portable SQL API

### Example with psycopg2

```

hostname = 'localhost'
username = 'yourusername'
password = 'yourpassword'
database = 'yourdbname'

def doQuery(conn):
    cur = conn.cursor()
    cur.execute("SELECT fname, lname FROM employee")
    for firstname, lastname in cur.fetchall():
        print(firstname, lastname)

print("Using psycopg2:")
myConnection = psycopg2.connect(host=hostname, user=username, password=password, dbname=database)
doQuery(myConnection)
myConnection.close()
```

### Example with PyGreSQL (pgdb)

```

hostname = 'localhost'
username = 'yourusername'
password = 'yourpassword'
database = 'yourdbname'

myConnection = pgdb.connect(host=hostname, user=username, password=password, database=database)
doQuery(myConnection)
myConnection.close()
```

### Step 4: Legacy Connection (Optional)

PyGreSQL also includes a legacy `pg` module:

```

conn = pg.DB(host="localhost", user="yourusername", passwd="yourpassword", dbname="yourdbname")
result = conn.query("SELECT fname, lname FROM employee")
for firstname, lastname in result.getresult():
    print(firstname, lastname)
conn.close()
```

> **Important Notes:**
- Replace `localhost`, `yourusername`, `yourpassword`, and `yourdbname` with your actual database credentials.
- Ensure PostgreSQL databases and users are already created before connecting.
- Always close connections after use to free resources.
- Use prepared statements for secure queries against SQL injection.