Mikhail Venkov Mikhail Venkov - 3 years ago 224
Python Question

Convert bytearray from a VARBINARY(16) column into an IP address

I need to extract large amount of data from a HP Vertica database and save it to a file. I am using official ODBC driver from Vertica with pyodbc.

This is what I have done so far:

cnxn = pyodbc.connect('DRIVER={Vertica};SERVER=localhost;DATABASE=db;UID=user;PWD=pw')
cnxn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
cnxn.setencoding(str, encoding='utf-8')
cnxn.setencoding(unicode, encoding='utf-8')
cur = cnxn.cursor()
cur.execute("SELECT * FROM schema.table LIMIT 3")


Then I read the data

for row in cur:
print row


Most of the fields are returned just fine - unicode text, numbers or datetimes. However for a field that stores IP Addresses I get the following:

bytearray(b'\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\no\x19\\')


How do I convert it to text?

Any help with be greatly appreciated!

Thanks!

Answer Source

VARBINARY(16) is 128 bits, which is just the right size for an IPv6 address. The sample data decodes to

0000:0000:0000:0000:0000:ffff:0a6f:195c

and the "IPv4-mapped IPv6 addresses" subsection of the Wikipedia article on IPv6 (ref: here) says that such an address is an IPv4 address (32 bits) mapped into an IPv6 format (128 bits).

::ffff:10.111.25.92

We can generate those string representations above from the raw bytearray data using a function like this:

def bytes_to_ip_address(byte_array):
    if byte_array[0:12] == bytearray(b'\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff'):
        return '{0}.{1}.{2}.{3}'.format(byte_array[12], byte_array[13], byte_array[14], byte_array[15])
    else:
        return ':'.join(['{0:02x}{1:02x}'.format(byte_array[i], byte_array[i + 1]) for i in range(0, len(byte_array), 2)])


if __name__ == '__main__':
    # examples
    fld = bytearray(b'\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\no\x19\\')
    print(bytes_to_ip_address(fld))  # 10.111.25.92
    fld = bytearray(b'\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\no\x19\\')
    print(bytes_to_ip_address(fld))  # 0100:0000:0000:0000:0000:ffff:0a6f:195c

Or, with Python3 we can use the ipaddress module:

import ipaddress


def bytes_to_ip_address(byte_array):
    ip6 = ipaddress.IPv6Address(bytes(byte_array))
    ip4 = ip6.ipv4_mapped
    if ip4 == None:
        return str(ip6)
    else:
        return str(ip4)


if __name__ == '__main__':
    # examples
    fld = bytearray(b'\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\no\x19\\')
    print(bytes_to_ip_address(fld))  # 10.111.25.92
    fld = bytearray(b'\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\no\x19\\')
    print(bytes_to_ip_address(fld))  # 100::ffff:a6f:195c
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download