python串連mysql之pymysql模組

標籤：

以下demo均以python2中的mysqldb模組

一、插入資料

?

123456789101112131415

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 MySQLdbconn = 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批量插入資料

批量插入資料

二、刪除資料

?

1234567891011121314

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

三、修改資料

?

12345678910111213

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

四、查資料

?

1234567891011121314151617181920212223242526272829303132333435363738394041424344

# ############################## 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 reCountprint nRetfor i in nRet:    print i[0],i[1]

　　　　

　　

python串連mysql之pymysql模組