Let us understand how to install Database Connector to connect to Postgres Database and run queries. We will also try to connect to the database and submit a query as part of the validation.
!pip install psycopg2-binary
import psycopg2
psycopg2.connect?
connection = psycopg2.connect(
host=‘pg.itversity.com’,
port=‘5432’,
database=‘sms_db’,
user=‘sms_user’,
password=‘itversity’
)
connection.close()
Here is the function which will return the connection object.
import psycopg2
def get_connection(host, port, database, user, password):
connection = None
try:
connection = psycopg2.connect(
host=host,
port=port,
database=database,
user=user,
password=password
)
except Exception as e:
raise(e)
return connection
Validating the get_connection function.
host = ‘pg.itversity.com’
port = ‘5432’
database = ‘sms_db’
user = ‘sms_user’
password = ‘itversity’
connection = get_connection(
host=host,
port=port,
database=database,
user=user,
password=password
)
connection.close()
The returned connection object exposes a function called as cursor.
orders_cursor = connection.cursor()
host = ‘pg.itversity.com’
port = ‘5432’
database = ‘sms_db’
user = ‘sms_user’
password = ‘itversity’
connection = get_connection(
host=host,
port=port,
database=database,
user=user,
password=password
)
orders_cursor = connection.cursor()
connection.close()
Using cursor, we can execute queries using execute function. It takes a valid query as a string.
query = """SELECT * FROM orders LIMIT 10"""
orders_cursor.execute(query)
To validate, we will use `information_schema.tables`. It is the table provided by Postgres to give information about the tables and views available. You might see some system tables as output.
host = ‘pg.itversity.com’
port = ‘5432’
database = ‘sms_db’
user = ‘sms_user’
password = ‘itversity’
connection = get_connection(
host=host,
port=port,
database=database,
user=user,
password=password
)
cursor = connection.cursor()
query = “SELECT * FROM information_schema.tables LIMIT 10”
cursor.execute(query)
for table_details in cursor:
print(table_details)
connection.close()