Joab Mendes Joab Mendes - 1 year ago 1048
Python Question

Psycopg2: cursor.execute is not working properly

So, I have the following code that inserts the data of an old database to a new one:

cur_old.execute("""SELECT DISTINCT module FROM all_students_users_log_course266""")
module_rows = cur_old.fetchall()

for row in module_rows:
cur_new.execute("""INSERT INTO modules(label) SELECT %s WHERE NOT EXISTS (SELECT 1 FROM modules WHERE label=%s)""", (row[0], row[0]))

The last line executes a query where labels are inserted into the new database table. I tested this query on
and it works as I want.

However, when execute the script, nothing is inserted on the
table. (Actually the sequences are updated, but none data is stored on the table).

Do I need to do anything else after I call the execute method from the cursor?

(Ps. The script is running till the end without any errors)

Answer Source

You forgot to do connection.commit(). Any alteration in the database has to be followed by a commit on the connection. For example, the sqlite3 documentation states it clearly in the first example:

# Save (commit) the changes.

And the first example in the psycopg2 documentation does the same:

# Make the changes to the database persistent
>>> conn.commit()
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download