book

MySQL for Python

Name: MySQL for Python
Author: Albert Lukaszewski
ISBN: 9781849510189

by Albert Lukaszewski

September 2010

Intermediate to advanced

440 pages

9h 23m

English

Packt Publishing

Read now

Unlock full access

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
ErrataPiracyQuestions
1. Getting Up and Running with MySQL for Python
Getting MySQL for PythonUsing a package manager (only on Linux)Using RPMs and yumUsing RPMs and urpmUsing apt tools on Debian-like systemsUsing an installer for WindowsUsing an egg fileInstalling egg handling softwareUsing a package manager (Linux)Without a package manager (Mac, Linux)On Microsoft WindowsInstalling MySQL for Python from an egg fileUsing a tarball (tar.gz file)
Importing MySQL for Python
Accessing online help when you need itMySQLdb_mysql
Connecting with a database
Creating a connection objectCreating a cursor objectInteracting with the databaseClosing the connection
Multiple database connections
Summary
2. Simple Querying
A brief introduction to CRUD
Forming a query in MySQL
SELECT* (asterisk)FROMstaff; (semicolon)Other helpful quantifiersWHEREGROUP BYHAVINGORDER BYLIMITINTO OUTFILE
Passing a query to MySQL
A simple SELECT statementModifying the results
Using user-defined variables
Determining characteristics of a database and its tables
Determining what tables existAssigning each table a numberOffering the options to the userAllowing the user to detail a search query
Changing queries dynamically
Pattern matching in MySQL queriesPutting it into practice
Project: A command-line search utility
Preparing a database for searchingPlanning your work, then working your planDevelop a well-abstracted search functionalitySpecifying the search term from the command-lineImplementing and incorporating the other functions: -t, -f, and -oIncluding an option for an output file
Room to grow
Summary
3. Simple Insertion
Forming a MySQL insertion statementINSERTINTOTable nameColumn namesVALUES<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 preliminariesA simple INSERT statementMore complex INSERT commands
Using user-defined variables
Using metadata
Querying the database for its structureRetrieving the table structure
Changing insertion values dynamically
Validating the value of nameValidating the value of priceQuerying the user for a correctionPassing fish and price for validation
Essentials: close and commit
In need of some closureWhat happened to commit?Why are these essentials non-essential?
Project: A command-line insertion utility
The necessary modulesThe main() thingCoding the flag systemTesting the values passed by the userTry to establish a database connectionShowing the tablesShowing the table structure, if desiredAccepting user input for the INSERT statementBuilding the INSERT statement from the user input and executing itCommitting changes and closing the connectionCoding the other functionsvalid_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
DatabaseErrorInterfaceError
Warnings in MySQL for Python
Handling exceptions passed from MySQL
Python exception-handlingCatching an exception from MySQLdbRaising an error or a warningMaking exceptions less intimidating
Catching different types of exceptions
Types of errorsDataErrorIntegrityErrorInternalErrorNotSupportedErrorOperationalErrorProgrammingErrorCustomizing for catchingCatching one type of exceptionCatching different exceptionsCombined catching of exceptionsRaising different exceptions
Creating a feedback loop
Project: Bad apples
The preambleMaking the connectionSending error messagesThe statement classThe __init__ methodStoring the statement typeForming the statementExecute the MySQL statementHandling any falloutThe main() thingTry, try againIf all else failsRoom to grow
Summary
5. Results Record-by-Record
The problem
Why?
Computing resourcesLocal resourcesWeb applicationsNetwork latencyServer-client communicationsApparent responsivenessPareto's Principle
How?
The fetchone() methodThe fetchmany() methodIteration: What is it?Generating loopswhile...if loopsThe for loopIteratorsIllustrative iterationIteration and MySQL for PythonGeneratorsUsing fetchone() in a generatorUsing fetchmany() in a generator
Project: A movie database
Getting SakilaCreating the Sakila databaseThe structure of SakilaPlanning it outThe SQL statements to be usedReturning the films of an actorReturning the actors of a filmAccepting user dataA MySQL query with classThe __init__ method: The consciousness of the classSetting the query's typeCreating the cursorForming the queryExecuting the queryFormatting the resultsFormatting a sampleFormatting a larger set of resultsThe main() thingCalling main()Running itRoom to grow
Summary
6. Inserting Multiple Entries
The problemWhy not a MySQL script?Lack of automationDebugging the processInefficient I/OWhy not iterate?A test sample: Generating primesComparing execution speeds
Introducing the executemany() method
executemany(): Basic syntax
executemany(): Multiple INSERT statementsexecutemany(): multiple SELECT statements
executemany(): Behind the scenes
MySQL server has gone awayCommand-line option configurationUsing a configuration fileMore than 16 MB is often unnecessary
Project: Converting a CSV file to a MySQL table
The preambleThe optionsDefining the connectionCreating convertThe main() functionCalling main()Room to grow
Summary
7. Creating and Dropping
Creating databasesTest first, create secondCREATE specificationsSpecifying the default character setSpecifying the collation for a databaseDeclaring collationFinding available character sets and collations
Removing or deleting databases
Avoiding errorsPreventing (illegal) access after a DROP
Creating tables
Covering our basesAvoiding errorsCreating temporary tables
Dropping tables
Playing it safeAvoiding errorsRemoving user privileges
Doing it in Python
Creating databases with MySQLdbTesting the outputDynamically configuring the CREATE statementDropping databases with MySQLdbCreating tables in PythonVerifying the creation of a tableAnother way to verify table creation
Dropping tables with MySQLdb
Project: Web-based administration of MySQL
CGI vs PHP: What is the difference?Basic CGIUsing PHP as a substitute for CGICGI versus PHP: When to use which?Some general considerations for this programProgram flowThe basic menuAuthorization detailsThree operational sections of the dialogueThe variablesPlanning the functionsCode of each functionConnecting without a databaseConnecting with a databaseDatabase actionTable actionQuery actionexecute()The HTML outputBasic definitionThe message attributeDefining header()Defining footer()Defining body()Defining page()Getting the dataUsing CGIUsing PHPDefining main()Room to grow
Summary
8. Creating Users and Granting Access
A word on security
Creating users in MySQL
Forcing the use of a passwordRestricting the client's host
Creating users from Python
Removing users in MySQL
DROPping users in Python
GRANT access in MySQL
Important dynamics of GRANTing accessThe GRANT statement in MySQLUsing REQUIREments of accessUsing a WITH clause
Granting access in Python
Removing privileges in MySQL
Basic syntaxAfter using REVOKE, the user still has access!?
Using REVOKE in Python
Project: Web-based user administration
New options in the codeAdding the functions: CREATE and DROPAdding CREATE and DROP to main()Adding the functions: GRANT and REVOKEAdding GRANT and REVOKE to main()Test the programNew options on the pageRoom to grow
Summary
9. Date and Time Values
Date and time data types in MySQLDATETIMEOutput formatInput formatsInput rangeUsing DATETIME in a CREATE statementDATEOutput and Input formatsInput rangeTIMESTAMPInput of valuesRangeDefaults, initialization, and updatingYEARTwo-digit YEAR valuesFour-digit YEAR valuesValid inputTIMEFormatInvalid 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 frameworkThe logger() functionCreating the databaseUsing the databaseCreating the tableForming the INSERT statementEnsure logging occursRoom to grow
Summary
10. Aggregate Functions and Clauses
Calculations in MySQLCOUNT()SUM()MAX()MIN()AVG()The different kinds of averageMeanMedianMode
Trimming results
DISTINCTGROUP_CONCAT()Specifying the delimiterCustomizing the maximum lengthUsing GROUP_CONCAT() with DISTINCT
Server-side sorting in MySQL
GROUP BYORDER BYUsing a universal quantifierSorting alphabetically or from low-to-highReversing the alphabet or sorting high-to-lowSorting with multiple keys
Putting it in Python
Project: Incorporating aggregate functions
Adding to qaction()New variablesNew statement formationRevising main()Setting up the optionsChanging the HTML form
Summary
11. SELECT Alternatives
HAVING clauseWHERE versus HAVING: SyntaxWHERE versus HAVING: Aggregate functionsWHERE versus HAVING: Application
Subqueries
Unions
Joins
LEFT and RIGHT joinsOUTER joinsINNER joinsNATURAL joinsCROSS joins
Doing it in Python
SubqueriesUnionsJoins
Project: Implement HAVING
Revising the Python backendRevising qaction()Revising main()Revising the optionsRevising the HTML interfaceRoom to grow
Summary
12. String Functions
Preparing results before their returnCONCAT() functionSUBSTRING() or MID()TRIM()Basic syntaxOptionsAlternativesREPLACE()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()DELIMITERThe function definitionCalling the functionDefining the function in PythonDefining the function as a Python valueSourcing the MySQL function as a Python moduleSourcing the function as MySQL codeRoom to grow
Summary
13. Showing MySQL Metadata
MySQL's system environmentENGINEThe most popular enginesTransactionsSpecifying the engineENGINE statusSHOW ENGINESProfilingSHOW PROFILESHOW PROFILESSHOW system variables
Accessing database metadata
DATABASESUsing the USE commandAccessing metadata about tablesSHOW TABLESSHOW TABLE STATUSShowing columns from a tableFUNCTION STATUSCREATE (DATABASE/FUNCTION/PROCEDURE/TABLE/VIEW)
Accessing user metadata
SHOW GRANTSPRIVILEGES
Project: Building a database class
Writing the classDefining fetchquery() and some core methodsRetrieving table status and structureRetrieving the CREATE statementsDefine main()—part 1Writing resproc()Define main()—part 2The preambleModules and variablesLogin and USEClosing out the programRoom to grow
Summary
14. Disaster Recovery
Every database needs a backup planOffline backupsLive backups
Choosing a backup method
Copying the table filesLocking and flushingLOCK TABLESFLUSHUnlocking the tablesRestoring the dataDelimited backups within MySQLUsing SELECT INTO OUTFILE to export dataUsing LOAD DATA INFILE to import dataArchiving from the command linemysqldumpViewing the backup fileOther optionsRestoring the datamysqlhotcopy
Backing up a database with Python
Summary
Index

Content preview from MySQL for Python

Trimming results

MySQL allows for two different ways of trimming results when using aggregate functions. One is a keyword that removes redundant data. The other is a function that pools the data into a single string value.

DISTINCT

The purpose of DISTINCT is to ensure that all results are unique. The examples above do not discriminate between duplicate values. Each value is treated as a separate record without quantitative comparison to the others, thus allowing for redundancy in the results.

This redundancy works well when comprehensiveness is required but can otherwise skew the results of a query. For example, if we want to know how many unique ratings are used in the film table of sakila, we would not want to use this query:

SELECT COUNT(rating) ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

MySQL Connector/Python Revealed: SQL and NoSQL Data Storage Using MySQL for Python Programmers

Publisher Resources

ISBN: 9781849510189Other

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

MySQL for Python

by Albert Lukaszewski

Trimming results

DISTINCT

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.