小小封收录于朝夕不倦

2020-10-19 约 400 字预计阅读 1 分钟

https://image.hikoutei.cn/blog/9O92xq.jpg!md

警告

本文最后更新于 2020-10-19，文中内容可能已过时。

当使用pymysql去数据库查询字段类型为ipv4时，会出现转换成int()失败，看看怎么解决吧！

问题

当使用pymysql去数据库查询字段类型为ipv4时，会出现转换成int()失败，会出现以下错误

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/opt/env/ansible/lib64/python3.6/site-packages/pymysql/cursors.py", line 163, in execute
    result = self._query(query)
  File "/opt/env/ansible/lib64/python3.6/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/opt/env/ansible/lib64/python3.6/site-packages/pymysql/connections.py", line 505, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/opt/env/ansible/lib64/python3.6/site-packages/pymysql/connections.py", line 724, in _read_query_result
    result.read()
  File "/opt/env/ansible/lib64/python3.6/site-packages/pymysql/connections.py", line 1076, in read
    self._read_result_packet(first_packet)
  File "/opt/env/ansible/lib64/python3.6/site-packages/pymysql/connections.py", line 1146, in _read_result_packet
    self._read_rowdata_packet()
  File "/opt/env/ansible/lib64/python3.6/site-packages/pymysql/connections.py", line 1184, in _read_rowdata_packet
    rows.append(self._read_row_from_packet(packet))
  File "/opt/env/ansible/lib64/python3.6/site-packages/pymysql/connections.py", line 1203, in _read_row_from_packet
    data = converter(data)
ValueError: invalid literal for int() with base 10: '10.2.17.34'

解决方案

在SQL语句中使用cast方法将类型转换为字符串，然后使用pymysql去查询

1
2


select cast('127.0.0.1' as varchar) from error_log;
# 2130706433

在python中使用模块来进行解析

1
2
3
4
5
6
7
8


# 导入相关模块包
import socket
import struct
# 将IP从整型转为字符串
socket.inet_ntoa(struct.pack("!I",2130706433))

# 将IP从字符串转为整型
int(socket.inet_aton('127.0.0.1').hex(),16)

ipaddress

1
2


int(ipaddress.IPv4Address("192.168.0.1"))
str(ipaddress.IPv4Address(3232235521))