Tips & Tricks on MySQL for Python

4 min read


MySQL for Python

MySQL for Python Integrate the flexibility of Python and the power of MySQL to boost the productivity of your Python applications

  • Implement the outstanding features of Python’s MySQL library to their full potential
  • See how to make MySQL take the processing burden from your programs
  • Learn how to employ Python with MySQL to power your websites and desktop applications
  • Apply your knowledge of MySQL and Python to real-world problems instead of hypothetical scenarios
  • A manual packed with step-by-step exercises to integrate your Python applications with the MySQL database server
        Read more about this book      

(For more resources on this subject, see here.)

Objective: Install a C compiler on Windows installation.

Tip: Windows binaries do not currently exist for the 1.2.3 version of MySQL for Python. To get them, you would need to install a C compiler on your Windows installation and compile the binary from source.

Objective: Use tar.gz to use egg file.

Tip: If you cannot use egg files or if you use an earlier version of Python, you should use the tar.gz file, a tar and gzip archive. The tar.gz archive follows the Linux egg files in the file listing. The current version of MySQL for Python is 1.2.3c1, so the file we want is as following:


This method is by far more complicated than the others. If at all possible, use your operating system’s installation method or an egg file.

Objective: Limitation of using MySQL for Python on Python version.

Tip: This version of MySQL for Python is compatible up to Python 2.6. It is worth noting that MySQL for Python has not yet been released for Python 3.0 or later versions. In your deployment of the library, therefore, ensure that you are running Python 2.6 or earlier. As noted, Python 2.5 and 2.6 have version-specifi c releases. Prior to Python 2.4, you will need to use either a tar.gz version of the latest release or use an older version of MySQL for Python. The latter option is not recommended.

Objective: It is important to phrase the query in such a way as to narrow the returned values as much as possible.

Tip: Here, instead of returning whole records, we tell MySQL to return only the namecolumn. This natural reduction in the data reduces processing time for both MySQL and Python. This saving is then passed on to your server in the form of more sessions able to be run at one time.

Objective: This hard-wiring of the search query allows us to test the connection before coding the rest of the function.

Tip: There may be a tendency here to insert user-determined variables immediately. With experience, it is possible to do this. However, if there are any doubts about the availability of the database, your best fallback position is to keep it simple and hardwired. This reduces the number of variables in making a connection and helps one to blackbox the situation, making troubleshooting much easier.

Objective: Readability counts.

Tip: The virtue of readability in programming is often couched in terms of being kind to the next developer who works on your code. There is more at stake, however. With readability comes not only maintainability but control. If it takes you too much effort to understand the code you have written, you will have a harder time controlling the program’s flow and this will result in unintended behavior. The natural consequence of unintended program behavior is the compromising of process stability and system security.

Objective: Quote marks not necessary when assigning MySQL statements.

Tip: It is not necessary to use triple quote marks when assigning the MySQL sentence to statement or when passing it to execute(). However, if you used only a single pair of either double or single quotes, it would be necessary to escape every similar quote mark. As a stylistic rule, it is typically best to switch to verbatim mode with the triple quote marks in order to ensure the readability of your code.

Objective: xrange() is much more memory efficient than range().

Tip: The differences between xrange() and range() are often overlooked or even ignored. Both count through the same values, but they do it differently. Where range() calculates a list the first time it is called and then stores it in memory, xrange() creates an immutable sequence that returns the next in the series each time it is called. As a consequence, xrange() is much more memory efficient than range(), especially when dealing with large groups of integers. As a consequence of its memory efficiency, however, it does not support functionality such as slicing, which range() does, because the series is not yet fully determined.

Objective: autocommit feature is useful in MySQL for Python .

Tip: Unless you are running several database threads at a time or have to deal with similar complexity, MySQL for Python does not require you to use either commit() or close(). Generally speaking, MySQL for Python installs with an autocommit feature switched on. It thus takes care of committing the changes for you when the cursor object is destroyed. Similarly, when the program terminates, Python tends to close the cursor and database connection as it destroys both objects.


Please enter your comment!
Please enter your name here