python的mysql模块_MySQL-python模块

1. Python 操作 Mysql 模块的安装

linux:

pip install MySQL-python 或 yum -y install MySQL-python

windows: exe安装包

http://pan.baidu.com/s/1c2ugfvE

2. SQL基本使用

2.1 数据库操作

show databases;

use [databasename];

create database [databasename] charset utf8;

2.2 数据表操作

create database py CHARSET('utf8')usepycreate tablestudents (

idint not null auto_increment primary key,

namechar(32) not null,

sexchar(12) not null,

agetinyint unsigned not null,

telchar(13) null default "-",

nalchar(64)

);

2.3 数据操作

insert into students(name,sex,age,tel,nal) values('alex','man',18,'151515151','CN')delete from students where id =2;update students set name = 'sb' where id =1;select * from students

2.4 其他

主键

外键

左右连接

3. Python MySQL API

3.1 插入数据

#!/use/bin/env python#-*- coding:utf-8 -*-

importMySQLdb

conn= MySQLdb.connect('py', user='root', passwd='py123', db='py')

cur=conn.cursor()info = [('go1','man',30,'13900000000', 'US'),('go2','man',28,'13900000001', 'HK'),]

info1= ('mihui','man',30,'13900000000', 'US')

info2= ['YeGangchan','man',28,'13900000001', 'HK']#reCount = cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)', info1)

reCount = cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)', info2)

reCount2= cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)', ('rain','female',18,'123000000000','NK'))

# 插入多条

reCount3 = cur.executemany('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)', info)

conn.commit()

cur.close()

conn.close()print(reCount)

注意：cur.lastrowid

3.2 删除数据

#!/use/bin/env python#-*- coding:utf-8 -*-

importMySQLdb

conn= MySQLdb.connect(host='py', user='root', passwd='py123', db='py')

cur=conn.cursor()

reCount= cur.execute('delete from students where name=%s',('syl'))

conn.commit()

cur.close()

conn.close()print(reCount)

3.3 修改数据

#!/use/bin/env python#-*- coding:utf-8 -*-

importMySQLdb

conn= MySQLdb.connect(host='py', user='root', passwd='py123', db='py')

cur=conn.cursor()

reCount= cur.execute('update students set name=%s where name=%s',('Laonanhai', 'Oldboy'))

conn.commit()

cur.close()

conn.close()print(reCount)

3.4 查询数据

#!/use/bin/env python#-*- coding:utf-8 -*-

importMySQLdb

conn= MySQLdb.connect('py', user='root', passwd='py123', db='py')

cur=conn.cursor()

reCount= cur.execute('select * from students')## fetchone()

print(cur.fetchone())print(cur.fetchone())print(cur.fetchone())

cur.scroll(-1, mode='relative') #游标相对上移一个

print(cur.fetchone())print(cur.fetchone())print('##------')

cur.scroll(0, mode='absolute') #游标移动到绝对位置0#print(cur.fetchone())#print(cur.fetchone())

print('##------ fetchmany()')#print(cur.fetchmany())

print(cur.fetchmany(4))

cur.close()

conn.close()print(reCount)

#!/use/bin/env python#-*- coding:utf-8 -*-

importMySQLdb

conn= MySQLdb.connect(host='py', user='root', passwd='py123', db='py')

cur=conn.cursor()#cur = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)

reCount= cur.execute('select * from students')

nRes=cur.fetchall()

cur.close()

conn.close()print(reCount)print(nRes)for i innRes:print(i[0],i[1])

3.5 事务回滚

students表创建语句：

CREATE TABLE `students` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` char(32) NOT NULL,

`sex` char(12) DEFAULT NULL,

`age` tinyint(3) unsigned NOT NULL,

`tel` char(13) DEFAULT '_',

`nal` char(64) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

如：select * from students现在的结果如下(id自增)

现在的最后一条数据的id为6

插入两条数据在commit()前行rollback()

#!/use/bin/env python#-*- coding:utf-8 -*-

importMySQLdb

conn= MySQLdb.connect(host='py', user='root', passwd='py123', db='py')

cur=conn.cursor()

reCount= cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)', ('rain2','man',26,'123000000002','NK'))

reCount2= cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)', ('rain3','female',22,'123000000003','NK'))

conn.rollback()

conn.commit()

cur.close()

conn.close()print(reCount)print(reCount2)

现在再来

select * from students现在的结果如下

最后一条数据的仍id为6

做一次真正数据插入：

#!/use/bin/env python#-*- coding:utf-8 -*-

importMySQLdb

conn= MySQLdb.connect(host='py', user='root', passwd='py123', db='py')

cur=conn.cursor()

reCount= cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)', ('rain2','man',26,'123000000002','NK'))

reCount2= cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)', ('rain3','female',22,'123000000003','NK'))#conn.rollback()

conn.commit()

cur.close()

conn.close()print(reCount)print(reCount2)

select * from students现在的结果如下

结果id跳过了7,8

id AUTO_INCREMENT信息保存在内存中，rollback时不回滚AUTO_INCREMENT信息