JIYIK CN >

SQL stands for Structured Query Language; it is a well-known language for interacting with relational databases. There are many ways to run SQL queries in Python.

pandasqlRun SQL queries in Python using

This package has a method similar to Rthat in . It provides a more familiar way to perform CRUD operations on a DataFrame.sqldfsqldfpandasql

Before we use pandasql, we must first install it using the following command.

#Python 3.x
pip install -U pandasql

We will pandasqlimport sqldfthe method from the module to run the query. We will then call the method with two parameters sqldf.

The first argument is a SQL query in string format. The second argument is a set of session/environment variables ( globals()or locals()).

In the following code, first, we Student.csvupload the data to the current working directory of the notebook. Then we read the student records Jupyterusing a traditional SQL query on the DataFrame ; it will display all the records in the DataFrame.Select

# Python 3.x
import pandas as pd
from pandasql import sqldf


def mysql(q):
    return sqldf(q, globals())


df = pd.read_csv("Student.csv")
mysql("SELECT * FROM df")

Output:

We have used the clause in the SQL query WHEREto display only the selected records that meet the given conditions in the following code.

# Python 3.x
import pandas as pd
from pandasql import sqldf


def mysql(q):
    return sqldf(q, globals())


df = pd.read_csv("Student.csv")
mysql("SELECT * FROM df WHERE Department = 'SE'")

Output:

DuckDBRun SQL queries in Python using

DuckDBIs a Python API and a database management system that uses SQL queries to interact with the database.

To use DuckDB, we should first install it using the following command.

#Python 3.x
pip install duckdb

In the following code, we import duckdbthe and Pandas packages, read the CSV file and run the query by using duckdbthe call query()method. We pass the query (as a parameter) to query()the method.

The code returns the result as a DataFrame. We can write any SQL query of our choice against the DataFrame.

# Python 3.x
import pandas as pd
import duckdb

df = pd.read_csv("Student.csv")
duckdb.query("SELECT * FROM df").df()

Output:

FugueRun SQL queries in Python using

FugueIt is a unified interface for distributed computing that allows users to run Python, Pandas, and SQL code on Spark and Dask without rewriting it.

We must install it before we can use it using the following command fugue.

#Python 3.x
pip install fugue[sql]

We have imported the Pandas and packages in the following code fugueand loaded the DataFrame with the CSV file. We then passed our SQL query to fsql()the method and called run()the method with it.

# Python 3.x
import pandas as pd
from fugue_sql import fsql

df = pd.read_csv("Student.csv")
query = "SELECT * FROM df PRINT"
fsql(query).run()

Output: