handle duplicates fields in psycopg2

I am writing web app using Flask(Python3.4) & psycopg2 to connect to postgres 9.4 database.

I have option in my web app where user can write their own query and execute it using web app and get output in html table in response.

I'm using cursor as conn.cursor(cursor_factory=psycopg2.extras.DictCursor) Which i can not change as it is used by other parts of web app also.

One of my user wrote SQL like below,

SELECT
name || ' - ' || address,
id,
salary || '; ' || id from company;

Output from psycopg2 (Wrong) :

?column?  id ?column?
text integer (4) text
500.55; 1   1   500.55; 1
500.55; 2   2   500.55; 2
500.55; 3   3   500.55; 3
500.55; 4   4   500.55; 4
999.99; 5   5   999.99; 5

Here the result I got is wrong, due to duplication of key/field name ?column? the second occurrence overwrite the first one.

Expected output was:

?column?  id ?column?
text integer (4) text
AAA - XY    1   500.55; 1
BBB - ZZ    2   500.55; 2
ABC - YY    3   500.55; 3
ABC - CC    4   500.55; 4
ABC - DD    5   999.99; 5

Server side code which send response:

# Fetch the column info 
if cur.description is not None:
    colinfo = [desc for desc in cur.description]

# Any rows?
if cur.rowcount > 0:
   result = []
   try:
      for row in cur:
         result.append(dict(row))
   except psycopg2.ProgrammingError:
       result = None

# response for html table
return Response(
    response=json.dumps(colinfo, result),
    status=status,
    mimetype="text/json"
)

Please suggest, How can I fix this with minimal code change?

Meanwhile I have also raised issue with psycopg2, https://github.com/psycopg/psycopg2/issues/454

---

The basic problem is that dictionary entries have to have unique keys. Column name can't be used as a key if there's going to be more than one column with the same name. However you could check for that and if there is a duplicate column name, make it unique somehow:

# Fetch the column info 
if cur.description is not None:
    colinfo = [desc for desc in cur.description]

# Any rows?
if cur.rowcount > 0:
   result = []
   rowdict = {}
   colnames = [desc[0] for desc in cur.description]

   try:
      for row in cur:
         rowdict = {}
         for x in range(0, len(row)):

             # make sure column name is unique, if not append index number
             if colnames[x] in rowdict:
                 rowdict[colnames[x] + "_" + str(x)] = row[x]
             else:
                 rowdict[colnames[x]] = row[x]

         result.append(rowdict)

   except psycopg2.ProgrammingError:
       result = None

# response for html table
return Response(
    response=json.dumps(colinfo, result),
    status=status,
    mimetype="text/json"
)

Depending on what you're doing on the client side, you may have to update colinfo as well to reflect any column name changes that were made. Another option would be to return a clear error message if there are duplicate column names so the user can take control over what column names they get back.

It's ultimately a bug in psycopg2 but it's only returning what postgres provides for column names in cases like this. It's not clear what it should do instead.

I'm assuming you've considered and dealt with the serious info security risks associated with executing SQL from a client. I'm not trying to be preachy but it's a public forum and I'd be remiss for not mentioning that in case anyone else is looking to do something like this.

链接地址: http://www.djcxy.com/p/60842.html

上一篇: Yii中的sql查询的位置（或任何支持ORM的框架）？

下一篇: 处理psycopg2中的重复字段