1.1 MySQLdb安装与简介
1、MySQLdb 模块的安装（python3中目前这个模块还不可用）参考博客
1. linux：
yum install MySQL-python
2. window：
http://files.cnblogs.com/files/wupeiqi/py-mysql-win.zip
1.2 MySQLdb基本使用
1、原生SQL语句使用回顾
create table students
(
id int not null auto_increment primary key,
name char(8) not null,
sex char(4) not null,
age tinyint unsigned not null,
tel char(13) null default "-"
);
1、创建表
insert into students(name,sex,age,tel) values('alex','man',18,'')
delete from students where id =2;
update students set name = 'sb' where id =1;
select * from students
2、原生SQL语句基本操作
2、插入数据
import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='',db='mydb')
cur = conn.cursor()
reCount = cur.execute('insert into UserInfo(Name,Address) values(%s,%s)',('alex','usa'))
# reCount = cur.execute('insert into UserInfo(Name,Address) values(%(id)s, %(name)s)',{'id':12345,'name':'wupeiqi'})
conn.commit()
cur.close()
conn.close()
print reCount
1、插入单条数据
import MySQLdb conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='',db='mydb') cur = conn.cursor() li =[
('alex','usa'),
('sb','usa'),
]
reCount = cur.executemany('insert into UserInfo(Name,Address) values(%s,%s)',li) conn.commit()
cur.close()
conn.close() print reCount
2、批量插入
3、删除数据
import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='',db='mydb')
cur = conn.cursor()
reCount = cur.execute('delete from UserInfo')
conn.commit()
cur.close()
conn.close()
print reCount
删除数据
4、修改数据
import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='',db='mydb')
cur = conn.cursor()
reCount = cur.execute('update UserInfo set Name = %s',('alin',))
conn.commit()
cur.close()
conn.close()
print reCount
修改数据
5、查询数据
# ############################## fetchone/fetchmany(num) ############################## import MySQLdb conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='',db='mydb')
cur = conn.cursor() reCount = cur.execute('select * from UserInfo') print cur.fetchone()
print cur.fetchone()
cur.scroll(-1,mode='relative')
print cur.fetchone()
print cur.fetchone()
cur.scroll(0,mode='absolute')
print cur.fetchone()
print cur.fetchone() cur.close()
conn.close() print reCount # ############################## fetchall ############################## import MySQLdb conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='',db='mydb')
#cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
cur = conn.cursor() reCount = cur.execute('select Name,Address from UserInfo') nRet = cur.fetchall() cur.close()
conn.close() print reCount
print nRet
for i in nRet:
print i[0],i[1]
查询数据