Home>

Take what I want to use as the condition of the WHERE statement as an argument, shape it and store it in wheres.
I want to execute a SELECT statement using the wheres as a conditional expression, but SyntaxError occurs.

import psycopg2
import config
def reference(formMemberList):
    dbusers ='postgres'
    dbnames ='CRM'
    passwords ='taku0217'
    conn = psycopg2.connect(" user=" + dbusers +" dbname=" + dbnames +" password=" + passwords)
    cur = conn.cursor()
    wheres = ""
    for menber in formMemberList:
        if(formMemberList.index(menber)!=0):
            wheres += "AND "
        wheres += menber
    cur.execute("SELECT * FROM customer WHERE'%s'" %wheres)
    rows = cur.fetchall()
    cur.execute("COMMIT")
    cur.close()
    conn.close()
    return rows
formMemberList = ["name:'takumi'", "birth:'1995-02-17'"]
rows = reference(formMemberList)
  • Answer # 1

    There seem to be two places like that.

    Single quote at joint

    cur.execute("SELECT * FROM customer WHERE'%s'" %wheres)
    This line encloses %s in single quotes, so the part after WHERE'Will be surrounded by.

    Contents of formMemberList

    "name:'takumi'"Therefore, it cannot be embedded in SQL as it is.
    This time, for the time being:->=I replaced it with and matched the shape.

    Extraordinary

    If you put "1 = 1" after where,ANDIt's convenient because you don't need to judge whether or not to insert.
    The sample code uses it to make things easier.

    Snake foot 2

    The member is menber, but I leave it as it is.

    Conclusion

    I cut out the necessary parts and wrote the minimum code. Please refer to the following.
    Thank you yymmt for pointing this out.

    def reference(formMemberList):
        wheres = "1 = 1"
        for menber in formMemberList:
            wheres += "AND "
            wheres += menber.replace(":", "=")
        print("SELECT * FROM customer WHERE %s" %wheres)
        return None
    formMemberList = ["name:'takumi'", "birth:'1995-02-17'"]
    rows = reference(formMemberList)