How to connect Postgres SQL using Python

How to connect Postgres SQL using Python

Step 1: Confirm Postgres SQL is running fine

C:\Users\mudhalvan>psql -U mudhalvan testdb01
psql (11.1)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page “Notes for Windows users” for details.
Type “help” for help.

testdb01=# \d emp
Table “public.emp”
Column | Type | Collation | Nullable | Default
——–+—————+———–+———-+———
eno | integer | | |
ename | character(10) | | |

testdb01=# select * from emp;
eno | ename
—–+————
1 | Mudhalvan
2 | Aarthi
(2 rows)

Step 2: Confirm Python is installed and it has Library (psycopg2) to connect Postgres

If not install it

C:\WINDOWS\system32>pip install psycopg2
Collecting psycopg2
Installing collected packages: psycopg2
Successfully installed psycopg2-2.7.7

Step 3: Connect to database.

C:\WINDOWS\system32>python

Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:06:47) [MSC v.1914 32 bit (Intel)] on win32
Type “help”, “copyright”, “credits” or “license” for more information.

import psycopg2

conn=psycopg2.connect(“dbname=testdb01 user=mudhalvan password=xxxx”)
cur=conn.cursor()
cur.execute(“select * from emp”)
for line in cur:
… print(line)
…
(1, ‘Mudhalvan ‘)
(2, ‘Aarthi ‘)
cur.close()
conn.close()

Now you can write a simple .py script with the above lines and confirm it.