PostgreSQL and Python
Installing the driver
Psycopg is a PostgreSQL driver for Python. It is a wrapper for the libpq, the official PostgreSQL client library. It can be installed as a stand-alone package through pip:
pip install psycopg2
SSL and Authentication
Compose PostgreSQL deployments are SSL enabled and use a self-signed certificate. If you want to make validate your connection to the server, you will need to:
- import the ssl package into your application
- download the certificate from the SSL Certificate (Self-Signed) section of your Deployment Overview and save it locally
- Supply the SSL options in your connection setup, including the path to the CA certificate in your application.
sslmode = 'require', sslrootcert = '/path/to/postgresql.crt'
Username and password credentials can either be the 'admin' user provided with your deployment, or a specific user you have setup on the database you wish to connect to. Details on managing users can be found on the User Management for PostgreSQL page. This example uses a previously created user 'example_application' on the database 'example_database'
user = 'example_application', password = 'password', database = 'example_database'
You supply all the connection information to the
psycopg2.connect command. Specifically, the information from the Connection Strings provided with your deployment, host and port.
Once the connection is opened, the
cursor allows Python to execute SQL commands. The sample command here inserts a first name and last name into a 'names' table. Once the cursor has executed the command,
connection.commit() writes the changes to the database.
import psycopg2 import ssl connection = psycopg2.connect( host = 'aws-us-east-1-portal.9.dblayer.com', port = 16436, user = 'example_application', password = 'password', sslmode = 'require', sslrootcert = '/Users/path_to/.certs/postgresql.crt', database = 'example_database') cursor = connection.cursor() cursor.execute("""INSERT INTO names (first_name, last_name) VALUES (%s, %s)""",('Mary', 'Malone')) connection.commit()
Still Need Help?
If this article didn't solve things, summon a human and get some help!
Updated over 3 years ago