Using MySQL in Python

Reason

Recently in the toss of a small thing need to crawl the Web page, and then parse, put the results into the database. Learn that Python has an advantage in this area and choose it. Because I have a server with MySQL installed, naturally use it. In the process of database operation encountered a lot of problems, here record, we encourage each other.

The call to MySQL in Python

After Baidu can be done by MYSQLDB database operation, view the document, understand that Python provides a MySQL when the direct implementation of the C language API. MySQLdb is a higher level of encapsulation, therefore, it is more convenient to use. We can use MySQL, but the better way is to use MYSQLDB

Problems encountered during installation

On this page http://sourceforge.net/projects/mysql-python/can be downloaded to the latest version of MySQLdb, after the decompression to perform the installation, there may be some problems.

• python setup.py build   Perform the installation will prompt no module named Setuptools  
  workaround, install

1	sudo apt-get install python-setuptools

• Execute again, may still be error mysql_config not found
  At this point we need to install Mysqld-dev

1 2	sudo apt-get install libmysqld-dev

• There may be errors again, like this '

  building ' mysql ' extension gcc- Pthread-fno-strict-aliasing-dndebug-g-fwrapv-o2-wall-wstrict-prototypes-fpic-dversion_info= (the "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 err Or:python.h:no such file or directory

Solution Solutions

1

This step is to install some of Python's development-Used header files.

• After basically three, there will be no more problems. However, if MySQL is installed on its own, and the Lib file is not placed under the/usr/local/lib will also error.
  The solution is to either soft-connect the file to this directory, or modify the system's/ETC/LD.SO.CNF file to place the directory where we are in lib. Both methods are available, and then in Ldconfig, let it take effect.
  For example, we use the first methodln -s /usr/local/mysql/lib/mysql/libmysqlclient* /usr/lib

Actual use

2. Introducing the MYSQLDB Library

   Import MySQLdb

4. Connecting to a database

   Conn=mysqldb.connect (host= "localhost", user= "root", passwd= "sa", db= "MyTable", charset= "UTF8")
   The Connect method provided is used to establish a connection to the database, receive several parameters, and return the connection object.

6. Executing statements and fetching results

   Cursor=conn.cursor () N=cursor.execute (Sql,param)
   First, we use the connection object to get a cursor object, and then we use the method provided by the cursor to do the work. These methods include two main classes: 1. Execute command, 2. Receive return value
   I'll say it in detail later.

8. End, close database connection
   You need to close the pointer object and the connection object separately. They have the same name.

1 2	cursor.close() conn.close()

Common Operations API

Support for transactional operations, standard method commit () Commit
Rollback () rollback
Cursor the method used to execute the command:
Callproc (self, procname, args): Used to execute stored procedure, received parameter is stored procedure name and parameter list, return value is the number of rows affected
Execute (Self, query, args): Executes a single SQL statement, receives the parameters for the SQL statement itself and the parameter list used, and returns the number of rows affected
Executemany (self, Query, args): Executes a heads-up SQL statement, but repeats the parameters in the list of parameters, the return value is the number of rows affected Nextset (self): move to the next result set
The cursor is used to receive the return value of the method:
Fetchall (self): receives all the returned result rows.
Fetchmany (self, Size=none): Receives a size bar that returns the result row. If the value of size is greater than the number of result rows returned, the cursor.arraysize data is returned.
Fetchone (self): Returns a result row.
Scroll (self, value, mode= ' relative '): Moves the pointer to a row. If mode= ' relative ', the value bar is moved from the current row, if mode= ' absolute ', Represents the move value bar from the first row of the result set.

In the end, insert a sentence

Computer upgrade to ubuntu14.04 re-installed, the previous blog warehouse has not, back from GitHub pulled back, the middle of a bit of error, I delete files, this article almost no, but fortunately now can see this article. haha ~ ~

Original address: http://blog.isming.me/blog/2014/04/27/use-mysql-in-python/, welcome reprint, reproduced please indicate the source!