Into the pit

Today, when inserting data into mysql, I accidentally used format and found a small pit
Let's look at a piece of code:
My data:

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

Database:

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

Code block:

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)
                //Use hereformatReport errors
                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()

Program error, format mismatch:

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

Exit pit

Format mismatch, print the following first:

(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) 

The reason is that format directly adds the string column ﹣ 3 to the string str. when the outermost quotation mark is removed, the value of the third bracket is the data that should be date type, and is inserted by string. But now it's not a string (no quotes), so it doesn't match and the insert fails. Modify the above code, that is, quote the third brace in string STR:

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

Result:

(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') 

The previous methods were:

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

It runs normally, indicating that mysqldb automatically helps us in the process of quotation marks. If format is used, we need to do it by ourselves.
Complete code block for pit exit:

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)#Just add a quotation mark
                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()

Database after insertion:

mysql> select *from data_3;
+----+---------------+---------------+---------------------+
| id | 4Cylinder vibration     | 3Cylinder vibration     | 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 |
+----+---------------+---------------+---------------------+

summary

• When there is an error in writing mysql statement, you can try to print it and view the original sql statement, which is easier to find out the problem.
• We need to continue to work hard