Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

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.

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning - Basic Level Course

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




My Personal Notes arrow_drop_up
Recommended Articles
Page :