Executing SQL query with Psycopg2 in Python

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)]