MySQL for Python. 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 Albert Lukaszewski

Spis treści książki

• 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
      • <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
  • 4. Exception Handling
    • Why errors and warnings are good for you
    • Errors versus warnings: Theres 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
    • 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
  • 10. Aggregate Functions and Clauses
    • Calculations in MySQL
      • COUNT()
      • SUM()
      • MAX()
      • MIN()
      • AVG()
        • The different kinds of average
          • Mean
          • Median
          • Mode
    • 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
  • 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
  • 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
  • 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
        • CREATE (DATABASE/FUNCTION/PROCEDURE/TABLE/VIEW)
    • 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
  • 14. Disaster Recovery
    • Every database needs a backup plan
      • Offline backups
      • Live backups
    • Choosing a backup method
      • Copying the table files
        • Locking and flushing
          • LOCK TABLES
          • FLUSH
        • 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
          • Viewing the backup file
          • Other options
          • Restoring the data
        • mysqlhotcopy
    • Backing up a database with Python
    • Summary
  • Index

pokaż cały spis treści

ukryj recenzje