39,59 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
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.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 552
Veröffentlichungsjahr: 2010
Ähnliche
Table of Contents
MySQL for Python
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
Albert Lukaszewski
Reviewers
Swaroop C H
Andy Dustman
Geert JM Vanderkelen
Acquisition Editor
Steven Wilding
Development Editor
Wilson D'souza
Technical Editors
Prashant Macha
Charumati Shankaran
Indexer
Hemangini Bari
Editorial Team Leader
Aanchal Kumar
Project Team Leader
Priya Mukherji
Project Coordinator
Prasad Rai
Proofreader
Aaron Nash
Production Coordinator
Shantanu Zagade
Cover Work
Shantanu Zagade
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.
Preface
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.
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.
What this book covers
Chapter 1, Getting Up and Running with MySQL for Python, helps you to install MySQL for Python specific software, how to import modules into your programs, connecting to a database, accessing online help, and creating a MySQL cursor proxy within your Python program. It also covers how to close the database connection from Python and how to access multiple databases within one program.
Chapter 2, Simple Querying, helps you to form and pass a query to MySQL, to look at user-defined variables, how to determine characteristics of a database and its tables, and program a command-line search utility. It also looks at how to change queries dynamically, without user input.
Chapter 3, Simple Insertion, shows forming and passing an insertion to MySQL, to look at the user-defined variables in a MySQL insertion, passing metadata between databases, and changing insertion statements dynamically without user input.
Chapter 4, Exception Handling, discusses ways to handle errors and warnings that are passed from MySQL for Python and the differences between them. It also covers several types of errors supported by MySQL for Python, and how to handle them effectively.
Chapter 5, Results Record-by-Record, shows situations in which record-by-record retrieval is desirable, to use iteration to retrieve sets of records in smaller blocks and how to create iterators and generators in Python. It also helps you in using fetchone() and fetchmany().
Chapter 6, Inserting Multiple Entries, discusses how iteration can help us execute several individual INSERT statements rapidly, when to use or avoid executemany(), and throttling how much data is inserted at a time.
Chapter 7, Creating and Dropping, shows to create and delete both databases and tables in MySQL, to manage database instances with MySQL for Python, and to automate database and table creation.
Chapter 8, Creating Users and Granting Access, focuses on creating and removing users in MySQL, managing database privileges with MySQL for Python, automating user creation and removal, to GRANT and REVOKE privileges, and the conditions under which that can be done.
Chapter 9, Date and Time Values, discusses what data types MySQL supports for date and time, when to use which data type and in what format and range, and frequently used functions for handling matters of date and time.
Chapter 10, Aggregate Functions and Clauses, shows how MySQL saves us time and effort by pre-processing data, how to perform several calculations using MySQL's optimized algorithms, and to group and order returned data by column.
Chapter 11, SELECT Alternatives, discusses how to use HAVING clauses, how to create temporary subtables, subqueries and joins in Python, and the various ways to join tables.
Chapter 12, String Functions, shows how MySQL allows us to combine strings and return the single, resulting value, how to extract part of a string or the location of a part, thus saving on processing, and how to convert cases of results.
Chapter 13, Showing MySQL Metadata, discusses the several pieces of metadata about a given table that we can access, which system variables we can retrieve, and how to retrieve user privileges and the grants used to give them.
Chapter 14, Disaster Recovery, focuses on when to implement one of several kinds of database backup plans, what methods of backup and disaster recovery MySQL supports, and how to use Python to back up databases
What you need for this book
The content of this book is written against MySQL 5.5, Python 2.5.2, and MySQL for Python 1.2.2. Development of the examples was done with MySQL 5.0, but everything was confirmed against the 5.5 documentation. As for operating systems, any of the main three will do: Microsoft Windows, Linux, or Mac. Any additional requirements of modules are discussed in the book as they come up.
Who this book is for
This book is meant for intermediate users of Python who want hassle-free access to their MySQL database through Python. If you are a Python programmer who wants database-support in your Python applications, then this book is for you. This book is a must-read for every focused user of the MySQL for Python library who wants real-world applications using this powerful combination of Python and MySQL.
Reader feedback
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title via the subject of your message.
If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or e-mail <[email protected]>.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book on, see our author guide on www.packtpub.com/authors.
Customer support
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Errata
Note
Downloading the example code for this book
You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
Questions
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.
Importing MySQL for Python
The name of the project MySQL for Python is the current version of a project that began under the rubric MySQLdb. Consequently, unlike most Python modules, the MySQL for Python module is not called by its name, but by its historic handle. To import the module, insert the following into a Python program or simply type it in a following Python shell:
To make working with the module easier, you can also import it with an alias:
This allows us to use mysql instead of MySQLdb when we access parts of the module.
When you do this, several things will occur. You need not be concerned about most of them, but you should be aware that MySQLdb depends upon a module called _mysql. The _mysql module is largely a Python adaptation of the MySQL C API.
Tip
This is important to note because it is this API that you will access through MySQL for Python.
MySQL for Python is a wrapper for accessing the _mysql API. A wrapper is essentially a system of macros, or trusted code, that allows you to do common tasks quickly. It allows you to program without having to repeat commonly used or accessed variables and functions. The _mysql module is a powerful and proven way of accessing a MySQL database. However, controlling it within a Python program can pose a challenge for some, like driving a Formula 1 car for the first time. So consider MySQL for Python as a system that allows you to harness the power of a Formula 1 racing car even if you're merely driving a Hyundai.
Unlike some systems of macros, MySQL for Python still allows you to access the classes and functions of _mysql. This is due to the nature of Python's import functionality.
Accessing online help when you need it
As with other modules, Python is able to provide online help about MySQL for Python. In the following sections, we look at the MySQLdb and _mysql modules in greater depth using Python's built-in help() function.
MySQLdb
After importing MySQLdb, you can read over the documentation that accompanies the module. In a Python shell, type:
You will then see a manual page detailing all of the functions and classes of MySQL for Python. It is well worth giving this a cursory read to familiarize yourself with the module. In the course of this book, we will cover most of these items from various angles.
As the help page indicates, MySQLdb includes the following modules:
Each of these is abstracted to the point of its own module in the source tree. Without a doubt, the most important part of the module is connections.py, without which we could not interface with MySQL. Where the others are static, the conversion module, convertors.py, allows you to define your own convertor on-the-fly.
The MySQLdb module itself has only one operating class that does not pertain to errors—DBAPISet. This is MySQLdb's internal object class for processing data. To interface with MySQL, however, we use functions. Of the several listed at the end of the MySQLdb help page, one uses connect() in every MySQLdb program.
At first glance, it may here be confusing to see that MySQLdb seems to have three ways of connecting with a database. In the list of functions, these are as follows:
Knowing the ins and outs of these functions is not necessary. It is, however, important to know that they exist and to recognize that the latter two are simply different ways of transferring data to the first. Connect() then passes the arguments to the connections. Connection() class, MySQLdb's MySQL database connection class, in the connections.py module.
_mysql
In looking over the module, you may also note that reference is made to the _mysql module, but it is not explicitly detailed. This is because it is a dependency and not part of the module itself. However, you can access the documentation for _mysql without importing it directly by using the MySQLdb namespace:
In the previous discussion about connections.Connection(), we stopped following the trail of the connection and any ensuing data transmission where MySQLdb stopped. In reality, however, the data does not stop there. When a connection or operational request is received by connections.Connection(), it is processed and passed to _mysql and subsequently to the MySQL API in C to perform it.
To handle this interface, _mysql uses two classes:
The first is used to establish communication with MySQL and thus returns a connection object. The second, as the name implies, returns a set containing the results from a MySQL command that a program sends. These results can be either the query results or an error. _mysql naturally passes the error to the calling process. In the case of MySQLdb, we then have a comprehensive toolbox to handle the errors that may arise.
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.
Chapter 2. Simple Querying
Record retrieval is without doubt the most common activity employed with regard to MySQL and other relational databases. Like most computer programs, MySQL functions on the basis of being invoked with parameters and returning results in accordance with them. As we seen, Python acts as an intermediary to that process. We can use it to access MySQL, login, and connect to a database of our choice.
In this chapter, we will look at the following:
Working through each of these points will help you at the end of the chapter, when we get to the project: a command-line search tool.
A brief introduction to CRUD
The four basic functions of any persistent storage system like MySQL spell CRUD:
These are key concepts, which each of the basic MySQL commands reflect.
There is nothing technical about the words themselves, but the concepts are very important. They represent the four activities that you can expect to be able to do in every relational database system you use. There are several alternatives to this acronym and keyword series (for example, SCUD for "select, create, update, and delete" or SIDU for "select, insert, delete, and update"). The point of each of these is that database functionality boils down to two sets of opposing activities:
Each of these will be addressed in the coming chapters. In this one, we start with reading data using SELECT.
Forming a query in MySQL
In order to best understand how to submit a query through MySQL for Python, it is important to ensure you understand how to submit a query in MySQL itself. The similarities between the two outnumber the differences, but the first may seem confusing if you don't properly understand the second.
MySQL statements have a basic structure. In following a set structure, they are formed like natural language statements. Being a computer program, it understandably responds very poorly to informational statements and only moderately well to questions. Almost all MySQL statements have an imperatival tone, expressing your command. This is reflective of the client-server relationship. The computer is the servant who exists to do the bidding of yourself as the client or, if you prefer, master.
The syntactic structure of a simple MySQL statement is not that different from the language you use every day. Where English would have:
MySQL would need to hear:
Let's look at the MySQL statement, comparing it to the English in detail.
SELECT
MySQL does not support natural language searching like Give me. Rather, like other programming languages including Python, MySQL has a set of reserved key words. These are largely single synonyms for common, core actions. For data retrieval, the key word is SELECT. It could have been GIMME or any of a score of similar ways of saying the same thing, but MySQL is consonant with the Zen of Python:
There should be one—and preferably only one—obvious way to do it
Therefore, the MySQL developers settled on a single keyword—one that just happens to be compliant with the SQL standard.
* (asterisk)
Being read up on your regular expressions, I am sure you recognize this universal quantifier. While it is one of the most commonly used, MySQL supports several metacharacters that you can use to nuance your searches.
Note
MySQL supports different kinds of metacharacters in different contexts. The following is a full list of metacharacters. (Note that not all of them may be supported in a given situation.)
