How to operate mysql in python _ MySQL

How to operate mysql in python and common problems in python

BitsCN.com

Install the mysql module

sudo easy_install mysql-python

Connect to database

1 #!/usr/bin/env python2 #coding=utf-83 import MySQLdb4 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

1 sql_content = "select * from user"2 cur.execute(sql_content)3 data = cur.fetchall()4 for i in data:5     print i

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,value3)cur.execute(sql_content)

2. use question marks instead.

sql_content = "insert into table(key1,key2,key3) values (?,?,?)"cur.execute(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 html code and the html code contains quotation marks (but double quotation marks or double quotation marks), if not processed, the SQL statement will be truncated, this will cause an insert error.

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

If the value contains a backslash (/), the data after the backslash (/) is escaped in python. if you do not insert a backslash (/), the characters may be lost or other unexpected results may occur.

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.

BitsCN.com