pythonlistdictionarymysql-pythonstring-concatenation

How to make a flexible argument list for cursor.execute in python3?


In fact, I want to increase and decrease the mysql query items and the values ​​(arguments) according to the status of the variable values.

based on my scenario I need to make a flexible mysql query individual with its arguments.

    connection = pymysql.connect(host="", user="", passwd="", db="")
    myquery = connection.cursor()

    insARGS = ""
    content_id = 1097
    page_title = "test page title"

    insQuery = 'UPDATE ex_content SET'
    insARGS = ""
    insARGSdictionary = {}

    if (len(page_title) > 0):
        print("page_title: "+page_title)
        print(type(page_title))
        pageTitleQuery = " c_title=%s"
        insQuery = insQuery + pageTitleQuery
        insARGS = "page_title"
        insARGSdictionary['page_title'] = page_title
    else:
        print("page_title is not bigger than 0")

    if (len(page_desc) > 0):
        print("page_desc: "+page_desc)
        pageDescQuery = ", c_meta_desc=%s"
        insQuery = insQuery + pageDescQuery
        insARGS = insARGS + ", page_desc"
        insARGSdictionary['page_desc'] = page_desc
    else:
        print("page_desc is not bigger than 0")
    if (len(page_heading) > 0):
        page_heading = ""
        print("page_heading: "+page_heading)
        pageHeadingQuery = ", c_heading=%s"
        insQuery = insQuery + pageHeadingQuery
        insARGS = insARGS + ", page_heading"
        insARGSdictionary['page_heading'] = page_heading
    else:
        print("page_heading is not bigger than 0")
    if (len(page_link) > 0):
        print("page_link: "+page_link)
        pageLinkQuery = ", c_link=%s"
        insQuery = insQuery + pageLinkQuery
        insARGS = insARGS + ", page_link"
        insARGSdictionary['page_link'] = page_link
    else:
        print("page_link is not bigger than 0")
    
    if content_id:
        print("content_id: "+str(content_id))
        contentIdQuery = ' WHERE c_id=%s '
        insQuery = insQuery + contentIdQuery
        insARGS = insARGS + ", content_id"
        insARGSdictionary['content_id'] = content_id

        list_of_dict_keys = list(insARGSdictionary.keys())
        queryArguments = (', '.join(map(str, list_of_dict_keys)))
        myquery.execute(insQuery, (queryArguments))

Also my data types in queryArguments may be vary (e.g. int or str or etc). Thank you in advance for the advice


Solution

  • the second argument to myquery.execute() should be the values, not the keys. You don't even need a dictionary, use a list that you append to as you add to the query.

    connection = pymysql.connect(host="", user="", passwd="", db="")
    myquery = connection.cursor()
    
    content_id = 1097
    page_title = "test page title"
    
    insQuery = 'UPDATE ex_content SET'
    insARGSlist = []
    
    if (len(page_title) > 0):
        print("page_title: "+page_title)
        print(type(page_title))
        pageTitleQuery = " c_title=%s"
        insQuery = insQuery + pageTitleQuery
        insARGSlist.append(page_title)
    else:
        print("page_title is not bigger than 0")
    
    if (len(page_desc) > 0):
        print("page_desc: "+page_desc)
        pageDescQuery = ", c_meta_desc=%s"
        insQuery = insQuery + pageDescQuery
        insARGSlist.append(page_desc)
    else:
        print("page_desc is not bigger than 0")
    if (len(page_heading) > 0):
        page_heading = ""
        print("page_heading: "+page_heading)
        pageHeadingQuery = ", c_heading=%s"
        insQuery = insQuery + pageHeadingQuery
        insARGSlist.append(page_heading)
    else:
        print("page_heading is not bigger than 0")
    if (len(page_link) > 0):
        print("page_link: "+page_link)
        pageLinkQuery = ", c_link=%s"
        insQuery = insQuery + pageLinkQuery
        insARGSlist.append(page_link)
    else:
        print("page_link is not bigger than 0")
    
    if content_id:
        print("content_id: "+str(content_id))
        contentIdQuery = ' WHERE c_id=%s '
        insQuery = insQuery + contentIdQuery
        insARGSlist.append(content_id)
    
        myquery.execute(insQuery, insARGSlist)