Book Image

MySQL for Python

By : Albert Lukaszewski
Book Image

MySQL for Python

By: Albert Lukaszewski

Overview of this book

Python is a dynamic programming language, which is completely enterprise ready, owing largely to the variety of support modules that are available to extend its capabilities. In order to build productive and feature-rich Python applications, we need to use MySQL for Python, a module that provides database support to our applications. Although you might be familiar with accessing data in MySQL, here you will learn how to access data through MySQL for Python efficiently and effectively.This book demonstrates how to boost the productivity of your Python applications by integrating them with the MySQL database server, the world's most powerful open source database. It will teach you to access the data on your MySQL database server easily with Python's library for MySQL using a practical, hands-on approach. Leaving theory to the classroom, this book uses real-world code to solve real-world problems with real-world solutions.The book starts by exploring the various means of installing MySQL for Python on different platforms and how to use simple database querying techniques to improve your programs. It then takes you through data insertion, data retrieval, and error-handling techniques to create robust programs. The book also covers automation of both database and user creation, and administration of access controls. As the book progresses, you will learn to use many more advanced features of Python for MySQL that facilitate effective administration of your database through Python. Every chapter is illustrated with a project that you can deploy in your own situation.By the end of this book, you will know several techniques for interfacing your Python applications with MySQL effectively so that powerful database management through Python becomes easy to achieve and easy to maintain.

Related Content you might be interested in

Current Title:

Small book image
MySQL for Python
Albert Lukaszewski
Sep, 2010 | 440 pages
No titles found
Table of Contents (20 chapters)
MySQL for Python
MySQL for Python
Credits
Credits
About the Author
About the Author
About the Reviewers
About the Reviewers
Preface
Preface
Free Chapter
1
Getting Up and Running with MySQL for Python
Getting Up and Running with MySQL for Python
Getting MySQL for Python
Importing MySQL for Python
Connecting with a database
Multiple database connections
Summary
2
Simple Querying
Simple Querying
A brief introduction to CRUD
Forming a query in MySQL
Passing a query to MySQL
Using user-defined variables
Determining characteristics of a database and its tables
Changing queries dynamically
Project: A command-line search utility
Room to grow
Summary
3
Simple Insertion
Simple Insertion
Forming a MySQL insertion statement
Helpful ways to nuance an INSERT statement
Passing an insertion through MySQL for Python
Using user-defined variables
Using metadata
Changing insertion values dynamically
Essentials: close and commit
Project: A command-line insertion utility
Summary
4
Exception Handling
Exception Handling
Why errors and warnings are good for you
Errors versus warnings: There's a big difference
The two main errors in MySQLdb
Warnings in MySQL for Python
Handling exceptions passed from MySQL
Catching different types of exceptions
Creating a feedback loop
Project: Bad apples
Summary
5
Results Record-by-Record
Results Record-by-Record
The problem
Why?
How?
Project: A movie database
Summary
6
Inserting Multiple Entries
Inserting Multiple Entries
The problem
Introducing the executemany() method
executemany(): Basic syntax
executemany(): Behind the scenes
Project: Converting a CSV file to a MySQL table
Summary
7
Creating and Dropping
Creating and Dropping
Creating databases
Removing or deleting databases
Creating tables
Dropping tables
Doing it in Python
Dropping tables with MySQLdb
Project: Web-based administration of MySQL
Summary
8
Creating Users and Granting Access
Creating Users and Granting Access
A word on security
Creating users in MySQL
Creating users from Python
Removing users in MySQL
DROPping users in Python
GRANT access in MySQL
Granting access in Python
Removing privileges in MySQL
Using REVOKE in Python
Project: Web-based user administration
Summary
9
Date and Time Values
Date and Time Values
Date and time data types in MySQL
Date and time types in Python
Date and time functions
Project: Logging user activity
Summary
10
Aggregate Functions and Clauses
Aggregate Functions and Clauses
Calculations in MySQL
Trimming results
Server-side sorting in MySQL
Putting it in Python
Project: Incorporating aggregate functions
Summary
11
SELECT Alternatives
SELECT Alternatives
HAVING clause
Subqueries
Unions
Joins
Doing it in Python
Project: Implement HAVING
Summary
12
String Functions
String Functions
Preparing results before their return
Accessing and using index data
Nuancing data
Project: Creating your own functions
Summary
13
Showing MySQL Metadata
Showing MySQL Metadata
MySQL's system environment
Accessing database metadata
Accessing user metadata
Project: Building a database class
Summary
14
Disaster Recovery
Disaster Recovery
Every database needs a backup plan
Choosing a backup method
Backing up a database with Python
Summary
Index
Index

Summary

In this chapter we have looked at where to find MySQL for Python, as it is not part of Python by default. We have also seen how to install it on both Windows and non-Windows systems—UNIX-like and Linux distributions. The authors of MySQL for Python have taken the pain out of this by providing a very easy way to install through an egg utility like EasyInstall.

Like most modules, MySQL for Python must be imported before you can use it in Python. So we then looked at how to import it. Unlike most modules, we saw that MySQL for Python needs to be imported by its earlier moniker, MySQLdb.

After that, we took a peek at what is waiting for us under the MySQL for Python covers using help(). We saw that MySQL for Python is not an interface to MySQL itself but to a MySQL Database API that is built into Python. It has a large number of classes for handling errors, but only one for processing data (There are different kinds of cursors). Further, it does not even use classes to access MySQL, but uses functions to process and pass information to _mysql, which then passes it to the C MySQL database interface.

Following this trail, we also saw that _mysql does not have a robust facility for handling errors, but only passes them to the calling process. That is why MySQL for Python has such a robust error handling facility.

Next, we saw how to connect to a MySQL database. As with most parts of Python, this is easy for beginners. But the function used is also sufficiently robust to handle the more complex needs of advanced solutions.

After connecting, we created a MySQLdb cursor and prepared to interact with the database. This showed that, while there are many things that MySQLdb will take care of for us (like connection closure), there are some things we need to do manually. In this instance, it is creating the cursor object that represents the MySQL cursor.

Finally, we saw that one can connect to multiple databases by simply using different object names for each connection. This has the consequence of necessitating different namespaces as we refer to the methods and attributes of each object. But it also allows one to bridge between databases across multiple hosts seamlessly and to present a unified interface for a user.

In the next chapter, we will see how to form a MySQL query and pass it from Python using variables from the system, MySQL, and the user.

Previous Section
End of Chapter 1
Next Chapter