There's a few tutorials on YouTube that illustrate this, one being How to connect to PSQL Database using psycopg2 + Python In this tutorial, you learned how to use the psycopg transaction and how to use transactions to insert data in the PostgreSQL database.I haven't seen anyone comment on this, but you can utilize _values to insert/update many rows of data at once, which I think is the intended solution to many inserts/updates. As a result, you can use the same connection object in the subsequent with statement in another transaction as follows: conn = nnect(dsn)Ĭonn.close() Code language: Python ( python ) Unlike other context manager objects, exiting the with block does not close the connection but only terminates the transaction. Psycopg commits the transaction if no exception occurs within the with block, and otherwise it rolls back the transaction. Starting from psycopg 2.5, the connection and cursor are Context Managers and therefore you can use them with the with statement: with nnect(dsn) as conn:Ĭur.execute(sql) Code language: Python ( python ) We query data from the parts and vendor_parts tables again, there is no new data, therefore, the function works as expected. insert or update on table "vendor_parts" violates foreign key constraint "vendor_parts_vendor_id_fkey" DETAIL: Key (vendor_id)=( 99) is not present in table "vendors". # no rows inserted into the parts and vendor_parts tablesĪdd_part( 'Power Amplifier', ( 99,)) Code language: Python ( python )Īn exception occurred. The program should not add a new part without assigning it to a vendor. Let’s insert another part, but this time, we use an invalid vendor id purposefully for the demonstration purpose. (12 rows) Code language: SQL (Structured Query Language) ( sql )Īs you see, we have successfully inserted the data into both parts and vendor_parts tables. Let’s query the parts and vendor_parts table to confirm the transaction. To test the add_part() function, we call it to insert some parts and assign them to the respective vendors as follows: if _name_ = '_main_':Īdd_part( 'LTE Modem', ( 1, 5)) Code language: Python ( python ) # assign parts provided by vendors for vendor_id in vendor_list:Ĭur.execute(assign_vendor, (vendor_id, part_id))Įxcept (Exception, psycopg2.DatabaseError) as error: Insert_part = "INSERT INTO parts(part_name) VALUES(%s) RETURNING part_id " # statement for inserting a new row into the vendor_parts tableĪssign_vendor = "INSERT INTO vendor_parts(vendor_id,part_id) VALUES(%s,%s)" The following add_part() function demonstrates the idea: #!/usr/bin/python import psycopg2ĭef add_part (part_name, vendor_list): # statement for inserting a new row into the parts table Then, you insert rows into the vendor_parts table. To do this, first, you insert a new row into the parts table and get the part id. Suppose you need to add a new part and assign the vendors who supply the part at the same time. We will use the parts and vendor_parts tables that we created in the creating table tutorial for the demonstration. The following shows a typical pattern for handling a transaction in psycopg: #!/usr/bin/python import psycopg2Ĭode language: Python ( python ) Postgres Python transaction example The autocommit mode is also useful when you execute statements required to execute outside a transaction such as CREATE DATABASE and VACUUM. This ensures that psycopg executes every statement and commits it immediately. Therefore, if you are developing a long-living application, you should call the commit() or rollback() method before leaving the connection unused for a long time.Īlternatively, you can set the autocommit attribute of the connection object to True. It is important to notice that a simple SELECT statement will start a transaction that may result in undesirable effects such as table bloat and locks. Closing the connection object or destroying it using the del will also result in an implicit rollback. And in case you want to cancel the changes, you call the rollback() method. If you want to commit all changes to the PostgreSQL database permanently, you call the commit() method. The connection class has two methods for ending a transaction: commit() and rollback(). If any statement fails, psycopg will abort the transaction. When you issue the first SQL statement to the PostgreSQL database using a cursor object, psycopg creates a new transaction.įrom that moment, psycopg executes all the subsequent statements in the same transaction. In psycopg, the connection class is responsible for handling transactions. Introduction to the transaction in psycopg Summary: in this tutorial, you will learn how to handle PostgreSQL transactions in Python using psycopg database adapter.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |