利用Python实现文本挖掘的教程
Table of ContentsPreface 1Chapter 1: Getting Up and Running with MySQL for Python 7Getting MySQL for Python 7Using a package manager (only on Linux) 8Using RPMs and yum 9Using RPMs and urpm 9Using apt tools on Debian-like systems 9Using an installer for Windows 10Using an egg file 10Using a tarball (tar.gz file) 14Importing MySQL for Python 17Accessing online help when you need it 18MySQLdb 18_mysql 19Connecting with a database 20Creating a connection object 20Creating a cursor object 22Interacting with the database 22Closing the connection 23Multiple database connections 23Summary 24Chapter 2: Simple Querying 25A brief introduction to CRUD 25Forming a query in MySQL 26SELECT 27* (asterisk) 27FROM 28staff 28; (semicolon) 29www.it-ebooks.infoTable of Contents[ ii ]Other helpful quantifiers 29WHERE 30GROUP BY 30HAVING 32ORDER BY 33LIMIT 35INTO OUTFILE 37Passing a query to MySQL 37A simple SELECT statement 38Modifying the results 39Using user-defined variables 40Determining characteristics of a database and its tables 41Determining what tables exist 42Assigning each table a number 43Offering the options to the user 43Allowing the user to detail a search query 44Changing queries dynamically 45Pattern matching in MySQL queries 45Putting it into practice 46Project: A command-line search utility 48Preparing a database for searching 49Planning your work, then working your plan 50Develop a well-abstracted search functionality 50Specifying the search term from the command-line 52Implementing and incorporating the other functions: -t, -f, and -o 55Including an option for an output file 57Room to grow 57Summary 58Chapter 3: Simple Insertion 59Forming a MySQL insertion statement 60INSERT 60INTO 61Table name 61Column names 61VALUES 63<some values> 64; (semicolon) 66Helpful ways to nuance an INSERT statement 66INSERT...SELECT... 66INSERT DELAYED… 70INSERT...ON DUPLICATE KEY UPDATE... 71www.it-ebooks.infoTable of Contents[ iii ]Passing an insertion through MySQL for Python 72Setting up the preliminaries 72A simple INSERT statement 73More complex INSERT commands 75Using user-defined variables 75Using metadata 77Querying the database for its structure 78Retrieving the table structure 80Changing insertion values dynamically 82Validating the value of name 83Validating the value of price 83Querying the user for a correction 84Passing fish and price for validation 84Essentials: close and commit 85In need of some closure 85What happened to commit? 85Why are these essentials non-essential? 85Project: A command-line insertion utility 86The necessary modules 86The main() thing 87Coding the flag system 88Testing the values passed by the user 88Try to establish a database connection 89Showing the tables 90Showing the table structure, if desired 90Accepting user input for the INSERT statement 91Building the INSERT statement from the user input and executing it 92Committing changes and closing the connection 93Coding the other functions 93valid_digit() and valid_string() 93valid_table() 94query() 94Calling main() 95Room to grow 99Summary 100Chapter 4: Exception Handling 101Why errors and warnings are good for you 101Errors versus warnings: There's a big difference 104The two main errors in MySQLdb 104DatabaseError 105InterfaceError 105Warnings in MySQL for Python 105www.it-ebooks.infoTable of Contents[ iv ]Handling exceptions passed from MySQL 105Python exception-handling 105Catching an exception from MySQLdb 106Raising an error or a warning 107Making exceptions less intimidating 108Catching different types of exceptions 109Types of errors 109DataError 110IntegrityError 110InternalError 111NotSupportedError 111OperationalError 111ProgrammingError 112Customizing for catching 113Catching one type of exception 113Catching different exceptions 114Combined catching of exceptions 115Raising different exceptions 115Creating a feedback loop 116Project: Bad apples 117The preamble 118Making the connection 119Sending error messages 119The statement class 121The main() thing 125Try, try again 126If all else fails 126Room to grow 127Summary 128Chapter 5: Results Record-by-Record 129The problem 129Why? 131Computing resources 131Local resources 132Web applications 133Network latency 134Server-client communications 134Apparent responsiveness 134Pareto's Principle 134How? 135The fetchone() method 135The fetchmany() method 136Iteration: What is it? 137Generating loops 138www.it-ebooks.infoTable of Contents[ v ]while...if loops 138The for loop 139Iterators 140Illustrative iteration 141Iteration and MySQL for Python 141Generators 142Using fetchone() in a generator 142Using fetchmany() in a generator 143Project: A movie database 144Getting Sakila 145Creating the Sakila database 145The structure of Sakila 146Planning it out 148The SQL statements to be used 148Returning the films of an actor 148Returning the actors of a film 149Accepting user data 150A MySQL query with class 150The __init__ method: The consciousness of the class 151Setting the query's type 151Creating the cursor 152Forming the query 153Executing the query 154Formatting the results 155Formatting a sample 155Formatting a larger set of results 156The main() thing 157Calling main() 158Running it 159Room to grow 159Summary 160Chapter 6: Inserting Multiple Entries 161The problem 161Why not a MySQL script? 162Lack of automation 162Debugging the process 162Why not iterate? 163A test sample: Generating primes 163Comparing execution speeds 166Introducing the executemany() method 166executemany(): Basic syntax 167executemany(): Multiple INSERT statements 168executemany(): Multiple SELECT statements 170www.it-ebooks.infoTable of Contents[ vi ]executemany(): Behind the scenes 170MySQL server has gone away 173Command-line option configuration 173Using a configuration file 174More than 16 MB is often unnecessary 174Project: Converting a CSV file to a MySQL table 175The preamble 175The options 176Defining the connection 177Creating convert 177The main() function 178Calling main() 181Room to grow 181Summary 182Chapter 7: Creating and Dropping 183Creating databases 183Test first, create second 184CREATE specifications 185Specifying the default character set 185Specifying the collation for a database 186Declaring collation 186Finding available character sets and collations 187Removing or deleting databases 187Avoiding errors 188Preventing (illegal) access after a DROP 188Creating tables 189Covering our bases 190Avoiding errors 191Creating temporary tables 191Dropping tables 192Playing it safe 192Avoiding errors 193Removing user privileges 193Doing it in Python 193Creating databases with MySQLdb 194Testing the output 194Dynamically configuring the CREATE statement 195Dropping databases with MySQLdb 195Creating tables in Python 195Verifying the creation of a table 196Another way to verify table creation 197Dropping tables with MySQLdb 198www.it-ebooks.infoTable of Contents[ vii ]Project: Web-based administration of MySQL 198CGI vs PHP: What is the difference? 199Basic CGI 200Using PHP as a substitute for CGI 202CGI versus PHP: When to use which? 203Some general considerations for this program 203Program flow 203The basic menu 204Authorization details 206Three operational sections of the dialogue 206The variables 206Planning the functions 207Code of each function 207Connecting without a database 207Connecting with a database 208Database action 208Table action 209Query action 210execute() 211The HTML output 212Basic definition 212The message attribute 213Defining header() 213Defining footer() 213Defining body() 214Defining page() 214Getting the data 214Using CGI 214Using PHP 215Defining main() 217Room to grow 218Summary 218Chapter 8: Creating Users and Granting Access 219A word on security 219Creating users in MySQL 220Forcing the use of a password 221Restricting the client's host 221Creating users from Python 223Removing users in MySQL 224DROPping users in Python 225GRANT access in MySQL 225Important dynamics of GRANTing access 226The GRANT statement in MySQL 226Using REQUIREments of access 229www.it-ebooks.infoTable of Contents[ viii ]Using a WITH clause 230Granting access in Python 231Removing privileges in MySQL 233Basic syntax 233After using REVOKE, the user still has access!? 233Using REVOKE in Python 235Project: Web-based user administration 236New options in the code 236Adding the functions: CREATE and DROP 239Adding CREATE and DROP to main() 240Adding the functions: GRANT and REVOKE 241Adding GRANT and REVOKE to main() 241Test the program 243New options on the page 244Room to grow 244Summary 245Chapter 9: Date and Time Values 247Date and time data types in MySQL 247DATETIME 248Output format 248Input formats 248Input range 249Using DATETIME in a CREATE statement 249DATE 249Output and Input formats 249Input range 250TIMESTAMP 250Input of values 250Range 251Defaults, initialization, and updating 251YEAR 252Two-digit YEAR values 252Four-digit YEAR values 252Valid input 253TIME 253Format 254Invalid values 255Date and time types in Python 256Date and time functions 257NOW() 260CURDATE() 260CURTIME() 261DATE() 261www.it-ebooks.infoTable of Contents[ ix ]DATE_SUB() and DATE_ADD() 262DATEDIFF() 266DATE_FORMAT() 267EXTRACT() 269TIME() 270Project: Logging user activity 270The log framework 272The logger() function 273Creating the database 273Using the database 274Creating the table 274Forming the INSERT statement 274Ensure logging occurs 275Room to grow 276Summary 277Chapter 10: Aggregate Functions and Clauses 279Calculations in MySQL 280COUNT() 281SUM() 282MAX() 283MIN() 284AVG() 284The different kinds of average 285Trimming results 287DISTINCT 287GROUP_CONCAT() 289Specifying the delimiter 290Customizing the maximum length 290Using GROUP_CONCAT() with DISTINCT 291Server-side sorting in MySQL 292GROUP BY 293ORDER BY 294Using a universal quantifier 294Sorting alphabetically or from low-to-high 295Reversing the alphabet or sorting high-to-low 296Sorting with multiple keys 298Putting it in Python 298Project: Incorporating aggregate functions 300Adding to qaction() 300New variables 301New statement formation 302Revising main() 305Setting up the options 308www.it-ebooks.infoTable of Contents[ x ]Changing the HTML form 309Summary 310Chapter 11: SELECT Alternatives 311HAVING clause 312WHERE versus HAVING: Syntax 312WHERE versus HAVING: Aggregate functions 312WHERE versus HAVING: Application 314Subqueries 317Unions 319Joins 321LEFT and RIGHT joins 321OUTER joins 323INNER joins 324NATURAL joins 326CROSS joins 327Doing it in Python 327Subqueries 328Unions 329Joins 329Project: Implement HAVING 330Revising the Python backend 331Revising qaction() 331Revising main() 333Revising the options 336Revising the HTML interface 337Room to grow 338Summary 339Chapter 12: String Functions 341Preparing results before their return 341CONCAT() function 342SUBSTRING() or MID() 343TRIM() 344Basic syntax 344Options 345Alternatives 346REPLACE() 347INSERT() 348REGEXP 350Accessing and using index data 354LENGTH() 354INSTR() or LOCATE() 355www.it-ebooks.infoTable of Contents[ xi ]INSTR() 356LOCATE() 356Nuancing data 357ROUND() 357FORMAT() 359UPPER() 360LOWER() 360Project: Creating your own functions 360Hello() 361Capitalise() 362DELIMITER 362The function definition 362Calling the function 364Defining the function in Python 365Defining the function as a Python value 365Sourcing the MySQL function as a Python module 366Sourcing the function as MySQL code 366Room to grow 367Summary 367Chapter 13: Showing MySQL Metadata 369MySQL's system environment 370ENGINE 371The most popular engines 372Transactions 372Specifying the engine 373ENGINE status 373SHOW ENGINES 374Profiling 375SHOW PROFILE 375SHOW PROFILES 376SHOW system variables 376Accessing database metadata 377DATABASES 377Using the USE command 378Accessing metadata about tables 378SHOW TABLES 378SHOW TABLE STATUS 379Showing columns from a table 379FUNCTION STATUS 380Accessing user metadata 383SHOW GRANTS 383PRIVILEGES 384Project: Building a database class 384Writing the class 384www.it-ebooks.infoTable of Contents[ xii ]Defining fetchquery() and some core methods 385Retrieving table status and structure 386Retrieving the CREATE statements 386Define main()—part 1 387Writing resproc() 388Define main()—part 2 389The preamble 389Modules and variables 390Login and USE 390Closing out the program 390Room to grow 391Summary 391Chapter 14: Disaster Recovery 393Every database needs a backup plan 394Offline backups 394Live backups 395Choosing a backup method 395Copying the table files 396Locking and flushing 397Unlocking the tables 398Restoring the data 398Delimited backups within MySQL 398Using SELECT INTO OUTFILE to export data 398Using LOAD DATA INFILE to import data 399Archiving from the command line 400mysqldump 400mysqlhotcopy 403Backing up a database with Python 405Summary 406Index 407
评论