PostgreSQL not INSERTing in Python on AWS Lambda with psycopg2

Question

PostgreSQL accessed by Python in AWS Lambda with psycopg2.
This function:

cur.execute("INSERT INTO table (column1, column2, column3) VALUES ('test1', 'test2', 'test3');")

Does not add any row to the database. Psycopg2 does not throw an error. PostgreSQL logs the statement without an error. HOWEVER! There is a fourth column, which is an auto-incremented id. The sequence used to keep track of the id is increased by one, even though the row isn't added. (Weird already)

If the same statement is sent in any other way (command line, pgAdmin, Adminer), it works.

I have no single idea what is happening.

EDIT: full code:

def db():
    with open('configEC2.json') as json_data_file:
        POSTGRES = json.load(json_data_file)
    db = psycopg2.connect( host=POSTGRES["sql"]["host"], user=POSTGRES["sql"]["user"], password=POSTGRES["sql"]["passwd"], dbname=POSTGRES["sql"]["db"] )
    return db
def dbSetup():
    dbR = db()
    cur = dbR.cursor()
    return cur

cur = dbSetup()
cur.execute("INSERT INTO table (column1, column2, column3) VALUES ('test1', 'test2', 'test3');")
cur.close()

quick question, is the query supposed to be like that or did you just use a sample query to show how its working? also can you show the whole part of your code, you might be forgetting to do a commit once you execute the query. — fixatd
– fixatd, Commented May 10, 2018 at 19:09
ok, would you mind adding the later part of your code? you're probably missing the commit which would explain why you're not getting an error and the record is not getting created. — fixatd
– fixatd, Commented May 10, 2018 at 19:12
@HonzaNijaký We need more code, just that single line has nothing wrong with it so we need more code to diagnose the problem. We should see the connect (minus sensitive info) through the closing of the cursor. — BShaps
– BShaps, Commented May 10, 2018 at 19:14

fixatd · Accepted Answer · 2018-05-10 19:46:30Z

5

Seems like you're not doing a commit which isn't saving your code, so I suggest doing:

def db():
    with open('configEC2.json') as json_data_file:
        POSTGRES = json.load(json_data_file)
    db = psycopg2.connect( host=POSTGRES["sql"]["host"], user=POSTGRES["sql"]["user"], password=POSTGRES["sql"]["passwd"], dbname=POSTGRES["sql"]["db"] )
    return db

conn = db()

cur = conn.cursor()

cur.execute("INSERT INTO table (column1, column2, column3) VALUES ('test1', 'test2', 'test3');")

conn.commit()

Note the conn.commit() since it's required to commit your insert into the database. I've made this mistake before and it was annoying to debug.

answered May 10, 2018 at 19:46

fixatd

1,4041 gold badge13 silver badges22 bronze badges

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

3 Comments

Honza Nijaký Over a year ago

Thanks, works. I thought you were supposed to do cur.commit(), but it's conn.commit().

fixatd Over a year ago

yeah, it's somewhat confusing when you've not seen the documentation. Got it wrong before as well! Cheers

BShaps Over a year ago

You should also be closing your cursor (and connection if you don't need to keep the connection open). In general it should go connect(), cursor(), execute(), commit(), cursor.close(), connection.close()

Collectives™ on Stack Overflow

PostgreSQL not INSERTing in Python on AWS Lambda with psycopg2

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related