read_sql to get MySQL data to DataFrame

Before collecting data from MySQL , you should have Python to MySQL connection and use the SQL dump to create student table with sample data.

More on Python & MySQL

We will use read_sql to execute query and store the details in Pandas DataFrame.
import mysql.connector
import pandas as pd 
my_connect = mysql.connector.connect(
      host="localhost",
      user="userid",
      passwd="******",
      database="my_tutorial"
    )
####### end of connection ####
my_data = pd.read_sql("SELECT * FROM student WHERE class='four'",my_connect)
print(my_data)
Output is here
   id         name class  mark     sex
0   1     John Deo  Four    75  female
1   4   Krish Star  Four    60  female
2   5    John Mike  Four    60  female
3   6    Alex John  Four    55    male
4  10     Big John  Four    55  female
5  15     Tade Row  Four    88    male
6  16        Gimmy  Four    88    male
7  21   Babby John  Four    69  female
8  31  Marry Toeey  Four    88    male
To get only specified columns
print(my_data[['name','class']])

Passing parameters to query

We will use params to pass parameters to our query
my_data = pd.read_sql("SELECT * FROM student WHERE class=%s",my_connect,params=('Five',))
Note that params takes list or tuple or dictionary. In the above code we used one tuple ( note the comma at the end of the tupple).
These are valid codes using different types of params
my_data = pd.read_sql("SELECT * FROM student WHERE class=%s",my_connect,params={'Four'})
Using List
my_data = pd.read_sql("SELECT * FROM student WHERE class=%s",my_connect,params=['Four'])
Using more parameters with SQL in ( read more on WHERE query )
sql="SELECT * FROM student WHERE class=%s and id in(%s,%s)"
my_data = pd.read_sql(sql,my_connect,params=['Four',6,10] )
print(my_data)

chunksize

We can get an iterator by using chunksize in terms of number of rows of records.
sql="SELECT * FROM student "
my_data = pd.read_sql(sql,my_connect,chunksize=3 )
print(next(my_data)) 
print("--End of first set of  records ---")
print(next(my_data))
Output is here

   id      name  class  mark     sex
0   1  John Deo   Four    75  female
1   2  Max Ruin  Three    85    male
2   3    Arnold  Three    55    male
--End of first set of  records ---
   id        name class  mark     sex
0   4  Krish Star  Four    60  female
1   5   John Mike  Four    60  female
2   6   Alex John  Four    55    male
DataFrame to_sql to pass data from DataFrame to MySQL table
Your Rating




Post your comments , suggestion , error , requirements etc here




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