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读取数据(和上面数据有重复的)

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

    cur=conn.cursor()

    cur.execute('create database if not exists 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')

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

    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)

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

查询t表中所有行:10

(1L, 2L)

A字段: 1 B字段：2

获取两行数据: 3  4

获取两行数据: 1  2

获取所有行       藜取所有行

3                    4                   

1                    2                   

本文转自 z597011036 51CTO博客，原文链接：http://blog.51cto.com/tongcheng/1540018，如需转载请自行联系原作者