How can I connect Postgres database from Google Colab?

Question

I can connect sqlite from Google Colab by uploading the database file and executing the following commands:

import sqlite3
con = sqlite3.connect('new.db')
cur = con.cursor()
cur.execute("SELECT * FROM page_log") # page_log is a table name in the new.db.
cur.fetchone()

This works fine since we can upload the database file in sqlite due to its nature. For postgres, the situation is not so clear, at least I could not figure out how to do it. I have checked this and implemented as:

%sql postgres://postgres:1234@postgres/postgres

but it did not work. The error message:

Connection info needed in SQLAlchemy format, example:
           postgresql://username:password@hostname/dbname
           or an existing connection: dict_keys([])

I tried this as well:

from sqlalchemy.orm import scoped_session, sessionmaker
engine = create_engine("postgres://postgres:1234@localhost:5432")
db = scoped_session(sessionmaker(bind=engine))
db.execute("SELECT * FROM page_log").fetchall()

The error:

OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?

Finally, this did not work as well.

db = psycopg2.connect(host='localhost', port=5432, user='postgres',
                          password='1234', dbname='postgres')

The error message:

OperationalError: could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?

I could not find a solution. I'd appreciate your help!

Answer 1

Just like JosMac suggested, PostgreSQL has to be installed somewhere.

The following is a workaround (assuming you are using a Mac):

• Install a PostgreSQL server locally, say this postgres APP (The reason I choose this is that you won't have to worry about setting up the server).
• Connect your Colab to a local runtime
• Access the server by %sql postgresql://username:@localhost:5432/username where username is your system username. As per other fields required by SQLAlchemy, they are taken care of by simply 'initializing' the APP: the password has been set to none, host to localhost, port tp 5432 and dbname to system username.