[psycopg2] How to use Postgres in Python

1. What is Postgres?

Relational Database Management System(RDBMS) is a set of software tools that allow multiple users to access data within a database. DBMS allows users or other programs to process and responds appropriately to use data.

 

For SQLite, only a single process is allowed to write to the database (It only process query statement one by one.). Therefore, there is a limit to working with the data environment or other users within the organization.

 

PostgresSQL is a relational database management system that emphasize scalability and compliance with standards and its primary function as a database server is to securely store data and return it when it responds to requests from other application source software. It can manage multiple loads, from small single-machine application to large Internet application with numerous concureent users.

 

Postgres, which work as a server, allows multiple users to access data when writing different types of query statements. Postgres uses server-client modelm which a plurality of users may access the database through the sever, simultaneously perform different query statements, and check the results.

 

2. How to use Postgres in Python?

Step 1 : Import the psycopg2 package

Like sqlite, Postgres uses an open source library called psycopg2 inside Python. To use Postgres in Python, we import psycopg2 library on python script. psycopg2 is an open-source library that implements the Postgres protocol to connect to our Postgres server.

 

import psycopg2

 

Step 2 : Connect to the server using the connect function

After importing psycopg2 library, then we declare connection object. The psycopg2.connect() method is used to connect to the database and receives the database and user name we want to connect to as an argument. The method specifies that "[username]'s user is trying to access [dbname]'s database".

 

conn = psycopg2.connect("dbname=db_name user=user_name")

 

The connection object generated by receiving the connection string is connected to the Postgres server and interacts with the database until the server is closed.

 

Step 3 : Obtain the cursor object for connection

To interact with the database, a query statement can be executed using the connection.cursor() method.

 

cursor = conn.cursor()

 

Step 4 : Issue SQL queries by using the execute method of the cursor

We can write a query statement in the same way as SQLite and then execute the command in the database using the cursor.execute() method. The cursor.execute() method outputs the execution results only as None/error. Therefore, We should use the cursor.fetchone() or cursor.fetchall() method, which stores the results.

 

query = "SELECT * FROM table;"
cursor.execute(query) 
users = cursor.fetchall()

 

One or more cursor.execute() is called a transaciton block and consists of logical units of execution.

 

Step 5 : Commit our changes with the commit method of the connection

Inside Postgres, the connection.commit() method is used to execute queires for transcations. The unit of transcation includes all queries that are executed after executing the psycopg2 module until committed.

 

conn.commit()

 

Step 6 : Close the connection with the close method

conn.close()

 

Source from :

• https://www.postgresql.org/docs/   
• initd.org/psycopg/docs/