Manipulate Woocommerce and Advanced Custom Fields SQL with Python
Dec 132018Getting 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 = ""
PASSWORD = ""
DB = ""
try:
connection = db.Connection(host=HOST, port=PORT,
user=USER, passwd=PASSWORD, db=DB)
dbhandler = connection.cursor()
dbhandler.execute('''
SELECT
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:
print(e)
finally:
connection.close()
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="[email protected]", passwd="password1")
def get_screenshot(url, prod_id):
DRIVER = 'chromedriver'
driver = webdriver.Chrome(DRIVER)
driver.get(url)
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]))
dbhandler.execute('''
UPDATE `wp_postmeta`
SET `meta_value`= '{}'
WHERE meta_key = 'ACF2_img'
AND `post_id` = '{}'
'''.format(image_url, item[1]))