python2.7连接mysql_python 2.7 操作mysql数据库 实例

create table msg(

id int primary key auto_increment,

title varchar(20),

name varchar(60),

content varchar(1000)

)charset utf8;

insert into msg (title,name,content) values

('test01','zz01','test content01'),

('test02','zz02','test content02'),

('test03','zz03','test content03'),

('test04','zz04','test content04'),

('test05','zz05','test content05');

update msg set content='test05' where name='zz05';

select * from msg;

delete from msg where name='zz05';

select * from msg;

import MySQLdb

conn=MySQLdb.connect(host='127.0.0.1',user='root',passwd='123456',db='mydb',port=3306,charset='utf8')

cur=conn.cursor()

cur.execute("insert into msg (title,name,content) values ('python','zz','test mysql insert')")

conn.commit()

sql='insert into msg(title,name,content) values (%s,%s,%s)'

cur.executemany(sql,[\

('test05','zz05','test content05'),\

('test06','zz06','test content06'),\

])

conn.commit()

create table user(

id int primary key auto_increment,

name varchar(10),

gender varchar(10)

)charset utf8;

import random

sql="insert into user (name,gender) values"

for i in range(100):

sql+="('user"+str(i)+"',"+str(random.randint(0,1))+"),"

print sql

sql=sql[:-1]

print sql

cur.execute(sql)

100L

conn.commit()

cur.execute("select * from user")

100L

cur.fetchall()

cur.fetchall()

cur.scroll(1,mode='absolute')

cur.fetchmany(1)

2L user1

cur.scroll(1,mode='relative')

cur.fetchmany(1)

user3

cur.scroll(0,mode='absolute')

row=cur.fetchone()

while row:

print row[2]

row=cur.fetchone()

try:

cur.execute("drop table if exits user")

conn.commit()

except:

conn.rollback()

cur.close()

conn.close()