Tips & Tricks on MySQL for Python


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.

        Read more about this book      

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

Objective: Using a main() function is good practice in programming.

Tip: In any size of program, using a main() function is good practice and results in a high degree of readability. Ideally, main() should be among the smallest of the functions in a program. The point is that main() should be the brains of the program that coordinates the activity of the classes and functions.

Objective: Blackboxing is useful for Python.

Tip: Blackboxing is jargon in the IT industry and simply means to isolate the parts of a problem so that each piece can be tested separately of the others. With this for loop, we can ensure that Python has properly assimilated the flagged input from the user.

Objective: Python will throw NameError.

Tip: The other functions must be inserted before the main() function is called, otherwise Python will throw a NameError.

Objective: Calling main() helps us to avoid lots of possible problems.

Tip: Calling main() as a result of this if statement at the very end of the program is like not connecting the power to an electric circuit until you are ready to test it. It helps us to avoid lots of possible problems.

Objective: Use print instead of raise.

Tip: Use of raise will provide a stack trace. If you do not want a stack trace printed, then you should simply use a print statement to output the error message.

Objective: docstrings.

Tip: If you are unclear on what docstrings are and why you should use them, see this rationale for their use:

Objective: Python supports a rowcount attribute.

Tip: It is worth noting that MySQL for Python supports a rowcount attribute of cursor and this is the preferred way of accessing the total of affected rows.

Objective: Escape from the wildcard with the backslash.

Tip: To match a value that incorporates one of the wildcards, escape from the wildcard with the backslash (\)like you do in Python. So to match ZERO_ we would use ZERO\_.

        Read more about this book      

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

Objective: MySQL does not use more memory than is necessary.

Tip: It is important to note that the 16 megabytes are not held in reserve. MySQL does not use more memory than is necessary for any transaction. The value specified only serves to cap how much memory MySQL may try to use.

Objective: In order to create databases in MySQL, the account you use must have the CREATE privilege on the database.

Tip: CREATE statements are also sensitive to user privileges. If a user is only granted CREATE privileges on a single database (for example, csv.*), then that user cannot create databases, but can create tables on that specific database.

Objective: The best practice is to drop every temporary table.

Tip: It is worth noting that the dropping of temporary tables is logged differently when the session ends rather than when they are overtly dropped. Therefore, the best practice is to drop every temporary table you create when you are done using it, even at the end of a session.

Objective: Exercise caution when using DROP command.

Tip: When the DROP command is executed, the table and its definition are deleted irrecoverably from the database. You should therefore exercise caution when using it.

Objective: Creating users using command: CREATE USER exemplar;

Tip: Note that if NO_AUTO_CREATE_USER is enabled in your MySQL configuration, this type of user creation will fail. This is particularly true in SQL_MODE.

Objective: Circumventing the administrative privileges.

Tip: Note also that if a user with access to a particular MySQL database has the ALTER privilege and is then granted the GRANT OPTION privilege, that user can then grant ALTER privileges to a user who has access to the mysql database, thus circumventing the administrative privileges otherwise needed.

Objective: Binary logs and InnoDB tablespaces are customizable.

Tip: Note that the location of administrative files, such as binary logs and InnoDB tablespaces are customizable and may not be in the data directory.


In this article we had a look at various tips & tricks to make working with MySQL for Python a little easier.

Further resources on this subject:

You've been reading an excerpt of:

MySQL for Python

Explore Title