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

Creating users in MySQL

To create a user in MySQL, our user account must have the universal CREATE USER privilege. In general, no user beyond the database administrator should have this as it allows the user to create, remove, rename, and revoke the privileges of users on the database. We will look at the granting of privileges later in this chapter.

Alternatively, if a user has universal INSERT privileges, that user can insert a new user and relevant data into the user table of the mysql database. This method is prone to error and can endanger the stability of the entire MySQL installation if something goes wrong. Therefore, we do not deal with it here.

When creating a user, we need to specify both the user's name or ID and the user's password. The basic syntax for user creation is as follows:

CREATE USER <userid>;

Breaking the statement up by its token, we first tell MySQL that we want to CREATE something. Then we clarify the object being created as a USER account. That account should...

Previous Section
End of Section 3
Next Section