Setting Up WooCommerce PostgreSQL Integration: 3 Easy Steps

Many businesses are coming online using E-Commerce websites. There are many platforms available that offer no code setup for non-technical people to quickly set up their E-Commerce website or online store. WooCommerce is one of the widely used WordPress plugins to let you convert your site into an online store.

E-Commerce websites contain lots of data related to products, order details, user information, their behavior, etc. It is essential to store all this data in a database such as PostgreSQL. Connecting WooCommerce PostgreSQL allows users to store their E-Commerce data in a safe place and further use it for Analytics and to optimize their site.

In this article, You will see how to connect WooCommerce PostgreSQL. The API route will be used for fetching data from WooCommerce using its REST API and posting it to a PostgreSQL table. The function performing this operation for WooCommerce PostgreSQL Integration can be sitting on a cloud server or your local machine. You can even use serverless AWS Lambda or Azure Functions integrating WooCommerce PostgreSQL.

Prerequisites

For connecting WooCommerce PostgreSQL, You need to have a WordPress site with WooCommerce installed. You also need a PostgreSQL Database to which you have to write access. Both the WordPress site and the PostgreSQL can be local or hosted on a cloud platform.

You will also need proficiency in one of the server-side scripting languages (Nodejs, Python, Go, etc.) to develop your script for WooCommerce PostgreSQL Integration and fetch data from WooCommerce and add it to PostgreSQL.

What is WooCommerce?

WooCommerce is the e-commerce plugin for WordPress, and given the popularity of WordPress, WooCommerce is quite popular as well. It is open-source and designed for small to mid-sized merchants who run their websites using WordPress. Being easy to set up and customize has led to the widespread adoption of this plugin.

WooCommerce allows you to add various functionalities to your store by installing various built-in extensions. WooCommerce offers flexible shipping options, payment gateways, automated tax calculation, Checkout, Product Analytics, and many other features that help you manage your E-commerce site efficiently.

Key Features of WooCommerce

Some of the main features of WooCommerce are listed below:

• Dynamic Price: WooCommerce automatically changes the total price for the product based on regional taxes and shipping charges.
• Integrations: WooCommerce provides easy integration with most advertising platforms and Data Analytics platforms to help you run Marketing Campaigns effectively.
• SEO Friendly: To build a successful online business, one needs good hands-on SEO. WooCommerce works on WordPress which adds more value to your website by default.

To know more about WooCommerce, click here.

What is PostgreSQL?

PostgreSQL is one of the most popular and robust databases. It is a relational database, i.e., the data points stored in different tables can be related to one another and queried together via joins. It is great for daily queries and even some analytical queries. It even has support for JSON columns, allowing you to enjoy the best of both worlds: relational databases and NoSQL databases. A strong online community and great documentation make it easy for anyone to get started with PostgreSQL. 

PostgreSQL supports both SQL and JSON for relational and non-relational queries. PostgreSQL is very popular in use cases due to its Robustness and Feature-Rich Query Layer.  It distinguishes itself by emphasizing integrations and extensibility. It is extendable since it interacts with a variety of other technologies and adheres to numerous database standards.

Key Features of PostgreSQL

Some of the main features of PostgreSQL are listed below:

• Data Recovery: PostgreSQL prevents any data loss and ensures the availability of data at times of system failure by offering data replication and point-in-time recovery features.
•  Reliable: PostgreSQL supports write-ahead logging, synchronous, asynchronous, and logical replication to avoid data loss during a disaster.  
• Security: PostgreSQL ensures that user data should remain safe by providing multiple authentication options such as SCRAM-SHA-256, Certificate, GSSAPI, SSPI, LDAP, etc, with the access control system.

To learn more about PostgreSQL, click here.

Steps to Set Up WooCommerce PostgreSQL Integration

Now that you have understood about WooCommerce and PostgreSQL. In this section, you will learn about the steps to set up WooCommerce PostgreSQL Integration. For integrating WooCommerce PostgreSQL, the WooCommerce API will be used. The following steps for connecting WooCommerce PostgreSQL are listed below:

• Step 1: Generate API keys for WooCommerce
• Step 2: Get PostgreSQL Credentials
• Step 3: Realize the WooCommerce PostgreSQL Integration

Step 1: Generate API keys for WooCommerce

• To get started with WooCommerce PostgreSQL Integration, you will need the API keys for WooCommerce.  In order to do that, log in to WordPress, and go to WooCommerce -> Settings ->Advanced -> Rest API. 
• Click on the ‘Add Key’ button. In the screen that opens up, add a description, and choose the WordPress user you’d like to generate the key for (the key will stop functioning if the user is removed). 
• Next, choose whether the key should have Read access, Write access, or both. 

• Then click on ‘Generate API Key’ and the key will be generated. Copy both the Consumer Key and Consumer Secret and paste them into some safe place.

Step 2: Get PostgreSQL Credentials

• If you have your PostgreSQL DB located on a cloud service, you can look at the documentation specific to your cloud service to get the credentials (DB Name, Host, Port, Username, and Password). 
• Alternatively, you can get the connection string. If you haven’t yet created a DB, you can create one for free on Heroku. See this.
• Also, create a table called products in your PostgreSQL DB. For the sake of simplicity, we will just consider four columns in this table.

CREATE TABLE products(
id int,
name varchar,
type varchar,
status varchar
)

