Python MySQL Learning Summary

No application can be separated from the data, so when learning python, of course, you should also learn how to use Python to manipulate the database. MYSQLDB is the Python module for MySQL database operations. Official INTRODUCTION:MYSQLDB is a thread-compatible interface to the popular MySQL database server that provides the Python D Atabase API. It is actually equivalent to translating the corresponding C interface.

The use of this database interface is mostly the execution of the connection database, execute query-> extract data, close the connection of these steps. MYSQLDB provides more critical objects, namely connection, Cursor, and Result. The specific use of the procedure is very simple to write, first write some people think more important, noteworthy places.

1, although in the MySQLdb.connect (host, user, PASSW, db) function, we often use only these parameters, but in fact there are many such as character sets, thread safety, SSL, etc. are also important parameters, use to be aware of the identity.

2. When you use the Connection.query () function to query, the connection object can return two result types, Store_result and Use_result,store_result, respectively. The result set is stored back to the client side, and Use_result is the result set saved on the server side, and a connection is maintained that consumes the server resources. At this point, you cannot make any other queries. It is recommended that you use Store_result unless you return a result set that is too large or cannot use limit.

3, extraction (FETCH) data return form most of the three cases. As a tuple (how=0); as dictionaries, key=column or table.column if duplicated (how=1); as dictionaries, key=table.column (how =2)

4, each fetch, within the result will produce the data position of the movement, that is, if there are 10 rows of data, the execution of Result.fetch_row (3,0), will get the first three rows, and then execute Result.fetch_row (3,0), you will get the middle three rows, So, fetch will cause position to move. It is also important to note that if you use Use_result, which means that the data is stored in the server, you cannot make any query operations until you fetch all the entries.

5. The cursor is not supported by MySQL itself, but the cursor is simulated by MYSQLDB. The important execution query method has execute and Executemany. Execute method, execute a single SQL statement, call the Executemany method is very useful, a large part of the database performance bottleneck is the network IO and disk IO to put multiple inserts together, only once IO, can effectively improve database performance. Cursors cursor has fetchone, Fetchmany, Fetchall three methods to extract data, each of which causes the cursor to swim, so you must focus on the cursor's position. The cursor's scroll (value, mode) method allows the cursor to be scrolled, and the mode parameter specifies whether to move relative to the current position (relative) or absolute position (absolute).

6, MYSQLDB provides a lot of functional methods, in the official guide is not fully listed, users can use Help to see, which provides a lot of convenient things.

7, for MySQL, if you use a support transaction of the storage engine, then after each operation, commit is necessary, otherwise it will not actually write to the database, corresponding rollback can be the corresponding rollback, but the commit is no longer rollback. Commit () can be called again after executing many SQL instructions, which can improve performance appropriately.

8, Executemany processing too much of the command is not necessarily good, because the data into the server side, may cause buffer overflow server side, and a large amount of data, there may be some unexpected trouble. Reasonable, batch executemany is a good way.

Python MySQL Learning Summary