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 -- -- -- (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 a sample -- Formatting a larger set of results -- The main() thing -- Calling main() -- Running it -- Room to grow -- Summary -- 6. Inserting Multiple Entries -- The problem -- Why not a MySQL script? -- Lack of automation -- Debugging the process -- Inefficient I/O -- Why not iterate? -- A test sample: Generating primes -- Comparing execution speeds -- Introducing the executemany() method -- executemany(): Basic syntax -- executemany(): Multiple INSERT statements -- executemany(): multiple SELECT statements -- executemany(): Behind the scenes -- MySQL server has gone away -- Command-line option configuration -- Using a configuration file -- More than 16 MB is often unnecessary -- Project: Converting a CSV file to a MySQL table -- The preamble -- The options -- Defining the connection -- Creating convert -- The main() function -- Calling main() -- Room to grow -- Summary -- 7. Creating and Dropping -- Creating databases -- Test first, create second -- CREATE specifications -- Specifying the default character set -- Specifying the collation for a database -- Declaring collation -- Finding available character sets and collations -- Removing or deleting databases -- Avoiding errors -- Preventing (illegal) access after a DROP -- Creating tables -- Covering our bases -- Avoiding errors -- Creating temporary tables -- Dropping tables -- Playing it safe -- Avoiding errors -- Removing user privileges -- Doing it in Python -- Creating databases with MySQLdb -- Testing the output -- Dynamically configuring the CREATE statement -- Dropping databases with MySQLdb -- Creating tables in Python -- Verifying the creation of a table -- Another way to verify table creation -- Dropping tables with MySQLdb -- Project: Web-based administration of MySQL -- CGI vs PHP: What is the difference? -- Basic CGI -- Using PHP as a substitute for CGI.

CGI versus PHP: When to use which? -- Some general considerations for this program -- Program flow -- The basic menu -- Authorization details -- Three operational sections of the dialogue -- The variables -- Planning the functions -- Code of each function -- Connecting without a database -- Connecting with a database -- Database action -- Table action -- Query action -- execute() -- The HTML output -- Basic definition -- The message attribute -- Defining header() -- Defining footer() -- Defining body() -- Defining page() -- Getting the data -- Using CGI -- Using PHP -- Defining main() -- Room to grow -- Summary -- 8. Creating Users and Granting Access -- A word on security -- Creating users in MySQL -- Forcing the use of a password -- Restricting the client's host -- Creating users from Python -- Removing users in MySQL -- DROPping users in Python -- GRANT access in MySQL -- Important dynamics of GRANTing access -- The GRANT statement in MySQL -- Using REQUIREments of access -- Using a WITH clause -- Granting access in Python -- Removing privileges in MySQL -- Basic syntax -- After using REVOKE, the user still has access!? -- Using REVOKE in Python -- Project: Web-based user administration -- New options in the code -- Adding the functions: CREATE and DROP -- Adding CREATE and DROP to main() -- Adding the functions: GRANT and REVOKE -- Adding GRANT and REVOKE to main() -- Test the program -- New options on the page -- Room to grow -- Summary -- 9. Date and Time Values -- Date and time data types in MySQL -- DATETIME -- Output format -- Input formats -- Input range -- Using DATETIME in a CREATE statement -- DATE -- Output and Input formats -- Input range -- TIMESTAMP -- Input of values -- Range -- Defaults, initialization, and updating -- YEAR -- Two-digit YEAR values -- Four-digit YEAR values -- Valid input -- TIME -- Format -- Invalid values.

Date and time types in Python.