基于Python中fetchall结果的MySQL更新或插入

Question

我需要通过本地python脚本在wordpress中设置一些用户元。因此，我不能对它使用WPupdate_user_meta，它必须手动完成

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()

现在我需要检查每个键的结果是否都有meta。 如果此用户的密钥已存在，则需要为此元运行更新。 如果此用户仍然没有此键的meta，则必须插入新行

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` ...")

…还有4次这样的事？。。似乎是一个循环的好地方，但我不太喜欢将其设为5倍队列的想法，特别是因为将来可能会有更多字段

我一直在考虑在fetchall结果中搜索meta_键中的匹配项，如果找到，则将所需数据添加到一个数组中，如果没有，则添加到另一个数组中。然后在最后运行一个更新和一个插入，假设两者都不是空的。如果我用半php风格编写，它大致如下所示：

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+")")

但绝对不知道如何正确地将其翻译成python。我不得不在谷歌上搜索“为什么dot不把一个字符串添加到另一个”之类的东西。有什么建议吗？或者也许有更好的方法？谢谢

Answer 1

我尽了最大的努力去适应上面的建议，但不知道如何使它起作用。最后我换了一种方式，它似乎以某种方式工作，所以我将发布完整的代码，以防有人发现它有用

它的作用：使用验证请求检查表中的队列，然后解析页面（单独的函数）并相应地更新用户配置文件

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()

Answer 2

它不完整，不能以这种方式更新行

但有了这个，你就可以开始提问了

如果用户id存在，则第一个select正好得到1行

对于这一点，用户id似乎不是正确的选择，但要获得您所能做的就足够了

如果查询没有条目，它将插入从任何地方获得的一些数据

插入时的更新是错误的，因为您必须插入5个新ORW或最多更新5行，但这对您编程来说更重要

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()