read_sql_table to read MySQL table Data to DataFrame

pd.read_sql_table(table_name, con, schema=None, index_col=None,
 coerce_float=True, parse_dates=None, columns=None, chunksize=None)
table_nameName of the Database table to collect data to DataFrame
conDatabase connection string
schemadefault = None, Name of the Schema in Database
index_colColumn to be used as index in DataFrame
coerce_floatbool, Converts non-string, non-numeric to float
parse_datedefault=None, List of columns to be parse as Date
columnsList of columns to return, by default all columns are available
chunksizeNumber 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

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2021 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer