close
The Wayback Machine - https://web.archive.org/web/20240930152745/https://www.geeksforgeeks.org/postgresql-connecting-to-the-database-using-python/
Open In App

PostgreSQL – Connecting to the database using Python

Last Updated : 30 Aug, 2020
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

PostgreSQL API for Python allows user to interact with the PostgreSQL database using the psycopg2 module. In this article we will look into the process of connecting to a PostgreSQL database using Python.

Prerequisites:

First we will need to install the psycopg2 module with the below command in the command prompt:

pip install psycopg2

 

Creating a Database:

For the purpose of example we will be needing a sample database. To create so, follow the below steps:

  • First open a PostgreSQL client tool like pgadmin4 or psql.
  • Second login to the database using your credentials.
  • Finally use the below command to create a database (say, School)
CREATE DATABASE school;

Connecting to the database:

To connect to the above created database (ie, school), we use the connect () function. The connect() function is used to create a new database session and it returns a new connection class instance.

To do so use the below syntax:

Syntax: conn = psycopg2.connect("dbname=suppliers user=postgres password=postgres")

To make it convenient to use you can use the below method:

1. First create a database.ini file with the credentials as shown below:

[postgresql]
host=localhost
database=school
user=postgres
password=5555

Now, the following config() function reads the database.ini file and returns connection parameters. The same config() function is added in the config.py file:

Python3




#!/usr/bin/python
from configparser import ConfigParser
  
  
def config(filename='database.ini', section='postgresql'):
    # create a parser
    parser = ConfigParser()
    # read config file
    parser.read(filename)
  
    # get section, default to postgresql
    db = {}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            db[param[0]] = param[1]
    else:
        raise Exception('Section {0} not found in the {1} file'.format(section, filename))
  
    return db


Example:

The following connect() function connects to the school database that we created earlier and returns  the PostgreSQL database version.

Python3




#!/usr/bin/python
import psycopg2
from config import config
  
def connect():
    """ Connect to the PostgreSQL database server """
    conn = None
    try:
        # read connection parameters
        params = config()
  
        # connect to the PostgreSQL server
        print('Connecting to the PostgreSQL database...')
        conn = psycopg2.connect(**params)
          
        # create a cursor
        cur = conn.cursor()
          
    # execute a statement
        print('PostgreSQL database version:')
        cur.execute('SELECT version()')
  
        # display the PostgreSQL database server version
        db_version = cur.fetchone()
        print(db_version)
         
    # close the communication with the PostgreSQL
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
            print('Database connection closed.')
  
  
if __name__ == '__main__':
    connect()


Output:

BERJAYA



Similar Reads

