MySQL for Python

MySQL for Python

by Albert Lukaszewski PhD
Released September 2010
Publisher(s): Packt Publishing
ISBN: 9781849510189

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Integrating MySQL and Python can bring a whole new level of productivity to your applications. This practical tutorial shows you how with examples and explanations that clarify even the most difficult concepts.

  • Implement the outstanding features of Python's MySQL library to their full potential
  • See how to make MySQL take the processing burden from your programs
  • Learn how to employ Python with MySQL to power your websites and desktop applications
  • Apply your knowledge of MySQL and Python to real-world problems instead of hypothetical scenarios
  • A manual packed with step-by-step exercises to integrate your Python applications with the MySQL database server

In Detail

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.

A practical manual packed with step-by-step examples to manage your MySQL database efficiently through Python

Table of contents

  1. MySQL for Python
    1. Table of Contents
    2. MySQL for Python
    3. Credits
    4. About the Author
    5. About the Reviewers
    6. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Errata
        2. Piracy
        3. Questions
    7. 1. Getting Up and Running with MySQL for Python
      1. Getting MySQL for Python
        1. Using a package manager (only on Linux)
          1. Using RPMs and yum
          2. Using RPMs and urpm
          3. Using apt tools on Debian-like systems
          4. Using an installer for Windows
          5. Using an egg file
            1. Installing egg handling software
              1. Using a package manager (Linux)
              2. Without a package manager (Mac, Linux)
              3. On Microsoft Windows
            2. Installing MySQL for Python from an egg file
          6. Using a tarball (tar.gz file)
      2. Importing MySQL for Python
        1. Accessing online help when you need it
        2. MySQLdb
        3. _mysql
      3. Connecting with a database
        1. Creating a connection object
        2. Creating a cursor object
        3. Interacting with the database
        4. Closing the connection
      4. Multiple database connections
      5. Summary
    8. 2. Simple Querying
      1. A brief introduction to CRUD
      2. Forming a query in MySQL
        1. SELECT
        2. * (asterisk)
        3. FROM
        4. staff
        5. ; (semicolon)
        6. Other helpful quantifiers
          1. WHERE
          2. GROUP BY
          3. HAVING
          4. ORDER BY
          5. LIMIT
          6. INTO OUTFILE
      3. Passing a query to MySQL
        1. A simple SELECT statement
        2. Modifying the results
      4. Using user-defined variables
      5. Determining characteristics of a database and its tables
        1. Determining what tables exist
        2. Assigning each table a number
        3. Offering the options to the user
        4. Allowing the user to detail a search query
      6. Changing queries dynamically
        1. Pattern matching in MySQL queries
        2. Putting it into practice
      7. Project: A command-line search utility
        1. Preparing a database for searching
        2. Planning your work, then working your plan
          1. Develop a well-abstracted search functionality
        3. Specifying the search term from the command-line
        4. Implementing and incorporating the other functions: -t, -f, and -o
        5. Including an option for an output file
      8. Room to grow
      9. Summary
    9. 3. Simple Insertion
      1. Forming a MySQL insertion statement
        1. INSERT
        2. INTO
        3. Table name
        4. Column names
        5. VALUES
        6. <some values>
        7. ; (semicolon)
      2. Helpful ways to nuance an INSERT statement
        1. INSERT...SELECT...
        2. INSERT DELAYED…
        3. INSERT...ON DUPLICATE KEY UPDATE...
      3. Passing an insertion through MySQL for Python
        1. Setting up the preliminaries
        2. A simple INSERT statement
        3. More complex INSERT commands
      4. Using user-defined variables
      5. Using metadata
        1. Querying the database for its structure
        2. Retrieving the table structure
      6. Changing insertion values dynamically
        1. Validating the value of name
        2. Validating the value of price
        3. Querying the user for a correction
        4. Passing fish and price for validation
      7. Essentials: close and commit
        1. In need of some closure
        2. What happened to commit?
        3. Why are these essentials non-essential?
      8. Project: A command-line insertion utility
        1. The necessary modules
        2. The main() thing
          1. Coding the flag system
          2. Testing the values passed by the user
          3. Try to establish a database connection
          4. Showing the tables
          5. Showing the table structure, if desired
          6. Accepting user input for the INSERT statement
          7. Building the INSERT statement from the user input and executing it
          8. Committing changes and closing the connection
        3. Coding the other functions
          1. valid_digit() and valid_string()
          2. valid_table()
          3. query()
        4. Calling main()
        5. Room to grow
      9. Summary
    10. 4. Exception Handling
      1. Why errors and warnings are good for you
      2. Errors versus warnings: There's a big difference
      3. The two main errors in MySQLdb
        1. DatabaseError
        2. InterfaceError
      4. Warnings in MySQL for Python
      5. Handling exceptions passed from MySQL
        1. Python exception-handling
        2. Catching an exception from MySQLdb
        3. Raising an error or a warning
        4. Making exceptions less intimidating
      6. Catching different types of exceptions
        1. Types of errors
          1. DataError
          2. IntegrityError
          3. InternalError
          4. NotSupportedError
          5. OperationalError
          6. ProgrammingError
        2. Customizing for catching
          1. Catching one type of exception
          2. Catching different exceptions
          3. Combined catching of exceptions
          4. Raising different exceptions
      7. Creating a feedback loop
      8. Project: Bad apples
        1. The preamble
        2. Making the connection
        3. Sending error messages
          1. The statement class
            1. The __init__ method
            2. Storing the statement type
            3. Forming the statement
            4. Execute the MySQL statement
            5. Handling any fallout
        4. The main() thing
          1. Try, try again
          2. If all else fails
        5. Room to grow
      9. Summary
    11. 5. Results Record-by-Record
      1. The problem
      2. Why?
        1. Computing resources
          1. Local resources
          2. Web applications
        2. Network latency
          1. Server-client communications
          2. Apparent responsiveness
        3. Pareto's Principle
      3. How?
        1. The fetchone() method
        2. The fetchmany() method
        3. Iteration: What is it?
        4. Generating loops
          1. while...if loops
          2. The for loop
        5. Iterators
          1. Illustrative iteration
        6. Iteration and MySQL for Python
        7. Generators
          1. Using fetchone() in a generator
          2. Using fetchmany() in a generator
      4. Project: A movie database
        1. Getting Sakila
        2. Creating the Sakila database
        3. The structure of Sakila
        4. Planning it out
        5. The SQL statements to be used
          1. Returning the films of an actor
          2. Returning the actors of a film
        6. Accepting user data
        7. A MySQL query with class
          1. The __init__ method: The consciousness of the class
          2. Setting the query's type
          3. Creating the cursor
          4. Forming the query
          5. Executing the query
        8. Formatting the results
          1. Formatting a sample
          2. Formatting a larger set of results
        9. The main() thing
        10. Calling main()
        11. Running it
        12. Room to grow
      5. Summary
    12. 6. Inserting Multiple Entries
      1. The problem
        1. Why not a MySQL script?
          1. Lack of automation
          2. Debugging the process
            1. Inefficient I/O
        2. Why not iterate?
          1. A test sample: Generating primes
          2. Comparing execution speeds
      2. Introducing the executemany() method
      3. executemany(): Basic syntax
        1. executemany(): Multiple INSERT statements
        2. executemany(): multiple SELECT statements
      4. executemany(): Behind the scenes
        1. MySQL server has gone away
          1. Command-line option configuration
          2. Using a configuration file
          3. More than 16 MB is often unnecessary
      5. Project: Converting a CSV file to a MySQL table
        1. The preamble
        2. The options
        3. Defining the connection
        4. Creating convert
        5. The main() function
        6. Calling main()
        7. Room to grow
      6. Summary
    13. 7. Creating and Dropping
      1. Creating databases
        1. Test first, create second
        2. CREATE specifications
          1. Specifying the default character set
        3. Specifying the collation for a database
          1. Declaring collation
          2. Finding available character sets and collations
      2. Removing or deleting databases
        1. Avoiding errors
        2. Preventing (illegal) access after a DROP
      3. Creating tables
        1. Covering our bases
        2. Avoiding errors
        3. Creating temporary tables
      4. Dropping tables
        1. Playing it safe
        2. Avoiding errors
        3. Removing user privileges
      5. Doing it in Python
        1. Creating databases with MySQLdb
          1. Testing the output
          2. Dynamically configuring the CREATE statement
        2. Dropping databases with MySQLdb
        3. Creating tables in Python
        4. Verifying the creation of a table
        5. Another way to verify table creation
      6. Dropping tables with MySQLdb
      7. Project: Web-based administration of MySQL
        1. CGI vs PHP: What is the difference?
        2. Basic CGI
        3. Using PHP as a substitute for CGI
          1. CGI versus PHP: When to use which?
        4. Some general considerations for this program
        5. Program flow
        6. The basic menu
          1. Authorization details
          2. Three operational sections of the dialogue
          3. The variables
        7. Planning the functions
        8. Code of each function
          1. Connecting without a database
          2. Connecting with a database
          3. Database action
          4. Table action
          5. Query action
          6. execute()
        9. The HTML output
          1. Basic definition
          2. The message attribute
          3. Defining header()
          4. Defining footer()
          5. Defining body()
          6. Defining page()
        10. Getting the data
          1. Using CGI
          2. Using PHP
        11. Defining main()
        12. Room to grow
      8. Summary
    14. 8. Creating Users and Granting Access
      1. A word on security
      2. Creating users in MySQL
        1. Forcing the use of a password
        2. Restricting the client's host
      3. Creating users from Python
      4. Removing users in MySQL
      5. DROPping users in Python
      6. GRANT access in MySQL
        1. Important dynamics of GRANTing access
        2. The GRANT statement in MySQL
        3. Using REQUIREments of access
        4. Using a WITH clause
      7. Granting access in Python
      8. Removing privileges in MySQL
        1. Basic syntax
        2. After using REVOKE, the user still has access!?
      9. Using REVOKE in Python
      10. Project: Web-based user administration
        1. New options in the code
        2. Adding the functions: CREATE and DROP
        3. Adding CREATE and DROP to main()
        4. Adding the functions: GRANT and REVOKE
        5. Adding GRANT and REVOKE to main()
        6. Test the program
        7. New options on the page
        8. Room to grow
      11. Summary
    15. 9. Date and Time Values
      1. Date and time data types in MySQL
        1. DATETIME
          1. Output format
          2. Input formats
          3. Input range
          4. Using DATETIME in a CREATE statement
        2. DATE
          1. Output and Input formats
          2. Input range
        3. TIMESTAMP
          1. Input of values
          2. Range
          3. Defaults, initialization, and updating
        4. YEAR
          1. Two-digit YEAR values
          2. Four-digit YEAR values
          3. Valid input
        5. TIME
          1. Format
          2. Invalid values
      2. Date and time types in Python
      3. Date and time functions
        1. NOW()
        2. CURDATE()
        3. CURTIME()
        4. DATE()
        5. DATE_SUB() and DATE_ADD()
        6. DATEDIFF()
        7. DATE_FORMAT()
        8. EXTRACT()
        9. TIME()
      4. Project: Logging user activity
        1. The log framework
        2. The logger() function
          1. Creating the database
          2. Using the database
          3. Creating the table
          4. Forming the INSERT statement
        3. Ensure logging occurs
        4. Room to grow
      5. Summary
    16. 10. Aggregate Functions and Clauses
      1. Calculations in MySQL
        1. COUNT()
        2. SUM()
        3. MAX()
        4. MIN()
        5. AVG()
          1. The different kinds of average
            1. Mean
            2. Median
            3. Mode
      2. Trimming results
        1. DISTINCT
        2. GROUP_CONCAT()
          1. Specifying the delimiter
          2. Customizing the maximum length
          3. Using GROUP_CONCAT() with DISTINCT
      3. Server-side sorting in MySQL
        1. GROUP BY
        2. ORDER BY
          1. Using a universal quantifier
          2. Sorting alphabetically or from low-to-high
          3. Reversing the alphabet or sorting high-to-low
          4. Sorting with multiple keys
      4. Putting it in Python
      5. Project: Incorporating aggregate functions
        1. Adding to qaction()
          1. New variables
          2. New statement formation
        2. Revising main()
        3. Setting up the options
        4. Changing the HTML form
      6. Summary
    17. 11. SELECT Alternatives
      1. HAVING clause
        1. WHERE versus HAVING: Syntax
        2. WHERE versus HAVING: Aggregate functions
        3. WHERE versus HAVING: Application
      2. Subqueries
      3. Unions
      4. Joins
        1. LEFT and RIGHT joins
        2. OUTER joins
        3. INNER joins
        4. NATURAL joins
        5. CROSS joins
      5. Doing it in Python
        1. Subqueries
        2. Unions
        3. Joins
      6. Project: Implement HAVING
        1. Revising the Python backend
          1. Revising qaction()
          2. Revising main()
          3. Revising the options
        2. Revising the HTML interface
        3. Room to grow
      7. Summary
    18. 12. String Functions
      1. Preparing results before their return
        1. CONCAT() function
        2. SUBSTRING() or MID()
        3. TRIM()
          1. Basic syntax
          2. Options
          3. Alternatives
        4. REPLACE()
        5. INSERT()
        6. REGEXP
      2. Accessing and using index data
        1. LENGTH()
        2. INSTR() or LOCATE()
          1. INSTR()
          2. LOCATE()
      3. Nuancing data
        1. ROUND()
        2. FORMAT()
        3. UPPER()
        4. LOWER()
      4. Project: Creating your own functions
        1. Hello()
        2. Capitalise()
          1. DELIMITER
          2. The function definition
          3. Calling the function
          4. Defining the function in Python
          5. Defining the function as a Python value
          6. Sourcing the MySQL function as a Python module
          7. Sourcing the function as MySQL code
          8. Room to grow
      5. Summary
    19. 13. Showing MySQL Metadata
      1. MySQL's system environment
        1. ENGINE
          1. The most popular engines
          2. Transactions
          3. Specifying the engine
          4. ENGINE status
        2. SHOW ENGINES
        3. Profiling
          1. SHOW PROFILE
          2. SHOW PROFILES
        4. SHOW system variables
      2. Accessing database metadata
        1. DATABASES
          1. Using the USE command
        2. Accessing metadata about tables
          1. SHOW TABLES
          2. SHOW TABLE STATUS
          3. Showing columns from a table
          4. FUNCTION STATUS
          5. CREATE (DATABASE/FUNCTION/PROCEDURE/TABLE/VIEW)
      3. Accessing user metadata
        1. SHOW GRANTS
        2. PRIVILEGES
      4. Project: Building a database class
        1. Writing the class
          1. Defining fetchquery() and some core methods
          2. Retrieving table status and structure
          3. Retrieving the CREATE statements
        2. Define main()—part 1
        3. Writing resproc()
        4. Define main()—part 2
        5. The preamble
          1. Modules and variables
          2. Login and USE
        6. Closing out the program
        7. Room to grow
      5. Summary
    20. 14. Disaster Recovery
      1. Every database needs a backup plan
        1. Offline backups
        2. Live backups
      2. Choosing a backup method
        1. Copying the table files
          1. Locking and flushing
            1. LOCK TABLES
            2. FLUSH
          2. Unlocking the tables
          3. Restoring the data
        2. Delimited backups within MySQL
          1. Using SELECT INTO OUTFILE to export data
          2. Using LOAD DATA INFILE to import data
        3. Archiving from the command line
          1. mysqldump
            1. Viewing the backup file
            2. Other options
            3. Restoring the data
          2. mysqlhotcopy
      3. Backing up a database with Python
      4. Summary
    21. Index

Product information

  • Title: MySQL for Python
  • Author(s): Albert Lukaszewski PhD
  • Release date: September 2010
  • Publisher(s): Packt Publishing
  • ISBN: 9781849510189