MySQL for Python

431 Pages·2010·2.72 MB·English

by  Albert Lukaszewski

MySQL for Python
Integrate the flexibility of Python and the power of
MySQL to boost the productivity of your applications
Albert Lukaszewski, PhD

BIRMINGHAM - MUMBAI
MySQL for Python
Copyright © 2010 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval
system, or transmitted in any form or by any means, without the prior written
permission of the publisher, except in the case of brief quotations embedded in
critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy
of the information presented. However, the information contained in this book is
sold without warranty, either express or implied. Neither the author, nor Packt
Publishing, and its dealers and distributors will be held liable for any damages
caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the
companies and products mentioned in this book by the appropriate use of capitals.
However, Packt Publishing cannot guarantee the accuracy of this information.
First published: September 2010
Production Reference: 1160910
Published by Packt Publishing Ltd.
32 Lincoln Road
Olton
Birmingham, B27 6PA, UK.
ISBN 978-1-849510-18-9
www.packtpub.com
Cover Image by Vinayak Chittar ([email protected])
Credits
Author Editorial Team Leader
Albert Lukaszewski Aanchal Kumar
Reviewers Project Team Leader
Swaroop C H Priya Mukherji
Andy Dustman
Geert JM Vanderkelen Project Coordinator
Prasad Rai
Acquisition Editor
Steven Wilding Proofreader
Aaron Nash
Development Editor
Wilson D'souza Production Coordinator
Shantanu Zagade
Technical Editors
Prashant Macha Cover Work
Shantanu Zagade
Charumati Shankaran
Indexer
Hemangini Bari
About the Author
Albert Lukaszewski is principal consultant for Lukaszewski Consulting Services
in southeast Scotland. He has programmed computers for 30 years. Much of his
experience has related to text processing, database systems, and Natural Language
processing (NLP). Currently he consults on database applications for companies in
the financial and publishing industries.
In addition to MySQL for Python, Albert Lukaszewski has also written "About
Python", a column for the New York Times subsidiary, About.com.
Many people had a hand in this work beyond my typing at
the keyboard. Some contributed by their effort and others by
their sacrifice. Thanks to the team at Packt for their consistent
understanding and support. I am particularly thankful to Steven
Wilding for help and support above and beyond the call of duty.

Thanks also to Andy Dustman, Geert Vanderkelen, and Swaroop for
their helpful review of this book and for making so many significant
and helpful recommendations. This book would be much the poorer
were it not for their suggestions.

To Richard Goodrich, who first introduced me to Python, thank you
for liberating me from bondage to that other P-language. Funny
what a little problem can lead to.

My heartfelt thanks and appreciation go to my wife, Michelle, and
my sons, Cyrus and Jacob. The latter was born during the writing
of this book and consistently brightens even the darkest Scottish
weather with his smile. I appreciate your sacrifice. I could not have
written this book without your support.