Connecting PostgreSQL with SQLAlchemy in Python
In this article, we will discuss how to connect PostgreSQL with SQLAlchemy in Python. In order to connect with any Database management system, it is essential to create an engine object, that serves as a central source of connection by providing a connection pool that manages the database connections. This SQLAlchemy engine is a global object which
3 min read
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 setup.py build sudo python setup.py installCreate a
4 min read
Connecting to SQL Database using SQLAlchemy in Python
In this article, we will see how to connect to an SQL database using SQLAlchemy in Python. To connect to a SQL database using SQLAlchemy we will require the sqlalchemy library installed in our python environment. It can be installed using pip - !pip install sqlalchemyThe create_engine() method of sqlalchemy library takes in the connection URL and r
3 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 not exist, then it'll be created. sqliteConnection =
2 min read
Connecting Pandas to a Database with SQLAlchemy
In this article, we will discuss how to connect pandas to a database and perform database operations using SQLAlchemy. The first step is to establish a connection with your existing database, using the create_engine() function of SQLAlchemy. Syntax: from sqlalchemy import create_engine engine = create_engine(dialect+driver://username:password@host:
3 min read
Storing a BLOB in a PostgreSQL Database using Python
This article focuses on, Storing BLOB in a PostgreSQL database. BLOB is a Binary large object (BLOB) is a data type that can store any binary data.To Store Blob data in a Postgres database Table, we will use psycopg2.The table for storing BLOB data in PostgreSQL is called a Large Object table and the data type is byte.We can store png, jpg, gif, pd
3 min read
PostgreSQL - Export PostgreSQL Table to CSV file
In this article we will discuss the process of exporting a PostgreSQL Table to a CSV file. Here we will see how to export on the server and also on the client machine. For Server-Side Export: Use the below syntax to copy a PostgreSQL table from the server itself: Syntax: COPY Table_Name TO 'Path/filename.csv' CSV HEADER; Note: If you have permissio
2 min read
PostgreSQL - Installing PostgreSQL Without Admin Rights on Windows
If you are a part of a corporation, it is highly unlikely that you have the admin privileges to install any external software. But the curious souls that all software developers are, in this article, we will see the detailed process of installation of PostgreSQL without having administrator rights on our Windows machine. Installation: Follow the be
3 min read
Count total number of changes made after connecting SQLite to Python
In this article, we are going to see how to count total changes since the SQLite database connection is open using Python. To get the total number of changes we use the connection object's total_changes property. Class Instance: sqlite3.Connection Syntax: <connection_object>.total_changes Return Value: Total no. of rows inserted, deleted, upd
3 min read
Making a Flask app using a PostgreSQL database
The Postgres database can be accessed via one of two methods in Python. Installing PgAdmin4 is the first step because it offers a user interface for interacting with databases and another for using the psycopg2 connector. In this post, we'll concentrate on a different approach that lets us alter the Postgres database: using the psycopg2 connector.
4 min read
How to Export PostgreSQL Database Without Data Using SQL?
When we are working with the PostgreSQL database, there are multiple times we need to export the database structure. This approach is useful when we create a skeleton database or migrate the schema changes for different environments or systems. In this article, we will explore the process of exporting a database without data using SQL with the help
3 min read
How to Migrate a MySQL Database to PostgreSQL using pgloader?
Database migration is a common task in software development when switching between different database management systems (DBMS). In this article, we'll explore how to migrate a MySQL database to PostgreSQL using a powerful tool called pgloader. We'll cover the concepts involved, and the steps required, and provide detailed examples with outputs to
6 min read
Python | Database management in PostgreSQL
PostgreSQL is an open source object-relational database management system. It is well known for its reliability, robustness, and performance. PostgreSQL has a variety of libraries of API (Application programmable interface) that are available for a variety of popular programming languages such as Python. It provides a lot of features for Database m
6 min read
Python PostgreSQL - Create Database
In this article, we will discuss how to create database in PostgreSQL using pysopg2 in Python. CREATE DATABASE is one of the Data Definition Language ( DDL ) statements supported by the PostgreSQL Database Management System. It is used to create database in PostgreSQL. Database name should be always unique. If it already exists then it shows that t
1 min read
Insert Python list into PostgreSQL database
In this article, we will discuss how to Insert a Python list into PostgreSQL database using pyscopg2 module. Psycopg2 is the most popular PostgreSQL adapter for the Python programming language. Psycopg2 is a DB API 2.0 compliant PostgreSQL driver that is actively developed. It is designed for multi-threaded applications and manages its own connecti
2 min read
How To Connect and run SQL queries to a PostgreSQL database from Python
This article focus on connecting to a PostgreSQL database from Python. Installation: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 psycopg2pip3 install psycopg2Creating a Database You can create a Database in 2
2 min read
PyQt5 QSpinBox - Connecting two spin boxes with each other
In this article we will see how we can connect two spin boxes with each other such that every time value change in the one spin box should reflect in other spin box as well for example when we have fixed the width to height ratio of image and allow user to set width and height using spin box, as ratio is fixed therefore change in any dimension shou
2 min read
PostgreSQL - Loading a Database
In this article we will look into the process of loading a PostgreSQL database into the PostgreSQL database server. Before moving forward we just need to make sure of two things: PostgreSQL database server is installed on your system. A sample database. For the purpose of this article, we will be using a sample database which is DVD rental database
3 min read
PostgreSQL - Rename Database
In PostgreSQL, the ALTER DATABASE RENAME TO statement is used to rename a database. The below steps need to be followed while renaming a database: Disconnect from the database that you want to rename by connecting to a different database.Terminate all connections, connected to the database to be renamed.Now you can use the ALTER DATABASE statement
1 min read
How to use PostgreSQL Database in Django?
This article revolves around how can you change your default Django SQLite-server to PostgreSQL. PostgreSQL and SQLite are the most widely used RDBMS relational database management systems. They are both open-source and free. There are some major differences that you should be consider when you are choosing a database for your applications. Also ch
2 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 frontend web-based tool, suite with ODBC or JDBC suppor
3 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, data is stored in such a way that it makes data tra
6 min read
How to Implement PostgreSQL Database in Rails Application?
In this article, we are going to look into the implementation of PostgreSQL in a rails application. As we know that database is a very important part of any web application that's why today modern web applications like Flipkart, Amazon, Netflix all the websites are use database. Before going forward we need to know a little about what is the databa
2 min read
How to Migrate a PostgreSQL Database to MySQL
Moving a database from one platform to another can be tough, but with careful planning and execution, it can be done smoothly. In this article, we'll go over how to migrate a PostgreSQL database to MySQL, which are both popular RDBMS. We'll cover preparation, schema conversion, data migration, and testing, using detailed examples and outputs to hel
5 min read
How to Dump and Restore PostgreSQL Database?
PostgreSQL remains among the most efficient and widely applied open-source relational database management systems. It provides the superior function of saving, configuring, and extracting information most effectively. In the process of migrating data, creating backups, or transferring databases between environments the necessity of knowledge on how
5 min read
PostgreSQL - Copy Database
Copying databases can be essential for testing, backup, or migration purposes. PostgreSQL provides several methods to accomplish this efficiently. Here, we will discuss the process of copying a PostgreSQL database on the same server or from one server to another. Copying a PostgreSQL Database Within the Same ServerSometimes, you may need to create
3 min read
PostgreSQL - Create Database
PostgreSQL provides multiple ways to create a database, catering to different user preferences and requirements. This article will discuss various methods to create a database in PostgreSQL, including using the psql shell and pgAdmin. Creating a Database Using psql ShellTo create a database through the psql shell, you can use the CREATE DATABASE st
3 min read
PostgreSQL ALTER DATABASE
The PostgreSQL ALTER DATABASE statement is a powerful tool used for modifying an existing database.  The features of a database, once created can be changed using the ALTER DATABASE statement.  Let's explore the syntax of PostgreSQL ALTER DATABASE, the various actions that can be performed, and practical examples for efficiently managing your Postg
3 min read
PostgreSQL - Restore Database
Restoring a PostgreSQL database involves copying backed-up data and restoring it to its original or a new location. This process is crucial for returning data to its pre-loss, pre-corruption state, ensuring data integrity and availability. Common reasons for database restoration include human errors, malicious attacks, power outages, and natural di
4 min read
PostgreSQL - Size of a Database
In PostgreSQL, managing storage and understanding database size is critical for performance monitoring and resource management. PostgreSQL offers built-in functions that allow you to evaluate the size of databases, helping you manage storage more effectively. One of the most commonly used functions for this purpose is the 'pg_database_size()' funct
2 min read