How to Connect and Use PostgreSQL in Python

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

Download and Install PostgreSQL

PostgreSQL is an excellent choice for your programming projects. You can download and install the required version of PostgreSQL according to your OS. Postgres is available for download on standard operating systems like Windows, macOS, and Ubuntu Linux.

The installation process will differ from OS to OS, so you should follow the installation steps to ensure a smooth setup experience.

Install the Required Library

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 get an error message (for example, "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 eases the complexity of installing Python packages.

Fetch Credentials Using pgAdmin4

You can use the pgAdmin4 app to manage your Postgres databases 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 necessary.

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

1. From the application menu, open pgAdmin4.
2. Click on the Servers menu on the left side of your application screen.
3. Enter the Postgres password you entered during setup.

   [画像:Password prompt to connect to Postgres server]

4. Once you connect to the server, right-click on the PostgreSQL 16 item, then choose Properties.

   [画像:Postgres server drop down menu showing properties option]

5. Within the Properties dialog box, click on Connection.
6. Make a note of the Host name, Port number, and Username.

   [画像:Connection details window within Postgres server]

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 a 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 every line of code.

conn.set_session(autocommit = True)

You can run these statements in one go to connect to a local instance of your Postgres server.

How to Create a Postgres Database

A Postgres database plays a pivotal role in storing a collection of related tables. Create a new one with the CREATE DATABASE SQLcommand which 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 should always take care to handle any exceptions that may arise. This example simply prints any error that occurs but, in production code, you’ll want to take appropriate action.

Checking the Database in pgAdmin4

When you run the above query, you can check that it successfully created a database using pgAdmin4. Head over to the interface, refresh the existing list of databases, and look for the new database.

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

How to Drop a Postgres Database

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

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

Instead of the create command, you need to use the drop command. Post execution, you won't see the database in question.