Get Inserted Key Before Commit Session

Get inserted key before commit session

You could use flush() to flush changes to the database and thus have your primary-key field updated:

parent = Parent()
db.session.add(parent)
db.session.flush()

print parent.id  # after flush(), parent object would be automatically
                 # assigned with a unique primary key to its id field 

child = Child()
child.parent_id = parent.id
db.session.add(child)

sqlalchemy flush() and get inserted id?

Your sample code should have worked as it is. SQLAlchemy should be providing a value for f.id, assuming its an autogenerating primary-key column. Primary-key attributes are populated immediately within the flush() process as they are generated, and no call to commit() should be required. So the answer here lies in one or more of the following:

1. The details of your mapping
2. If there are any odd quirks of the backend in use (such as, SQLite doesn't generate integer values for a composite primary key)
3. What the emitted SQL says when you turn on echo

Get primary key of inserted row after session.execute has run in python

Perhaps you can use OUTPUT then (https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15). Assume your id column is report_id

"""INSERT INTO reports (report_type, report_source, received_date) 
OUTPUT Inserted.report_id 
VALUES (3, 'Email', '24-06-2021 12:00')"""

Get inserted primary key in MS SQL before COMMIT

Both solution, using OUTPUT clause or using SCOPE_IDENTITY, should work just fine even if you started a transaction. But I don't see any of them actually used in the code you posted. The OUTPUT clause must be attached to the very INSERT you write, something like:

$insert_Proposal = 'INSERT INTO PROPOSALS( ';
$insert_Proposal .= 'ApprovedByDev, ';
...
$insert_Proposal .= ') ';
$insert_Proposal .= 'OUTPUT INSERTED.id '
$insert_Proposal .= ' VALUES ( ';
$insert_Proposal .= sqlSafeVars($_POST['ApprovedByDev'], "varchar") .', '; 
...
$insert_Proposal .= ')';

and you need to execute $insert_Proposal as a statement that returns results.

Getting the id of the last record inserted for Postgresql SERIAL KEY with Python

You might be able to use the RETURNING clause of the INSERT statement like this:

result = conn.execute("INSERT INTO user (name, country_id) VALUES ('Homer', 123)
                       RETURNING *")

If you only want the resulting id:

result = conn.execute("INSERT INTO user (name, country_id) VALUES ('Homer', 123)
                        RETURNING id")
[new_id] = result.fetchone()

Get/use autoincremented Primary Key of a new record as a Foreign Key for a record in another table

You can use inserted_primary_key to find the primary key of the most recently inserted record.

https://kite.com/python/docs/sqlalchemy.engine.ResultProxy.inserted_primary_key

According to the docs it's auto-populated depending on your databse backend, here's an example from https://overiq.com/sqlalchemy-101/crud-using-sqlalchemy-core/

ins = insert(customers)

r = conn.execute(ins, 
    first_name = "Tim", 
    last_name = "Snyder", 
    username = "timsnyder", 
    email = "timsnyder@mail.com",
    address = '1611 Sundown Lane',
    town = 'Langdale'
)
r.inserted_primary_key

Get the inserted primary key ids using bulk_save_objects

you need to add return_defaults = True in bulk_save_object method like below to get primary key of records

for x in y:
   obj = Post(...)
   obj_list.append(obj)
session.bulk_save_objects(obj_list,return_defaults = True)
session.commit()

for i in obj_list:
   print(i.id)

---

Related Topics