Constructing SQL Queries With Python

The ability to quickly and easily write SQL queries is a crucial part of database interaction in Python development. Creating intricate and comprehensible SQL queries is made easier with Pypika, a robust and user-friendly Python package. In this blog article, we’ll go over the fundamentals of Pypika, examine a few typical use cases, and evaluate its features using concrete examples.

Introduction to Pypika

Pypika is a Python library designed to generate SQL queries dynamically. Its primary objective is to simplify the process of building complex queries without sacrificing readability. It provides a fluent API that allows users to create SQL queries in a Pythonic manner, using method chaining and expressive syntax.

Installation

To get started, install Pypika using pip:

pip install pypika

Basic Query Construction

Let’s start with a simple example to showcase how Pypika can be used to create basic SQL queries:

1. Select All Columns from a Table:

from pypika import Query, Table

query = Query.from_('users').select('*')
print(query.get_sql())

## Output
>> SELECT * FROM "users";

2. Select Specific Columns:

from pypika import Query, Table

query = Query.from_('users').select('id', 'name', 'age', 'salary')

# Output
print(query.get_sql())

>> SELECT "id","name","age","salary" FROM "users"

3. Insert Query

from pypika import Query, Table

# Define a table
users = Table('users')

# Construct an INSERT query
query = Query.into(users).columns('name', 'age','salary').insert('Alice', 30, 10000)

# Output the generated query
print(query.get_sql())

>> INSERT INTO "users" ("name","age","salary") VALUES ('Alice',30,10000)

4. Updates Queries

from pypika import Query, Table

# Define a table
users = Table('users')

# Construct an UPDATE query
query = Query.update(users).set(users.age, 32).where(users.name == 'Alice')

# Output the generated query
print(query.get_sql())

>> UPDATE "users" SET "age"=32 WHERE "name"='Alice'

5. Delete Query

from pypika import Query, Table

# Define a table
users = Table('users')

# Construct a DELETE query
query = Query.from_(users).where(users.age < 30).delete()

# Output the generated query
print(query.get_sql())

>> DELETE FROM "users" WHERE "age"<30

6. Subqueries

from pypika import Query, Table

# Define tables
orders = Table(‘orders’)
customers = Table(‘customers’)

# Construct a subquery
subquery = Query.from_(orders).select(‘SUM(amount)’).where(orders.customer_id == customers.id)

# Main query using the subquery
query = Query.from_(customers).select(customers.name, subquery).where(subquery > 1000)

# Output the generated query
print(query.get_sql())

>> SELECT "name",(SELECT "orders"."SUM(amount)" FROM "orders" WHERE "orders"."customer_id"="customers"."id") FROM "customers" WHERE (SELECT "orders"."SUM(amount)" FROM "orders" WHERE "orders"."customer_id"="customers"."id")>1000

7. Functions and Aliasing

from pypika import Query, Table, functions as fn

# Define tables
products = Table(‘products’)

# Construct a query with functions and aliasing
query = Query.from_(products).select(fn.Count(‘*’).as_(‘total_products’))

# Output the generated query
print(query.get_sql())

>> SELECT COUNT(*) "total_products" FROM "products"

8. Case queries

from pypika import Query, Table, Case

# Define a table
students = Table('students')

# Construct a query with a CASE statement
query = Query.from_(students).select(
    students.name,
    Case()
    .when(students.score > 90, 'A')
    .when(students.score > 80, 'B')
    .else_('C').as_('grade'))

print(query.get_sql())

>> SELECT "name",CASE WHEN "score">90 THEN 'A' WHEN "score">80 THEN 'B' ELSE 'C' END "grade" FROM "students"

9. Ordering and Limiting

from pypika import Query, Table

# Define a table
products = Table('products')

# Construct a query with ordering and limiting
query = Query.from_(products).select('name', 'price').orderby('price').limit(10)

# Output the generated query
print(query.get_sql())


>> SELECT "name","price" FROM "products" ORDER BY "price" LIMIT 10

10. Union Queries

from pypika import Query, Table

# Define tables
table1 = Table('table1')
table2 = Table('table2')

# Construct a query with UNION
query = Query.from_(table1).select('*').union(Query.from_(table2).select('*'))

# Output the generated query
print(query.get_sql())


>> (SELECT * FROM "table1") UNION (SELECT * FROM "table2")

For more advanced queries check out the official documentation of Pypika. And I am also going to write advance for that star the GitHub repository.

Reference:

https://pypika.readthedocs.io/en/latest/#

Thank you for reading. If you find something wrong or better ways to do it, let me know in the comments below.

If you like the post, hit the 👏 button below so that others may find it useful. You can follow me on GitHub and connect with me on LinkedIn.

PlainEnglish.io 🚀

Thank you for being a part of the In Plain English community! Before you go:

• Be sure to clap and follow the writer️
• Learn how you can also write for In Plain English️
• Follow us: X | LinkedIn | YouTube | Discord | Newsletter
• Visit our other platforms: Stackademic | CoFeed | Venture