Use MYSQL in python

The reason is that you need to capture the online page for a small item recently. Then parse. Put the results in the database. If you understand the advantages of Python in this regard, you can choose it. Because I have a server installed with mysql, it is natural to use it. Many problems have been encountered during the database operation. let's record them here. My reasons in python

In the near future, a small item needs to be crawled on the web page. Then parse. Put the results in the database.

If you understand the advantages of Python in this regard, you can choose it.

Because I have a server installed with mysql, it is natural to use it. Many problems have been encountered during the database operation. let's record them here.

Call mysql in python

Baidu can then perform database operations through MySQLdb. View the document and learn that python provides a C language API for mysql directly. MySQLdb is an encapsulation of it on a higher layer, 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, refer.

When you run and install python setup. py build, the system prompts No module named setuptools.

Solution: Install

Sudo apt-get install python-setuptools

If you run the command again, mysql_config not found may still fail.

In this case, we need to install mysqld-dev

Sudo apt-get install libmysqld-dev

An error may occur when you run it again. Similar to 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. A linux-i686-2.7/mysql. o-DBIG_JOINS = 1-fno-strict-aliasing-DUNIV_LINUX In file already ded 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, there will be no other problems after the first three methods.

However, assume that mysql is installed by yourself. In addition, if the lib file is not put below/usr/local/lib, an error is returned.

The solution is to connect the file to this folder, or modify the/etc/ld. so. cnf file of the system and put the folder where lib is located. Both methods can be used. Then in ldconfig, make 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 database

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

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

Run the statement and obtain the result

Cursor = conn. cursor () nw.cursor.exe cute (SQL, param)

First, we use the connection object to obtain a cursor object. Next, we will use the method provided by cursor to work. These methods include: 1. run the command, 2. receive the returned value.

I will discuss it later. I will not go into detail here

End. Close database connection

The pointer object and connection object must be closed separately. they have the same name.

Cursor. close ()

Conn. close ()

Frequently used API operations

Support for transaction operations, standard method commit () commit

Rollback () rollback

Cursor is used to run commands:

Callproc (self, procname, args): used to run a stored procedure. the number of records received is the name of the stored procedure and the number of records list. the returned value is the number of affected rows.

Execute (self, query, args): runs a single SQL statement, receives the number of partitions and the number of partitions used. the returned value is the number of affected rows.

Executemany (self, query, args): runs a single-pick SQL statement, but repeatedly runs the number of partitions in the sequence table. the returned value is the number of affected rows nextset (self): move to the next result set.

Cursor is used to receive the returned value:

Fetchall (self): receives all returned result rows.

Fetchmany (self, size = None): Receives the size of returned results rows. if the value of size is greater than the number of returned results rows, the cursor. arraysize data is returned.

Fetchone (self): returns a result line.

Scroll (self, value, mode = 'relative '): move the pointer to a row. if mode = 'relative ', the value Bar is moved from the current row. if mode = 'absolute', the value Bar is moved from the first row of the result set.

Insert the last sentence

I upgraded my computer to ubuntu14.04 and installed it again. I lost my blog repository and pulled it back from github. An error occurred. I deleted the file. this article almost disappeared. But now I can see this article.