澳门新浦京娱乐场网站-www.146.net-新浦京娱乐场官网
做最好的网站

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 such as fetchone() , fetchmany() , and fetchall() .

This tutorial shows you how to query data from a MySQL database in Python by using MySQL Connector/Python API such as fetchone() , fetchmany()http://www.mysqltutorial.org/python-mysql-query/。 , and fetchall() .

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

We will show you how to use fetchone() , fetchmany() , and  fetchall() methods in more detail in the following sections.

MySQL Cursor

MySQL Cursor

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

Querying data with fetchone

The  fetchone() method returns the next row of a query result set or None in case there is no row left. Let’s take a look at the following code:

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 from mysql.connector import MySQLConnection, Error from python_mysql_dbconfig import read_db_config     def query_with_fetchone():     try:         dbconfig = read_db_config()         conn = MySQLConnection(**dbconfig)         cursor = conn.cursor()         cursor.execute("SELECT * FROM books")           row = cursor.fetchone()           while row is not None:             print(row)             row = cursor.fetchone()       except Error as e:         print(e)       finally:         cursor.close()         conn.close()     if __name__ == '__main__':     query_with_fetchone()

Let’s examine the code in detail:

Summary: in this tutorial, you will learn how to use MySQL cursor in stored procedures to iterate through a result set returned by a SELECT statement.

Summary: in this tutorial, you will learn how to use MySQL cursor in stored procedures to iterate through a result set returned by a SELECT statement.

  1. Connect to the MySQL Database, you get a MySQLConnection object.
  2. Instantiate a  MySQLCursor object from the the MySQLConnection object.
  3. Use the cursor to execute a query by calling its  execute() method.
  4. Use fetchone() ,  fetchmany() or  fetchall() method to fetch data from the result set.
  5. Close the cursor as well as the database connection by calling the  close() method of the corresponding object.

Querying data with fetchall

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

 

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 from mysql.connector import MySQLConnection, Error from python_mysql_dbconfig import read_db_config     def query_with_fetchall():     try:         dbconfig = read_db_config()         conn = MySQLConnection(**dbconfig)         cursor = conn.cursor()         cursor.execute("SELECT * FROM books")         rows = cursor.fetchall()           print('Total Row(s):', cursor.rowcount)         for row in rows:             print(row)       except Error as e:         print(e)       finally:         cursor.close()         conn.close()     if __name__ == '__main__':     query_with_fetchall()

The logic is similar to the example with the  fetchone() method except for the  fetchall()method call part. Because we fetched all rows from the books table into the memory, we can get the total rows returned by using the  rowcount property of the cursor object.

Introduction to MySQL cursor

To handle a result set inside a stored procedure, you use a cursor. A cursor allows you to iterate a set of rows returned by a query and process each row accordingly.

MySQL cursor is read-only, non-scrollable and asensitive.

  • Read only: you cannot update data in the underlying table through the cursor.
  • Non-scrollable: you can only fetch rows in the order determined by the SELECT statement. You cannot fetch rows in the reversed order. In addition, you cannot skip rows or jump to a specific row in the result set.
  • Asensitive: there are two kinds of cursors: asensitive cursor and insensitive cursor. An asensitive cursor points to the actual data, whereas an insensitive cursor uses a temporary copy of the data. An asensitive cursor performs faster than an insensitive cursor because it does not have to make a temporary copy of data. However, any change that made to the data from other connections will affect the data that is being used by an asensitive cursor, therefore, it is safer if you don’t update the data that is being used by an asensitive cursor. MySQL cursor is asensitive.

You can use MySQL cursors in stored procedures, stored functions, and triggers.

Introduction to MySQL cursor

To handle a result set inside a stored procedure, you use a cursor. A cursor allows you to iterate a set of rows returned by a query and process each row accordingly.

MySQL cursor is read-only, non-scrollable and asensitive.

  • Read only: you cannot update data in the underlying table through the cursor.
  • Non-scrollable: you can only fetch rows in the order determined by the SELECT statement. You cannot fetch rows in the reversed order. In addition, you cannot skip rows or jump to a specific row in the result set.
  • Asensitive: there are two kinds of cursors: asensitive cursor and insensitive cursor. An asensitive cursor points to the actual data, whereas an insensitive cursor uses a temporary copy of the data. An asensitive cursor performs faster than an insensitive cursor because it does not have to make a temporary copy of data. However, any change that made to the data from other connections will affect the data that is being used by an asensitive cursor, therefore, it is safer if you don’t update the data that is being used by an asensitive cursor. MySQL cursor is asensitive.

You can use MySQL cursors in stored procedures, stored functions, and triggers.

We will show you how to use fetchone() , fetchmany() , and  fetchall() methods in more detail in the following sections.

Querying data with fetchmany

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 with the  fetchmany() 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 use  fetchmany() method.

First, we develop a generator that chunks the database calls into a series of  fetchmany() calls as follows:

 

