Reading Records From the MySQL Database

00:00 In this lesson, you will learn how to retrieve records from the MySQL database using SQL SELECT and WHERE commands, and Python fetchall() and fetchmany() commands.

00:11 Before you proceed, make sure that movies, ratings, and reviewers tables are populated with data. You can make use of the MySQL Workbench to confirm.

00:29 Next, you’ll create a new file.

00:40 Let’s call it MySQLcode_retrieve.

00:48 In this code, you’ll reuse a code snippet for connecting to the online movie rating database. You can grab it from your previous file.

01:01 Next, connect to the database and enter your username as root and your password.

01:12 You are connected to the database. To retrieve records, you need to write an SQL query using a SELECT command. Type sql = in quotes, type SELECT.

01:26 If you want to retrieve all columns, then you type an asterisk symbol * FROM movies LIMIT 5. In this query, you use the LIMIT clause to retrieve a limited number of rows received from the SELECT statement.

01:46 To execute this statement, type this: connection.cursor() as cursor: cursor.execute( sql) result = cursor.fetchall() for row in result: print(row).

02:20 Let’s run the cell. The result variable holds the records returned from using fetchall(). It’s a list of tuples representing individual records from the table.

02:35 You can also retrieve a specific number of rows using fetchmany() command. To prepare for data retrieval using fetchmany(), I recommend enabling buffered mode over cursor object.

02:50 Specifically, define cursor as connection. cursor(buffered =True). Why do you need a buffered cursor?

03:08 It’ll allow you to execute several queries in a sequence avoiding a well-known internal error on finding unread results. This error has to do with how the Python MySQL connector handles execution of retrieval requests. fetchmany() typically returns a specified number of rows, and the rest of the rows is left behind.

03:33 When you execute another query on the unbuffered cursor, the MySQL connector will find leftover rows from your previous query and generate an internal error reporting unread results found. When you enable buffered mode on your cursor, the MySQL connector will load the entire set of rows from your query into memory, and further queries will be executed without encountering unread results.

04:05 Let’s define a new variable, sql1, as an SQL statement without using a LIMIT clause. SELECT an asterisk symbol FROM movies.

04:21 To execute it, type with cursor: cursor.execute(sql1) result = cursor. fetchmany( 5). You would like to return the first five rows. for row in result: print(row).

04:55 Let’s run the cell. As you can see, you have arrived at the same result as the one generated by the previous execution. Whether you use a LIMIT clause in an SQL query or the fetchmany() command in the Python MySQL connector depends on your use case.

05:19 If you need to retrieve only the first few rows of a query, the LIMIT clause is the better choice as it optimizes performance at the database level.

05:31 For small to medium-sized datasets, you can fetch rows in batches using the fetchmany() command within a loop. This approach significantly reduces memory usage since only one batch of data is loaded into memory at a time.

05:49 However, for large datasets, fetchmany() alone may not provide full memory optimization and alternative memory-efficient techniques should be considered.

06:03 You can also query for selected columns. Define sql2 as the following query: SELECT title, release_year FROM movies LIMIT 5 with connection.cursor() as cursor: cursor.execute( sql2) result = cursor. fetchall() for row in result: print(row). Run it.

06:52 Observe that this code outputs only the two specified columns, title and release_year. You can also filter table records by specific criteria using the WHERE clause.

07:09 For example, to retrieve all movies with a box office collection greater than $300 million, you can write the following query: sql3 = in triple quotes, type SELECT title, collection_in_mil FROM movies WHERE collection_in_mil > 300 ORDER BY collection_ in_mil DESC.

07:56 Here you have also added an ORDER BY clause to return the results from the highest to the lowest earner. To execute this query, let’s copy the previous code snippet.

08:17 Let’s run it. As you can see, you have generated a list of all movies that have made above $300 million dollars in revenue, with Avengers being the top performer.

08:33 You can take advantage of MySQL string format manipulations like concatenating strings. Often, websites will show the movie title along with its release year.

08:47 Let’s retrieve the titles of the top five grossing movies concatenated with the release years. Type sql4 =

09:02 SELECT CONCAT( and in parenthesis type title, left parenthesis with the space in front of it, comma release_year, and right parenthesis.

09:23 And you would like to return collection_

09:29 in_mil FROM movies ORDER BY collection_in_mil

09:42 DESC stands for descending order.

09:48 Let’s copy and paste the execution code snippet.

09:57 Let’s execute the cell. As a result, you see here all movies appearing as concatenated name and release year along with their revenue.

10:12 Congratulations. You have learned how to use a combination of SQL and Python data query and retrieval commands to pull data of interest from the MySQL database.