All geek questions in one place

Getting id of created record in SQLAlchemy

How can I get the id of the created record in SQLAlchemy?

I do:

engine.execute("insert into users values (1,'john')")
+4
source share
2 answers

When you execute a simple text statement, you are at the mercy of the DBAPI that you use, regardless of whether or not the new PK value is available and by what means. With SQlite and MySQL DBAPI, you get it as result.lastrowid, which just gives you the .lastrowid value for the cursor. With PG, Oracle, etc. There is no ".lastrowid" - as someone said that you can use "RETURNING" for those in which the results are accessible through result.fetchone () (although using RETURNING with oracle again does not use SQLAlchemy expression constructs, it requires several inconvenient steps), or if RETURNING is not available, you can use direct access to the sequence (NEXTVAL in pg) or the operation "post fetch" (CURRVAL in PG, @@ identity or scope_identity () in MSSQL).

Sounds complicated? That's why you better use table.insert() . The primary SQLAlchemy system for providing newly created PCs is designed to work with these constructs. Wherever you are, the result.last_inserted_ids() method gives you a newly created (possibly composite) PK in all cases, regardless of the backend. The above methods are .lastrowid, sequence execution, RETURNING, etc. Designed for you (0.6 uses RETURNING when available).

+14
source

An additional sentence is added here: RETURNING

t

 INSERT INTO users (name, address) VALUES ('richo', 'beaconsfield') RETURNING id

Then, just getting a string, such as your insert, was a SELECT statement.

+3
source

Source: https://habr.com/ru/post/1300125/

More articles:


All Articles