1 2 3 4 5 6 7 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 use the  iter_row() generator to fetch 10 rows at a time as shown below:

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 def query_with_fetchmany():     try:         dbconfig = read_db_config()         conn = MySQLConnection(**dbconfig)         cursor = conn.cursor()           cursor.execute("SELECT * FROM books")           for row in iter_row(cursor, 10):             print(row)       except Error as e:         print(e)       finally:         cursor.close()         conn.close()

This tutorial shows you how to query data from a MySQL database in Python by using MySQL Connector/Python API such as fetchone()...

Working with MySQL cursor

First, you have to declare a cursor by using the DECLARE statement:

 

1
DECLARE cursor_name CURSOR FOR SELECT_statement;

The cursor declaration must be after any variable declaration. If you declare a cursor before variables declaration, MySQL will issue an error. A cursor must always be associated with aSELECT statement.

Next, you open the cursor by using the OPEN statement. The OPEN statement initializes the result set for the cursor, therefore, you must call the OPEN statement before fetching rows from the result set.

 

1
OPEN cursor_name;

Then, you use the FETCH statement to retrieve the next row pointed by the cursor and move the cursor to the next row in the result set.

 

1
FETCH cursor_name INTO variables list;

After that, you can check to see if there is any row available before fetching it.

Finally, you call the CLOSE statement to deactivate the cursor and release the memory associated with it as follows:

 

1
CLOSE cursor_name;

When the cursor is no longer used, you should close it.

When working with MySQL cursor, you must also declare a NOT FOUND handler to handle the situation when the cursor could not find any row. Because each time you call the FETCHstatement, the cursor attempts to read the next row in the result set. When the cursor reaches the end of the result set, it will not be able to get the data, and a condition is raised. The handler is used to handle this condition.

To declare a NOT FOUND handler, you use the following syntax:

 

1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

Where finished is a variable to indicate that the cursor has reached the end of the result set. Notice that the handler declaration must appear after variable and cursor declaration inside the stored procedures.

The following diagram illustrates how MySQL cursor works.

图片 1

Working with MySQL cursor

First, you have to declare a cursor by using the DECLARE statement:

 

1
DECLARE cursor_name CURSOR FOR SELECT_statement;

The cursor declaration must be after any variable declaration. If you declare a cursor before variables declaration, MySQL will issue an error. A cursor must always be associated with aSELECT statement.

Next, you open the cursor by using the OPEN statement. The OPEN statement initializes the result set for the cursor, therefore, you must call the OPEN statement before fetching rows from the result set.

 

1
OPEN cursor_name;

Then, you use the FETCH statement to retrieve the next row pointed by the cursor and move the cursor to the next row in the result set.

 

1
FETCH cursor_name INTO variables list;

After that, you can check to see if there is any row available before fetching it.

Finally, you call the CLOSE statement to deactivate the cursor and release the memory associated with it as follows:

 

1
CLOSE cursor_name;

When the cursor is no longer used, you should close it.

When working with MySQL cursor, you must also declare a NOT FOUND handler to handle the situation when the cursor could not find any row. Because each time you call the FETCHstatement, the cursor attempts to read the next row in the result set. When the cursor reaches the end of the result set, it will not be able to get the data, and a condition is raised. The handler is used to handle this condition.

To declare a NOT FOUND handler, you use the following syntax:

 

1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

Where finished is a variable to indicate that the cursor has reached the end of the result set. Notice that the handler declaration must appear after variable and cursor declaration inside the stored procedures.

The following diagram illustrates how MySQL cursor works.

图片 2

Querying data with fetchone

The  fetchone() method returns the next row of a query result set or None in case there is no row left. Let’s take a look at the following code:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config
 
 
def query_with_fetchone():
    try:
        dbconfig = read_db_config()
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM books")
 
        row = cursor.fetchone()
 
        while row is not None:
            print(row)
            row = cursor.fetchone()
 
    except Error as e:
        print(e)
 
    finally:
        cursor.close()
        conn.close()
 
 
if __name__ == '__main__':
    query_with_fetchone()

Let’s examine the code in detail:

  1. First, we connected to the database by create a new  MySQLConnection object
  2. Second, from the  MySQLConnection object, we instantiated a new  MySQLCursor object
  3. Third, we executed a query that selects all rows from the books table.
  4. Fourth, we called  fetchone() method to fetch the next row in the result set. In the  while loop block, we printed out the content of the row and move to the next row until all rows are fetched.
  5. Fifth, we closed both cursor and connection objects by invoking the  close() method of the corresponding object.

MySQL Cursor Example

We are going to develop a stored procedure that builds an email list of all employees in theemployees table in the MySQL sample database.

First, we declare some variables, a cursor for looping over the emails of employees, and a NOT FOUND handler:

 

1
2
3
4
5
6
7
8
9
10
DECLARE finished INTEGER DEFAULT 0;
DECLARE email varchar(255) DEFAULT "";
 
-- declare cursor for employee email
DEClARE email_cursor CURSOR FOR
SELECT email FROM employees;
 
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;

Next, we open the email_cursor by using the OPEN statement:

 

1
OPEN email_cursor;

