Book Image

MySQL for Python

By : Albert Lukaszewski
Book Image

MySQL for Python

By: Albert Lukaszewski

Overview of this book

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.

Related Content you might be interested in

Current Title:

Small book image
MySQL for Python
Albert Lukaszewski
Sep, 2010 | 440 pages
No titles found
Table of Contents (20 chapters)
MySQL for Python
MySQL for Python
Credits
Credits
About the Author
About the Author
About the Reviewers
About the Reviewers
Preface
Preface
Free Chapter
1
Getting Up and Running with MySQL for Python
Getting Up and Running with MySQL for Python
Getting MySQL for Python
Importing MySQL for Python
Connecting with a database
Multiple database connections
Summary
2
Simple Querying
Simple Querying
A brief introduction to CRUD
Forming a query in MySQL
Passing a query to MySQL
Using user-defined variables
Determining characteristics of a database and its tables
Changing queries dynamically
Project: A command-line search utility
Room to grow
Summary
3
Simple Insertion
Simple Insertion
Forming a MySQL insertion statement
Helpful ways to nuance an INSERT statement
Passing an insertion through MySQL for Python
Using user-defined variables
Using metadata
Changing insertion values dynamically
Essentials: close and commit
Project: A command-line insertion utility
Summary
4
Exception Handling
Exception Handling
Why errors and warnings are good for you
Errors versus warnings: There's a big difference
The two main errors in MySQLdb
Warnings in MySQL for Python
Handling exceptions passed from MySQL
Catching different types of exceptions
Creating a feedback loop
Project: Bad apples
Summary
5
Results Record-by-Record
Results Record-by-Record
The problem
Why?
How?
Project: A movie database
Summary
6
Inserting Multiple Entries
Inserting Multiple Entries
The problem
Introducing the executemany() method
executemany(): Basic syntax
executemany(): Behind the scenes
Project: Converting a CSV file to a MySQL table
Summary
7
Creating and Dropping
Creating and Dropping
Creating databases
Removing or deleting databases
Creating tables
Dropping tables
Doing it in Python
Dropping tables with MySQLdb
Project: Web-based administration of MySQL
Summary
8
Creating Users and Granting Access
Creating Users and Granting Access
A word on security
Creating users in MySQL
Creating users from Python
Removing users in MySQL
DROPping users in Python
GRANT access in MySQL
Granting access in Python
Removing privileges in MySQL
Using REVOKE in Python
Project: Web-based user administration
Summary
9
Date and Time Values
Date and Time Values
Date and time data types in MySQL
Date and time types in Python
Date and time functions
Project: Logging user activity
Summary
10
Aggregate Functions and Clauses
Aggregate Functions and Clauses
Calculations in MySQL
Trimming results
Server-side sorting in MySQL
Putting it in Python
Project: Incorporating aggregate functions
Summary
11
SELECT Alternatives
SELECT Alternatives
HAVING clause
Subqueries
Unions
Joins
Doing it in Python
Project: Implement HAVING
Summary
12
String Functions
String Functions
Preparing results before their return
Accessing and using index data
Nuancing data
Project: Creating your own functions
Summary
13
Showing MySQL Metadata
Showing MySQL Metadata
MySQL's system environment
Accessing database metadata
Accessing user metadata
Project: Building a database class
Summary
14
Disaster Recovery
Disaster Recovery
Every database needs a backup plan
Choosing a backup method
Backing up a database with Python
Summary
Index
Index

Doing it in Python

As we have seen in previous chapters, processing a SELECT query in Python is as simple as execute() and fetchall(). However, Python also allows us to build statements dynamically, and this applies to joins, unions, and subqueries as well.

Subqueries

If we want column from table1, but the column reference for the subquery is colref, from both table1 and table2, we can write the following:

#!/usr/bin/env python

import MySQLdb

mydb = MySQLdb.connect('localhost', 'skipper', 'secret', 'sakila')
cursor = mydb.cursor()

table1 = 'film'
table2 = 'film_actor'
column = 'film_id, title'
colref = 'film_id'

statement = "SELECT %s FROM %s WHERE %s IN (SELECT %s FROM %s)" %(column, table1, colref, colref, table2)

cursor.execute(statement)
results = cursor.fetchall()

for i in results: print i[0], '\t', i[1]

The results obviously will be the title and identifier for each title in film. We can further nuance this for reader input to allow searches by the name of the actor.

#!/usr/bin/env...
Previous Section
End of Section 6
Next Section