How to connect and use PostgreSQL in Python

If you are writing a Python program that needs to access data from a Postgres database, you will need to know how to connect the two. Once you establish a connection, you can use it to run queries and fetch or save data.

Download and install PostgreSQL

PostgreSQL is a great choice for your programming projects. You can download and install the required version of PostgreSQL depending on your operating system. Postgres is available for download on standard operating systems such as Windows, macOS, and Linux Ubuntu.

The installation process will vary between operating systems, so you should follow the installation steps to ensure a smooth setup experience.

Install required libraries

You can use the psycopg2 library to connect to a PostgreSQL database from Python. Run this command in the Python interpreter to check if the library is installed:

import psycopg2

If you receive an error message (e.g. "No module named 'psycopg2'"), install the library with this command:

pip install psycopg2

PIP is a Python package manager that you can install on Windows, Mac, or Linux. It helps reduce the complexity of installing Python packages.

Fetch credentials using pgAdmin4

You can use the pgAdmin4 application to manage your Postgres database in a GUI environment. You may have installed it at the same time as you installed Postgres, but you can download pgAdmin4 and install it separately if needed.

Here's how you can use pgAdmin4 to get your server credentials:

1. From the applications menu, open pgAdmin4.

 

2. Click the Servers menu on the left side of the application screen.

3. Enter the Postgres password you entered during setup.

How to connect and use PostgreSQL in Python Picture 1

4. After you connect to the server, right-click the PostgreSQL 16 entry, then select Properties.

How to connect and use PostgreSQL in Python Picture 2

5. In the Properties dialog box, click Connection.

6. Record Host name, Port number and Username.

How to connect and use PostgreSQL in Python Picture 3

Connect to the Postgres server

With your credentials in hand, you can use the psycopg2 library to establish a connection to your Postgres server. To do so, you need to use the connect function as follows:

conn = psycopg2.connect(host='localhost', port= '5432', user='postgres', password='your password here')

Next, you must use the cursor function to execute Postgres commands in the Python environment:

cur = conn.cursor()

Finally, you can set the auto-commit flag to ensure Python executes and commits each code statement. This way, you don't need to pass separate commit statements after each line of code.

conn.set_session(autocommit = True)

You can run these commands at the same time to connect to a local instance of the Postgres server.

How to create a Postgres database

The Postgres database plays a key role in storing a collection of related tables. Create a new command using the CREATE DATABASE SQL command that you can pass to the cursor object's execute method:

try: cur.execute('''CREATE DATABASE DB_NAME''') except psycopg2.Error as e: print(e)

You must always be careful to handle any exceptions that may arise. This example simply prints any errors that occur but in production code you will want to take appropriate action.

 

How to connect and use PostgreSQL in Python Picture 4

Check the database in pgAdmin4

When you run the above query, you can check if the database using pgAdmin4 has been created successfully. Go to the application interface, refresh the list of existing databases and find the new database.

For example, if you create a sample database named music using the above query, it will show up in the list of databases under the Postgres16 > Databases category.

How to connect and use PostgreSQL in Python Picture 5

How to delete Postgres database

If you don't want to keep a specific database, you can use the following command to delete it:

try: cur.execute('''DROP DATABASE MUSIC''') except psycopg2.Error as e: print(e)

How to connect and use PostgreSQL in Python Picture 6

Instead of the create command, you need to use the drop command. Once executed, you will not see the database in question.

3.5 ★ | 2 Vote

May be interested

  • Module time in PythonModule time in Python
    python has a time module used to handle time-related tasks. tipsmake.com will work with you to find out the details and functions related to the time specified in this module. let's follow it!
  • Python data type: string, number, list, tuple, set and dictionaryPython data type: string, number, list, tuple, set and dictionary
    in this section, you'll learn how to use python as a computer, grasp python's data types and take the first step towards python programming.
  • How to install Python on Windows, macOS, LinuxHow to install Python on Windows, macOS, Linux
    to get started with python, you first need to install python on the computer you are using, be it windows, macos or linux. below is a guide to installing python on your computer, specific to each operating system.
  • How to set up Python to program on WSLHow to set up Python to program on WSL
    get started with cross-platform python programming by setting up python on the windows subsystem for linux. here's how to set up python for wsl programming.
  • Multiple choice quiz about Python - Part 4Multiple choice quiz about Python - Part 4
    continue python's thematic quiz, part 4 goes back to the topic core data type - standard data types in python. let's try with quantrimang to try the 10 questions below.
  • How to use Closure in PythonHow to use Closure in Python
    in this article, tipsmake.com will work with you to learn about closure in python, how to define a closure and why you should use it. let's go find the answer!
  • Functions in PythonFunctions in Python
    what is python function? how is the syntax, components, and function types in python? how to create functions in python? these questions will be answered in the python lesson below.
  • How to use GPT-3 with PythonHow to use GPT-3 with Python
    feel free to use cool gpt-3 technology with your own python scripts using openai's handy api. here are details on how to use gpt-3 with python.
  • Multiple choice quiz about Python - Part 7Multiple choice quiz about Python - Part 7
    following the previous test set, part 7 continues with the topic built-in functions in python. let's try with quantrimang to try the 10 questions below.
  • The next () function in PythonThe next () function in Python
    the next () function in python returns the next element in the iterator. you can add a default value to return if iterable is already the last element.