Http://www.mysqltutorial.org/python-mysql-query,

Http://www.mysqltutorial.org/python-mysql-query,

This tutorial shows you how to query data from a MySQL database in Python by using MySQL Connector/Python API suchfetchone(),fetchmany(), Andfetchall().

To query data in a MySQL database from Python, you need to do the following steps:

We will show you how to usefetchone(),fetchmany(), Andfetchall()Methods in more detail in the following sections.

Querying data with fetchone

Thefetchone()Method returns the next row of a query result set orNoneIn case there is no row left. Let's take a look at the following code:

 

123456789101112131415161718192021222324252627

From mysql. connector import MySQLConnection, Errorfrom python_mysql_dbconfig import read_db_config def query_with_fetchone (): try: dbconfig = read_db_config () conn = MySQLConnection (** dbconfig) cursor = conn. cursor () cursor.exe cute ("SELECT * FROM books") row = cursor. fetchone () while row is not None: print (row) row = cursor. fetchone () failed T Error as e: print (e) finally: cursor. close () conn. close () if _ name _ = '_ main _': query_with_fetchone ()

Let's examine the code in detail:

Querying data with fetchall

In case the number of rows in the table is small, you can usefetchall()Method to fetch all rows from the database table. See the following code.

 

 

1234567891011121314151617181920212223242526

From mysql. connector import MySQLConnection, Errorfrom python_mysql_dbconfig import read_db_config def query_with_fetchall (): try: dbconfig = read_db_config () conn = MySQLConnection (** dbconfig) cursor = conn. cursor () cursor.exe cute ("SELECT * FROM books") rows = cursor. fetchall () print ('total Row (s): ', cursor. rowcount) for row in rows: print (row) failed T Error as e: print (e) finally: cursor. close () conn. close () if _ name _ = '_ main _': query_with_fetchall ()

The logic is similar to the example withfetchone()Method used t forfetchall()Method call part. Because we fetched all rows from the books table into the memory, we can get the total rows returned by usingrowcountProperty of the cursor object.

Querying data with fetchtasks

For a relatively big table, it takes time to fetch all rows and return the result set. In addition,fetchall()Needs to allocate enough memory to store the entire result set in the memory. This is inefficient and not a good practice.

MySQL Connector/Python provides us withfetchmany()Method that returns the next number of rows (n) of the result set, which allows us to balance between time and memory space. Let's take a look at how do we usefetchmany()Method.

First, we develop a generator that chunks the database callinto a seriesfetchmany()CILS as follows:

 

1234567

Def iter_row (cursor, size = 10): while True: rows = cursor. fetchmany (size) if not rows: break for row in rows: yield row

Second, we can useiter_row()Generator to fetch 10 rows at a time as shown below:

 

1234567891011121314151617

Def query_with_fetchconnection (): try: dbconfig = read_db_config () conn = MySQLConnection (** dbconfig) cursor = conn. cursor () cursor.exe cute ("SELECT * FROM books") for row in iter_row (cursor, 10): print (row) failed T Error as e: print (e) finally: cursor. close () conn. close ()