Mysql for python

Mysql for pythonMysql for pythonMysql for pythonMy SQL for PythonCopyright 2010 Packt PublishingAll rights reserved. No part of this book may be reproduced, stored in a retrievalsystem, or transmitted in any form or by any means, without the prior writtenpermission of the publisher, except in the case of brief quotations embedded incritical articles or reviewsEvery effort has been made in the preparation of this book to ensure the accuracyinformation presented. However the information contained in this book issold without warranty, either express or implied. Neither the author, nor PacktPublishing, and its dealers and distributors will be held liable for any damagescaused or alleged to be caused directly or indirectly by this bookPackt Publishing has endeavored to provide trademark information about all of thecompanies and products mentioned in this book by the appropriate use of capitalsHowever, Packt Publishing cannot guarantee the accuracy of this information.First published: September 2010Production reference: 1160910Published by Packt Publishing Ltd.32 Lincoln roadOltonBirmingham, B27 6PA, UKIsBN978-1-849510-189www.packtpub.comCover Image by Vinayak Chittar (vinayak. chittaragmail comCreditsAuthorEditorial Team LeaderAlbert LukaszewskiAanchal kumarReviewersProject Team LeaderCHPriya MukherjiAndy dustmanGeert JM VanderkeleProject CoorPrasad raiAcquisition EditorSteven WildingProofreaderAaron nashDevelopment EditorWilson dsouzaProduction coordinatorShantanu AgadeTechnical editorsPrashant machCover workShantanu AgadeCharumati shankaranHemanAbout the authorAlbert Lukaszewski is principal consultant for Lukaszewski Consulting Servicesin southeast Scotland. He has programmed computers for 30 years. Much of hisexperience has related to text processing, database systems, and Natural Languageprocessing(NLP). Currently he consults on database applications for companies inthe financial and publishing industriesIn addition to MysQL for Python, Albert Lukaszewski has also written"AboutPython",acolumnfortheNewYorkTimessubsidiary,About.com.Many people had a hand in this work beyond my typing atthe keyboard. Some contributed by their effort and others byytheir sacrifice. Thanks to the team at packt for their consistentunderstanding and support. I am particularly thankful to StevenWilding for help and support above and beyond the call of dutyThanks also to Andy Dustman, Geert Vanderkelen, and Swaroop fortheir helpful review of this book and for making so many significantand helpful recommendations. This book would be much the poorerwere it not for their suggestionsTo Richard Goodrich, who first introduced me to Python, thank youfor liberating me from bondage to that other P-language Funnywhat a little problem can lead toMy heartfelt thanks and appreciation go to my wife, Michelle, andmy sons, Cyrus and Jacob. The latter was born during the writingof this book and consistently brightens even the darkest scottishweather with his smile. I appreciate your sacrifice. I could not havewritten this book without your supportFinally, my thanks to my brother Larry who first introduced me tothe world of computing. I would probably not know anything aboutcomputer programming if you had not left me your TRS-80. So thisis all your fault, and I am glad you did it.About the ReviewersSwaroop C Hhas previously worked at Yahoo! and Adobe, has co-founded astartup, has written two technical books (one of which is used as a text book inmore than ten universities worldwide), writes a popular blog that has beenconsistently rated one of the top ten blogs in India, and is a marathonerMoredetailsathttp://www.swaroopch.com/about/.He has written two technical books-A Byte of Python and A Byte of Vim-beginnerbooks to Python and Vim respectively. They are freely available under the CreativeCommonslicenseonhiswebsitewww.swaroopch.com.AndyDustman(http://profiles.googlecom/farcepest)istheprimaryauthor of MysQLdb, the MysQL interface for PythonThe MySQL-Python project is supported and funded purely by volunteers anddonationsbytheusercommunityat(http://sourceforge.net/projects/mysqlpython/)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 hisspare time, he rides motorcyclesI would like to thank Kyle Vander Beek, who has recently become aco-developer on MySQLdb, and has helped to push me a bit to getsome things done. 12 years is a long time to be working on a project,and motivation is sometimes hard to come byEd Landa, for taking a chance on a grad school dropout, and forgiving me the opportunity to release MysQLdb under an opersource licenseLaura Michaletz, who encourages me and somehow manages tomake me feel like a superstarAnd my wife, Wendy, for being there for me for three decades.Geert JM Vanderkelen is a member of the My SQL Support Team at Sun, awholly-owned subsidiary of Oracle. He is based in germany and has worked forMySQL AB since April, 2005. Before joining MysQL he worked as developer, DBAand SysAdmin for various companies in Belgium and Germany. Today Geertpecializes in MysQL Cluster and works together with colleagues around the worldto ensure continued support for both customers and community. Geert is also themaintainer of My SQL Connector/PythonTable of contentsPrefaceChapter 1: Getting Up and Running with MysQL for PythonGetting MysQL for PythonUsing a package manager(only on LinuxUsing RPMs and yumUsing RPMs and upm778999Using apt tools on Debian-like systemsUsing an installer for WindowsUsing an egg fileUsing a tarball (tar gz file)14Importing My sQL for PythonAccessing online help when you need it4My SQLdb889Connecting with a databaseCreating a connection object20Creating a cursor objectInteracting with the databaseClosing the connection23Multiple database connections23Summary24Chapter 2: Simple Querying25a brief introduction to crudForming a query in My SQL26SELECTaster27FROM28staff28(semicolon)Table of ContentsOther helpful quantifiersWHEREGROUP BYHAVING32ORDER BY33LIMIT35INTO OUTFILE3Passing a query to MySQL3了A simple SELECT statementModifying the resultsUsing user-defined variablesDetermining characteristics of a database and its tablesDetermining what tables exist42Assigning each table a numberOffering the options to the user43Allowing the user to detail a search queryChanging queries dynamically45Pattern matching in MysQL queries45Putting it into practice46Project: A command-line search utility48Preparing a database for searching49Planning your work, then working your planDevelop a well-abstracted search functionality5Specifying the search term from the command-line5Implementing and incorporating the other functions: -t-f, and-055Including an option for an output file57Room to grow57Summary58Chapter 3: Simple Insertion59Forming a MysQl insertion statementINSERT61Table name61Column names61VALUEScsome values>semicolonf6Helpful ways to nuance an INSERT statement66INSERT SELECTINSERT DELAYED70INSERLON DUPLICATE KEY UPDATE71[i]Table of ContentsPassing an insertion through My SQL for PythonSetting up the preliminariesA simple INSERT statement73More complex INSERT commands75Using user-defined variables75Using metadataQuerying the database for its structure78Retrieving the table structure80Changing insertion values dynamically82Validating the value of nameValidating the value of price83Querying the user for a correction84Passing fish and price for validation84Essentials: close and commitIn need of some closure85What happened to commit?85Why are these essentials non-essential?Project: A command-line insertion utility86The necessary modules86The main( thingCoding the flag system8Testing the values passed by the userTry to establish a database connectionshowing the tablesshowing the table structure, if desired90Accepting user input for the INSERT statementgBuilding the INSERT statement from the user input and executing it92Committing changes and closing the connectiongCoding the other functionsvalid digit( and valid string)valid tablen94query94Calling main(95grTOwuma100Chapter 4: Exception Handling101Why errors and warnings are good for you101Errors versus warnings: Theres a big difference104The two main errors in MysQLdb104Database error10nterface error105Warnings in My SQL for Python105