pandas read_sql() method implementation with Examples

pandas read_sql() method implementation with Examples

Pandas is a great python package for managing large datasets. You can read excel data, CSV data and manipulate them easily using pandas. All these manipulations are done by the inbuilt method provided by the panda’s library. Pandas read_sql() method is one of them. In this entire tutorial, you will learn how to implement the pandas read_sql() method in python through steps.

Syntax of pandas read_sql() method

The most common syntax for this method is the below.

pandas.read_sql(sql, con, index_col=None)

sql: It is a SQL query you want to perform on the database.

con: Database connection.

index_col : Column(s) to set as index(MultiIndex), default is None.

Steps to implement Pandas read_sql() method

In this entire section, you will learn to implement the read_sql() method. You will learn how to connect and create a table, database, and perform any SQL query on a table inside the database. Just follow the steps for deep understanding.

Step 1: Import all the required libraries

The first step is to import all the necessary libraries. In my example I am using only two packages one is pandas and the other is sqlite3( Python SQL package).  Let’s import them using the import statement.

import panadas as pd
from sqlite3 import connect

Step 2: Create a Database

Python has a local DB package and it is Sqlite3. I am using it for creating a database. For this, You have to use the connect() constructor. Let’s create the database “stocks.db“. Add the following line of code.

conn = connect("stocks.db")

Step 3: Read the data

Now the next step is to read the dataset. Here I am using Meta Company(formerly FACEBOOK) stock data which is time-series data. You can download it from Yahoo Finance. However, you can use your own dataset. In pandas, you can read CSVs files using the pandas.read_csv() method. Let’s read the dataset using it.

data = pd.read_csv("FB.csv")

Step 4: Insert the dataset

After reading the dataset, now its turn to make a table for the dataset and insert all the database values inside the table at once. To do so I will use the dataframe.to_sql() pandas method. Continue with the following line of code.

data.to_sql("meta",conn)

Make sure to run the above line at once only as if you run it again you will get the table is already present in the database error.

Step 5: Implement the pandas read_sql() method

After all the above steps let’s implement the pandas.read_sql() method. For example, I want to output all the columns and rows for the table “FB” from the “stocks.db” database. To do so I have to pass the SQL query and the database connection as the argument. Run the complete code given below.

import pandas as pd
from sqlite3 import connect
conn = connect("stocks.db")
data = pd.read_csv("FB.csv")
data.to_sql("meta",conn)
df = pd.read_sql("SELECT * FROM meta LIMIT 5",conn)
print(df)

Output

Querying the entire FB Table
Querying the entire FB Table

In the same way, you can output the close price of the Facebook stock from the table.

import pandas as pd
from sqlite3 import connect
conn = connect("stocks.db")
data = pd.read_csv("FB.csv")
data.to_sql("meta",conn)
df= pd.read_sql("SELECT Close FROM meta ",conn)
print(df)

Output

Querying the close price of the FB Table
Querying the close price of the FB Table

Conclusion

Pandas read_sql() method allows you to run any SQL queries from the database. In my example I used SQLite but you can use any database framework. These are steps for implementing read_sql() method in python. I hope you have liked this tutorial. If you have any questions then you can contact us for more help.

Source:

sqlite documentation

pandas documentation

 

Join our list

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

Thank you for signup. A Confirmation Email has been sent to your Email Address.

Something went wrong.

 
Thank you For sharing.We appreciate your support. Don't Forget to LIKE and FOLLOW our SITE to keep UPDATED with Data Science Learner