Mysql fetch all python

Fetch data from MySQL table in Python Program

This Python tutorial is all about how to Retrieve data from MySQL Table in Python. Here we will be focusing on how to use SQL SELECT FROM Statement in Python Program.

We will discuss from the beginning level so that this tutorial considered easy for the learners too.

Read data from MySQL Database Table in Python

To read MySQL Data in Python we need to learn some basics of setting up our MySQL Connection with our Python program.

Stepts to be followed before starting to fetch data from a MySQL Table in Python

1. Install MySQL Connector in Python – Please note that in this article we will be working with MySQL Connector/Python, as this is one of the best connectors. But if you wish you can use other modules such as PyMySQL, MySQLDB, OurSQL. All the modules are almost the same in terms of method, syntax and the way of accessing a database.
2. Connect MySQL with Python – You must have to build the connection successfully between the database and your Python Program.
3. Create a database in MySQL using Python– If you don’t have a database to work with then create a database where you can keep your table and all the data.
4. Create MySQL table in Python – If you don’t have a table create one with this tutorial.
5. Insert data into MySQL Table in Python Programming – You are going to learn how to fetch data, so it is always better to insert the data first in the table.

Once you are done with all the 5 steps mentioned above you are ready to Fetch MySQL data from table using Python.

Steps: Read data from MySQL table in Python

• Execution of SELECT Query using execute() method.
• Process the execution result set data.
• Use fetchall(), fetchmany(), fetchone() based on your needs to return list data.
• Use for loop to return the data one by one.

The following things are mandatory to fetch data from your MySQL Table

• Password and username of your MySQL server to connect MySQL with your Python Program.
• Database name and table name.

Here is a demo table from where I am going to fetch data in Python

Demo table to show How to Fetch MySQL Data in Python

Table name: codespeedy

Python Program to fetch data from MySQL table with SELECT Query

The below Python program will return all the rows from the table codespeedy.

Fetch all rows from a MySQL Table in Python using fetchall()

import mysql.connector db_connection = mysql.connector.connect( host="localhost", user="username_of_your_MySQL_server", passwd="password_of_your_mysql_server", database="your_database_name" ) my_database = db_connection.cursor() sql_statement = "SELECT * FROM CODESPEEDY" my_database.execute(sql_statement) output = my_database.fetchall() for x in output: print(x)

('Java', '80 Hours', 'Advanced') ('Php', '70 Hours', 'Basic') ('Python', '100 Hours', 'intermediate')

Explanation of this Python Code:

