MySQL for Python

Table of Contents

• Preface

• Chapter 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
      • 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

• Chapter 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

• Chapter 3: Simple Insertion
  • Forming a MySQL insertion statement
    • INSERT
    • INTO
    • Table name
    • Column names
    • VALUES
    • <some 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

• Chapter 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 main() thing
      • Try, try again
      • If all else fails
    • Room to grow
  • Summary

• Chapter 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

• Chapter 6: Inserting Multiple Entries
  • The problem
    • Why not a MySQL script?
      • Lack of automation
      • Debugging the process
    • 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

• Chapter 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

• Chapter 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

• Chapter 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
  • Date and time functions
    • NOW()
    • CURDATE()
    • CURTIME()
    • DATE()
    • DATE_SUB() and DATE_ADD()
    • DATEDIFF()
    • DATE_FORMAT()
    • EXTRACT()
    • TIME()
  • Project: Logging user activity
    • The log framework
    • The logger() function
      • Creating the database
      • Using the database
      • Creating the table
      • Forming the INSERT statement
    • Ensure logging occurs
    • Room to grow
  • Summary

• Chapter 10: Aggregate Functions and Clauses
  • Calculations in MySQL
    • COUNT()
    • SUM()
    • MAX()
    • MIN()
    • AVG()
      • The different kinds of average
  • Trimming results
    • DISTINCT
    • GROUP_CONCAT()
      • Specifying the delimiter
      • Customizing the maximum length
      • Using GROUP_CONCAT() with DISTINCT
  • Server-side sorting in MySQL
    • GROUP BY
    • ORDER BY
      • Using a universal quantifier
      • Sorting alphabetically or from low-to-high
      • Reversing the alphabet or sorting high-to-low
      • Sorting with multiple keys
  • Putting it in Python
  • Project: Incorporating aggregate functions
    • Adding to qaction()
      • New variables
      • New statement formation
    • Revising main()
    • Setting up the options
    • Changing the HTML form
  • Summary

• Chapter 11: SELECT Alternatives
  • HAVING clause
    • WHERE versus HAVING: Syntax
    • WHERE versus HAVING: Aggregate functions
    • WHERE versus HAVING: Application
  • Subqueries
  • Unions
  • Joins
    • LEFT and RIGHT joins
    • OUTER joins
    • INNER joins
    • NATURAL joins
    • CROSS joins
  • Doing it in Python
    • Subqueries
    • Unions
    • Joins
  • Project: Implement HAVING
    • Revising the Python backend
      • Revising qaction()
      • Revising main()
      • Revising the options
    • Revising the HTML interface
    • Room to grow
  • Summary

• Chapter 12: String Functions
  • Preparing results before their return
    • CONCAT() function
    • SUBSTRING() or MID()
    • TRIM()
      • Basic syntax
      • Options
      • Alternatives
    • REPLACE()
    • INSERT()
    • REGEXP
  • Accessing and using index data
    • LENGTH()
    • INSTR() or LOCATE()
      • INSTR()
      • LOCATE()
  • Nuancing data
    • ROUND()
    • FORMAT()
    • UPPER()
    • LOWER()
  • Project: Creating your own functions
    • Hello()
    • Capitalise()
      • DELIMITER
      • The function definition
      • Calling the function
      • Defining the function in Python
      • Defining the function as a Python value
      • Sourcing the MySQL function as a Python module
      • Sourcing the function as MySQL code
      • Room to grow
  • Summary

• Chapter 13: Showing MySQL Metadata
  • MySQL's system environment
    • ENGINE
      • The most popular engines
      • Transactions
      • Specifying the engine
      • ENGINE status
    • SHOW ENGINES
    • Profiling
      • SHOW PROFILE
      • SHOW PROFILES
    • SHOW system variables
  • Accessing database metadata
    • DATABASES
      • Using the USE command
    • Accessing metadata about tables
      • SHOW TABLES
      • SHOW TABLE STATUS
      • Showing columns from a table
      • FUNCTION STATUS
  • Accessing user metadata
    • SHOW GRANTS
    • PRIVILEGES
  • Project: Building a database class
    • Writing the class
      • Defining fetchquery() and some core methods
      • Retrieving table status and structure
      • Retrieving the CREATE statements
    • Define main()—part 1
    • Writing resproc()
    • Define main()—part 2
    • The preamble
      • Modules and variables
      • Login and USE
    • Closing out the program
    • Room to grow
  • Summary

• Chapter 14: Disaster Recovery
  • Every database needs a backup plan
    • Offline backups
    • Live backups
  • Choosing a backup method
    • Copying the table files
      • Locking and flushing
      • Unlocking the tables
      • Restoring the data
    • Delimited backups within MySQL
      • Using SELECT INTO OUTFILE to export data
      • Using LOAD DATA INFILE to import data
    • Archiving from the command line
      • mysqldump
      • mysqlhotcopy
  • Backing up a database with Python
  • Summary

• Index

Albert Lukaszewski, PhD

Sample chapters

You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

Code Downloads

Download the code and support files for this book.

Submit Errata

Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.