python mysqldb模块_python模块:MySQLdb模块

1.系统必须安装MySQL-python软件，否则python没有连接的模块(在Linux系统)

[root@node1 python]# yum install MySQL-python

2.安装mysql数据库

[root@node1 python]# yum install mysql-server mysql

[root@node1 python]# /etc/init.d/mysqld restart

Stopping              mysqld:           [  OK  ]

Starting           mysqld:           [  OK  ]

[root@node1 python]#

3.用python创建数据库和表,并插入数据

[root@node1 python]# vim mysql1.py

#!/bin/env python

#!-*- coding:UTF-8 -*-

import MySQLdb,exceptions

try:

conn=MySQLdb.connect(host='localhost',user='root',passwd='system',port=3306)   #连接数据库

cur=conn.cursor()      #打开数据库

cur.execute('create database if not exists tong')      #创建数据库

cur.execute('use tong')

cur.execute('create table if not exists t (a int ,b int)')    #创建表

cur.execute('insert into tii values(1,2),(3,4)')      #插入数据

cur.execute('commit')     #提交事物

cur.close()

conn.close()

except MySQLdb.Error,e:

print "MySQL Error %d: %s" %(e.args[0],e.args[1])

[root@node1 python]# ./mysql1.py

[root@node1 python]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 282

Server version: 5.6.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from tong.t;          #查询数据

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

| a    | b    |

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

|    1 |    2 |

|    3 |    4 |

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

2 rows in set (0.00 sec)

mysql>

4.利用python读取数据(和上面数据有重复的)

#!/bin/env python

#!-*- coding:UTF-8 -*-

import MySQLdb,exceptions

try:

conn=MySQLdb.connect(host='localhost',user='root',passwd='system',port=3306)

cur=conn.cursor()

cur.execute('create database if not exists tong')

cur.execute('use tong')

cur.execute('create table if not exists t (a int ,b int)')

cur.execute('insert into t values(1,2),(3,4)')

cur.execute('commit')

cur.close()

conn.close()

except MySQLdb.Error,e:

print "MySQL Error %d: %s" %(e.args[0],e.args[1])

try:

conn=MySQLdb.connect(host='localhost',user='root',passwd='system',db='tong',port=3306)

cur=conn.cursor()

rows=cur.execute('select * from t')

print "there has %s rows record" %rows

result=cur.fetchone()            #获取下一个查询结果集

print result

print "A字段: %s B字段：%s" %result

results1=cur.fetchmany(2)       #获取指定的行数

for i,j in results1:

print "字段A的值: %s  字段B的值:%s" %(i,j)

results2=cur.fetchall()         #获取全部行数

print "字段A所有值       字段B所有值"

for i,j in results2:

print "%-20s %-20s" %(i,j)

except MySQLdb.Error,e:

print "MySQL Error %d: %s" %(e.args[0],e.args[1])

[root@node1 python]# chmod 755 mysql1.py

[root@node1 python]# ./mysql1.py

查询t表中所有行:10

(1L, 2L)

A字段: 1 B字段：2

获取两行数据: 3  4

获取两行数据: 1  2

获取所有行       藜取所有行

3                    4

1                    2

3                    4

1                    2

3                    4

1                    2

3                    4

[root@node1 python]#