SQL psycopg2 insert variable that is a list of variable length into database-CodePudding

I am trying to write a row of observations into my database, but I have some unique variable called list_variable which is a list of strings that can be of length 1-3. So sometimes ['string1'] but sometimes also ['string1','string2'] or ['string1','string2','string3'].

When I try to add this to my database by:

def add_to_cockroach_db():
        cur.execute(f"""
        INSERT INTO database (a, b, c)
        VALUES ({time.time()}, {event},{list_variable};  <--- this one
        """)
        conn.commit()

I would get the following error (values have been changed for readability):

SyntaxError: at or near "[": syntax error
DETAIL:  source SQL:
INSERT INTO database (a, b, c)
        VALUES (a_value, b_value, ['c_value_1', 'c_value_2'])
                                  ^
HINT:  try \h VALUES

It seems that having a variable that is a list is not allowed, how could I make this work out?

Thanks in advance!

**edit

list_variable looks e.g., like this = ['value1','value2']

CodePudding user response：

You can either cast it to string using

str(['c_value_1', 'c_value_2'])

which looks like this:

"['c_value_1', 'c_value_2']"

or join the elements of your list with a delimiter you choose. This for example generates a comma separated string.

",".join(['c_value_1', 'c_value_2'])

which looks like this:

'c_value_1,c_value_2'

Like Maurice Meyer has already pointed out in the comments, it is better to pass your values as a list or as a tuple instead of formatting the query yourself. Your command could look like this depending on the solution you choose:

cur.execute("INSERT INTO database (a, b, c) VALUES (%s, %s, %s)", (time.time(), event, ",".join(list_variable)))

CodePudding user response：

There are a few ways you could accomplish this.

The simplest way is to call str on the list and insert the result into a string (VARCHAR) column. While this works, it's not easy to work with the values in database queries, and when it's retrieved from the database it's a string, not a list.

Using a VARCHAR[] column type - an array of string values - reflects the actual data type, and enables use of PostgreSQL's array functions in queries.

Finally, you could use a JSONB column type. This allows storage of lists or dicts, or nested combinations of both, so it's very flexible, and PostgreSQL provides functions for working with JSON objects too. However it might be overkill if you don't need the flexibility, or if you want to be strict about the data.

This script shows all three methods in action:

import psycopg2                                                   
from psycopg2.extras import Json                                   

DROP = """DROP TABLE IF EXISTS t73917632"""  
CREATE = """\                                                       
CREATE TABLE t73917632 (
    s VARCHAR NOT NULL,
    a VARCHAR[] NOT NULL,                    
    j JSONB NOT NULL                         
)
"""
INSERT = """INSERT INTO t73917632 (s, a, j) VALUES (%s, %s, %s)"""
SELECT = """SELECT s, a, j FROM t73917632"""
                                             
v = ['a', 'b', 'c']                          
                                             
with psycopg2.connect(dbname='test') as conn:
    with conn.cursor() as cur:
        cur.execute(DROP)  
        cur.execute(CREATE)
        conn.commit()                                                                       
        cur.execute(INSERT, (str(v), v, Json(v)))
        conn.commit()                                                                       
        cur.execute(SELECT)                                                                 
        for row in cur:                                                                     
            print(row)

Output:

("['a', 'b', 'c']", ['a', 'b', 'c'], ['a', 'b', 'c'])

It's worth observing that if the array of strings represents some kind of child relationship to the table - for example the table records teams, and the string array contains the names of team members - it is usually a better design to insert each element in the array into a separate row in a child table, and associate them with the parent row using a foreign key.