Extract string from a Tuple in python with psycopg2

Question

I have written a web.py service in python to access PostGres and get the table names inside the specific database.

CODE:

 def GET(self,r):
          web.header('Access-Control-Allow-Origin',      '*')
          web.header('Access-Control-Allow-Credentials', 'true')
          tables = []
          datasetID = web.input().dataSetID
          cursor = conn.cursor()
          cursor.execute("select relname from pg_class where relkind='r' and relname !~ '^(pg_|sql_)';")
          tablesWithDetails =    cursor.fetchall()
          print tablesWithDetails
          for x in tablesWithDetails:
            x.replace("(", "")
            x.replace(")","")
            tables.append(x)
            print tables;

This prints the table as follows,

[('acam_datasegregationdetails',), ('acam_datasegregationheader',), ('idn_accessinformation',), ('idn_b2cuseraccountmapping',), ('idn_b2cuserdevicemapping',), ('idn_b2cusers',), ('idn_roles',), ('idn_useraccountmapping')]

Needed Output:

['acam_datasegregationdetails', 'acam_datasegregationheader', idn_accessinformation', 'idn_b2cuseraccountmapping', 'idn_b2cuserdevicemapping', 'idn_b2cusers', 'idn_roles', 'idn_useraccountmapping']

try this tables.append(x[0]) if that does not work can you type type(x) — The6thSense
– The6thSense, Commented Nov 23, 2015 at 10:48
the result from cursor.fetchall() is a List of Tuple when you iterate over it you get tuples which is in your case x which is a one element tuple. Now x[0] gives first element of the tuples — The6thSense
– The6thSense, Commented Nov 23, 2015 at 10:53

Clodoaldo Neto · Accepted Answer · 2015-11-23 11:29:09Z

7

Drop that loop and in instead do

tables = [t[0] for t in tablesWithDetails]

It will build a list containing the first element of each tuple in the result set.

Or even simpler (and cheaper), if you want a list then return an array which will be adapted to a list by Psycopg:

cursor.execute("""
    select array_agg(relname)
    from pg_class
    where relkind='r' and relname !~ '^(pg_|sql_)';"
""")
tables = cursor.fetchall()[0][0]

edited Nov 23, 2015 at 11:29

answered Nov 23, 2015 at 11:21

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Remi Guan Over a year ago

Then how to run x.replace("(", "") and x.replace(")", "")?

Clodoaldo Neto Over a year ago

@Kevin As I understand that is wrong. The OP was trying to "fix" or "cast" the tuples in the returned list supposing it could somehow be treated as a string.

Remi Guan Over a year ago

Hmm...maybe. However tuples don't have .replace() method. Upvoted.

The6thSense Over a year ago

@KevinGuan that is what bothers be it should throw an error stating there is no .replace method in a tuple

Remi Guan Over a year ago

@VigneshKalai: Do you mean about: Why doesn't OP's code raise an error about that .replace?

|

The6thSense · Accepted Answer · 2015-11-23 11:04:20Z

2

The problem is due this piece of code

tables.append(x)

When you execute cursor.fetchall() you will get a List of Tuples

and when you do for x in tablesWithDetails: you are iterating over the list by one tuple at a time

So when you do tables.append(x) you are appending a single element tuple to the list

To change that you could do this tables.append(x[0]) it appends the first element of the tuple

answered Nov 23, 2015 at 11:04

The6thSense

8,3559 gold badges38 silver badges67 bronze badges

3 Comments

Remi Guan Over a year ago

By the way, what about tables.extend(x)?

The6thSense Over a year ago

@KevinGuan it would certainly work at this condition but what if there are many element in the tuple then extend would append all the element

Remi Guan Over a year ago

Yep, just think that use .extend would be more clear at this condition :P

Collectives™ on Stack Overflow

Extract string from a Tuple in python with psycopg2

2 Answers 2

9 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related