python Using MYSQL in Python

Reason

　Recently, I have been tinkering with a small tool that needs to crawl online pages. Then parse. Put the results into the database.

　 Knowing that Python has advantages in this aspect, I chose it.

Since I have a server with mysql installed on it, I naturally use it. I encountered many problems during the operation of the database. I will record them here for everyone’s mutual encouragement.

　Call of mysql in python

　Baidu will be able to perform database operations through MySQLdb. Check the documentation and learn that python provides a mysql that directly implements the c language API of mysql. MySQLdb is a higher-level encapsulation of it, so it is more convenient to use. We can use mysql, but a better way is to use MySQLdb

Problems encountered during installation

On this page http://sourceforge.net/projects/mysql-python/ you can download the latest version of MySQLdb and unzip it There may be some issues when running the installation later.

　 Running the installation through python setup.py build will prompt No module named setuptools

　 Solution, installation

　 sudo apt-get install python-setuptools

　 If you run it again, the error may still occur mysql_config not found

At this time we You need to install mysqld-dev

　sudo apt-get install libmysqld-dev

　An error may occur if you run it again. Something like this`

　Building 'mysql' extension gcc -pthread -fno-strict-aliasing -DNDEBUG -g -fwrapv -O2 -Wall -Wstrict-prototypes -fPIC -Dversion_info=(1,2,3,'final',0 ) -Dversion=1.2.3 -I/usr/include/mysql -I/usr/include/python2.7 -c mysql.c -o build/temp.linux-i686-2.7/mysql.o -DBIG_JOINS=1 - fno-strict-aliasing -DUNIV_LINUX -DUNIV_LINUX In file included from mysql.c:29:0: pymemcompat.h:10:20: fatal error: Python.h: No such file or directory

　　Solution

　sudo apt- get install python-dev

　This step is to install some development header files for python.

Basically, after the first three methods, there will be no other problems.

　But assume that mysql is installed by yourself. Moreover, if the lib file is not placed below /usr/local/lib, an error will be reported.

　The solution is to soft-link the file to this folder, or change the system's /etc/ld.so.cnf file and put the folder where our lib is located. Both methods work. Then in ldconfig, just let it take effect.

　For example, we use the first method ln -s /usr/local/mysql/lib/mysql/libmysqlclient* /usr/lib

　Actual use

　Introduce the MySQLdb library

　Import MySQLdb

　Connect to the database

　conn= MySQLdb.connect(host="localhost", user="root", passwd="sa", db="mytable", charset="utf8")

　The connect method provided is used to establish a connection with the database and receive several Parameters, return the connection object.

　 Run the statement and get the result

　cursor=conn.cursor() n=cursor.execute(sql,param)

　First, we use the connection object to obtain a cursor object. Next, We will use the methods provided by cursor to work. These methods include two major categories: 1. Run the command, 2. Receive the return value

We will talk about it in detail later. Unknown here

　End. To close the database connection

, you need to close the pointer object and the connection object separately. They have methods with the same name

　 cursor.close()

　 conn.close()

　 Frequently used operation API

　 Support for transaction operations, standards Method commit() Submit

　 Rollback() Rollback

Method used by cursor to run commands:

List, the return value is the number of affected rows

　execute(self, query, args): Run a single sql statement, the parameters received are the sql statement itself and the parameter list used, the return value is the number of affected rows

　executemany(self, query, args): Run a single SQL statement, but repeatedly run the parameters in the parameter list, and the return value is the number of affected rows nextset(self): Move to the next result set

Used by cursor Methods to receive return values:

　fetchall(self): Receive all returned result rows.

　fetchmany(self, size=None): Receive size return result rows. Assume that the value of size is greater than the number of returned result rows. , will return cursor.arraysize pieces of data.

　fetchone(self): Return a result row.

　Scroll(self, value, mode='relative'): Move the pointer to a certain row. Assume mode='relative', It means moving the value bar from the current row. Assuming mode='absolute', it means moving the value bar from the first row of the result set.

　 Insert a sentence at the end

The computer was upgraded to ubuntu14.04 and installed again. The previous The blog repository is gone, and it has been pulled back from github again. Something went wrong. I deleted the file and this article was almost gone. It’s just a good thing that I can read this article now.