Executing SQL query with Psycopg2 in Python

Last Updated : 17 Oct, 2021

In this article, we are going to see how to execute SQL queries in PostgreSQL using Psycopg2 in Python.

Psycopg2 is a PostgreSQL database driver, it is used to perform operations on PostgreSQL using python, it is designed for multi-threaded applications. SQL queries are executed with psycopg2 with the help of the execute() method. It is used to Execute a database operation query or command.

Parameters can be provided in the form of a sequence or a mapping, and they’ll be tied to variables in the operation. Positional ( % s) or named ( % (name)s) placeholders are used to specify variables. execute() method returns “none” if the query is properly executed (without errors).

Example 1: Executing the “create table” command.

Python3

import psycopg2 
  
conn = psycopg2.connect( 
    database="geeks", user='postgres',  
  password='root', host='localhost', port='5432'
) 
  
conn.autocommit = True
cursor = conn.cursor() 
  
sql = '''CREATE TABLE employees(emp_id int,emp_name varchar, \ 
salary decimal); '''
  
cursor.execute(sql) 
  
conn.commit() 
conn.close() 

Output:

Example 2: Executing the “insert command”

Python3

import psycopg2 
  
conn = psycopg2.connect( 
    database="geeks", user='postgres', 
    password='root', host='localhost', port='5432'
) 
  
conn.autocommit = True
cursor = conn.cursor() 
  
sql = '''insert into employee values('191351','divit','100000.0'), 
                                     ('191352','rhea','70000.0'); 
 '''
  
cursor.execute(sql) 
  
conn.commit() 
conn.close() 

Output:

Example 3: Executing the “select” command.

Python3

import psycopg2 
  
conn = psycopg2.connect( 
    database="geeks", user='postgres', 
    password='root', host='localhost', port='5432'
) 
  
conn.autocommit = True
cursor = conn.cursor() 
  
sql = '''SELECT * FROM employee;'''
  
cursor.execute(sql) 
results = cursor.fetchall() 
print(results) 
  
conn.commit() 
conn.close() 

Output:

[(1216755, ‘raj’, ‘data analyst’, 1000000, 2), (1216756, ‘sarah’, ‘App developer’, 60000, 3), (1216757, ‘rishi’, ‘web developer’, 60000, 1), (1216758, ‘radha’, ‘project analyst’, 70000, 4), (1216759, ‘gowtam’, ‘ml engineer’, 90000, 5), (1216754, ‘rahul’, ‘web developer’, 70000, 5), (191351, ‘divit’, ‘100000.0’, None, None), (191352, ‘rhea’, ‘70000.0’, None, None)]

Python MySQL - Create Database

isitapol2002

Improve

Article Tags :

Practice Tags :

python

Executing SQL query with Psycopg2 in Python

Example 1: Executing the “create table” command.

Python3

Example 2: Executing the “insert command”

Python3

Example 3: Executing the “select” command.

Python3

Similar Reads

Introduction to SQL

Setting Up the Environment

SQL Basics

SQL Operators

Working with Data

SQL Queries

Data Manipulation

Data Analysis

Data Visualization

Thank You!

What kind of Experience do you want to share?