Home / IT Books / MySQL for Python

MySQL for Python

MySQL for Python

This is a practical, tutorial-style book that includes many examples to demonstrate the full potential of MySQL for Python. Every chapter starts with an explanation of the various areas for using MySQL for Python and ends with work on a sample application using the programming calls just learned. All complicated concepts are broken down to be very easy to understand. Everything in the book is designed to help you learn and use MySQL for Python to address your programming needs in the fastest way possible. This book is meant for intermediate users of Python who want hassle-free access to their MySQL database through Python. If you are a Python programmer who wants database-support in your Python applications, then this book is for you. This book is a must-read for every focused user of the MySQL for Python library who wants real-world applications using this powerful combination of Python and MySQL.

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
Connecting with a database
Creating a connection object
Creating a cursor object
Interacting with the database
Closing the connection
Multiple database connections
Chapter 2: Simple Querying
A brief introduction to CRUD
Forming a query in MySQL
* (asterisk)
; (semicolon)
Other helpful quantifiers
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
Chapter 3: Simple Insertion
Forming a MySQL insertion statement
Table name
Column names
<some values>
; (semicolon)
Helpful ways to nuance an INSERT statement
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 87
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()
Calling main()
Room to grow
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
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
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
Chapter 5: Results Record-by-Record
The problem
Computing resources
Local resources
Web applications
Network latency
Server-client communications
Apparent responsiveness
Pareto’s Principle
The fetchone() method
The fetchmany() method
Iteration: What is it?
Generating loops
while…if loops
The for loop
Illustrative iteration
Iteration and MySQL for Python
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
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
Chapter 7: Creating and Dropping 183
Creating databases 183
Test first, create second 184
CREATE specifications 185
Specifying the default character set 185
Specifying the collation for a database 186
Declaring collation 186
Finding available character sets and collations 187
Removing or deleting databases 187
Avoiding errors 188
Preventing (illegal) access after a DROP 188
Creating tables 189
Covering our bases 190
Avoiding errors 191
Creating temporary tables 191
Dropping tables 192
Playing it safe 192
Avoiding errors 193
Removing user privileges 193
Doing it in Python 193
Creating databases with MySQLdb 194
Testing the output 194
Dynamically configuring the CREATE statement 195
Dropping databases with MySQLdb 195
Creating tables in Python 195
Verifying the creation of a table 196
Another way to verify table creation 197
Dropping tables with MySQLdb 198
Project: Web-based administration of MySQL 198
CGI vs PHP: What is the difference? 199
Basic CGI 200
Using PHP as a substitute for CGI 202
CGI versus PHP: When to use which? 203
Some general considerations for this program 203
Program flow 203
The basic menu 204
Authorization details 206
Three operational sections of the dialogue 206
The variables 206
Planning the functions 207
Code of each function 207
Connecting without a database 207
Connecting with a database 208
Database action 208
Table action 209
Query action 210
execute() 211
The HTML output 212
Basic definition 212
The message attribute 213
Defining header() 213
Defining footer() 213
Defining body() 214
Defining page() 214
Getting the data
Using CGI
Using PHP
Defining main()
Room to grow
Chapter 8: Creating Users and Granting Access 219
A word on security 219
Creating users in MySQL 220
Forcing the use of a password 221
Restricting the client’s host 221
Creating users from Python 223
Removing users in MySQL 224
DROPping users in Python 225
GRANT access in MySQL 225
Important dynamics of GRANTing access 226
The GRANT statement in MySQL 226
Using REQUIREments of access 229
Using a WITH clause 230
Granting access in Python 231
Removing privileges in MySQL 233
Basic syntax 233
After using REVOKE, the user still has access!? 233
Using REVOKE in Python 235
Project: Web-based user administration 236
New options in the code 236
Adding the functions: CREATE and DROP 239
Adding CREATE and DROP to main() 240
Adding the functions: GRANT and REVOKE 241
Adding GRANT and REVOKE to main() 241
Test the program
New options on the page
Room to grow
Chapter 9: Date and Time Values 247
Date and time data types in MySQL 247
Output format 248
Input formats 248
Input range 249
Using DATETIME in a CREATE statement 249
DATE 249
Output and Input formats 249
Input range 250
Input of values 250
Range 251
Defaults, initialization, and updating 251
YEAR 252
Two-digit YEAR values 252
Four-digit YEAR values 252
Valid input 253
TIME 253
Format 254
Invalid values 255
Date and time types in Python 256
Date and time functions 257
NOW() 260
DATE() 261
DATE_SUB() and DATE_ADD() 262
TIME() 270
Project: Logging user activity 270
The log framework 272
The logger() function 273
Creating the database 273
Using the database 274
Creating the table 274
Forming the INSERT statement 274
Ensure logging occurs 275
Room to grow
Chapter 10: Aggregate Functions and Clauses 279
Calculations in MySQL 280
COUNT() 281
SUM() 282
MAX() 283
MIN() 284
AVG() 284
The different kinds of average 285
Trimming results 287
Specifying the delimiter 290
Customizing the maximum length 290
Server-side sorting in MySQL 292
Using a universal quantifier 294
Sorting alphabetically or from low-to-high 295
Reversing the alphabet or sorting high-to-low 296
Sorting with multiple keys 298
Putting it in Python 298
Project: Incorporating aggregate functions 300
Adding to qaction() 300
New variables 301
New statement formation 302
Revising main() 305
Setting up the options 308
Changing the HTML form 309
Summary 310
Chapter 11: SELECT Alternatives 311
HAVING clause 312
WHERE versus HAVING: Syntax 312
WHERE versus HAVING: Aggregate functions 312
WHERE versus HAVING: Application 314
Subqueries 317
Unions 319
Joins 321
LEFT and RIGHT joins 321
OUTER joins 323
INNER joins 324
NATURAL joins 326
CROSS joins 327
Doing it in Python 327
Subqueries 328
Unions 329
Joins 329
Project: Implement HAVING 330
Revising the Python backend 331
Revising qaction() 331
Revising main() 333
Revising the options 336
Revising the HTML interface 337
Room to grow 338
Summary 339
Chapter 12: String Functions 341
Preparing results before their return 341
CONCAT() function 342
SUBSTRING() or MID() 343
TRIM() 344
Basic syntax 344
Options 345
Alternatives 346
INSERT() 348
Accessing and using index data 354
LENGTH() 354
INSTR() or LOCATE() 355
INSTR() 356
LOCATE() 356
Nuancing data 357
ROUND() 357
FORMAT() 359
UPPER() 360
LOWER() 360
Project: Creating your own functions 360
Hello() 361
Capitalise() 362
The function definition 362
Calling the function 364
Defining the function in Python 365
Defining the function as a Python value 365
Sourcing the MySQL function as a Python module 366
Sourcing the function as MySQL code 366
Room to grow 367
Summary 367
Chapter 13: Showing MySQL Metadata 369
MySQL’s system environment 370
The most popular engines 372
Transactions 372
Specifying the engine 373
ENGINE status 373
Profiling 375
SHOW system variables 376
Accessing database metadata 377
Using the USE command 378
Accessing metadata about tables 378
Showing columns from a table 379
Accessing user metadata 383
Project: Building a database class 384
Writing the class 384
Defining fetchquery() and some core methods 385
Retrieving table status and structure 386
Retrieving the CREATE statements 386
Define main()—part 1 387
Writing resproc() 388
Define main()—part 2 389
The preamble 389
Modules and variables 390
Login and USE 390
Closing out the program 390
Room to grow 391
Summary 391
Chapter 14: Disaster Recovery 393
Every database needs a backup plan 394
Offline backups 394
Live backups 395
Choosing a backup method 395
Copying the table files 396
Locking and flushing 397
Unlocking the tables 398
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
Backing up a database with Python

MySQL for Python

Top books

About huydam

Check Also

[UWP] The program could not build Windows Universal Samples

If you get this error like this: “Type universe cannot resolve assembly: System.Runtime, Version=, Culture=neutral, …

Leave a Reply

Your email address will not be published. Required fields are marked *