• db_connection variable stores the required info to build our MySQL Connection.
• We have stored the MySQL SELECT query in variable sql_statement.
• execute() method is used for the execution of our MySQL Query.
• Then fetchall() method is used to return all the result set as a list of tuples.
  • to learn more on fetchall() documentation of fetchall()

  Read a particular row from MySQL table in Python

  Here we will fetch a particular row from MySQL table in Python

  For example, let’s fetch all the data from row no 3.

  import mysql.connector db_connection = mysql.connector.connect( host="localhost", user="username_of_your_MySQL_server", passwd="password_of_your_mysql_server", database="your_database_name" ) my_database = db_connection.cursor() sql_statement = "SELECT * FROM CODESPEEDY" my_database.execute(sql_statement) output = my_database.fetchall() print (output3)

  ('Python', '100 Hours', 'intermediate')

  So you can see that from our table only the data from row no 3 is printed.

  n will be the row number, we want to be fetched.

  To print the data from row no 1 you have to use:

  fetchmany() method to select limited number of rows from MySQL table in Python

  The example table we have given is consisting of 3 rows. But just think of it, what will happen if you are having millions of rows and you need to fetch a fewer number of rows from the table. In this type of situations fetchmany() decreases the time of execution of our code. As our program does not need to fetch all the rows one by one where we only need a fewer number of rows.

  fetchmany() method allow us to select a fewer number of rows from MySQL table in Python

  If you want to select only first 4 rows from a table just put 4 instead of that n.

  Example of fetchmany() in Python to select a fewer number of rows from a MySQL Database table

  Again we are taking the first demo table in this example.

  

  Demo table to show How to Fetch MySQL Data in Python – fetchmany() method

  import mysql.connector db_connection = mysql.connector.connect( host="localhost", user="username_of_your_MySQL_server", passwd="password_of_your_mysql_server", database="your_database_name" ) my_database = db_connection.cursor() sql_statement = "SELECT * FROM CODESPEEDY" my_database.execute(sql_statement) output = my_database.fetchmany(size=2) for x in output: print(x)

  ('Java', '80 Hours', 'Advanced') ('Php', '70 Hours', 'Basic')

  So you can see we have set the size=2, thus we are getting only the first two rows from our MySQL table.

  Default size of fetchmany() method is one.

  fetchone() example in Python to select a single row from a MySQL table

  import mysql.connector db_connection = mysql.connector.connect( host="localhost", user="username_of_your_MySQL_server", passwd="password_of_your_mysql_server", database="your_database_name" ) my_database = db_connection.cursor() sql_statement = "SELECT * FROM CODESPEEDY" my_database.execute(sql_statement) output = my_database.fetchone() for x in output: print(x)

  We hope that this Python MySQL tutorial on How to read MySQL Data in Python was helpful.

  2 responses to “Fetch data from MySQL table in Python Program”

  Hey,
  i wanted to know how to print the output data in table format and sort the particular column in ascending order(any column)

  Источник

  Python fetchone fetchall records from MySQL

  Method fetchone collects the next row of record from the table.
  We defined my_conn as connection object.

  my_cursor = my_conn.cursor() my_cursor.execute("SELECT * FROM student") my_result = my_cursor.fetchone() # we get a tuple #print each cell ( column ) in a line print(my_result) #Print each colomn in different lines. for x in my_result: print(x)

  Tutorial on MySQL SELECT Query

  The retured data from database is a tuple. In above code we can check the data type of variable my_result like this

  print(type(my_result)) # Output is

  The output of our above code is here

  (1, 'John Deo', 'Four', 75, 'female') 1 John Deo Four 75 female

  This is our first record of the student table.

  To display columns we can use like this

  my_cursor = my_conn.cursor() my_cursor.execute("SELECT * FROM student") my_result = my_cursor.fetchone() print("Student Student Name = ",my_result[1]) print("Student Student Mark = ",my_result[3]) print("Student gender Student Student Name = ",my_result[1]) print("Student Student Mark = ",my_result[3]) print("Student gender SELECT * FROM student") my_result = my_cursor.fetchone() while my_result is not None: print(my_result) my_result = my_cursor.fetchone()

  (1, 'John Deo', 'Four', 75, 'female') (2, 'Max Ruin', 'Three', 85, 'male') (3, 'Arnold', 'Three', 55, 'male') - - - - - - - - - - - -

  my_cursor = my_conn.cursor() my_cursor.execute("SELECT * FROM student") for my_result in my_cursor: print(my_result)

  fetchall()

  my_cursor = my_conn.cursor() my_cursor.execute("SELECT * FROM student") my_result=my_cursor.fetchall() for row in my_result: print(row)

  get a list with column values

  q="SELECT id,name FROM student WHERE " my_cursor=my_conn.execute(q) my_result=my_cursor.fetchall() my_ids = [row[0] for row in my_result] # All id as list my_names = [row[1] for row in my_result] # All names as list

  q="SELECT class,count(*) as no FROM student GROUP BY class"

  Getting column names

  q="SELECT id,name FROM student WHERE " my_cursor=my_conn.execute(q) columns=list(my_cursor.keys()) # column names as list 

  fetchmany()

  my_cursor = my_conn.cursor() my_cursor.execute("SELECT * FROM student") my_result_top=my_cursor.fetchmany(size=3) #my_result=my_cursor.fetchall() for row in my_result_top: print(row)

  (1, 'John Deo', 'Four', 75, 'female') (2, 'Max Ruin', 'Three', 85, 'male') (3, 'Arnold', 'Three', 55, 'male')

  my_cursor = my_conn.cursor() my_cursor.execute("SELECT * FROM student") my_result_top=my_cursor.fetchmany() for row in my_result_top: print(row)

  (1, 'John Deo', 'Four', 75, 'female')

  my_cursor = my_conn.cursor() my_cursor.execute("SELECT * FROM student") my_result_top=my_cursor.fetchmany(size=3) for row in my_result_top: print(row) my_result=my_cursor.fetchall() for row in my_result: print(row)

  (1, 'John Deo', 'Four', 75, 'female') (2, 'Max Ruin', 'Three', 85, 'male') (3, 'Arnold', 'Three', 55, 'male') (4, 'Krish Star', 'Four', 60, 'female') (5, 'John Mike', 'Four', 60, 'female') (6, 'Alex John', 'Four', 55, 'male') - - - - - - - - - - - -

  Note that after using fetchmany(size=3), fetchall() returns balance rows starting from 4th record.

  You can see we are not using the SQL again, so it is evident that after retrieving all data from MySQL ( by using the SQL ) records are stored in Python memory and we only retrieve part of the available records by using fetchmany() and rest of the records are collected through fetchall() from Python memory and not from database again.

  my_cursor = my_conn.cursor() my_cursor.execute("SELECT * FROM student WHERE row in my_result: print(row)

  my_cursor.execute("SELECT * FROM student LIMIT 0,10")

  Changing Query

  We used SQL to collect all the records from MySQL table. We can change this SQL to limit the number of records. This is better way than collecting all records and using limited numbers from them by using fetchmany() or fetchone(). We can change the SQL part like this ( in above code )
  Using sqlalchemy to Manage MySQL

  Dictionary from database record set

  from sqlalchemy import create_engine my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial") query = "SELECT * FROM student LIMIT 0,5" my_data = list(my_conn.execute(query)) # SQLAlchem engine result set my_dict = <> # Create an empty dictionary my_list = [] # Create an empty list for row in my_data: my_dict[[row][0][0]] = row # id as key my_list.append(row[1]) # name as list print(my_dict) print(my_list) # Print the other values for matching Name for i, j in my_dict.items(): if j[1] == "Arnold": print(i, j[0], j[1], j[2])

  Источник