Use MySQLadbapi to obtain the auto-increment id under Twisted

Source: Internet
Author: User
Django's ORM has a very convenient function. In fact, it should be said that it is a very basic function. After you call save () to insert a model to the database, the created

D jango's ORM has a very convenient function. It should also be said to be a very basic function. After you call save () to insert a model to the database, the created

D jango's ORM has a very convenient function. It should also be said to be a very basic function. After you call save () to insert a model to the database, the created auto-increment id is synchronized to the current model. The default return value for calling INSERT in SQL is the number of inserted rows. For the current application, it is actually a meaningless return value, therefore, it is very pleasant for Django's ORM to be able to handle auto-incremental id synchronization.

However, Django is not used. Recently, the adbapi provided by Twisted is used. How can I get the auto-increment id?

If it is PostgreSQL, it is very simple to add a RETURNING statement to the INSERT statement:

Insert into distributors (did, dname)
VALUES (DEFAULT, 'xyz widgets') RETURNING did;
But what about MySQL?

I have been using MySQL For A While recently, but from the perspective of command line auto-completion, I have obviously felt the gap with PostgreSQL. Honestly, I don't know why so many people love MySQL so much ......

I searched StackOverflow and found two methods:

SELECT LAST_INSERT_ID ();
Or use the mysql_insert_id () function of connector. For MySQLdb in Python, This Is The insert_id () function of connection, for example:

Conn = MySQLdb. connect (host = 'heiven', user = 'God ',
Passwd = 'Jesus', db = 'elysium ')
Cursor = conn. cursor ()
Cursor.exe cute ('insert INTO account VALUES (% s, % s )',
('Satan', 'male or female, who knows '))
New_id = conn. insert_id () # <=
The process is still simple, but for Twisted's adbapi, our work is not over. Why? Because adbapi uses a thread pool to manage MySQL connections, each Query call will obtain a thread from the thread pool and then forward the corresponding transaction to the thread for processing. The default transaction is divided by a single SQL statement. Therefore, the default Interface cannot meet the requirements for other operations such as MySQL after execution. Let me try again. pg is good ......

If the default Interface is not enough, we can only expand it. Looking at the source code of adbapi, we can find that for general transactions, adbapi actually uses the runInteraction interface function. For specific transactions, adbapi regards the corresponding callback as its first parameter, and then specifies the thread to run the callback in the deferToThreadPool. Therefore, we only need to define a new transaction for MySQL.

The new transactions we need to define are as follows:

Def runMySQLInsert (self, * args, ** kw ):
Assert self. dbapiName = 'mysqldb'
Return self. runInteraction (self. _ runMySQLInsert, * args, ** kw)

Def _ runMySQLInsert (self, trans, * args, ** kw ):
Trans.exe cute (* args, ** kw)
Return trans. connection. insert_id ()
OK. Next, Patch the two functions Monkey to adbapi :)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.