dashwood.net -

Ryan Stefan's Micro Blog

Manipulate Woocommerce and Advanced Custom Fields SQL with Python

Dec 132018

Getting advanced custom fields data out of a Wordpress database in Python was starting to look tricky, but of course it turned out to be just a matter of knowing the right trick. Pretty hard to find information on these types of database queries though. I thought it was going to get really hairy because I ran across a tutorial for getting advanced custom field data by using "AND meta_value LIKE "field\_%" so I was trying all these variations and it was giving me something like field_345wesef435.

On top of that getting woocommerce product meta data has an underscore in front of it like this "_regular_price", so I was trying those two things thinking that the underscore was just a convention of Wordpress meta data.

I thought I was going to have to use that as a key in a completely different table or something and was starting to get a headache.  Apparently meta data keys are just completely random and up to the developer. So I head over to the official ACF forums and someone was like "oh yeah you just use the field name as the key", anyways that worked instantly and I feel stupid. Here's the code:

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import MySQLdb as db

HOST = "server ip"
PORT = 3306
USER = ""
DB = ""

    connection = db.Connection(host=HOST, port=PORT,
                               user=USER, passwd=PASSWORD, db=DB)

    dbhandler = connection.cursor()
        wp_posts.post_title AS Product, #SQL aliases are just to make it easier to read
        wp_posts.ID AS PID,
        wp_postmeta1.meta_value AS Price,
        wp_postmeta2.meta_value AS ACF1,
        wp_postmeta3.meta_value AS ACF2,
        GROUP_CONCAT( wp_terms.name ORDER BY wp_terms.name SEPARATOR ', ' ) AS ProductCategories
        FROM wp_posts
        LEFT JOIN wp_postmeta wp_postmeta1
        ON wp_postmeta1.post_id = wp_posts.ID
        AND wp_postmeta1.meta_key = '_regular_price'
        LEFT JOIN wp_postmeta wp_postmeta2
        ON wp_postmeta2.post_id = wp_posts.ID
        AND wp_postmeta2.meta_key = 'acf_field_name' #no underscore in front
        LEFT JOIN wp_postmeta wp_postmeta3
        ON wp_postmeta3.post_id = wp_posts.ID
        AND wp_postmeta3.meta_key = 'acf_field_name'
        LEFT JOIN wp_term_relationships
        ON wp_term_relationships.object_id = wp_posts.ID
        LEFT JOIN wp_term_taxonomy
        ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
        AND wp_term_taxonomy.taxonomy = 'product_cat'
        LEFT JOIN wp_terms
        ON wp_term_taxonomy.term_id = wp_terms.term_id
        WHERE wp_posts.post_type = 'product'
        AND wp_posts.post_status = 'publish'
        GROUP BY wp_posts.ID
        ORDER BY wp_posts.post_title ASC
    result = dbhandler.fetchall()
    for item in result:
        if item[2] != None:
            print('Hoorah! - {} is meta af'.format(item[0]))

except Exception as e:


Then I created a script to screenshot + upload from a URL in the meta and then save the image path in another meta field:

import ftplib

# connection part is working 
ftp = ftplib.FTP('ftp.example.com')
ftp.login(user="username@example.com", passwd="password1")

def get_screenshot(url, prod_id):
    DRIVER = 'chromedriver'
    driver = webdriver.Chrome(DRIVER)
    driver.set_window_size(width, height)
    driver.execute_script("document.body.style.overflow = 'hidden';window.scrollBy(0, 150);")
    WebDriverWait(driver, 10)

    screenshot = driver.get_screenshot_as_png()
    image = Image.open(BytesIO(screenshot)).convert('RGB')

    #left, top, right, bottom
    cropped = image.crop((250,160,950,608))
    buffered = BytesIO()
    cropped.save('./images/{}_ACF2.png'.format(prod_id), format="PNG", optimize=True, quality=100) #temp save to buffer
    f = open('./images/{}_ACF2.png'.format(prod_id), 'rb')
    ftp.storbinary('STOR /public_html/example.com/wp-content/uploads/{}_ACF2.png'.
        format(prod_id), f)

    result_url = '/wp-content/uploads/{}_ACF2.png'.format(prod_id)

