How to operate MySQL using Python and FAQs

ArticleDirectory

• Install the MySQL Module
• Connect to database
• Focus on inserting and updating data

 

Install the MySQL Module

Sudo easy_install mysql-Python

Connect to database

 1   #  ! /Usr/bin/ENV Python  2   #  Coding = UTF-8  3   Import  Mysqldb  4 Conn = mysqldb. Connect ('  Localhost  ' , '  Root  ' , '  Rootpass  ' , '  OJ  ' , Charset = '  Utf8  '  )  5 Cur = conn. cursor ()

When connecting to the database, pay attention to the database encoding. Otherwise, a bunch of coding problems will be very worrying ~ It is best to use utf8 encoding in a unified manner.

Execute SQL statements

 
1SQL _content ="Select * from user"2 Cur.exe cute (SQL _content)3Data =Cur. fetchall ()4 ForIInData:5PrintI
 

You can use fetchone to obtain a result. To insert data, perform the commit operation. Otherwise, the data will not be written to the database. It is best to execute all the SQL statements before commit. Practice has proved that this will greatly increase the speed.

Focus on inserting and updating data

There are two ways to insert or update data

1. concatenate SQL statements:

 
SQL _content ="Insert into table (key1, key2, key3) values (% s, % s, % s)"%(Value1,value2,value320.cur.exe cute (SQL _content)
 

2. Use question marks instead.

 
SQL _content ="Insert into table (key1, key2, key3) values (?,?,?)"Cur.exe cute (SQL _content, (value1, value2, value3 ))
 

 

If the value is uncertain, the preceding two statements have the risk of SQL injection.

For example, if the value is an htmlCodeIf quotation marks (but double quotation marks or double quotation marks) exist in the HTML code, if not processed, the SQL statement will be truncated and an insertion error will be thrown.

If the value contains a comma, the previous key does not match the subsequent value, and the data cannot be inserted correctly.

If there is a backslash \ In the value, the data behind the backslash will be escaped in Python. If you do not insert it, it will cause loss of characters or other undesirable results.

Because there are many symbols with special meanings in SQL and python, if they cannot be well processed, there will be many problems. Fortunately, the mysqldb module provides us with an escape_string method, this method can automatically handle the above problems, which is particularly convenient.

In addition, it is not recommended to insert multiple data records at a time, because inserting multiple data records at a time may result in a mismatch between the key and value. Yesterday we had a hard time dealing with this issue for one afternoon. Finally, an SQL statement is split into multiple statements, that is, a value is inserted at a time to solve the problem.