Using INSERT with a PostgreSQL Database using Python

Question

I am trying to insert data into a PostgreSQL database table using Python. I don't see any syntax errors but, for some reason, my data isn't getting inserted into the database.

conn = psycopg2.connect(connection)
cursor = conn.cursor()
items = pickle.load(open(pickle_file,"rb"))

for item in items:
    city = item[0]
    price = item[1]
    info = item[2]

    query =  "INSERT INTO items (info, city, price) VALUES (%s, %s, %s);"
    data = (info, city, price)

    cursor.execute(query, data)

For string interpolation you can now use: ('{0}', '{1}', '{2}') in place of (%s, %s, %s) above. — zero_cool
– zero_cool, Commented Jan 17, 2017 at 3:38

GuillaumeDufay · Accepted Answer · 2012-01-31 06:49:06Z

56

You have to commit the transaction.

conn.commit()

If there's no reason to think the transaction will fail, it's faster to commit after the for loop finishes.

answered Jan 31, 2012 at 6:49

GuillaumeDufay

1,1279 silver badges13 bronze badges

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

1 Comment

GuillaumeDufay Over a year ago

np, i make the same mistake all the time ;)

Collectives™ on Stack Overflow

Using INSERT with a PostgreSQL Database using Python

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related