How to convert sql varchar array to Python list?
NickName:Dmitrijs Zubriks Ask DateTime:2014-01-09T01:54:15

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?

Copyright Notice:Content Author:「Dmitrijs Zubriks」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/21002999/how-to-convert-sql-varchar-array-to-python-list

Answers
Craig Ringer 2014-01-09T01:09:46

Given:\n\nCREATE TABLE pgarray ( x text[] );\nINSERT INTO pgarray(x) VALUES (ARRAY['ab','cd','ef']);\n\n\nThen psycopg2 will take care of array unpacking for you. Observe:\n\n>>> import psycopg2\n>>> conn = psycopg2.connect('dbname=regress')\n>>> curs = conn.cursor()\n>>> curs.execute('SELECT x FROM pgarray;')\n>>> row = curs.fetchone()\n>>> row\n(['ab', 'cd', 'ef'],)\n>>> row[0][0]\n'ab'\n>>> print( ', '.join(row[0]))\nab, cd, ef\n",


More about “How to convert sql varchar array to Python list?” related questions

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. Inser...

Show Detail

pyspark hive sql convert array(map(varchar, varchar)) to string by rows

I would like to transform a column of array(map(varchar, varchar)) to string as rows of a table on presto db by pyspark hive sql programmatically from jupyter notebook python3. example user_id ...

Show Detail

Convert sql subquery to list or varchar

I want to know if we can convert a subquery result to comma separated list in varchar datatype. For eg. If I have a product table. And I have product image table with foreign key of product. now I...

Show Detail

convert varchar array to date array-pl sql

I am trying to convert a a varchar array into a date array. Currently the date is being accessed from a Java class in the form of a String but it needs to be a date there. Therefore I need to conve...

Show Detail

how to select convert list varchar for WHERE IN() SQL Query

how to select convert list varchar for WHERE IN() SQL Query select code from table1 Code 10223 10240 10047 10174 10187 10192 10212 10213 10195 select * table2 from where code in

Show Detail

How to convert varchar containing list of uniqueidentifiers into a list of uniqueidentifiers in SQL Server

In the query data type of employeeId column is uniqueidentifier. I am getting following error while executing Conversion failed when converting from a character string to uniqueidentifier. decl...

Show Detail

Convert INT to VARCHAR SQL

I am using Sybase and I am doing a select which returns me a column called "iftype", but its type is int and I need to convert into varchar. When I try to do the select without the convert function...

Show Detail

How to convert varchar from sql server to Image in android

We have image stored in SQL Server as blob or varchar which is something similar to: 0xFFD8FFE000104A46494600010100000...(Goes till 1945 characters). How do I convert this to an image for imagevi...

Show Detail

T SQL How to convert Varchar to Date

I'm using MS SQL Server 2016. I have a field 'MyDate' in table 'MyTable'. It contains dates in varchar(10) in the format dd/mm/yyyy e.g. 21/01/2020 How do I convert these to SQL Datetime format?...

Show Detail

How to convert Varchar to Int in sql server 2008?

How to convert Varchar to Int in sql server 2008. i have following code when i tried to run it wont allowed me to convert Varchar to Int. Select Cast([Column1] as INT) Column1 is of Varchar(21)...

Show Detail