CODEWITHSUNDEEP
pythonsqlpython-3.xpostgresqlpsycopg2
Harsh Dave
Harsh Dave

Reputation: 29

Unable to INSERT into PostgreSQL with psycopg2 python library

I am new to working with SQL and Postgres specifically and am trying to write a simple program that stores a course id and some URLs in an SQL table with two columns. I am using the psycopg2 python library.

I am able to read from the table using:

def get_course_urls(course):
    con = open_db_connection()
    cur = con.cursor()
    query = f"SELECT urls FROM courses WHERE course = '{course}'"
    cur.execute(query)
    rows = cur.fetchall()
    cur.close()
    close_db_connection(con)
    urls = []
    for url in rows:
        urls.extend(url[0])
    return urls

However, I am unable to insert into the table using:

def format_urls_string(urls):
    return '{"' + '","'.join(urls) + '"}'


def add_course_urls(course, urls):
    con = open_db_connection()
    cur = con.cursor()
    query = f"INSERT INTO courses (course, urls) VALUES ('{course}', '{format_urls_string(urls)}');"
    print(query)
    cur.execute(query)
    cur.close()
    close_db_connection(con)

add_course_urls("CS136", ["http://google.com", "http://wikipedia.com"])

I do not think anything is wrong with my query because when I run the same query in the SQL Shell it works as I want it to.

This is what the columns look like.

The locks on the columns say that the columns are READ-ONLY, however, I am able to insert through the shell. I feel like this is a very minor fix but since I am new to PostgreSQL, I am having some trouble.

Your help is appreciated!

Upvotes: 1

Views: 2130

Answers (1)

Tim Roberts
Tim Roberts

Reputation: 54649

This is the danger of doing the substitution yourself, instead of letting the db connector do it. You looked at your string, yes? You're writing

... VALUES ('CS136', '['http://google.com','http://wikipedia.com']')

which is obviously the wrong syntax. It needs to be

... VALUES ('CS136', '{"http://google.com","http://wikipedia.com"}')

which Python's formatter won't generate. So, you can either format the insertion string by hand, or put placeholders and pass the parameters to the cursor.execute call:

    query = "INSERT INTO courses (course, urls) VALUES (%s,%s);"
    cur.execute( query, (course, urls) )

Upvotes: 1

Related Questions