read_sql to create Pandas DataFrame by using query from MySQL database table with options.
https://www.plus2net.com/python/panda...
0:00:01 Introduction to process of reading data from MySQL
0:00:25 Connection to MySQL database using SQLAlchemy
0:01:36 Query to read data from MySQL database table
0:02:14 Create Pandas DataFrame using Data from Mysql table
0:03.13 Parameter to pass using params option
0:05:34 index_col
0:06:07 columns option to collect given columns only.
0:7:46 Chunksize
WE will first connect to MySQL database by using SQLAlchemy connection engine. This connection string we will use in our further query execution.
Inside the SQLAlchemy connection string you can enter your MySQL login details like userid , password , host name , database name.
After connecting we will create the query to retrieve the rows.
We will use pandas sql_read() method to execute query by using SQL and connection string.
import pandas as pd
from sqlalchemy import create_engine
my_conn=create_engine("mysql+mysqldb://userid:password@localhost/my_db")
query="SELECT * FROM student WHERE class='Five'"
my_data=pd.read_sql(query,my_conn)
print(my_data)
Using params
We can pass parameters separately by using params query. This is to prevent injection attack.
query="SELECT * FROM student WHERE class=%s"
my_data=pd.read_sql(query,my_conn,params=('Five',))
Using index_col
We can specify which column of the mysql database table can be used as index column in our created DataFrame.
my_data=pd.read_sql(query,my_conn,index_col='id')
chunksize
By using chunksize option we can specify number of records to retrieve as we get one iterator as output.
my_data=pd.read_sql(query,my_conn,chunksize=3)
print(next(my_data))
print("----End of first set---")
print(next(my_data))
columns
We can specify the columns required from the table ( not query ) by suing a list or by tuple.
my_data=pd.read_sql('student',my_conn,columns=['id','name'])
На этой странице сайта вы можете посмотреть видео онлайн read_sql to create Pandas DataFrame by using query from MySQL database table with options. длительностью часов минут секунд в хорошем качестве, которое загрузил пользователь plus2net 04 Март 2021, поделитесь ссылкой с друзьями и знакомыми, на youtube это видео уже посмотрели 2,965 раз и оно понравилось 29 зрителям. Приятного просмотра!
