MySQL for Python

MySQL for Python
eBook: $29.99
Formats: PDF, PacktLib, ePub and Mobi formats
save 15%!
Print + free eBook + free PacktLib access to the book: $79.98    Print cover: $49.99
save 37%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Table of Contents
Sample Chapters
  • Implement the outstanding features of Python's MySQL library to their full potential
  • See how to make MySQL take the processing burden from your programs
  • Learn how to employ Python with MySQL to power your websites and desktop applications
  • Apply your knowledge of MySQL and Python to real-world problems instead of hypothetical scenarios
  • A manual packed with step-by-step exercises to integrate your Python applications with the MySQL database server

Book Details

Language : English
Paperback : 440 pages [ 235mm x 191mm ]
Release Date : September 2010
ISBN : 1849510180
ISBN 13 : 9781849510189
Author(s) : Albert Lukaszewski, PhD
Topics and Technologies : All Books, Big Data and Business Intelligence, Oracle Other, MySQL, Open Source, Python

Table of Contents

Chapter 1: Getting Up and Running with MySQL for Python
Chapter 2: Simple Querying
Chapter 3: Simple Insertion
Chapter 4: Exception Handling
Chapter 5: Results Record-by-Record
Chapter 6: Inserting Multiple Entries
Chapter 7: Creating and Dropping
Chapter 8: Creating Users and Granting Access
Chapter 9: Date and Time Values
Chapter 10: Aggregate Functions and Clauses
Chapter 11: SELECT Alternatives
Chapter 12: String Functions
Chapter 13: Showing MySQL Metadata
Chapter 14: Disaster Recovery
  • Chapter 1: Getting Up and Running with MySQL for Python
    • Getting MySQL for Python
      • Using a package manager (only on Linux)
        • Using RPMs and yum
        • Using RPMs and urpm
        • Using apt tools on Debian-like systems
        • Using an installer for Windows
        • Using an egg file
        • Using a tarball (tar.gz file)
    • Importing MySQL for Python
      • Accessing online help when you need it
      • MySQLdb
      • _mysql
    • Connecting with a database
      • Creating a connection object
      • Creating a cursor object
      • Interacting with the database
      • Closing the connection
    • Multiple database connections
    • Summary
  • Chapter 2: Simple Querying
    • A brief introduction to CRUD
    • Forming a query in MySQL
      • SELECT
      • * (asterisk)
      • FROM
      • staff
      • ; (semicolon)
      • Other helpful quantifiers
        • WHERE
        • GROUP BY
        • HAVING
        • ORDER BY
        • LIMIT
        • INTO OUTFILE
    • Passing a query to MySQL
      • A simple SELECT statement
      • Modifying the results
    • Using user-defined variables
    • Determining characteristics of a database and its tables
      • Determining what tables exist
      • Assigning each table a number
      • Offering the options to the user
      • Allowing the user to detail a search query
    • Changing queries dynamically
      • Pattern matching in MySQL queries
      • Putting it into practice
    • Project: A command-line search utility
      • Preparing a database for searching
      • Planning your work, then working your plan
        • Develop a well-abstracted search functionality
      • Specifying the search term from the command-line
      • Implementing and incorporating the other functions: -t, -f, and -o
      • Including an option for an output file
    • Room to grow
    • Summary
  • Chapter 3: Simple Insertion
    • Forming a MySQL insertion statement
      • INSERT
      • INTO
      • Table name
      • Column names
      • VALUES
      • <some values>
      • ; (semicolon)
    • Helpful ways to nuance an INSERT statement
      • INSERT...SELECT...
    • Passing an insertion through MySQL for Python
      • Setting up the preliminaries
      • A simple INSERT statement
      • More complex INSERT commands
    • Using user-defined variables
    • Using metadata
      • Querying the database for its structure
      • Retrieving the table structure
    • Changing insertion values dynamically
      • Validating the value of name
      • Validating the value of price
      • Querying the user for a correction
      • Passing fish and price for validation
    • Essentials: close and commit
      • In need of some closure
      • What happened to commit?
      • Why are these essentials non-essential?
    • Project: A command-line insertion utility
      • The necessary modules
      • The main() thing
        • Coding the flag system
        • Testing the values passed by the user
        • Try to establish a database connection
        • Showing the tables
        • Showing the table structure, if desired
        • Accepting user input for the INSERT statement
        • Building the INSERT statement from the user input and executing it
        • Committing changes and closing the connection
      • Coding the other functions
        • valid_digit() and valid_string()
        • valid_table()
        • query()
      • Calling main()
      • Room to grow
    • Summary
  • Chapter 4: Exception Handling
    • Why errors and warnings are good for you
    • Errors versus warnings: There's a big difference
    • The two main errors in MySQLdb
      • DatabaseError
      • InterfaceError
    • Warnings in MySQL for Python
    • Handling exceptions passed from MySQL
      • Python exception-handling
      • Catching an exception from MySQLdb
      • Raising an error or a warning
      • Making exceptions less intimidating
    • Catching different types of exceptions
      • Types of errors
        • DataError
        • IntegrityError
        • InternalError
        • NotSupportedError
        • OperationalError
        • ProgrammingError
      • Customizing for catching
        • Catching one type of exception
        • Catching different exceptions
        • Combined catching of exceptions
        • Raising different exceptions
    • Creating a feedback loop
    • Project: Bad apples
      • The preamble
      • Making the connection
      • Sending error messages
        • The statement class
      • The main() thing
        • Try, try again
        • If all else fails
      • Room to grow
    • Summary
  • Chapter 5: Results Record-by-Record
    • The problem
    • Why?
      • Computing resources
        • Local resources
        • Web applications
      • Network latency
        • Server-client communications
        • Apparent responsiveness
      • Pareto's Principle
    • How?
      • The fetchone() method
      • The fetchmany() method
      • Iteration: What is it?
      • Generating loops
        • while...if loops
        • The for loop
      • Iterators
        • Illustrative iteration
      • Iteration and MySQL for Python
      • Generators
        • Using fetchone() in a generator
        • Using fetchmany() in a generator
    • Project: A movie database
      • Getting Sakila
      • Creating the Sakila database
      • The structure of Sakila
      • Planning it out
      • The SQL statements to be used
        • Returning the films of an actor
        • Returning the actors of a film
      • Accepting user data
      • A MySQL query with class
        • The __init__ method: The consciousness of the class
        • Setting the query's type
        • Creating the cursor
        • Forming the query
        • Executing the query
      • Formatting the results
        • Formatting a sample
        • Formatting a larger set of results
      • The main() thing
      • Calling main()
      • Running it
      • Room to grow
    • Summary
  • Chapter 6: Inserting Multiple Entries
    • The problem
      • Why not a MySQL script?
        • Lack of automation
        • Debugging the process
      • Why not iterate?
        • A test sample: Generating primes
        • Comparing execution speeds
    • Introducing the executemany() method
    • executemany(): Basic syntax
      • executemany(): Multiple INSERT statements
      • executemany(): Multiple SELECT statements
    • executemany(): Behind the scenes
      • MySQL server has gone away
        • Command-line option configuration
        • Using a configuration file
        • More than 16 MB is often unnecessary
    • Project: Converting a CSV file to a MySQL table
      • The preamble
      • The options
      • Defining the connection
      • Creating convert
      • The main() function
      • Calling main()
      • Room to grow
    • Summary
  • Chapter 7: Creating and Dropping
    • Creating databases
      • Test first, create second
      • CREATE specifications
        • Specifying the default character set
      • Specifying the collation for a database
        • Declaring collation
        • Finding available character sets and collations
    • Removing or deleting databases
      • Avoiding errors
      • Preventing (illegal) access after a DROP
    • Creating tables
      • Covering our bases
      • Avoiding errors
      • Creating temporary tables
    • Dropping tables
      • Playing it safe
      • Avoiding errors
      • Removing user privileges
    • Doing it in Python
      • Creating databases with MySQLdb
        • Testing the output
        • Dynamically configuring the CREATE statement
      • Dropping databases with MySQLdb
      • Creating tables in Python
      • Verifying the creation of a table
      • Another way to verify table creation
    • Dropping tables with MySQLdb
    • Project: Web-based administration of MySQL
      • CGI vs PHP: What is the difference?
      • Basic CGI
      • Using PHP as a substitute for CGI
        • CGI versus PHP: When to use which?
      • Some general considerations for this program
      • Program flow
      • The basic menu
        • Authorization details
        • Three operational sections of the dialogue
        • The variables
      • Planning the functions
      • Code of each function
        • Connecting without a database
        • Connecting with a database
        • Database action
        • Table action
        • Query action
        • execute()
      • The HTML output
        • Basic definition
        • The message attribute
        • Defining header()
        • Defining footer()
        • Defining body()
        • Defining page()
      • Getting the data
        • Using CGI
        • Using PHP
      • Defining main()
      • Room to grow
    • Summary
  • Chapter 8: Creating Users and Granting Access
    • A word on security
    • Creating users in MySQL
      • Forcing the use of a password
      • Restricting the client's host
    • Creating users from Python
    • Removing users in MySQL
    • DROPping users in Python
    • GRANT access in MySQL
      • Important dynamics of GRANTing access
      • The GRANT statement in MySQL
      • Using REQUIREments of access
      • Using a WITH clause
    • Granting access in Python
    • Removing privileges in MySQL
      • Basic syntax
      • After using REVOKE, the user still has access!?
    • Using REVOKE in Python
    • Project: Web-based user administration
      • New options in the code
      • Adding the functions: CREATE and DROP
      • Adding CREATE and DROP to main()
      • Adding the functions: GRANT and REVOKE
      • Adding GRANT and REVOKE to main()
      • Test the program
      • New options on the page
      • Room to grow
    • Summary
  • Chapter 9: Date and Time Values
    • Date and time data types in MySQL
      • DATETIME
        • Output format
        • Input formats
        • Input range
        • Using DATETIME in a CREATE statement
      • DATE
        • Output and Input formats
        • Input range
        • Input of values
        • Range
        • Defaults, initialization, and updating
      • YEAR
        • Two-digit YEAR values
        • Four-digit YEAR values
        • Valid input
      • TIME
        • Format
        • Invalid values
    • Date and time types in Python
    • Date and time functions
      • NOW()
      • CURDATE()
      • CURTIME()
      • DATE()
      • DATE_SUB() and DATE_ADD()
      • DATEDIFF()
      • DATE_FORMAT()
      • EXTRACT()
      • TIME()
    • Project: Logging user activity
      • The log framework
      • The logger() function
        • Creating the database
        • Using the database
        • Creating the table
        • Forming the INSERT statement
      • Ensure logging occurs
      • Room to grow
    • Summary
  • Chapter 10: Aggregate Functions and Clauses
    • Calculations in MySQL
      • COUNT()
      • SUM()
      • MAX()
      • MIN()
      • AVG()
        • The different kinds of average
    • Trimming results
      • DISTINCT
      • GROUP_CONCAT()
        • Specifying the delimiter
        • Customizing the maximum length
        • Using GROUP_CONCAT() with DISTINCT
    • Server-side sorting in MySQL
      • GROUP BY
      • ORDER BY
        • Using a universal quantifier
        • Sorting alphabetically or from low-to-high
        • Reversing the alphabet or sorting high-to-low
        • Sorting with multiple keys
    • Putting it in Python
    • Project: Incorporating aggregate functions
      • Adding to qaction()
        • New variables
        • New statement formation
      • Revising main()
      • Setting up the options
      • Changing the HTML form
    • Summary
  • Chapter 11: SELECT Alternatives
    • HAVING clause
      • WHERE versus HAVING: Syntax
      • WHERE versus HAVING: Aggregate functions
      • WHERE versus HAVING: Application
    • Subqueries
    • Unions
    • Joins
      • LEFT and RIGHT joins
      • OUTER joins
      • INNER joins
      • NATURAL joins
      • CROSS joins
    • Doing it in Python
      • Subqueries
      • Unions
      • Joins
    • Project: Implement HAVING
      • Revising the Python backend
        • Revising qaction()
        • Revising main()
        • Revising the options
      • Revising the HTML interface
      • Room to grow
    • Summary
  • Chapter 12: String Functions
    • Preparing results before their return
      • CONCAT() function
      • SUBSTRING() or MID()
      • TRIM()
        • Basic syntax
        • Options
        • Alternatives
      • REPLACE()
      • INSERT()
      • REGEXP
    • Accessing and using index data
      • LENGTH()
      • INSTR() or LOCATE()
        • INSTR()
        • LOCATE()
    • Nuancing data
      • ROUND()
      • FORMAT()
      • UPPER()
      • LOWER()
    • Project: Creating your own functions
      • Hello()
      • Capitalise()
        • DELIMITER
        • The function definition
        • Calling the function
        • Defining the function in Python
        • Defining the function as a Python value
        • Sourcing the MySQL function as a Python module
        • Sourcing the function as MySQL code
        • Room to grow
    • Summary
  • Chapter 13: Showing MySQL Metadata
    • MySQL's system environment
      • ENGINE
        • The most popular engines
        • Transactions
        • Specifying the engine
        • ENGINE status
      • Profiling
        • SHOW PROFILE
      • SHOW system variables
    • Accessing database metadata
        • Using the USE command
      • Accessing metadata about tables
        • SHOW TABLES
        • Showing columns from a table
    • Accessing user metadata
    • Project: Building a database class
      • Writing the class
        • Defining fetchquery() and some core methods
        • Retrieving table status and structure
        • Retrieving the CREATE statements
      • Define main()—part 1
      • Writing resproc()
      • Define main()—part 2
      • The preamble
        • Modules and variables
        • Login and USE
      • Closing out the program
      • Room to grow
    • Summary
  • Chapter 14: Disaster Recovery
    • Every database needs a backup plan
      • Offline backups
      • Live backups
    • Choosing a backup method
      • Copying the table files
        • Locking and flushing
        • Unlocking the tables
        • Restoring the data
      • Delimited backups within MySQL
        • Using SELECT INTO OUTFILE to export data
        • Using LOAD DATA INFILE to import data
      • Archiving from the command line
        • mysqldump
        • mysqlhotcopy
    • Backing up a database with Python
    • Summary

