The use of mysqldb in Python

<1>linux version

http://sourceforge.net/projects/mysql-python/download, in the installation is to first install Setuptools, and then in the download file directory, modify Mysite.cfg, Specify the path to the local MySQL mysql-config file

<2>windows version

Online Search for a http://www.technicalbard.com/files/MySQL-python-1.2.2.win32-py2.6.exe

After installation, import MySQLdb will appear deprecationwarning:the sets module is deprecated such a warning, Google's

The reason is that 2.6 does not know sets this module, but has added the set built-in functions. Locate the __init__.py in the MySQLdb folder, comment out the from sets import Immutableset class Dbapiset (immutableset): Add Class Dbapiset (Frozenset): Find converters.py comment out from sets import Baseset, Set. Then modify the set in line 45th and row 129 to set.

Get.

PS: I am downloading the mysql-python-1.2*** version in http://www.lfd.uci.edu/~gohlke/pythonlibs/

Here's how to start the demo:

Python code

1     #-*-coding:utf-8-*-2     #MySQLdb3     ImportTime , MySQLdb4          5     #Connection6Conn=mysqldb.connect (host="localhost", user="Root", passwd="Root", db="Test", charset="UTF8")    7cursor =conn.cursor ()8       9     #Delete a tableTensql ="drop table if exists user"   One cursor.execute (SQL) A        -     #Create -sql ="CREATE table if not exists user (name varchar (primary) key, created Int (ten))"   the cursor.execute (SQL) -        -     #Write -sql ="INSERT INTO User (name,created) VALUES (%s,%s)"      +param = ("AAA", Int (Time.time ())) -n =Cursor.execute (Sql,param) +     Print 'Insert', N A           at     #Write Multiple lines -sql ="INSERT INTO User (name,created) VALUES (%s,%s)"      -param = (("BBB", int (Time.time ())), ("CCC", 33), ("DDD", 44) )   -n =Cursor.executemany (Sql,param) -     Print 'Insertmany', N -        in     #Update -sql ="Update user set name=%s where name= ' aaa '"      toparam = ("zzz")       +n =Cursor.execute (Sql,param) -     Print 'Update', N the           *     #Enquiry $n = Cursor.execute ("SELECT * from user")      Panax Notoginseng      forRowinchCursor.fetchall (): -         PrintRow the          forRinchrow: +             PrintR A           the     #Delete +sql ="Delete from user where name=%s"      -param = ("BBB")       $n =Cursor.execute (Sql,param) $     Print 'Delete', N -        -     #Enquiry then = Cursor.execute ("SELECT * from user")       -     PrintCursor.fetchall ()Wuyi        the cursor.close () -           Wu     #Submit - Conn.commit () About     #Close $Conn.close ()

Output Result:

Insert 1
Insertmany 3
Update 1
(U ' BBB ', 1340790602L)
Bbb
1340790602
(U ' CCC ', 33L)
Ccc
33
(U ' ddd ', 44L)
Ddd
44
(U ' zzz ', 1340790602L)
zzz
1340790602
Delete 1
(U ' CCC ', 33L), (U ' ddd ', 44L), (U ' zzz ', 1340790602L))

Basic use as above, or is very simple, further use has not yet operated, first from the Internet to find some information to put up, for follow-up view

1. Introduction of MYSQLDB Library

Import MySQLdb

2. Establish a connection 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 to the database, receive several parameters, and return the connection object.

The more commonly used parameters include
Host: the database hostname. By default, the local host is used.
User: Database login name. The default is the current user.
PASSWD: The Secret of database landing. Default is empty.
DB: The name of the database to use. No default value.
The TCP port used by the Port:mysql service. The default is 3306.
CharSet: Database encoding.

More information on the parameters can be found here.
Http://mysql-python.sourceforge.net/MySQLdb.html

The connection object is then provided with support for transactional operations, and the standard method
Commit () Commit
Rollback () rollback

