CODEWITHSUNDEEP
pythonsqlpostgresqlpsycopg2
Dmitrijs Zubriks
Dmitrijs Zubriks

Reputation: 2806

How to convert sql varchar array to Python list?

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?

Upvotes: 0

Views: 2469

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324511

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

Upvotes: 1

fog
fog

Reputation: 3391

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

Upvotes: 0

Related Questions