0

tldr; Can someone explain how to optimally structure a psycopg INSERT statement using variables for the data being entered?

I am attempting to store basic data types into a PostgreSQL database using psycopg. The first block of code is my attempt to store values into the ideatest database, and the ideastorage table. The second block of code was written to retrieve everything currently in the ideastorage table.

I return no syntax errors when executing the first block, but I'm pretty sure it doesn't work. The variables have already been defined and tested, but I think the %s string reference is where my issues lie.

    with psycopg.connect('dbname=ideatest user=postgres password=password') as conn: 
        with conn.cursor() as cur:
            cur.execute('''
                INSERT INTO ideastorage (given_idea_id, idea_designation, idea_summary, idea_creation_time)
                VALUES (%s, %s, %s, %s)''',
                (ideaID, ideaName, ideaSummary, ideaTime)
            )

This second block returns 'None,' but I think that's because my insert syntax is incorrect.

     with psycopg.connect('dbname=ideaTest user=postgres password=password') as conn: 
        with conn.cursor() as cur:
            cur.execute('''
                SELECT idea_designation, idea_summary, idea_creation_time
                FROM ideastorage
                ORDER BY idea_designation'''
            )
            cur.fetchall()

So, I think my error lies with how I'm inserting data into the table. I've checked the documentation here, but I believe the syntax for psycopg (I'm using psycopg3, technically) is different from the psycopg2 syntax - and even that syntax tells me my first block won't work. How should I properly write the first block to insert variables into the database? (I understand variables in python are basically just pointers).

snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
Caritas
  • 45
  • 3
  • The syntax looks correct for the `INSERT`. The context manager `with psycopg.connect( ... ` will `commit()` the transaction. You need to do something like `rs = cur.fetchall()` and then `return rs` somewhere. – Adrian Klaver Oct 26 '21 at 22:14
  • Do blocks return things? As far as I know functions return things, by using "return". Blocks don't return things. – jjanes Oct 26 '21 at 23:59
  • That is why I said 'return rs somewhere'. There is more to this code then what is being shown. It is up to the OP to decide where to deal with the results. – Adrian Klaver Oct 27 '21 at 15:35

0 Answers0