Finally, my thanks to my brother, Larry, who first introduced me to
the world of computing. I would probably not know anything about
computer programming if you had not left me your TRS-80. So this
is all your fault, and I am glad you did it.
About the Reviewers
Swaroop C H has previously worked at Yahoo! and Adobe, has co-founded a
startup, has written two technical books (one of which is used as a text book in
more than ten universities worldwide), writes a popular blog that has been
consistently rated one of the top ten blogs in India, and is a marathoner.
More details at http://www.swaroopch.com/about/.
He has written two technical books—A Byte of Python and A Byte of Vim—beginner
books to Python and Vim respectively. They are freely available under the Creative
Commons license on his website www.swaroopch.com.
Andy Dustman (http://profiles.google.com/farcepest) is the primary
author of MySQLdb, the MySQL interface for Python.
The MySQL-Python project is supported and funded purely by volunteers and
donations by the user community at (http://sourceforge.net/projects/mysql-
python/).
Andy has been using Python since 1997, and currently works on Django applications
(using MySQL, of course) when not doing system and network administration. In his
spare time, he rides motorcycles.
I would like to thank Kyle VanderBeek, who has recently become a
co-developer on MySQLdb, and has helped to push me a bit to get
some things done. 12 years is a long time to be working on a project,
and motivation is sometimes hard to come by.

Ed Landa, for taking a chance on a grad school dropout, and for
giving me the opportunity to release MySQLdb under an open
source license.

Laura Michaletz, who encourages me and somehow manages to
make me feel like a superstar.

And my wife, Wendy, for being there for me for three decades.
Geert JM Vanderkelen is a member of the MySQL Support Team at Sun, a
wholly-owned subsidiary of Oracle. He is based in Germany and has worked for
MySQL AB since April, 2005. Before joining MySQL he worked as developer, DBA
and SysAdmin for various companies in Belgium and Germany. Today Geert
specializes in MySQL Cluster and works together with colleagues around the world
to ensure continued support for both customers and community. Geert is also the
maintainer of MySQL Connector/Python.
Table of Contents
Preface 1
Chapter 1: Getting Up and Running with MySQL for Python 7
Getting MySQL for Python 7
Using a package manager (only on Linux) 8
Using RPMs and yum 9
Using RPMs and urpm 9
Using apt tools on Debian-like systems 9
Using an installer for Windows 10
Using an egg file 10
Using a tarball (tar.gz file) 14
Importing MySQL for Python 17
Accessing online help when you need it 18
MySQLdb 18
_mysql 19
Connecting with a database 20
Creating a connection object 20
Creating a cursor object 22
Interacting with the database 22
Closing the connection 23
Multiple database connections 23
Summary 24
Chapter 2: Simple Querying 25
A brief introduction to CRUD 25
Forming a query in MySQL 26
SELECT 27
* (asterisk) 27
FROM 28
staff 28
; (semicolon) 29
Table of Contents
Other helpful quantifiers 29
WHERE 30
GROUP BY 30
HAVING 32
ORDER BY 33
LIMIT 35
INTO OUTFILE 37
Passing a query to MySQL 37
A simple SELECT statement 38
Modifying the results 39
Using user-defined variables 40
Determining characteristics of a database and its tables 41
Determining what tables exist 42
Assigning each table a number 43
Offering the options to the user 43
Allowing the user to detail a search query 44
Changing queries dynamically 45
Pattern matching in MySQL queries 45
Putting it into practice 46
Project: A command-line search utility 48
Preparing a database for searching 49
Planning your work, then working your plan 50
Develop a well-abstracted search functionality 50
Specifying the search term from the command-line 52
Implementing and incorporating the other functions: -t, -f, and -o 55
Including an option for an output file 57
Room to grow 57
Summary 58
Chapter 3: Simple Insertion 59
Forming a MySQL insertion statement 60
INSERT 60
INTO 61
Table name 61
Column names 61
VALUES 63
<some values> 64
; (semicolon) 66
Helpful ways to nuance an INSERT statement 66
INSERT...SELECT... 66
INSERT DELAYED… 70
INSERT...ON DUPLICATE KEY UPDATE... 71
[ ii ]
Table of Contents
Passing an insertion through MySQL for Python 72
Setting up the preliminaries 72
A simple INSERT statement 73
More complex INSERT commands 75
Using user-defined variables 75
Using metadata 77
Querying the database for its structure 78
Retrieving the table structure 80
Changing insertion values dynamically 82
Validating the value of name 83
Validating the value of price 83
Querying the user for a correction 84
Passing fish and price for validation 84
Essentials: close and commit 85
In need of some closure 85
What happened to commit? 85
Why are these essentials non-essential? 85
Project: A command-line insertion utility 86
The necessary modules 86
The main() thing 87
Coding the flag system 88
Testing the values passed by the user 88
Try to establish a database connection 89
Showing the tables 90
Showing the table structure, if desired 90
Accepting user input for the INSERT statement 91
Building the INSERT statement from the user input and executing it 92
Committing changes and closing the connection 93
Coding the other functions 93
valid_digit() and valid_string() 93
valid_table() 94
query() 94
Calling main() 95
Room to grow 99
Summary 100
Chapter 4: Exception Handling 101
Why errors and warnings are good for you 101
Errors versus warnings: There's a big difference 104
The two main errors in MySQLdb 104
DatabaseError 105
InterfaceError 105
Warnings in MySQL for Python 105
[ iii ]