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') use py create table students ( id int not null auto_increment primary key, name char(32) not null, sex char(12) not null, age tinyint unsigned not null, tel char(13) null default "-", nal char(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 -*- import MySQLdb 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 -*- import MySQLdb 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 -*- import MySQLdb 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 -*- import MySQLdb 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 -*- import MySQLdb 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 in nRes: 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 -*- import MySQLdb 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 -*- import MySQLdb 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信息