MySQLdb Mysql 交互

一、Python 操作 Mysql 模块的安装

 

linux：
    yum install MySQL-python
  
window：
    https://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

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

2.2、删除数据

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.3、修改数据

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.4、查数据

# ############################## 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]