How To Connect and run SQL Queries to a PostgreSQL Database from Python
Last Updated :
19 Nov, 2024
In this PostgreSQL Python tutorial, we will explain how to connect to a PostgreSQL database using Python and execute SQL queries. Using the powerful psycopg2 library, we can seamlessly interact with our PostgreSQL database from Python, making it easy to perform tasks like inserting, updating, and retrieving data.
Whether we're new to Python or a seasoned developer, mastering these techniques will enhance our ability to manage and manipulate data directly within our PostgreSQL database. In this article, we will walk us through the process of connecting to a PostgreSQL database using Python, running SQL queries, and handling results effectively.
Introduction to PostgreSQL and Python Integration
PostgreSQL is a widely-used open-source relational database management system known for its scalability, robustness, and advanced features. Python, with its flexibility, makes database connectivity seamless through libraries like psycopg2, enabling developers to interact with PostgreSQL databases for executing SQL queries.
Step 1: Install psycopg2 Library
The psycopg2 library is the most popular and widely used PostgreSQL adapter for Python.
- Install PostgreSQL, If you haven't installed it.
- We need to install the psycopg2 library to connect to a PostgreSQL database. Open the command prompt and run the below command to install psycopg2
pip3 install psycopg2
Step 2: Create a PostgreSQL Database
To interact with a database, we need a PostgreSQL database. We can create a Database in 2 Ways:
- Using pgAdmin 4 UI
- Using SQL query
1. Using pgAdmin 4 UI
Go to pgAdmin and Follow these Steps.
- Open pgAdmin and navigate to your server.
- Right-click on the server, select Create -> Database.
- Fill out the form (e.g., database name:
Workspace) and click Save.
Create Database from side menu pop-up2. Create Database Using SQL query
Go to pgAdmin and follow these Steps. Run the below Command in the Query tab
CREATE DATABASE WorkSpace;
Run the create database commandStep 3: Connect to PostgreSQL Database Using psycopg2
We need to connect to a PostgreSQL database using psycopg2.connect() function.
Where the attributes of connect() function are:
host: Hostname (e.g., localhost)
dbname: Database name
user: Username
password: Password
port: Port number (default is 5432)
In case we don't know any of these connect() function attributes, we can follow the below steps:
Python Example: Establishing Connection
Now You Know All the properties of this Database. To connect to the database, we need to pass the attributes as arguments to the connect() function.
Syntax
conn = psycopg2.connect(
host = 'localhost',
dbname = 'For_Practice',
user = 'postgres',
password = '[Password]',
port = 5432
)
Step 4: Create a Cursor
- Create a cursor(i.e., curr) object and call its execute() method to execute queries.
- Where execute() method is used to run a query that is passed as a string.
Syntax
cur = conn.cursor()
cur.execute('[SQL queries]')
Step 5: Close the Connection
In the end, We need to save the changes using commit() method and finally close the opened connection using close() method.
Syntax
conn.commit()
cur.close()
Complete Python Script Example
import psycopg2
conn = None
try:
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(
host = 'localhost',
dbname = 'For_Practice',
user = 'postgres',
password = '321654',
port = 5432
)
# Creating a cursor with name cur.
cur = conn.cursor()
print('Connected to the PostgreSQL database')
# Execute a query:
# To display the PostgreSQL
# database server version
cur.execute('SELECT version()')
print(cur.fetchone())
# Close the connection
cur.close()
except(Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
Output
Python Script ExampleConclusion
In conclusion, the psycopg2 library in Python is an essential tool for establishing a seamless Python PostgreSQL database connection and running SQL queries directly from our Python code. By mastering these techniques, we can efficiently manage and manipulate data in PostgreSQL databases, allowing for better integration of database operations into our Python applications. Whether for development or data analysis, this powerful combination enhances our productivity and workflow.
Similar Reads
PostgreSQL - Connect To PostgreSQL Database Server in Python The psycopg database adapter is used to connect with PostgreSQL database server through python. Installing psycopg: First, use the following command line from the terminal: pip install psycopg If you have downloaded the source package into your computer, you can use the setup.py as follows: python s
4 min read
PostgreSQL - Connecting to the Database using Python PostgreSQL in Python offers a robust solution for developers looking to interact with databases seamlessly. With the psycopg2 tutorial, we can easily connect Python to PostgreSQL, enabling us to perform various database operations efficiently. In this article, we will walk you through the essential
4 min read
How to Connect Python with SQL Database? In this article, we will learn how to connect SQL with Python using the MySQL Connector Python module. Below diagram illustrates how a connection request is sent to MySQL connector Python, how it gets accepted from the database and how the cursor is executed with result data.SQL connection with Pyth
2 min read
How to connect to SQLite database that resides in the memory using Python ? In this article, we will learn how to Connect an SQLite database connection to a database that resides in the memory using Python. But first let brief about what is sqlite. SQLite is a lightweight database software library that provides a relational database management system. Generally, it is a ser
3 min read
How to Convert SQL Query Results to Pandas Dataframe Using pypyodbc? In this article, we are going to see how to convert SQL Query results to a Pandas Dataframe using pypyodbc module in Python. We may need database results from the table using different queries to work on the data and apply any machine learning on the data to analyze the things and the suggestions be
2 min read
Sending data from a Flask app to PostgreSQL Database A database is used to store and maintain persistent data that can be retrieved and manipulated efficiently. we usually need a database, an organized collection of data for example in an e-commerce web app where we need to store the data about the users, orders, carts, shipping, etc. In a database, d
6 min read
PostgreSQL - Connect and Access a Database In this article, we will learn about how to access the PostgreSQL database. Once the database is created in PostgreSQL, we can access it in two ways using: psql: PostgreSQL interactive terminal program, which allows us to interactively enter, edit, and execute SQL commands.pgAdmin: A graphical front
3 min read
How to Log Queries in PostgreSQL using Python? Python has various database drivers for PostgreSQL. Current most used version is psycopg2. It fully implements the Python DB-API 2.0 specification. The psycopg2 provides many useful features such as client-side and server-side cursors, asynchronous notification and communication, COPY command suppor
4 min read
Python SQLite - Connecting to Database In this article, we'll discuss how to connect to an SQLite Database using the sqlite3 module in Python. Connecting to the Database Connecting to the SQLite Database can be established using the connect() method, passing the name of the database to be accessed as a parameter. If that database does no
2 min read
How to List Databases and Tables in PostgreSQL using PSQL PostgreSQL is a powerful, open-source object-relational database system. It provides a wide array of tools and features to manage databases, tables, and other database objects. In this article, we will explain how to list databases and tables in PostgreSQL using the psql command-line interface. We w
3 min read