Step 3: Realize the WooCommerce PostgreSQL Integration

• The REST API documentation for WooCommerce can be found here. 
• While you can make raw API requests, you can also use the library/package developed by WooCommerce for the various server-side scripting languages. The details can be found here. 
• We will use Python for this WooCommerce PostgreSQL Integration. Very specifically, we will write a script to fetch product details from WooCommerce and add them to a products table in PostgreSQL.
• To get started, install the WooCommerce python package. 
• You’ll also need pandas, sqlalchemy, and psycopg2. Pandas for creating a dataframe from the data received from Woocommerce, sqlalchemy for posting the dataframe to PostgreSQL and psycopg2 for serving as the engine for sqlalchemy.

pip install woocommerce
pip install pandas
pip install sqlalchemy
pip install psycopg2

• Next, open a text editor and write the following script for WooCommerce PostgreSQL Integration:

from woocommerce import API
from sqlalchemy import create_engine
import pandas as pd
import traceback
from io import StringIO
import csv

def psql_insert_copy(table, conn, keys, data_iter):
    # gets a DBAPI connection that can provide a cursor
    dbapi_conn = conn.connection
    with dbapi_conn.cursor() as cur:
        s_buf = StringIO()
        writer = csv.writer(s_buf)
        writer.writerows(data_iter)
        s_buf.seek(0)

        columns = ', '.join('"{}"'.format(k) for k in keys)
        if table.schema:
            table_name = '{}.{}'.format(table.schema, table.name)
        else:
            table_name = table.name

        sql = 'COPY {} ({}) FROM STDIN WITH CSV'.format(
            table_name, columns)
        cur.copy_expert(sql=sql, file=s_buf)

def insert_df_to_db(df, table_name, DB_URL, action = 'replace', index_col=False):
    try:
        engine = create_engine(DB_URL)
        df.to_sql(table_name, engine,if_exists=action, method = psql_insert_copy, index=index_col)
        engine.dispose()
        return True
    except Exception as e:
        sub = 'Exception in inserting into table ' +str(table_name)
        print(e)
        msg = str(traceback.format_exc())
        return False

DB_URL = "postgresql://user_name:password@host:port/db"

def woocommerce_product_to_pgsql():
    wcapi = API(
        url="https://yourwebsitedomain",
        consumer_key="ck_abcdefghijklmnopqrstuvwxyz",
        consumer_secret="cs_abcdefghijklmnopqrstuvwxyz",
        version="wc/v3"
    )

    r = wcapi.get("products")
    print(r.status_code)
    print(r.headers)
    print(r.json())

    relevant_fields_items = []
    for item in r.json():
        relevant_fields_item = {
            "id":item['id'],
            "name":item['name'],
            "type":item['type'],
            "status":item['status']
        }

        relevant_fields_items.append(relevant_fields_item)

    df = pd.DataFrame(relevant_fields_items)
    insert_df_to_db(df,'products',DB_URL)

if __name__ == '__main__':
  woocommerce_product_to_pgsql()

• Make sure to replace the DB_URL and the WooCommerce key and secret in the above script.
• This script essentially fetches the products from WooCommerce using the Python package, extracts the relevant fields, and adds them to PostgreSQL using the sqlalchemy package of Python. 
• The Woocommerce API returns a list of dicts, and we extract the relevant fields from each of the dicts and create another list of dicts. 
• This new list is converted to a pandas dataframe and added to PostgreSQL. Every time this script is run, the existing entries in the table are replaced (see the insert_df_to_db function). 
• The psql_insert_copy function is essentially a fast way of inserting a huge dataframe into PostgreSQL, using the COPY command.
• After running this script, you can check the products table on PostgreSQL. It will be populated with data. In this example of WooCommerce PostgreSQL Integration, it has just one product to start off with, so my table has just one entry.

• The above script for WooCommerce PostgreSQL Integration can be hosted on a cloud server and be run using a cron job to periodically update the WooCommerce items in a PostgreSQL table. 
• Note that the above script is just one small example of WooCommerce PostgreSQL Integration. Even within products, there are so many fields that you can fetch. And there are several tables apart from products that you can sync to PostgreSQL. These include orders, customers, refund status, and so on. You can find the list here.
• Note that this example of WooCommerce PostgreSQL Integration was shown with Python. However, WooCommerce official libraries are available in Javascript, Ruby, and PHP, and third-party libraries in Java, .NET, and Android. You can find the details here.

That’s it! You have completed WooCommerce PostgreSQL Integration.

Conclusion

In this article, you learned the steps to set up WooCommerce PostgreSQL Integration by fetching data from WooCommerce using its Python library (which in turn uses the WooCommerce REST API) and inserting the results into a table in PostgreSQL. This article can provide you with a starting point and a template for all the future integrations you wish to perform between WooCommerce and PostgreSQL.

Hevo Data provides an Automated No-code Data Pipeline that can help you transfer data from WooCommerce via MySQL. Also, Hevo caters to 100+ data sources (including 40+ free sources) and can seamlessly transfer your data to PostgreSQL within minutes. Hevo’s Data Pipeline enriches your data and manages the transfer process in a fully automated and secure manner without having to write any code. It will make your life easier and make data migration hassle-free.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. 

Share your experience of learning about WooCommerce PostgreSQL Integration in the comments section below!