cancel
Showing results for 
Search instead for 
Did you mean: 

Psycopg -- Python with PostgreSQL

 

Pre-requisites :

 

  • One should know some basic Python coding 
  • One should know basics of PostgreSQL

 

Description :

 

  • It is light weight adaptor/connector which helps Python Developers to connect their python programs to PostgreSQL database and perform some operation.

 

Installation :

 

  • You can download the package of Psycopg from the URL https://pypi.python.org/pypi/psycopg2/
  • You should have a Postgres cluster running in your environment
  • Export your LD_LIBRARY_PATH to the lib location of PostgreSQL
  • Run the command “python setup.py build_ext --pg-config /opt/PostgreSQL/9.2/bin/pg_config build” to build it from the source package that you have downloaded
  • After building the source, you need to install it using the following command “sudo python setup.py install”

 

Verifying Installation :

 

  • To verify that your Psycopg2 is installed properly, there is a Python command which you can run that performs basic check of your environment by running certain tests
  • Before running the below command, there are certain environmental variables that need to be exported. Those are PSYCOPG2_TESTDB which will any database name from the cluster, PSYCOPG2_TESTDB_HOST host on which Psycopg2 is installed, PSYCOPG2_TESTDB_PORT port on which your Postgres is running and PSYCOPG2_TESTDB_USER user using which you are connecting to the database
  • python -c "from psycopg2 import tests; tests.unittest.main(defaultTest='tests.test_suite')" --verbose

 

Examples :

 

  • In our first example, we will create a Python program which connects to the database and gives the confirmation of that
  • Create a file with the .py extension such as test.py and below are its contents :

#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database="postgres", user = "postgres", password = "edb", host = "127.0.0.1", port = "5432")
print "Opened database successfully”

 

  • In the above example, I am opening a connection which connects to a database “postgres”, which is running on host “127.0.0.1” and on port “5432”
  • If the connection is successful, it should give me a message that “Opened database successfully”

Give the “test.py” file executable permissions and run it using the below command :

[root@localhost tmp]# chmod +x test.py
[root@localhost tmp]# python test.py
Opened database successfully

 

  • In the next example, we will connect to the database and create a table in it. Create a file create_table.py and the program is below :


#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "postgres", user = "postgres", password = "edb", host = "127.0.0.1", port = "5432")
print "Opened database successfully"
cur = conn.cursor()
cur.execute('''CREATE TABLE COMPANY
(ID INT PRIMARY KEY     NOT NULL,
NAME           TEXT    NOT NULL,
AGE            INT     NOT NULL,
ADDRESS        CHAR(50),
SALARY         REAL);''')
print "Table created successfully"
conn.commit()
conn.close()

 

  • Here, I am opening the connection and using that connection I am creating a table in the database which I have mentioned in the connection string. Below is how I run it again :

[root@localhost tmp]# chmod +x create_table.py
[root@localhost tmp]# python create_table.py
Opened database successfully
Table created successfully

 

After the above run, you can check it by logging into the database to verify that the table is created or not

Hope this will give an idea how to integrate your Python code with PostgreSQL.

Version history
Revision #:
1 of 1
Last update:
‎01-31-2018 01:33 AM
Updated by:
 
Labels (2)
Contributors