Setup Database Client Libraries¶

Let us understand how to install Database Connector to connect to Postgres Database and run queries. We will also try to connect to database and submit a query as part of the validation.

You can install Postgres Python connector using python3 -m pip install psycopg2 or python3 -m pip install psycopg2-binary.
Typically we need following information for our applications to connect to database.
- Host Name or IP Address
- Port Number
- Database Name
- Username
- Password
We need to ensure that right permissions are provided on the database to the user to perform required operations.
For CRUD operations, the user need to have SELECT, INSERT, UPDATE and DELETE permissions at the least.

!pip install psycopg2-binary

Defaulting to user installation because normal site-packages is not writeable
Collecting psycopg2-binary
  Downloading psycopg2_binary-2.8.6-cp36-cp36m-manylinux1_x86_64.whl (3.0 MB)
     |████████████████████████████████| 3.0 MB 20.1 MB/s eta 0:00:01
?25hInstalling collected packages: psycopg2-binary
Successfully installed psycopg2-binary-2.8.6

import psycopg2

psycopg2.connect?

Signature:
psycopg2.connect(
    dsn=None,
    connection_factory=None,
    cursor_factory=None,
    **kwargs,
)
Docstring:
Create a new database connection.

The connection parameters can be specified as a string:

    conn = psycopg2.connect("dbname=test user=postgres password=secret")

or using a set of keyword arguments:

    conn = psycopg2.connect(database="test", user="postgres", password="secret")

Or as a mix of both. The basic connection parameters are:

- *dbname*: the database name
- *database*: the database name (only as keyword argument)
- *user*: user name used to authenticate
- *password*: password used to authenticate
- *host*: database host address (defaults to UNIX socket if not provided)
- *port*: connection port number (defaults to 5432 if not provided)

Using the *connection_factory* parameter a different class or connections
factory can be specified. It should be a callable object taking a dsn
argument.

Using the *cursor_factory* parameter, a new default cursor factory will be
used by cursor().

Using *async*=True an asynchronous connection will be created. *async_* is
a valid alias (for Python versions where ``async`` is a keyword).

Any other keyword parameter will be passed to the underlying client
library: the list of supported parameters depends on the library version.
File:      ~/.local/lib/python3.6/site-packages/psycopg2/__init__.py
Type:      function

connection = psycopg2.connect(
    host='localhost',
    port='5432',
    database='itversity_sms_db',
    user='itversity_sms_user',
    password='sms_password'
)
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 = 'localhost'
port = '5432'
database = 'itversity_sms_db'
user = 'itversity_sms_user'
password = 'sms_password'

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 = 'localhost'
port = '5432'
database = 'itversity_sms_db'
user = 'itversity_sms_user'
password = 'sms_password'

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)

Note

To validate we will use information_schema.tables. It is the table provided by Postgres to give the information of the tables and views that are available for us. You might end up seeing some system tables as output.

host = 'localhost'
port = '5432'
database = 'itversity_sms_db'
user = 'itversity_sms_user'
password = 'sms_password'

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()

('itversity_sms_db', 'public', 'users_range_part_default', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None)
('itversity_sms_db', 'public', 'users_range_part_2016', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None)
('itversity_sms_db', 'public', 'users_range_part_2019', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None)
('itversity_sms_db', 'public', 'users_range_part_2020', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None)
('itversity_sms_db', 'pg_catalog', 'pg_type', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None)
('itversity_sms_db', 'pg_catalog', 'pg_foreign_table', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None)
('itversity_sms_db', 'public', 'employees', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None)
('itversity_sms_db', 'pg_catalog', 'pg_roles', 'VIEW', None, None, None, None, None, 'NO', 'NO', None)
('itversity_sms_db', 'pg_catalog', 'pg_settings', 'VIEW', None, None, None, None, None, 'NO', 'NO', None)
('itversity_sms_db', 'pg_catalog', 'pg_prepared_statements', 'VIEW', None, None, None, None, None, 'NO', 'NO', None)

Mastering Python

Setup Database Client Libraries¶