PostgreSQL Python

This PostgresQL Python section shows you how to work with PostgreSQL database using Python programming language.

Python has various database drivers for PostgreSQL. Currently, the psycopg is the most popular PostgreSQL database adapter for the Python language. The psycopg fully implements the Python DB-API 2.0 specification.

The current version of the psycopg is 2 or psycopg2. The psycopg2 database adapter implemented in C as a libpq wrapper resulting in both fast and secure. The psycopg2 provides many useful features such as client-side and server-side cursors, asynchronous notification and communication, COPY command support, etc.

In addition, the psycopg2 driver supports many Python types out-of-the-box. The psycopg2 matches Python objects to the PostgreSQL data types e.g., list to the array, tuples to records, and dictionary to hstore.  If you want to customize and extend the type adaption, you can use a flexible object adaption system.

This PostgreSQL Python section covers the most common activities for interacting with PostgreSQL in Python application:

• Connecting to the PostgreSQL database server – shows you how to connect to the PostgreSQL database server from a Python application.
• Creating new PostgreSQL tables in Python – provides you with the steps of creating new tables in PostgreSQL using psycopg2.
• Inserting data into the PostgreSQL table in Python – explains to you how to insert data into a PostgreSQL database table in Python.
• Updating data in the PostgreSQL table in Python – learns various ways to update data in the PostgreSQL table.
• Querying data from the PostgreSQL tables – walks you through the steps of querying data from the PostgreSQL tables in a Python application.
• Calling PostgreSQL stored procedure in Python – guides you how to call stored procedures from the PostgreSQL database in a Python application.
• Handling PostgreSQL BLOB data in Python– gives you an example of inserting and selecting the PostgreSQL BLOB data in a Python application.
• Deleting data in PostgreSQL table in Python – shows you how to delete data in a table in Python.

For the demonstration purpose, we will use the suppliers sample database. The following picture illustrates structure of the suppliers database:

The suppliers database has the following tables:

1.  vendors table: stores vendor data.
2.  parts table: stores parts data.
3.  parts_drawings table: stores the drawing of a part.
4.  vendor_parts table: stores the data of which parts supplied by which vendor.