Format array data in table

Question

The data are presented in the format:

tags_list = [
    ['foo'],
    ['foo', 'boo', 'goo'],
    ['boo', 'zoo']
]

I'm writing this data to table:

for tags in tags_list:
    cursor.execute("""INSERT INTO tags_table VALUES (%s);""", (tags,))

BUT this way data in table becomes to type tuple:

(['foo'],)
(['foo', 'boo', 'goo'],)
(['boo', 'zoo'],)

while I'm expecting:

{'foo'}
{'foo', 'boo', 'goo'}
{'boo', 'zoo'}

Is it possible to convert data to normal PostgreSQL's ARRAY view?

@cᴏʟᴅsᴘᴇᴇᴅ then I'll get an exception not all arguments converted during string formatting — shmnff
– shmnff, Commented Dec 14, 2017 at 6:29
It doesn't make sense to me that you can vary the values inserted into the table. How does this even work? You don't even specify any columns names. I can't see how this would work. — cs95
– cs95, Commented Dec 14, 2017 at 6:30

Frank AK · Accepted Answer · 2017-12-14 11:57:02Z

2

I simple create a table by follow sql:

CREATE TABLE contacts (
 id serial PRIMARY KEY,
 name VARCHAR (100),
 phones TEXT []
);

I am assume you are use pydb and I have create a table like the below show

 id | name |    phones    
----+------+--------------
  1 |      | {123,222,33}

My python code is just simple insert into one list .

import pgdb


conn = pgdb.connect(database='test')


cur = conn.cursor()

lst = ['123','222','33']
cur.execute('insert into contacts(phones) values (%s)', (lst,))
conn.commit()

It's work for me! I guess you did not commit your cursor or your field type is no right!

Back to your example, i have create a table like yours:

CREATE TABLE tags_table(tags TEXT[]);

Before run my python code, let check table .

test=# select * from tags_table;
 tags 
------
(0 rows)

and my python code :

#import pgdb
#conn = pgdb.connect(database='test')
#if psycopg2 has used
#try this 
import psycopg2
conn = psycopg2.connect(database='test')
cursor = conn.cursor()

tags_list = [
    ['foo'],
    ['foo', 'boo', 'goo'],
    ['boo', 'zoo']
]


for tags in tags_list:
    cursor.execute("""INSERT INTO tags_table(tags) VALUES (%s);""", (tags,))

conn.commit()

After run the above code, my table got those result:

test=# select * from tags_table;
     tags      
---------------
 {foo}
 {foo,boo,goo}
 {boo,zoo}
(3 rows)

I don't really understand why you need to show the result as {} but there are a simple way to do this by declare your own List type.

class MyList(list):
    def __str__(self):
        items = ','.join(self)
        return '{' +'{}'.format(items if items else '') + '}'
    def __repr__(self):
        return self.__str__()


for i in d:
    j = MyList(i[0])
    print j

and you will got the result as below show!

{foo}
{foo,boo,goo}
{boo,zoo}
{foo}
{foo,boo,goo}
{boo,zoo}

edited Dec 14, 2017 at 11:57

answered Dec 14, 2017 at 7:05

Frank AK

1,78117 silver badges33 bronze badges

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

7 Comments

shmnff Over a year ago

when I'm checking table in python script, data type in table is tuple

Frank AK Over a year ago

Yes, python show list with () but pg show list with {}.

shmnff Over a year ago

that's cool but problem is how to fix that behavior in python))

Frank AK Over a year ago

@AntoshaShmonoff hey，as you expect result ，your maybe expect to covert it to pg style with ｛｝，it is very easy to do it with python but you should know python will be use ｛｝to set type

shmnff Over a year ago

and you're right again, sets has no indexes, so they cannot be converted to postgres arrays

|

shmnff · Accepted Answer · 2017-12-14 14:13:08Z

0

Here's some compromise solution:

cursor.execute("""SELECT * FROM tags_table;""")
rows = cursor.fetchall()
for row in rows:
    print(row[0])

¯ \ _ (ツ) _ / ¯

answered Dec 14, 2017 at 14:13

shmnff

7412 gold badges16 silver badges34 bronze badges

Collectives™ on Stack Overflow

Format array data in table

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related