SQLAlchemy Query to Find IP Addresses from an IP Range in Bulk

Working with databases often involves complex queries and data manipulation tasks. When dealing with IP addresses, you might find yourself needing to extract specific addresses from a range for various purposes. In this article, we'll explore how to use SQLAlchemy, a powerful Object-Relational Mapping (ORM) library for Python, to efficiently find IP addresses from an IP range in bulk. This technique can be invaluable in scenarios like network management, cybersecurity, and data analysis.

We assume that the start and end IP ranges are stored in the database as integers.

Here's how you can use SQLAlchemy to efficiently find IP addresses within an IP range in bulk:

from sqlalchemy import and_, or_


ip_addrs = [
    "127.1.1.1",
    "127.1.1.2",
    "127.3.3.3",
    "127.3.1.4"
    ]

filters = []
#ip addresses in integer format
ip_addrs_integer = []
query = session.query(IpTable)
for ip in ip_addrs:
    ip_integer = ip_to_integer(ip)
    filters.append(and_(IpTable.start_integer_ip <= ip_integer, IpTable.end_integer_ip >= ip_integer))
    ip_addrs_integer.append(ip_integer)
ip_results = query.filter(or_(*filters)).limit(50).all()
for ip_integer in ip_addrs_integer:
    for ip_res in ip_results:
        if ip_res.start <= ip_integer and ip_res.end >= ip_integer:
            ip_res.ip = integer_to_ip(ip_integer)
            break
#print ip details obtain from database
for ip_detail in ip_results:
    print("ip =", ip_detail.ip)
    print("isp =", ip_detail.isp)
    print("country =", ip_detail.country)

The above code requires conversion of IP to integer and integer to IP. Learn how to do it here.