Download
Introduction
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.
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.
What this book covers
Chapter 1, Getting Up and Running with MySQL for Python, helps you to install MySQL for Python specific software, how to import modules into your programs, connecting to a database, accessing online help, and creating a MySQL cursor proxy within your Python program. It also covers how to close the database connection from Python and how to access multiple databases within one program.
Chapter 2, Simple Querying, helps you to form and pass a query to MySQL, to look at user-defined variables, how to determine characteristics of a database and its tables, and program a command-line search utility. It also looks at how to change queries dynamically, without user input.
Chapter 3, Simple Insertion, shows forming and passing an insertion to MySQL, to look at the user-defined variables in a MySQL insertion, passing metadata between databases, and changing insertion statements dynamically without user input.
Chapter 4, Exception Handling, discusses ways to handle errors and warnings that discusses ways to handle errors and warnings that to handle errors and warnings that are passed from MySQL for Python and the differences between them. It also covers several types of errors supported by MySQL for Python, and how to handle them effectively.
Chapter 5, Results Record-by-Record, shows situations in which record-by-record retrieval is desirable, to use iteration to retrieve sets of records in smaller blocks and how to create iterators and generators in Python. It also helps you in using fetchone() and fetchmany().
Chapter 6, Inserting Multiple Entries, discusses how iteration can help us execute several individual INSERT statements rapidly, when to use or avoid executemany(), and throttling how much data is inserted at a time.
Chapter 7, Creating and Dropping, shows to create and delete both databases and tables in MySQL, to manage database instances with MySQL for Python, and to automate database and table creation.
Chapter 8, Creating Users and Granting Access, focuses on creating and removing users in MySQL, managing database privileges with MySQL for Python, automating user creation and removal, to GRANT and REVOKE privileges, and the conditions under which that can be done.
Chapter 9, Date and Time Values, discusses what data types MySQL supports for date and time, when to use which data type and in what format and range, and frequently used functions for handling matters of date and time.
Chapter 10, Aggregate Functions and Clauses, shows how MySQL saves us time and effort by pre-processing data, how to perform several calculations using MySQL's optimized algorithms, and to group and order returned data by column.
Chapter 11, SELECT Alternatives, discusses how to use HAVING clauses, how to create temporary subtables, subqueries and joins in Python, and the various ways to join tables.
Chapter 12, String Functions, shows how MySQL allows us to combine strings and return the single, resulting value, how to extract part of a string or the location of a part, thus saving on processing, and how to convert cases of results.
Chapter 13, Showing MySQL Metadata, discusses the several pieces of metadata about a given table that we can access, which system variables we can retrieve, and how to retrieve user privileges and the grants used to give them.
Chapter 14, Disaster Recovery, focuses on when to implement one of several kinds of database backup plans, what methods of backup and disaster recovery MySQL supports, and how to use Python to back up databases
What you need for this book
The content of this book is written against MySQL 5.5, Python 2.5.2, and MySQL for Python 1.2.2. Development of the examples was done with MySQL 5.0, but everything was confirmed against the 5.5 documentation. As for operating systems, any of the main three will do: Microsoft Windows, Linux, or Mac. Any additional requirements of modules are discussed in the book as they come up.
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.
Introduction
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.
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.
What this book covers
Chapter 1, Getting Up and Running with MySQL for Python, helps you to install MySQL for Python specific software, how to import modules into your programs, connecting to a database, accessing online help, and creating a MySQL cursor proxy within your Python program. It also covers how to close the database connection from Python and how to access multiple databases within one program.
Chapter 2, Simple Querying, helps you to form and pass a query to MySQL, to look at user-defined variables, how to determine characteristics of a database and its tables, and program a command-line search utility. It also looks at how to change queries dynamically, without user input.
Chapter 3, Simple Insertion, shows forming and passing an insertion to MySQL, to look at the user-defined variables in a MySQL insertion, passing metadata between databases, and changing insertion statements dynamically without user input.
Chapter 4, Exception Handling, discusses ways to handle errors and warnings that discusses ways to handle errors and warnings that to handle errors and warnings that are passed from MySQL for Python and the differences between them. It also covers several types of errors supported by MySQL for Python, and how to handle them effectively.
Chapter 5, Results Record-by-Record, shows situations in which record-by-record retrieval is desirable, to use iteration to retrieve sets of records in smaller blocks and how to create iterators and generators in Python. It also helps you in using fetchone() and fetchmany().
Chapter 6, Inserting Multiple Entries, discusses how iteration can help us execute several individual INSERT statements rapidly, when to use or avoid executemany(), and throttling how much data is inserted at a time.
Chapter 7, Creating and Dropping, shows to create and delete both databases and tables in MySQL, to manage database instances with MySQL for Python, and to automate database and table creation.
Chapter 8, Creating Users and Granting Access, focuses on creating and removing users in MySQL, managing database privileges with MySQL for Python, automating user creation and removal, to GRANT and REVOKE privileges, and the conditions under which that can be done.
Chapter 9, Date and Time Values, discusses what data types MySQL supports for date and time, when to use which data type and in what format and range, and frequently used functions for handling matters of date and time.
Chapter 10, Aggregate Functions and Clauses, shows how MySQL saves us time and effort by pre-processing data, how to perform several calculations using MySQL's optimized algorithms, and to group and order returned data by column.
Chapter 11, SELECT Alternatives, discusses how to use HAVING clauses, how to create temporary subtables, subqueries and joins in Python, and the various ways to join tables.
Chapter 12, String Functions, shows how MySQL allows us to combine strings and return the single, resulting value, how to extract part of a string or the location of a part, thus saving on processing, and how to convert cases of results.
Chapter 13, Showing MySQL Metadata, discusses the several pieces of metadata about a given table that we can access, which system variables we can retrieve, and how to retrieve user privileges and the grants used to give them.
Chapter 14, Disaster Recovery, focuses on when to implement one of several kinds of database backup plans, what methods of backup and disaster recovery MySQL supports, and how to use Python to back up databases
What you need for this book
The content of this book is written against MySQL 5.5, Python 2.5.2, and MySQL for Python 1.2.2. Development of the examples was done with MySQL 5.0, but everything was confirmed against the 5.5 documentation. As for operating systems, any of the main three will do: Microsoft Windows, Linux, or Mac. Any additional requirements of modules are discussed in the book as they come up.
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.
