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

Changing queries dynamically

But what if the user does not want to submit a precise query but needs a list of the possibilities? There are a couple of ways to clarify the search. We could first keep a list of the common search queries. This is something done often by the likes of Google and Yahoo!. This works very well with large datasets served through web servers because it uses a static list of terms and simply culls them out. For more dedicated applications, one can use MySQL's pattern matching ability to present known options on-the-fly.

Pattern matching in MySQL queries

Where Python's regular expression engine is very robust, MySQL supports the two following metacharacters for forming regular expressions:

  • %: Zero or more characters matched in aggregate

  • _ : Any single character matched individually

Pattern matching is always a matter of comparison. Therefore, with either of these, never use operators of equality.

SELECT * FROM menu WHERE name = 's%'; 		        WRONG
SELECT * FROM menu WHERE...
Previous Section
End of Section 7
Next Section