if item[3] and not item[4]:
    image_url = get_screenshot(item[3], item[1])
    print('Hoorah! - {} is meta af and cost\'s: {}'.format(item[0], item[2]))
        UPDATE `wp_postmeta`
        SET `meta_value`= '{}'
        WHERE meta_key = 'ACF2_img'
        AND `post_id` = '{}'
        '''.format(image_url, item[1]))

Selenium Screenshots and Cropping with Django

Dec 132018

It was actually pretty difficult to resize and crop a screenshot taken from selelium, but here's how I did it:

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

Takes a screenshot of a website given the URL, crops it, and then displays it on a Django template via base64
Optional parameters go in the URL like this https://www.example.com/?w=800&h=600&save=true
""" import base64 import os import urllib.parse as urlparse from django.shortcuts import render from django.http import HttpResponse from django.conf import settings from datetime import datetime from selenium import webdriver from selenium.webdriver.support.ui import WebDriverWait import PIL from PIL import Image import base64 from io import BytesIO DRIVER = 'chromedriver'
#Browser driver directory: /usr/local/bin/[driver file goes here]
#Chrome driver file: https://sites.google.com/a/chromium.org/chromedriver/downloads def get_screenshot(request): """ Take a screenshot and return a png file based on the url. """ width = 1024 height = 768 if request.method == 'POST' and 'url' in request.POST: url = request.POST.get('url', '') if url is not None and url != '': params = urlparse.parse_qs(urlparse.urlparse(url).query) if len(params) > 0: if 'w' in params: width = int(params['w'][0]) if 'h' in params: height = int(params['h'][0]) driver = webdriver.Chrome(DRIVER) driver.get(url) driver.set_window_size(width, height) driver.execute_script("document.body.style.overflow = 'hidden';window.scrollBy(0, 100);") WebDriverWait(driver, 10)
#This works, but decided not to manipulate data for ethical reasons #driver.execute_script("document.getElementsByClassName('entry-title')[0].innerHTML = 'Changed H1 Title';"); #WebDriverWait(driver, 10) if 'save' in params and params['save'][0] == 'true': now = str(datetime.today().timestamp()) img_dir = settings.MEDIA_ROOT img_name = ''.join([now, '_image.png']) full_img_path = os.path.join(img_dir, img_name) if not os.path.exists(img_dir): os.makedirs(img_dir) driver.save_screenshot(full_img_path) screenshot = open(full_img_path, 'rb').read() var_dict = {'screenshot': img_name, 'save': True} else: size = (600, 415) screenshot = driver.get_screenshot_as_png() image = Image.open(BytesIO(screenshot)).convert('RGB') resized = image.resize(size) cropped = resized.crop((125,100,600,315)) buffered = BytesIO() cropped.save(buffered, format="JPEG", optimize=True, quality=100) img_str = base64.b64encode(buffered.getvalue()) var_dict = {'screenshot': img_str} driver.quit() return render(request, 'home.html', var_dict) else: return HttpResponse('Error')



New Woot Clone / Drop-shipping Website

Dec 102018

So I finally found a project that me and my co-worker can both work on fluidly. That idea came to me in the form of a DIY ukulele ad on Facebook. I thought that it was similar enough to my dad and I's pen holder project to look into. Turns out that it's a clever drop shipping website that uses Facebook videos to sell products. They have in their shipping "These products sell out fast because they are so good, but don't worry we have a backup warehouse in Asia" or something like that. This is obviously complete BS, but I thought it was clever and decided to start copying them. My project however, has rapidly evolved way past them.

I spent all weekend making a clone of woot.com and it actually went extremely well. The only down side is that I thought it was Saturday yesterday, but it turned out to be Sunday and now I'm back at work :(.

I actually don't want to give this idea away, so I won't be posting details, but I will share some of the things I found that made making the website possible. Widgets:

  • AMR Shortcode any Widget
  • Advanced Custom Fields
  • Mega Main Menu
  • Product of the Day for WooCommerce

So I did find a daily deal plugin that essentially displays a list of products each day of the week inside of a widget. There of course is also a premium version that does things like random products, shortcodes, and some other features, but I just needed the base functionality anyways. Also, instead of creating my own shortcode out of it I just used a plugin called AMR Shortcode any Widget and it worked perfect.

After that I cracked open the product of the day widget code and added 8 different product categories each with their own days of the week. Then I added a radio button for if its part of the header or not. So the shortcode on the page widget and the widget inside of the Mega Main Menu hover are completely different.

if ($menu_mode == 1) {
    echo '<a href="' . $deal_urls[$products_cat] . '" title="' . get_the_title() . '">';
} else {
    echo '<a href="' . get_permalink() . '" title="' . get_the_title() . '">';