Albert Lukaszewski, PhD

Albert Lukaszewski is a principal consultant for Lukaszewski Consulting Services in southeast Scotland. He has programmed computers for nearly 30 years. Much of his experience is 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.

Sorry, we don't have any reviews for this title yet.

Code Downloads

Download the code and support files for this book.

Submit Errata

Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.


- 1 submitted: last submission 04 Feb 2014

Errata type: Code     |      Page number: 49

In the Preparing a database for searching section, first numbered bullet


Instead of:

CREATE world;


It should be:


Sample chapters

You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

Frequently bought together

MySQL for Python +    JIRA 5.x Development Cookbook =
50% Off
the second eBook
Price for both: $45.60

Buy both these recommended eBooks together and get 50% off the cheapest eBook.

What you will learn from this book

  • Explore the various means to install MySQL for Python, from using an egg to unrolling a tarball
  • Query the database and retrieve records through MySQL for Python
  • Implement insertion of data into a MySQL database in Python
  • Carry out error-handling in MySQL for Python in order to ensure the robustness of programs
  • Use secure logging techniques to record how your users use your programs
  • Carry out record-by-record retrieval to save a lot of overhead while retrieving data
  • Handle insertion of large amounts of data using iteration and the executemany function
  • Automate the creation and removal of databases and tables using MySQL for Python
  • Use the MySQL for Python library to automate user creation and to administer access controls
  • Log user activity with MySQL for Python by using MySQL's date and time support
  • See how to revise database programs to include more functionality
  • Use aggregate functions to make MySQL take the burden off your web server
  • Save time and processing resources by scripting JOINs and subqueries

In Detail

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.

A practical manual packed with step-by-step examples to manage your MySQL database efficiently through Python


This is a practical, tutorial-style book that includes many examples to demonstrate the full potential of MySQL for Python. Every chapter starts with an explanation of the various areas for using MySQL for Python and ends with work on a sample application using the programming calls just learned. All complicated concepts are broken down to be very easy to understand.

Everything in the book is designed to help you learn and use MySQL for Python to address your programming needs in the fastest way possible.

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.

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software