Unable to INSERT into PostgreSQL with psycopg2 python library

Question

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.

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!

Tim Roberts · Accepted Answer · 2021-03-16 04:20:22Z

1

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) )

answered Mar 16, 2021 at 4:20

Tim Roberts

55.3k4 gold badges28 silver badges41 bronze badges

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

6 Comments

Harsh Dave Over a year ago

Hey @Tim Roberts, thanks for replying. I am actually passing the URLs list into a function i created to format it properly. This is the query string that is passed to cur.execute(query) -> "INSERT INTO courses (course, urls) VALUES ('CS136', '{"http://google.com","http://wikipedia.com"}')"

Tim Roberts Over a year ago

Hard for us to know that if the code doesn't show it. In any case, it's better to let the db connector do it. Have you tried adding con.commit() after the execute?

Harsh Dave Over a year ago

The con.commit() did the trick! Thank you so much! Also, I am having a little trouble understanding what you mean by let the db connector do it, since I have not used this library before. If you do not mind, could you expand on this a little bit?

Tim Roberts Over a year ago

You are using psycopg2 to communicate with the Postgres server. psycopg2 (which I like) is the "database connector" here. The database connectors all know how to do parameter substitution that is absolutely safe from SQL injection attacks, doing all the right quoting and such. It's always better to use the %s placeholders (which vary by connector) and let IT do the escaping and substitution.

Tim Roberts Over a year ago

And, as a side note, it is possible to turn on "auto-commit" so that you don't have to do a manual commit after every insert/update operation.

|

Collectives™ on Stack Overflow

Unable to INSERT into PostgreSQL with psycopg2 python library

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related