Then, we iterate the email list, and concatenate all emails where each email is separated by a semicolon(;):

 

1
2
3
4
5
6
7
8
get_email: LOOP
FETCH email_cursor INTO v_email;
IF v_finished = 1 THEN
LEAVE get_email;
END IF;
-- build email list
SET email_list = CONCAT(v_email,";",email_list);
END LOOP get_email;

After that, inside the loop we used the v_finished variable to check if there is any email in the list to terminate the loop.

Finally, we close the cursor using the CLOSE statement:

 

1
CLOSE email_cursor;

The build_email_list stored procedure is as follows:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
DELIMITER $$
 
CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000))
BEGIN
 
DECLARE v_finished INTEGER DEFAULT 0;
        DECLARE v_email varchar(100) DEFAULT "";
 
-- declare cursor for employee email
DEClARE email_cursor CURSOR FOR
SELECT email FROM employees;
 
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET v_finished = 1;
 
OPEN email_cursor;
 
get_email: LOOP
 
FETCH email_cursor INTO v_email;
 
IF v_finished = 1 THEN
LEAVE get_email;
END IF;
 
-- build email list
SET email_list = CONCAT(v_email,";",email_list);
 
END LOOP get_email;
 
CLOSE email_cursor;
 
END$$
 
DELIMITER ;

You can test the build_email_list stored procedure using the following script:

 

1
2
3
SET @email_list = "";
CALL build_email_list(@email_list);
SELECT @email_list;

In this tutorial, we have shown you how to use MySQL cursor to iterate a result set and process each row accordingly.

MySQL Cursor Example

We are going to develop a stored procedure that builds an email list of all employees in theemployees table in the MySQL sample database.

First, we declare some variables, a cursor for looping over the emails of employees, and a NOT FOUND handler:

 

1
2
3
4
5
6
7
8
9
10
DECLARE finished INTEGER DEFAULT 0;
DECLARE email varchar(255) DEFAULT "";
 
-- declare cursor for employee email
DEClARE email_cursor CURSOR FOR
SELECT email FROM employees;
 
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;

Next, we open the email_cursor by using the OPEN statement:

 

1
OPEN email_cursor;

Then, we iterate the email list, and concatenate all emails where each email is separated by a semicolon(;):

 

1
2
3
4
5
6
7
8
get_email: LOOP
FETCH email_cursor INTO v_email;
IF v_finished = 1 THEN
LEAVE get_email;
END IF;
-- build email list
SET email_list = CONCAT(v_email,";",email_list);
END LOOP get_email;

After that, inside the loop we used the v_finished variable to check if there is any email in the list to terminate the loop.

Finally, we close the cursor using the CLOSE statement:

 

1
CLOSE email_cursor;

The build_email_list stored procedure is as follows:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
DELIMITER $$
 
CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000))
BEGIN
 
DECLARE v_finished INTEGER DEFAULT 0;
        DECLARE v_email varchar(100) DEFAULT "";
 
-- declare cursor for employee email
DEClARE email_cursor CURSOR FOR
SELECT email FROM employees;
 
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET v_finished = 1;
 
OPEN email_cursor;
 
get_email: LOOP
 
FETCH email_cursor INTO v_email;
 
IF v_finished = 1 THEN
LEAVE get_email;
END IF;
 
-- build email list
SET email_list = CONCAT(v_email,";",email_list);
 
END LOOP get_email;
 
CLOSE email_cursor;
 
END$$
 
DELIMITER ;

You can test the build_email_list stored procedure using the following script:

 

1
2
3
SET @email_list = "";
CALL build_email_list(@email_list);
SELECT @email_list;

In this tutorial, we have shown you how to use MySQL cursor to iterate a result set and process each row accordingly.

Querying data with fetchall

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

 

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config
 
 
def query_with_fetchall():
    try:
        dbconfig = read_db_config()
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM books")
        rows = cursor.fetchall()
 
        print('Total Row(s):', cursor.rowcount)
        for row in rows:
            print(row)
 
    except Error as e:
        print(e)
 
    finally:
        cursor.close()
        conn.close()
 
 
if __name__ == '__main__':
    query_with_fetchall()

The logic is similar to the example with the  fetchone() method except for the  fetchall()method call part. Because we fetched all rows from the books table into the memory, we can get the total rows returned by using the  rowcount property of the cursor object.

Querying data with fetchmany

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 with the  fetchmany() 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 use  fetchmany() method.

First, we develop a generator that chunks the database calls into a series of  fetchmany() calls as follows:

 

1
2
3
4
5
6
7
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 use the  iter_row() generator to fetch 10 rows at a time as shown below:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
def query_with_fetchmany():
    try:
        dbconfig = read_db_config()
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
 
        cursor.execute("SELECT * FROM books")
 
        for row in iter_row(cursor, 10):
            print(row)
 
    except Error as e:
        print(e)
 
    finally:
        cursor.close()
        conn.close()

本文由澳门新浦京娱乐场网站发布于数据库,转载请注明出处:http://www.mysqltutorial.org/python-mysql-query/