The python operation database error

Question

I use python operation postgresql database, the implementation of sql, it removed the quotation marks, resulting in inquiries failed, how to avoid?

def build_sql(self,table_name,keys,condition):
    print(condition)
    # condition = {
    #     "os":["Linux","Windows"],
    #     "client_type":["ordinary"],
    #     "client_status":'1',
    #     "offset":"1",
    #     "limit":"8"
    # }
    sql_header = "SELECT %s FROM %s" % (keys,table_name)
    sql_condition = []
    sql_range = []
    sql_sort = []
    sql_orederby = []
    for key in condition:
        if isinstance(condition[key],list):
            sql_condition.append(key+" in ("+",".join(condition[key])+")")
        elif key == 'limit' or key == 'offset':
            sql_range.append(key + " " + condition[key])
        else:
            sql_condition.append(key + " = " + condition[key])
    print(sql_condition)
    print(sql_range)
    sql_condition = [str(i) for i in sql_condition]
    if not sql_condition == []:
        sql_condition = " where " + " and ".join(sql_condition) + " "
    sql = sql_header + sql_condition + " ".join(sql_range)
    return sql

Error：

MySQL Error Code : column "winxp" does not exist
LINE 1: ...T * FROM ksc_client_info where base_client_os in (WinXP) and...

Please don't tag it with postgresql if it is for MySQL only (and error suggests that) — Łukasz Kamiński, Aug 02 '17 at 12:42

score 1 · Accepted Answer · answered Aug 02 '17 at 12:48

Mind you I do not have much Python experience, but basically you don't have single quotes in that sequence, so you either need to add those before passing it to function or for example during join(), like that:

sql_condition.append(key+" in ("+"'{0}'".format("','".join(condition[key]))+")")

You can see other solutions in those questions:

Join a list of strings in python and wrap each string in quotation marks

Add quotes to every list elements

The python operation database error

1 Answers1