Use your business data to your advantage with the help of Syncfusion’s new data science offerings. Discover how a custom big data solution can provide your company with valuable predictions about key market trends.

Tips & Tricks on MySQL for Python

Packt Publishing

Integrating MySQL and Python can bring a whole new level of productivity to your applications. This practical tutorial shows you how with examples and explanations that clarify even the most difficult concepts.

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:

MySQL-python-1.2.3c1.tar.gz

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.

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.

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.

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.

Summary

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

Alerts & Offers

Series & Level

We understand your time is important. Uniquely amongst the major publishers, we seek to develop and publish the broadest range of learning and information products on each technology. Every Packt product delivers a specific learning pathway, broadly defined by the Series type. This structured approach enables you to select the pathway which best suits your knowledge level, learning style and task objectives.

Learning

As a new user, these step-by-step tutorial guides will give you all the practical skills necessary to become competent and efficient.

Beginner's Guide

Friendly, informal tutorials that provide a practical introduction using examples, activities, and challenges.

Essentials

Fast paced, concentrated introductions showing the quickest way to put the tool to work in the real world.

Cookbook

A collection of practical self-contained recipes that all users of the technology will find useful for building more powerful and reliable systems.

Blueprints

Guides you through the most common types of project you'll encounter, giving you end-to-end guidance on how to build your specific solution quickly and reliably.

Mastering

Take your skills to the next level with advanced tutorials that will give you confidence to master the tool's most powerful features.

Starting

Accessible to readers adopting the topic, these titles get you into the tool or technology so that you can become an effective user.

Progressing

Building on core skills you already have, these titles share solutions and expertise so you become a highly productive power user.