python的mysql模块_Python 操作 Mysql 模块

一、Python 操作 Mysql 模块的安装

linux：

yum install MySQL-python

window：

http://files.cnblogs.com/files/wupeiqi/py-mysql-win.zip

二、Python MySQL API

2.1、插入数据

import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',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

2.2、批量插入数据

import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',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

注意：cur.lastrowid

2.3、删除数据

import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')

cur = conn.cursor()

reCount = cur.execute('delete from UserInfo')

conn.commit()

cur.close()

conn.close()

print reCount

2.4、修改数据

import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')

cur = conn.cursor()

reCount = cur.execute('update UserInfo set Name = %s',('alin',))

conn.commit()

cur.close()

conn.close()

print reCount

2.5、查数据

# ############################## fetchone/fetchmany(num) ##############################

import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',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='1234',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]