Can Python update/insert into Wordpress-MySQL based on fetchall results?

Hitoris
May 13, 2023
116 views
0 votes
2 Answers

I need to set some user meta in my wordpress through local python script. Hence I can’t use the WP update_user_meta for it – it has to be done manually.

import mysql.connector as mysql
cnx = mysql.connect(host=HOST, database=DATABASE, user=USER, password=PASSWORD)
cursor = cnx.cursor()
get_meta = ("SELECT * FROM `ff_usermeta` WHERE `user_id`= 1 AND (`meta_key`='nickname' OR `meta_key`='info' OR `meta_key`='bg' OR `meta_key`='avatar' OR `meta_key`='profile_updated')")
cursor.execute(get_meta)
meta = cursor.fetchall()
#some processing of the result
cursor.execute(q, (...))
cnx.commit()
cursor.close()
cnx.close()

Now I need to check if the result has meta with each of the keys.
If the key already exists for this user, it needs to run UPDATE for this meta.
If this user still has no meta of this key, it has to INSERT new row.

if(there's no 'nickname' in meta_key on either of 5 or less rows):
   q = ("INSERT INTO `ff_usermeta` ...")
else: 
   q = ("UPDATE `ff_usermeta` ...")

…and 4 more times like that?.. Seems like a good place for a cycle, but I don’t really like the idea to make it 5x queues, especially since there might be more fields in the future.

I was thinking along the lines of searching the fetchall result for matches in meta_key, and if found, adding required data to one array, if not – to another. And then just running one update and one insert at the end, assuming both are not empty. If I were to write it in semi-php style, it would look roughly like this:

if(in_array("nickname", meta))
    for_update .= "`nickname`='"+data[0]+"', "
else:
    fields .= "`nickname`, "
    vals .= "'"+data[0]+"', "
if(in_array("bg", meta)):
    for_update .= "`bg`='"+data[1]+"', "
else:
    fields .= "`bg`, "
    vals .= "'"+data[1]+"', "
    
if(for_update):
    update = ("UPDATE `ff_usermeta` SET "+for_update+" WHERE 1")
if(fields):
    insert = ("INSERT INTO `ff_usermeta`("+fields+") VALUES ("+vals+")")

But absolutely no clue how to translate it correctly to python. I had to google it up to things like "why dot not working to add one string to another". Any advice? Or perhaps there is a better way? Thanks!

Answers

Chosen as BEST ANSWER

I tried my best to adapt the suggestion above, but couldn't figure out how to make it work. Eventually I went another way, and it seems to work somehow, so I'll post the full code in case anyone would find it useful.

What it does: checks the queue in table with validation request, then parses a page (separate function) and updates user profile accodringly.

import mysql.connector as mysql
import time
from datetime import datetime
cnx = mysql.connect(host=HOST, database=DATABASE, user=USER, password=PASSWORD)
while True: #endless loop as a temporary scheduler
    cursor = cnx.cursor()
    #getting first request in the queue - 0: id, 1: url, 2: parse, 3: status, 4: user, 5: user_page, 6: req_date, 7: action
    cursor.execute("SELECT * FROM `ff_qq` WHERE status = 0 LIMIT 1")
    row = cursor.fetchone()
    if row:
        status = 1 #processed
        if row[7] == "verify":
            get_user = ("SELECT * FROM `ff_users` WHERE ID = %s LIMIT 1")
            cursor.execute(get_user, (row[4],))
            user = cursor.fetchone() #0 - ID, 5 - user_url, 8 - user_status, 9 - display_name
            #separate function that returns data to insert into mysql
            udata = verify(row) #0 - nickname, 1 - fb_av, 2 - fb_bg, 3 - fb_info, 4 - owner
            ustat = row[1].split("/authors/")
            if udata['owned'] or user[8] == ustat[1]:
                update_user = ("UPDATE `ff_users` SET user_status = %s, display_name = %s, user_url = %s WHERE ID = %s LIMIT 1")
                cursor.execute(update_user, (ustat[1], udata['nickname'], row[1], user[0]))
                status = 2 #success
                get = ("SELECT `meta_value` FROM `ff_usermeta` WHERE `user_id`= %s AND `meta_key`='ff_capabilities' LIMIT 1")
                cursor.execute(get, (row[4],))
                rights = cursor.fetchone()
                if rights == 'a:1:{s:10:"subscriber";b:1;}':
                    promote = ("UPDATE `ff_usermeta` SET `meta_value` = 'a:1:{s:6:"author";b:1;}' "
                                       "WHERE `user_id` = %s AND `meta_key`='ff_capabilities' LIMIT 1")
                    cursor.execute(promote, (row[0],))
                #list of meta_key values in same order as returned data
                ff = ['nickname', 'fb_av', 'fb_bg', 'fb_info']
                for x in range(0,3): #goes through each one of the above list
                    if udata[ff[x]]: #yes this actually works, who would've thought?..
                        #current meta_key added directly into the string
                        get = ("SELECT `meta_value` FROM `ff_usermeta` WHERE `user_id`= %s AND `meta_key`='" + ff[x] + "' LIMIT 1")
                        cursor.execute(get, (row[4],))
                        meta = cursor.fetchone()
                        if(meta): #update if it exists, otherwise insert new row
                            qq = ("UPDATE `ff_usermeta` SET `meta_value` = %s "
                                       "WHERE `user_id` = %s AND `meta_key`='" + ff[x] + "' LIMIT 1")
                        else:
                            qq = ("INSERT INTO `ff_usermeta`(`meta_value`, `meta_key`, `user_id`) "
                                   "VALUES ('%s','" + ff[x] + "','%s'")
                        cursor.execute(qq, (udata[ff[x]], row[0])) #same execute works for both
            else:
                status = 3 #verification failed
        #update queue to reflect its status
        update = ("UPDATE `ff_qq` SET status = %s WHERE id = %s LIMIT 1")
        cursor.execute(update, (status, row[0]))
        cnx.commit()
        cursor.close()
    now = datetime.now()
    print(now.strftime("%d.%m.%Y %H:%M:%S"))
    time.sleep(180) #sleep until it's time to re-check the queue
cnx.close()

(Edit)

It is not complete, you can not update your rows in that way.

But with this you can start to make your query

The frist select gets exactly 1 row, if the user_id exists.

The user_id doesn’t seem the right choice for this, but to get what you can do it is enough.

If the query doesn’t have an entry, the it will insert some data you get from anywhere

The update as the insert are in that form wrong as you have to insert 5 new orws or update max 5 rows, but that is more for you to programm

import mysql.connector as mysql
HOST = "localhost"
DATABASE = ""
USER = "root"
PASSWORD = "mypassword"
cnx = mysql.connect(host=HOST, database=DATABASE, user=USER, password=PASSWORD)

cnx = mysql.connect(host=HOST, database=DATABASE, user=USER, password=PASSWORD)
cursor = cnx.cursor()
user_id = 1
get_meta = ("""SELECT  umeta_id, user_id , MAX(IF( `meta_key`='nickname', meta_value,'')) AS 'nickname' , MAX(IF( `meta_key`='info', meta_value,'')) AS 'info' , MAX(IF( `meta_key`='bg', meta_value,'')) AS 'bg' , MAX(IF( `meta_key`='avatar', meta_value,''NULL'')) AS 'avatar' , MAX(IF (`meta_key`='profile_updated', meta_value,'')) AS 'profile_updated' FROM `ff_usermeta`  WHERE `user_id`= %s GROUP BY umeta_id, user_id:""")
result = cursor.execute(get_meta,(user_id,))
if result > 0:
    data = cursor.fetchone()
    for_update = "";
    #some processing of the result
    if not data["nickname"]:
        for_update += "`nickname`='"+data["nickname"]+"', "
        if not data["bg"]:
            for_update += "`bg`='"+data["bg"]+"', "
        query = ("UPDATE `ff_usermeta` SET "+for_update+" WHERE user_id = " + user_id)
else:
    #here are no data to be gathered as there is no user_id present add new user
    nickname = ""
    bg= ""
    info = ""
    avatar = ""
    profile_updated = ""
    fields= ""
    vals = ""
    fields += "`nickname`,`info`, `bg`,`avatar`,`profile_updated`"
    vals += "'"+nickname+"', "+"'"+info+"', "+"'"+bg+"', "+"'"+avatar+"', "+"'"+profile_updatedfo+"'"
    query = ("INSERT INTO `ff_usermeta`("+fields+") VALUES ("+vals+")")
cursor.execute(query)
cnx.commit()
cursor.close()
cnx.close()

Please signup or login to give your own answer.

Click here to cancel reply.

Can Python update/insert into WordPress-MySQL based on fetchall results?

Answers