read_sql_table read data from MySQL table to Pandas DataFrame using SQLAlchemy create

pd.read_sql_table(table_name, con, schema=None, index_col=None,
 coerce_float=True, parse_dates=None, columns=None, chunksize=None)

`table_name`	Name of the Database table to collect data to DataFrame
`con`	Database connection string
`schema`	default = None, Name of the Schema in Database
`index_col`	Column to be used as index in DataFrame
`coerce_float`	bool, Converts non-string, non-numeric to float
`parse_date`	default=None, List of columns to be parse as Date
`columns`	List of columns to return, by default all columns are available
`chunksize`	Number of rows to be included on each Chunk, iterator is returned.

Database Connection

We will use SQLAlchemy to connect to MySQL database.
We used our sample table student.

Query and display record

Replace your MySQL login details.

import pandas as pd 
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")
my_data = pd.read_sql_table('student',my_conn)
print(my_data)

Output-There are 35 records , sample records are shown here.

    id         name  class  mark  gender
0    1     John Deo   Four    75  female
1    2     Max Ruin  Three    85    male
2    3       Arnold  Three    55    male
3    4   Krish Star   Four    60  female
4    5    John Mike   Four    60  female
-------
-------

index_col

You can see there is one index column in our table id. We can use that as our index. The sql and display part only changes here.

my_data = pd.read_sql_table('student',my_conn,index_col='id')
print(my_data)

Output ( sample )

           name  class  mark  gender
id                                  
1      John Deo   Four    75  female
2      Max Ruin  Three    85    male
3        Arnold  Three    55    male
4    Krish Star   Four    60  female
--------
--------

columns

my_data = pd.read_sql_table('student',my_conn,
  columns=['name','class'],index_col='id')
print(my_data)

Output ( sample )

           name  class
id                    
1      John Deo   Four
2      Max Ruin  Three
3        Arnold  Three
------
------

chunksize

my_data = pd.read_sql_table('student',my_conn,
  index_col='id',chunksize=3)
print(next(my_data))
print(next(my_data))

Output

        name  class  mark  gender
id                               
1   John Deo   Four    75  female
2   Max Ruin  Three    85    male
3     Arnold  Three    55    male
          name class  mark  gender
id                                
4   Krish Star  Four    60  female
5    John Mike  Four    60  female
6    Alex John  Four    55    male

Data input and output from Pandas DataFrame
Pandas DataFrame read_sql to_sql

Pandas DataFrame Python & MySQL Python- Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

read_sql_table to read MySQL table Data to DataFrame