cancel
Showing results for 
Search instead for 
Did you mean: 

Psycopg -- Python with PostgreSQL

Pre-requisites:

  • Basic Python coding knowledge 
  • Basic PostgreSQL knowledge

 

Description:

  • Psycopg is a lightweight adaptor/connector which helps Python Developers connect their Python programs to PostgreSQL databases.

 

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 yourLD_LIBRARY_PATHto thelib/ location of PostgreSQL
  • Run the commandpython setup.py build_ext --pg-config <YOUR CONFIG LOCATION HERE> buildto 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 Your 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.
  • Create a file with the .py extension calledtest.pywith the following code: 
#!/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 host127.0.0.1 and on port5432
  • If the connection is successful, it should printOpened database successfullyas the message.
  • Here, we give the test.py file executable permissions and execute it successfully:
[root@localhost tmp]# chmod +x test.py 
[root@localhost tmp]# python test.py 
Opened database successfully
  • If it does not connect successfully, it will throw an error like this:
[root@66b473b71c07 /]# python test.py
Traceback (most recent call last):
File "test.py", line 2, in <module>
conn = psycopg2.connect(database="postgres", user = "baduser", password = "edb", host = "127.0.0.1", port = "5444")
File "/usr/lib64/python2.7/site-packages/psycopg2/__init__.py", line 126, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL: role "baduser" does not exist
  • In this next example, we will connect to the database and create a table in it. Create a filecreate_table.pywhich contains the following code:
#!/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()
  • When we execute the script, it will open a connection to the database specified and create a new table. After create_table.pyruns successfully, log into the database and run select * from company;to see your new table. Here's the output from running the script successfully:
[root@66b473b71c07 /]# python create_table.py
Opened database successfully
Table created successfully
[root@66b473b71c07 /]# psql
psql.bin (11.3.10)
Type "help" for help.

postgres=# select * from company;
id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)

I hope that this article will allow you to begin integrating your Python code with PostgreSQL.

Version history
Revision #:
3 of 3
Last update:
‎08-19-2019 04:15 PM
Updated by:
 
Labels (2)