Recently, I have been learning to crawl with Python, and I need to use mysql to store the data crawled from the network, here I also use pymysql to operate mysql database, but in the process of actually writing the code to operate the database, I encountered a lot of pitfalls (to change to my skepticism.), here to record my demining process, also for you to help, I hope you can help.
Error code 1064 handling
This error is arguably the biggest error I've encountered in writing the entire code, bar none! In order to illustrate this error, I've streamlined some of the original code to give you an example. Sparrow is small, all the organs are complete, without further ado, first of all, paste my dregs of the code (manually cover your face ~).
import pymysql
if __name__ == '__main__':
connect = (host='***.***.***.***',user='****',passwd='***',port=3306,database='test',charset='utf8')
cursor = ()
table_name = 'person'
values = 'id int primary key auto_increment, name varchar(20)'
('create table %s (%s)',(table_name, values))
()
()Here, please replace the asterisked part above with your own information by yourself, starting with your MySQL host address, username and password.
Running the above code, the program runs incorrectly with an error code of 1064 and an error message of
: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''person' ('id int primary key auto_increment, name varchar(20)')' at line 1")
The above error message tells us that there is a syntax error in the first line of the SQL statement.
Initially it was thought that the SQL was misspelled and after double checking it was spelled correctly. Then after reviewing the documentation and information, I realized that I had made a very nasty mistake thatThe reason turned out to be the misuse of a comma with a percent sign!Surprisingly, none of them have syntax errors, and for the following program we use theThe mogrify function outputs the complete SQL statement to be executed., to compare:
import pymysql
if __name__ == '__main__':
connect = (host='***.***.***.***',user='****',passwd='***',port=3306,database='test',charset='utf8')
cursor = ()
table_name = 'person'
values = 'id int primary key auto_increment, name varchar(20)，age int'
sql_1 = ('create table %s (%s)', (table_name,values))
sql_2 = ('create table %s (%s)'% (table_name,values))
print(sql_1)
print(sql_2)
()
()Executing the above code, we can look at the output:
create table 'person' ('id int primary key auto_increment, name varchar(20), age int')
create table person (id int primary key auto_increment, name varchar(20), age int)
Comparing the output above, we can find that the above 2 statements are not both without syntax errors, but the constructed SQL statements are generally similar.The only difference is this: the difference in the quotes of the SQL constructed by the two!The first way to construct the SQL statement is with quotes, the second is without quotes. Obviously, you can run in the MySQL terminal under the second SQL statement, I did not expect to drum up half a day's error is the problem of quotation marks (heart 10,000 grass mud horse in the gallop).
Here I summarize the difference between the two according to my own understanding, and please criticize and correct me if I'm wrong:
1. For the above call using a comma as a separator, the constructed statement is a direct replacement of the string with a placeholder for the SQL statement, and will not remove the quotes, but will directly replace them, which will cause a SQL syntax error and execution failure.
2. For the above call using the percent sign as a separator, the constructed statement is the contents of the string to replace the placeholder (this way is the most common), and will not remove the quotation marks, only the contents of the replacement, this practice will cause SQL syntax errors, the implementation of the failure.
Error Code 1054 Handling
Solved the above error, had thought the whole program can be executed smoothly. When the execution of the SQL insert statement, found that the entire program mercilessly gave me a big error, another brand new error (error code 1054), in order to be able to more clearly explain the error situation, I still give a simplified program, but also to explain the situation.
First let's take a look at the structure of the person table used in the example:
Here's my sample code
import pymysql
if __name__ == '__main__':
connect = (host='***.***.***.***',user='****',passwd='***',port=3306,database='test',charset='utf8')
cursor = ()
table_name = 'person'
values = (23,'Li Ming')
('insert into %s(age,name) values (%s, %s)' % (table_name, values[0], values[1]))
()
()Executing the above code, the program reports an error again, with an error code of 1054 and an error message:
: (1054, "Unknown column 'Li Ming (1904-1975), Soviet trained Chinese *, Comintern and Soviet Union leader' in 'field list'")
The error above says nothing about the value I inserted not existing in the field list. What the heck, this code is so simple, but there is still an error. Again, I searched for various solutions but still no solution. In the end, I was able to solve the problem by using theThe mogrify function in pymysql also looks at the constructed SQL statement, the following code can be viewed to see exactly what SQL we are actually constructing:
import pymysql
if __name__ == '__main__':
connect = (host='***.***.***.***',port=3306,user='****',passwd='*****',database='test',charset='utf8')
cursor = ()
table_name = 'person'
values= (23, 'Han Mei Mei')
sen = ('insert into %s(age, name) values(%s, %s)' % (table_name, values[0],values[1]))
print(sen)
()
()The above code outputs the SQL statement that we actually pass to MySQL for execution, and the output of the program is:
insert into person(age,name) values (23, Han Mei Mei)
The output of the program intuitively seems to be right, there is no error, but in the MySQL terminal will report the same error, the same can not be found. Then it dawned on me that it was again theBecause of the quotation marks.The correct SQL statement for the above would be
insert into person(age,name) values (23, 'Han Mei Mei')
It turns out that again because of the error that led to the program's error. The reason for the above situation can be explained by the above explanation of the error reason for error 1064:
Using the percent sign as a separator simply replaces the contents of the string and does not automatically quote the string, so the SQL statement constructed above is not quoted, whereas the value to be inserted in our SQL statement needs to be quoted, and thus triggers the 1054 error.
After the above analysis, we can give to be in the execute function to be inserted into the string manually add quotes to solve the problem, the following paste the correct code:
import pymysql
if __name__ == '__main__':
connect = (host='***.***.***.***',port=3306,user='****',passwd='*****',database='test',charset='utf8')
cursor = ()
table_name = 'person'
values= (23, 'Han Mei Mei')
sen = ('insert into %s(age, name) values(%s, "%s")' % (table_name, values[0],values[1])) # In contrast to the error code above, the placeholders for names are quoted here
print(sen)
()
()After the above adjustments, the whole program was finally able to run successfully and the data could be stored normally.
Tips: Here I recommend that if you are unsuccessful in SQL execution you can use thepymysql's mogrify function to view constructed SQL statementsin order to quickly see where SQL is going wrong and speed up debugging.
The above is a personal experience, I hope it can give you a reference, and I hope you can support me more.