3. Execute SQL statements and receive return values
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

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 single SQL statement, but repeats the parameters in the list of parameters, with the returned value being 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.

The following code is a complete example.
#使用sql语句, the parameters to be received here are in the%s placeholder. Note that no matter what type of data you want to insert, the placeholder will always use%s
Sql= "INSERT into cdinfo values (%s,%s,%s,%s,%s)"
#param应该为tuple或者list
Param= (Title,singer,imgurl,url,alpha)
#执行, if successful, the value of n is 1
N=cursor.execute (Sql,param)

#再来执行一个查询的操作
Cursor.execute ("SELECT * from Cdinfo")
#我们使用了fetchall这个方法. In this way, the CDs will be saved as the full result of the query return. Each result is a tuple-type data that consists of a tuple
Cds=cursor.fetchall ()
#因为是tuple, so you can use result sets like this
Print Cds[0][3]
#或者直接显示出来 to see what the result set looks like.
Print CDs

MySQLdb, in fact, and Python built-in sqlite3 are basically the same way to use. MySQLdb by default, query result rows are returned as tuple, which is not convenient to access and must be read as 0,1.

The result is like this:

(U ' CCC ', 33L)
(U ' ddd ', 44L)
(U ' zzz ', 1340790602L)

Before using Sqllite3, you can modify the Row_factory property of the Connection object to use Sqlite3. Row, so that the result set of data row is the dictionary form, can be accessed with the field name, then MYSQLDB is also there is such a method, after searching on the internet found that there are dictcursor in the mysqldb, to do this is very simple, That is, establishing a database connection is passing the Cusorclass parameter, or passing the Cusorclass parameter when you get the cursor object:
Conn=mysqldb.connect (host= "localhost", user= "root", passwd= "root", db= "test", charset= "UTF8", cursorclass= MySQLdb.cursors.DictCursor)
cursor = Conn.cursor ()
Or
Conn=mysqldb.connect (host= "localhost", user= "root", passwd= "root", db= "test", charset= "UTF8")
cursor = conn.cursor (cursorclass=mysqldb.cursors.dictcursor)

The return result is this:

{' Name ': U ' CCC ', ' created ': 33L}
{' Name ': U ' ddd ', ' Created ': 44L}
{' Name ': U ' zzz ', ' Created ': 1340790602L}

#如果需要批量的插入数据, just do it.
Sql= "INSERT into cdinfo values (0,%s,%s,%s,%s,%s)"
#每个值的集合为一个tuple, the entire set of parameters consists of a tuple, or list
Param= ((Title,singer,imgurl,url,alpha), (TITLE2,SINGER2,IMGURL2,URL2,ALPHA2))
#使用executemany方法来批量的插入数据. This is a really cool way!
N=cursor.executemany (Sql,param)

4. Close the database connection
You need to close the pointer object and the connection object separately. They have the same name.
Cursor.close ()
Conn.close ()

Four steps to complete, the basic database operation is this. Here are two useful connections
MYSQLDB User guide: http://mysql-python.sourceforge.net/MySQLdb.html
MySQLdb Document: http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb-module.html

5 encoding (prevents garbled)

Points to note:

1 python file Set encoding utf-8 (file front plus #encoding =utf-8)
2 MySQL Database Charset=utf-8
3 Python connection MySQL is plus parameter Charset=utf8
4 set Python's default encoding to Utf-8 (sys.setdefaultencoding (utf-8)

1     #Encoding=utf-82     ImportSYS3     ImportMySQLdb4         5 Reload (SYS)6Sys.setdefaultencoding ('Utf-8')   7         8Db=mysqldb.connect (user='Root', charset='UTF8')

Note: The configuration file settings for MySQL must also be configured as UTF8

Set the MySQL my.cnf file, set the default character set (usually in/etc/mysql/my.cnf) in the [Client]/[mysqld] section:

[Client]
Default-character-set = UTF8
[Mysqld]
Default-character-set = UTF8

The use of mysqldb in Python