How to convert sql varchar array to Python list?

Question

I'm using psycopg2 for interacting with PostgreSQL database in Python2.7.

psycopg2 saves list in database at first in varchar field, and then I need simply to get the same Python list back.

Insert:

data = ['value', 'second value']
with psycopg2.connect(**DATABASE_CONFIG) as connection:
    cursor = connection.cursor()
    cursor.execute("INSERT INTO table_name (varchar_field) VALUES (%s)", (data)
    connection.commit()

In pgAdmin it looks like: {value, second_value}

Then I tried to do something like this:

with psycopg2.connect(**DATABASE_CONFIG) as connection:
    cursor = connection.cursor()
    cursor.execute("SELECT varchar_field FROM table_name")

    for row in cursor:
        for data_item in row: # here I want to iterate through the saved list (['value', 'second_value']), but it returns string: '{value, second_value}'
            print data_item

I have found possible solution, but I have no idea how to implement it in my code.

So, how can I retrieve back Python List from sql ARRAY type?

What's the type of varchar_field? I hope it is an array, i.e., something like varchar(4096)[]. If it is a simple varchar the array conversion won't work. — fog
– fog, Commented Jan 9, 2014 at 16:49

Craig Ringer · Accepted Answer · 2014-01-09 01:09:46Z

1

Given:

CREATE TABLE pgarray ( x text[] );
INSERT INTO pgarray(x) VALUES (ARRAY['ab','cd','ef']);

Then psycopg2 will take care of array unpacking for you. Observe:

>>> import psycopg2
>>> conn = psycopg2.connect('dbname=regress')
>>> curs = conn.cursor()
>>> curs.execute('SELECT x FROM pgarray;')
>>> row = curs.fetchone()
>>> row
(['ab', 'cd', 'ef'],)
>>> row[0][0]
'ab'
>>> print( ', '.join(row[0]))
ab, cd, ef

answered Jan 9, 2014 at 1:09

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

Comments

fog · Accepted Answer · 2014-01-08 18:27:37Z

0

psycopg2 already does that for you. If the PostgreSQL column type is a text array, i.e., text[] you should get a python list of strings. Just try to access the first item returned by the query instead of the whole result tuple:

for row in cursor:
    for data_item in row[0]:
    # Note the index '0' ^ here.
        print data_item

answered Jan 8, 2014 at 18:27

fog

3,4411 gold badge27 silver badges32 bronze badges

3 Comments

Dmitrijs Zubriks Over a year ago

It does not work, because it returns string. In this case it will print '{'. I know, that psycopg2 converts from Python to SQL types, but I doubt that it does for SQL -> Python.

Craig Ringer Over a year ago

@dimazubrik Your Python & psycopg2 versions?

fog Over a year ago

@dimazubrik it does! I can see various problems here: you're using a really old psycopg2 version, or your column isn't of the correct type.

Collectives™ on Stack Overflow

How to convert sql varchar array to Python list?

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related