Quick Links
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.
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 likeWindows,macOS, andUbuntu 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’re able to use thepsycopg2library to connect to a PostgreSQL database from Python. Run this command in the Python interpreter to check if the library is installed:
If you get an error message (for example, “No module named ‘psycopg2’"), install the library with this command:
PIP is a Python package manager that you caninstall 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 candownload pgAdmin4and install it separately if necessary.
Here’s how you can use pgAdmin4 to obtain your server credentials:
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 theconnectfunction as follows:
Next, you must use the cursor function to execute Postgres commands in a Python environment:
Finally, you may 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.
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 theCREATE DATABASESQLcommand which you may pass to the cursor object’sexecutemethod:
You should always take care tohandle any exceptionsthat 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 calledmusicusing the above query, it should show up in the list of databases below thePostgres16 > Databasescategory.
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:
Instead of the create command, you need to use the drop command. Post execution, you won’t see the database in question.
