Learn how to connect to a PostgreSQL database instance with one of the world's most famous programming languages: Python.
Managed Databases (also called Cloud Databases) allow you to focus on building and deploying cloud applications while OVHcloud takes care of the database infrastructure and maintenance in operational conditions.
You can find an example in the GitHub examples repository.
Requirements
- access to the OVHcloud Control Panel
- a Public Cloud project in your OVHcloud account
- a PostgreSQL database running on your OVHcloud Public Cloud Databases (this guide can help you to meet this requirement)
- configure your PostgreSQL instance to accept incoming connections
- a Python environment with a stable version and public network connectivity (Internet). This guide was made using Python 3.9.7
Concept
A PostgreSQL instance can be managed in multiple ways. One of the easiest, yet powerful, is to use a Command Line Interface (CLI), as shown in our guide: Connect to PostgreSQL with CLI.
Another way is to interact directly using a programming language, such as Python. Python is one of the major programming languages in the world, especially in the Data ecosystem.
To do this, we will need to set up our Python environment with PostgreSQL drivers, configure our Public Cloud Databases for PostgreSQL instances to accept incoming connections, and code in Python to perform a few example actions.
Instructions
Set up your Python environment
To interact with your PostgreSQL instance using Python, your development environment needs to be configured with:
- A compatible version of Python
- Psycopg2
Please follow the official Psycopg - PostgreSQL database adapter for Python to get the latest information.
Once your Python environment is set up and you begin executing a python --version in your command line interface (CLI), you should see information about the version as shown below :
laptop$ python3 --version Python 3.9.7
In the same console, by typing a pip list check if psycopg2 is correctly installed :
laptop$ pip list Package Version ---------------------- ------- cryptography 3.3.2 mysql-connector-python 8.0.27 pip 20.3.4 protobuf 3.19.3 psycopg2 2.8.6 pyOpenSSL 20.0.1 setuptools 52.0.0 six 1.16.0 wheel 0.34.2 (...)
We are now ready to learn how to connect to our PostgreSQL instance!
Connect with Python
Using psycopg2
Psycopg is the most popular PostgreSQL database adapter for the Python programming language.
import psycopg2
user = 'avnadmin'
password = 'Nl2xxxxxxxxxxxxxxxYvC'
hostname = 'postgresql-6xxxxxxx-ocxxxxxc5.database.cloud.ovh.us'
port = '20235'
connection = psycopg2.connect(f"postgres://{user}:{password}@{hostname}:{port}/defaultdb?sslmode=require")
cursor = connection.cursor()
cursor.execute("select * from pg_stat_ssl where ssl = True")
stat_ssl = cursor.fetchone()
print(stat_ssl)
After executing your Python code, the result shown in the CLI should be like this:
(2604173, True, 'TLSv1.3', 'TLS_AES_256_GCM_SHA384', 256, None, None, None)
Go further
For more information and tutorials, please see our other Cloud Databases support guides or explore the guides for other OVHcloud products and services.