How can I connect to remote database using psycopg3

Question

I'm using Psycopg3 (not 2!) and I can't figure out how can I connect to a remote Postgres server

psycopg.connect(connection_string)

https://www.psycopg.org/psycopg3/docs/

Thanks!

Answer 1

Psycopg3 uses the postgresql connection string which can either be a string of keyword=value elements (separated by spaces)

with psycopg.connect("host=your_server_hostname port=5432 dbname=your_db_name") as conn:

or a URI

with psycopg.connect("postgresql://user:user_password@db_server_hostname:5432") as conn:

So, put all together with their example (mixed with one example from above connection strings) from their docs:

# Note: the module name is psycopg, not psycopg3
import psycopg

# Connect to an existing database
with psycopg.connect("postgresql://user:user_password@db_server_hostname:5432") as conn:

    # Open a cursor to perform database operations
    with conn.cursor() as cur:

        # Execute a command: this creates a new table
        cur.execute("""
            CREATE TABLE test (
                id serial PRIMARY KEY,
                num integer,
                data text)
            """)

        # Pass data to fill a query placeholders and let Psycopg perform
        # the correct conversion (no SQL injections!)
        cur.execute(
            "INSERT INTO test (num, data) VALUES (%s, %s)",
            (100, "abc'def"))

        # Query the database and obtain data as Python objects.
        cur.execute("SELECT * FROM test")
        cur.fetchone()
        # will return (1, 100, "abc'def")

        # You can use `cur.fetchmany()`, `cur.fetchall()` to return a list
        # of several records, or even iterate on the cursor
        for record in cur:
            print(record)

        # Make the changes to the database persistent
        conn.commit()

Answer 2

There are two options for connection string according to the documentation

conninfo – The connection string (a postgresql:// url or a list of key=value pairs) to specify where and how to connect.