How To Connect To PostgreSQL Database And Execute SQL Statement Use Psycopg2 In Python

Psycopg is a popular python postgresql database driver library which provide access to postgresql database server. Use psycopg, you can connect to postgresql database and execute sql statement easily in python source code. This article will tell you how to connect to postgresql database server use psycopg2, it also tell you how to run sql statement use psycopg2 in python source code.

Before start we should install postgresql database server and create a database with name dev2qa, you can read article How To Install And Manage ( Create, Read, Update, Delete ) PostgreSQL Database On Ubuntu Linux to learn more.

1. Install Psycopg.

1. Open a terminal and use pip install to install the psycopg2 module.

   $ pip3 install psycopg2
   

2. But the installation process maybe fail, and you may find below error messages in the console.

   Error: b'You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.\n'

3. To fix above error, you need to install libpq-dev in ubuntu ( my linux os is ubuntu, if you use other os, install it with that os compatible command. )

   ~$ sudo apt-get install libpq-dev python-dev

4. When above command successfully install libpq-dev and python-dev module, use pip install to install psycopg2 again. If you see text like Successfully installed psycopg in the output console, this means psycopg2 has been installed successfully.

   ~$ pip3 install psycopg2
   Collecting psycopg2
     Using cached https://files.pythonhosted.org/packages/23/7e/93c325482c328619870b6cd09370f6dbe1148283daca65115cd63642e60f/psycopg2-2.8.2.tar.gz
   Building wheels for collected packages: psycopg2
     Running setup.py bdist_wheel for psycopg2 ... done
     Stored in directory: /home/zhaosong/.cache/pip/wheels/3b/d0/9c/fbbaca1e768e108fdcb88a9a50ea43de141adf842741f8623f
   Successfully built psycopg2
   Installing collected packages: psycopg2
   Successfully installed psycopg2-2.8.2

5. Verify psycopg installation information.

   ~$ pip3 show psycopg2
   Name: psycopg2
   Version: 2.8.2
   Summary: psycopg2 - Python-PostgreSQL Database Adapter
   Home-page: http://initd.org/psycopg/
   Author: Federico Di Gregorio
   Author-email: fog@initd.org
   License: LGPL with exceptions or ZPL
   Location: /home/zhaosong/.local/lib/python3.6/site-packages
   Requires: 
   

2. Use Psycopg To Connect PostgreSQL Database Server In Python Source Code.

Below is the steps of how to use psycopg2 to connect and execute sql to postgresql database server in python source code.

1. Import psycopg2 module.

   import psycopg2

2. Invoke psycopg2 connect method to get postgresql database server connection.

   conn = psycopg2.connect(host=host_ip, port=port_number, database=db_name, user=user_name, password=password)

3. Invoke posgresql connection object’s cursor() method to get database cursor object.

   cursor = conn.cursor()

4. Use the cursor object to execute sql statement.

   cursor.execute(sql)

5. If the sql is a select sql statement, then invoke cursor object’s fetchone(), fetchall() or fetchmany() method get the returned result.

   result = cursor.fetchone()

6. When the database operation ending, do not forget close the cursor and connection object.

   # close cursor object before close connection.
   cursor.close()
   
   # close connection object.
   conn.close()

3. Use Psycopg2 In Python Source Code Example.

Below example contains a python class PostgresqlManager, this class contains several method that can get postgresql database connection, get database cursor, execute sql statement, close cursor and cloase database connection.

PostgresqlManager.py

import psycopg2

'''
This class has two instance variable, _conn and _cursor.
These two private variable is used to store postgresql database connection and cursor object.
'''
class PostgresqlManager:

    # get postgresql database connection use provided database parameters.
    def get_connection(self, host_ip, port_number, db_name, user_name, password):

        conn = psycopg2.connect(host=host_ip, port=port_number, database=db_name, user=user_name, password=password)

        # store the database connection object to private instance variable.
        self._conn = conn

        # print connection properties
        print("This postgresql database connection properties : ", conn.get_dsn_parameters(), "\n")

        print("******* get postgresql database connection ********", "\n")

    # close the db cursor and connection object.
    def close_connection(self):

        if self._cursor is not None:
            self._cursor.close()

        if self._conn is not None:
            self._conn.close()

        print("******* close postgresql database connection ********", "\n")


    # get db cursor object.
    def get_cursor(self):

        if self._conn is not None:

            if not hasattr(self, '_cursor') or self._cursor is None or self._cursor.closed:

               # save the db cursor object in private instance variable.
               self._cursor = self._conn.cursor()


    # execute select sql command.
    def execute_select_sql(self, sql):

        self.get_cursor()

        self._cursor.execute(sql)

        # get the sql execution result.
        result = self._cursor.fetchone()

        print("Record is : ", result, "\n")



if __name__ == '__main__':

    # first create an instance of PostgresqlManager class.
    postgresql_manager = PostgresqlManager()

    # get db connection with provided postgresql database properties.
    postgresql_manager.get_connection('localhost', 5432, 'dev2qa', 'test_user', 'test_user')

    # execute a select sql statement.
    postgresql_manager.execute_select_sql('select version();')

    # close the db cursor and connection object.
    postgresql_manager.close_connection()

When you run above python code, you will get below result.

This postgresql database connection properties :  {'user': 'test_user', 'dbname': 'dev2qa', 'host': 'localhost', 'port': '5432', 'tty': '', 'options': '', 'sslmode': 'prefer', 'sslcompression': '1', 'krbsrvname': 'postgres', 'target_session_attrs': 'any'} 

******* get postgresql database connection ******** 

Record is :  ('PostgreSQL 10.7 (Ubuntu 10.7-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit',) 

******* close postgresql database connection ********

Reference

1. The psycopg2 connection class.