pymysql中插入数据时format的坑

入坑

今天向mysql插入数据时，无意间使用到了format，发现了个小坑坑
先看一段代码：
我的数据：

72	232	2022/11/01
231	233	2022/01/01
231	234	2022/12/01

数据库：

+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| 4气缸振动     | decimal(10,6)    | NO   |     | NULL    |                |
| 3气缸振动     | decimal(10,6)    | NO   |     | NULL    |                |
| time          | datetime         | NO   |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+

代码块：

from pymysql import connect
def db():
    try:
        conn = connect(host="127.0.0.1",port=3306,user='root',database="mac_test01",password="hbes8235952LY_",charset="utf8")
        cs1 = conn.cursor()
        with open("../circle_data/927.txt",'r',encoding='gbk')as f:
            lines = f.readlines()
            # print(lines)---[xx,xx,xx]
            for line in lines:
                # print(line)
                column_1 = line.strip().split('\t')[0]
                column_2 = line.strip().split('\t')[1]
                column_3 = line.strip().split('\t')[2]
                # print(column_1,"******",column_2)
                此处用format报错
                str = "insert into data_3 values(0,{},{},{}) ".format(column_1,column_2,column_3)
                count = cs1.execute(str)
                print(str)
        conn.commit()
        cs1.close()
        conn.close()
    except Exception as e:
        print(e)

if __name__ == '__main__':
    db()

程序报错，格式不匹配：

(django_py3) [root@itcast run]# python3 test_fomart_db.py 
(1292, "Incorrect datetime value: '183.818181818000000000' for column 'time' at row 1")

出坑

格式不匹配，先打印下：

(django_py3) [root@itcast run]# python3 test_fomart_db.py 
insert into data_3 values(0,72,232,2022/11/01) 
insert into data_3 values(0,231,233,2022/01/01) 
insert into data_3 values(0,231,234,2022/12/01) 

可知原因在于format直接把字符串column_3加入字符串str中，当最外面引号被去掉后，第三个括号的值为本应为date类型数据，通过字符串方式插入。但现在不是字符串（没引号）了，所以不匹配，插入失败了。修改上面代码，即字符串str中给第三个大括号加引号即可：

str = "insert into data_3 values(0,{},{},'{}') ".format(column_1,column_2,column_3)
print(str)

结果：

(django_py3) [root@itcast run]# python3 test_fomart_db.py 
insert into data_3 values(0,72,232,'2022/11/01') 
insert into data_3 values(0,231,233,'2022/01/01') 
insert into data_3 values(0,231,234,'2022/12/01') 

之前用的方法为：

str = "insert into data_3 values (0,%s,%s,%s)"
count = cs1.execute(str,(column_1,column_2,column_3))

运行正常，说明加引号的过程mysqldb自动帮我们做了嘿嘿，而如果用format需要自己动手做。
出坑的完整代码块：

from pymysql import connect
def db():
    try:
        conn = connect(host="127.0.0.1",port=3306,user='root',database="mac_test01",password="hbes8235952LY_",charset="utf8")
        cs1 = conn.cursor()
        with open("../circle_data/927.txt",'r',encoding='gbk')as f:
            lines = f.readlines()
            # print(lines)---[xx,xx,xx]
            for line in lines:
                # print(line)
                column_1 = line.strip().split('\t')[0]
                column_2 = line.strip().split('\t')[1]
                column_3 = line.strip().split('\t')[2]
                # print(column_1,"******",column_2)

                str = "insert into data_3 values(0,{},{},'{}') ".format(column_1,column_2,column_3)#加个引号即可
                print(str)
                count = cs1.execute(str)
                # str = "insert into data_3 values (0,%s,%s,%s)"
                # count = cs1.execute(str,(column_1,column_2,column_3))

        conn.commit()
        cs1.close()
        conn.close()
    except Exception as e:
        print(e)

if __name__ == '__main__':
    db()

插入后数据库：

mysql> select *from data_3;
+----+---------------+---------------+---------------------+
| id | 4气缸振动     | 3气缸振动     | time                |
+----+---------------+---------------+---------------------+
|  1 |     72.000000 |    232.000000 | 2022-11-01 00:00:00 |
|  2 |    231.000000 |    233.000000 | 2022-01-01 00:00:00 |
|  3 |    231.000000 |    234.000000 | 2022-12-01 00:00:00 |
+----+---------------+---------------+---------------------+

总结

• 自己写mysql语句出现错误时，可以尝试着打印下，查看原始sql语句，比较容易查出问题。
• 还要继续努力啊