MySQL for Python

Albert Lukaszewski, PhD

• Publisher: Packt Publishing
• Year: 2010
• ISBN:
  9781849510189
• Edition: 1
• Topic: Informatik
• Subtopic: Programmierung in SQL

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

Approach

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.

MySQL for Python

---

Table of Contents

MySQL for Python

Credits

About the Author

About the Reviewers

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Errata

Piracy

Questions

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

Installing egg handling software

Using a package manager (Linux)

Without a package manager (Mac, Linux)

On Microsoft Windows

Installing MySQL for Python from 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

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

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...

INSERT DELAYED…

INSERT...ON DUPLICATE KEY UPDATE...

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

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 __init__ method

Storing the statement type

Forming the statement

Execute the MySQL statement

Handling any fallout

The main() thing

Try, try again

If all else fails

Room to grow

Summary

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...