Background

An error is reported when the shell script runs the python write data script task:

  File "./xxx.py", line 59, in xxx
    cur.execute(insert_sql(col_string, str(data_list)))
psycopg2.ProgrammingError: column "it’s adj for sb and it's adj of sb" does not exist
LINE 1: ...1', 'student', 16, '八年级下册', 20211028, 50347, "it’s adj ...

reason

After print output, it is found that this number is written with an error:

the difference between it’s adj for sb & it's adj of sb 

It can be seen that it is actually a mixture of ' and ' (as an obsessive-compulsive disorder patient who writes in accordance with internet writing norms, I really can’t stand this mixture).

Then the related problem is found on stackoverflow: insert text with single quotes in PostgreSQL. However, in my opinion, its solution still does not meet my requirements. After all, the data written to PG cannot be forcibly converted from a single ' to ' ' because of an error. Therefore, I replaced ' with ' , found that the execution was still an error, and realized that the key problem was not here.

Then I found the reason in this blog. According to the official website of wiki.postgresql:

PostgreSQL uses only single quotes for this (i.e. WHERE name = 'John'). Double quotes are used to quote system identifiers; field names, table names, etc. (i.e. WHERE "last name" = 'Smith').
MySQL uses ` (accent mark or backtick) to quote system identifiers, which is decidedly non-standard.

Because the complete format of the data written above is:

insert into xxx (field1, field2, field3) values ('student', "The difference between it's adj for sb and it's adj of sb", 'Concept class')

It is found that only field 2 is ", while the double quotation marks " of PG represent the system identifier and cannot be written into PG at all. In fact, I guess it is because there is ' inside the string, resulting in the external quotation marks becoming ".

So I made character replacement in Python code:

str.replace("\"", "\'").replace("\'s ", "’s ")

First replace the external " with ', and then replace ' with '. However, it should be noted that 's may match ' student ', so I matched one more space and replaced 's empty with 's empty .