1.      要想使Python可以操作MySQL数据库，首先需要安装MySQL-python包，在CentOS上可以使用一下命令来安装

yum install -y MySQL-python

 

2.      下面的程序创建了一个到mysql数据库的连接，然后执行了一个简单的查询，并打印查询结果

#!/usr/bin/python

import MySQLdb

#start connect mysql，先创建连接

conn = MySQLdb.connect(host = "127.0.0.1",user = "root",passwd = "***",db = "mysql")

cursor = conn.cursor()

#查询mysql数据的版本

cursor.execute("select version()")

row = cursor.fetchone()

print "mysql Version:",row[0]

#close connection

cursor.close()

conn.close ()

 

执行结果：

# python a

mysql Version: 5.5.8

 

3. 下面看一个数据库建表和插入操作

#!/usr/bin/python

import MySQLdb

#start connect mysql

conn = MySQLdb.connect(host = "127.0.0.1",user = "root",passwd = "***",db = "mysql")

cursor = conn.cursor()

cursor.execute("""create database test5""")

cursor.execute("""use test5 """)

cursor.execute("""create table test(id int,col1 varchar(40),col2 varchar(40),col3 varchar(40)) """)

cursor.execute("""insert into test(id,col1,col2,col3) values(1,'a','b','c'),(2,'aa','bb','cc'),(3,'aaa','bbb','ccc') """)

conn.commit()

cursor.close()

conn.close ()

 

执行结果：

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

| test1              |

| test2              |

| test3              |

| test4              |

| test5              |

+--------------------+

9 rows in set (0.00 sec)

 

3.      下面再来看看查询，查询通常有两种方式：

一种是使用cursor.fetchall()获取所有查询结果，然后再一行一行的迭代;

另一种每次通过cursor.fetchone()获取一条记录，直到获取的结果为空为止。

看一下下面的例子：

先看cursor.fetchall()的：

#!/usr/bin/python

import MySQLdb

#start connect mysql

conn = MySQLdb.connect(host = "127.0.0.1",user = "root",passwd = "***",db = "mysql")

cursor = conn.cursor()

cursor.execute("use test5")

cursor.execute("select * from test")

rows=cursor.fetchall()

for row in rows: print "%d,%s,%s,%s"% (row[0],row[1],row[2],row[3])

print "Number of rows returned:%d"% cursor.rowcount

cursor.close()

conn.close ()

 

执行结果：

# python c

1,a,b,c

2,aa,bb,cc

3,aaa,bbb,ccc

Number of rows returned:3

 

再看cursor.fetchone()的：

#!/usr/bin/python

import MySQLdb

#start connect mysql

conn = MySQLdb.connect(host = "127.0.0.1",user = "root",passwd = "Exia@OO",db = "mysql")

cursor = conn.cursor()

cursor.execute("use test5")

cursor.execute("select * from test")

while(True):

        row=cursor.fetchone()

        if row == None :

                break

        print "%d,%s,%s,%s"% (row[0],row[1],row[2],row[3])

print "Number of rows returned:%d"% cursor.rowcount

#close connection

cursor.close()

conn.close ()

 

执行结果：

# python d

1,a,b,c

2,aa,bb,cc

3,aaa,bbb,ccc

Number of rows returned:3