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.
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)
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)
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.
Join our list
Subscribe to our mailing list and get interesting stuff and updates to your email inbox.