Introduction
Python is a highly level programming language that allows you to build general purpose applications. By integrating Python with a Vultr Managed Database for PostgreSQL, you can automate most application processes for fast scaling and high level development of new features to match your user needs.
Depending on your Python application structure, integrating a managed PostgreSQL database best suites the following types of applications:
- E-commerce web applications.
- A data warehouse for mobile apps and desktop applications.
- Geospatial, analytics, and vector applications.
This guide explains how to use Vultr Managed Database for PostgreSQL in Python applications. By leveraging the database's high availability, you're to create a console application that makes database queries to retrieve stored information.
Prerequisites
Before you begin, be sure to:
- Deploy a Vultr Managed Database for PostgreSQL
- Deploy a Vultr Cloud Server Distribution of your choice
This guide uses a Linux server, but depending on your Python application backend, you can apply the instructions on any distribution including Windows Servers.
- Using SSH, connect to the server console
- Create a non-root sudo user account and switch to the new account
Install the Python PostgreSQL Adapter
To integrate Python with a Vultr Managed Database for PostgreSQL, install the required psycopg adapter package that allows you to connect to the database. Depending on your Linux distribution, install the PostgreSQL adapter as described in the steps below.
1.Update the server:
On Ubuntu/Debian:
$ sudo apt update
On CentOS, and RHEL distributions:
$ sudo dnf update
2.Install the PostgreSQL client tool
$ sudo apt-get install -y postgresql-client
OR
$ sudo dnf install postgresql
3.Install the Python Pip Package Manager
$ sudo apt install -y python3-pip
OR
$ sudo dnf install python3-pip
4.Using pip, install the PostgreSQL psycopg adapter package
$ pip install psycopg
Set Up the DatabaseTo connect Python to your Vultr Managed Database for PostgreSQL, create a sample database. Within the database, create a table and add sample records you can retrieve using the Python application as described below.
This sample Python application requires a sample PostgreSQL database with one table. This table stores customers' names and their unique customer_ids. Follow the steps below to set up the database:
1.Using the PostgreSQL client tool psql, log in to the Vultr Managed Database for PostgreSQL cluster
$ psql -p 16751 -h vultr-prod-aaa.com -d defaultdb -U vultradmin
Or, copy and use the connection string in your Vultr Managed Database dashboard
postgres://vultradmin:example-password@vultr-prod-aaa.com:16751/defaultdb
Replace the above psql connection details with your actual database values as below:
- Username: vultradmin
- Password: example-password
- Host: vultr-prod-aaa.com
- Port: 16751
When prompted enter the correct Vultr Managed Database for PostgreSQL database to connect to your database.
2.Create a new xyz_company database
defaultdb=> CREATE DATABASE xyz_company;
3.Switch to the new xyz_company database
defaultdb=> \c xyz_company;
4.Create a new sample customers table.
xyz_company=> CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
first_name VARCHAR (50),
last_name VARCHAR (50)
);
5.Add sample data to the customers table
xyz_company=> INSERT INTO customers (first_name, last_name) VALUES ('MARY', 'ROE');
INSERT INTO customers (first_name, last_name) VALUES ('PETER', 'SMITH');
INSERT INTO customers (first_name, last_name) VALUES ('JOHN', 'ALEX');6.View the customers table data to verify that all records are available
xyz_company=> SELECT
customer_id ,
first_name,
last_name
FROM customers;
Output:
customer_id | first_name | last_name
-------------+------------+-----------
1 | MARY | ROE
2 | PETER | SMITH
3 | JOHN | ALEX
(3 rows)
7.Exit the PostgreSQL database console
xyz_company=> \q
Create a PostgreSQL Database ModuleIn Python, a central database connection module allows you to reuse code in different application files without applying the logic in each file. In this section, create a Python connection file that works as a central module for PostgreSQL connections.
1.Create a new project directory
$ mkdir project
2.Switch to the new directory
$ cd project
3.Using a text editor such as Nano, create a new Python file postgresql_connector.py
$ nano postgresql_connector.py
4.Add the following code to the file. Replace the psycopg.connect values with your actual Vultr Managed Database for PostgreSQL values
import psycopg
class PostgresqlConnector:
def __init__(self):
self.db_conn = psycopg.connect(
host = "vultr-prod-aaa.com",
port = "16751",
user = "vultradmin",
password = "example-password",
dbname = "xyz_company"
)
def insert_customer(self, query_string, json_data):
db_cur = self.db_conn.cursor()
db_cur.execute(query_string, [json_data['first_name'], json_data['last_name']])
self.db_conn.commit()
return "Success"
def get_customers(self, query_string):
db_cur = self.db_conn.cursor()
db_cur.execute(query_string)
return db_cur.fetchall()
def update_customer(self, query_string, json_data):
db_cur = self.db_conn.cursor()
db_cur.execute(query_string, [json_data['first_name'], json_data['last_name'], json_data['customer_id']])
self.db_conn.commit()
return "Success"
def delete_customer(self, query_string, json_data):
db_cur = self.db_conn.cursor()
db_cur.execute(query_string, [json_data['customer_id']])
self.db_conn.commit()
return "Success"
Save and close the file.
In the above application file:
- PostgresqlConnector() is a class module with five methods:
- __init__() executes every time you create an instance of the PostgresqlConnector class to establish a connection to the PostgreSQL database using the self.db_conn = psycopg.connect(...) function
- insert_customer(self, query_string, json_data) takes a JSON payload and populates the customers table using the INSERT SQL statement
- get_customers(self, query_string) retrieve all customers from the database
- update_customer(self, query_string, json_data) updates a customer that matches a given customer_id value
- delete_customer(self, query_string, json_data) deletes a customer from the database table
Create the Main Python Application
1.Create a new main.py file
$ nano main.py
2.Add the following code to the file
import http.server
from http import HTTPStatus
import socketserver
import json
import postgresql_connector
class HttpServerHandler(http.server.SimpleHTTPRequestHandler):
def set_headers(self):
self.send_response(HTTPStatus.OK)
self.send_header('Content-type', 'application/json')
self.end_headers()
def do_POST(self):
self.set_headers()
json_data = json.loads(self.rfile.read(int(self.headers['Content-Length'])))
pg_conn = postgresql_connector.PostgresqlConnector()
query_string = "insert into customers (first_name, last_name) values (%s, %s)"
resp = {"data": pg_conn.insert_customer(query_string, json_data)}
self.wfile.write(bytes(json.dumps(resp, indent = 2) + "\r\n", "utf8"))
def do_GET(self):
self.set_headers()
pg_conn = postgresql_connector.PostgresqlConnector()
query_string = 'select * from customers'
resp = {"data": pg_conn.get_customers(query_string)}
self.wfile.write(bytes(json.dumps(resp, indent = 2) + "\r\n", "utf8"))
def do_PUT(self):
self.set_headers()
json_data = json.loads(self.rfile.read(int(self.headers['Content-Length'])))
pg_conn = postgresql_connector.PostgresqlConnector()
query_string = 'update customers set first_name = %s, last_name = %s where customer_id = %s'
resp = {"data": pg_conn.update_customer(query_string, json_data)}
self.wfile.write(bytes(json.dumps(resp, indent = 2) + "\r\n", "utf8"))
def do_DELETE(self):
self.set_headers()
json_data = json.loads(self.rfile.read(int(self.headers['Content-Length'])))
pg_conn = postgresql_connector.PostgresqlConnector()
query_string = 'delete from customers where customer_id = %s'
resp = {"data": pg_conn.delete_customer(query_string, json_data)}
self.wfile.write(bytes(json.dumps(resp, indent = 2) + "\r\n", "utf8"))
httpd = socketserver.TCPServer(('', 8080), HttpServerHandler)
print("HTTP server started at port 8080...")
try:
httpd.serve_forever()
except KeyboardInterrupt:
httpd.server_close()
print("HTTP server stopped.")Save and close the file.
In the above main.py file:
- The import section adds the HTTP server and postgresql_connector module you created earlier to the application functions
- HttpServerHandler(http.server.SimpleHTTPRequestHandler) is a web server handler class that listens for incoming HTTP connections on your defined port 8080 with the following methods:
- set_headers(self) sets the correct HTTP headers when the Python application responds to HTTP clients
- do_POST(self) handles all HTTP POST requests to redirect an INSERT query to the PostgresqlConnector() class
- do_GET(self) runs an HTTP GET method to retrieve data from the customers table using the PostgresqlConnector() class. This method runs the select * from customers SQL statement
- do_PUT(self) executes an HTTP PUT method to update customer details in the database table using the update customers set first_name = %s, last_name = %s where customer_id = %s SQL statement
- do_DELETE(self) runs a function that deletes a customer from the database using the delete from customers where customer_id = %s SQL statement
Test the Python Application
You have developed a Python application with the necessary modules and functions. To verify that your application reads and writes to your Vultr Managed Database for PostgreSQl, run the following operations.
1.Run the application in the background
$ python3 main.py &
Output:
HTTP server started at port 8080...
2.Using the curl utility tool, create a new customer using the following HTTP POST method
$ curl -X POST http://localhost:8080/ -H 'Content-Type: application/json' -d '{"first_name": "ANNE", "last_name": "HENRY"}'
Output:
{
"data": "Success"
}3.Using the HTTP GET method, retrieve all customers in the database
$ curl -X GET http://localhost:8080/
Output:
{
"data": [
[
1,
"MARY",
"ROE"
],
[
2,
"PETER",
"SMITH"
],
[
3,
"JOHN",
"ALEX"
],
[
4,
"ANNE",
"HENRY"
]
]
}4.Using the PUT method, update a customer's details
$ curl -X PUT http://localhost:8080/ -H 'Content-Type: application/json' -d '{"first_name": "MARY ANNE", "last_name": "SMITH", "customer_id": 1}'
Output.
{
"data": "Success"
}
5.Delete a customer from the database
$ curl -X DELETE http://localhost:8080/ -H 'Content-Type: application/json' -d '{"customer_id": 4}'
Output:
{
"data": "Success"
}6.To stop the Python application background process, view the running jobs
$ jobs
Output:
[1]+ Running python3 main.py &
Keep note of the background process ID
7.Stop the process by ID. For example, for job ID 1, run:
$ kill %1
Conclusion
You have created a Python application that connects to a Vultr Managed Database for PostgreSQL to perform read and write tasks. Depending on your Python application structure, create the necessary databases, and implement the correct connector with your desired SQL statements to interact with your PostgreSQL database.